On 21 Mar 2013, at 10:52pm, Tim Streater <t...@clothears.org.uk> wrote:
> I have a column defined as TEXT and I insert utf-8 text into it. In > particular, there are byte sequences that might look as follows: > ... 74 6F 20 C2 A3 32 35 30 ... (in hex) > which represents: > ... to £250 ... > I used Navicat for SQLite Lite to peer at the data as hex and text in the > column, and it looks as I've presented it. Thanks for the detailed report which saves us a lot of time asking questions. Can you use the sqlite shell tool, rather than Navicat, to look at the database file and see the contents ? You can download compiled versions for most platforms from <http://www.sqlite.org/download.html> In particular please use something like SELECT myField,length(myField) FROM myTable to check how long it thinks the field is. > The problem comes when I try to retrieve the data using a PHP script. I want > the data back as bytes because I'm going to encode it as quoted printable, > with decimal byte values greater than 127 being separately encoded. This > would give the above as: > ... to =C2=A3250 ... > But: the byte stream I get back on doing a SELECT is always *missing* the C2 > byte. What am I missing here? I'm using the PHP PDO interface. Again, can you try retreiving the length of the field as part of the same SELECT and see whether the field length agrees with the C2 being missing ? And also compare that with the result of doing strlen(theString) as a PHP command and find out whether all three agree ? You could try doing same thing using the sqlte3 interface which is closer to 'pure sqlite3' if you think that'll help. Using that library will involve a cleaner path with fewer conversions and processes. It's less likely to give you such problems. $dbConnection = new SQLite3('testdb.sqlite'); $dbConnection->exec("INSERT INTO myTable VALUES ('to £250'"); // should really check return value $retrieved = $dbConnection->querySingle('SELECT theValue,length(theValue) FROM myTable', true); echo $retrieved[0], $retrieved[1], strlen($retrieved[0]); If that works, you could write with one and read with the other and use that to figure out whether the fault is in the bit that stores or retrieves the value. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
