On 28 Aug 2018, at 15:36, Keith Medcalf <kmedc...@dessus.com> wrote: > On Tuesday, 28 August, 2018 07:50, Tim Streater <t...@clothears.org.uk> wrote:
>>How does it know not to do that if I want to send some binary data to a Text >>column? > > Simply because you do not request that those things be done. > > So, the "things" that may occur depend on the APIs you use to read/write the > data, and the "translations" that you might have asked SQLite3 to do. For > example, if your database is created with the 'encoding' set to one of the > UTF-16 variants, and you use the sqlite3_bind_text() to put "text" in that > column that is not valid UTF-8 that can be translated to UTF-16 then > explosions will occur. Similarly if the database encoding is UTF-8 (the > default) and you use sqlite3_bind_text16() to try and store UTF16 data in the > database, and that data is not valid UTF-16, then explosions will occur. > > If however, you database encoding is UTF-8 (the default) and you use > sqlite_bind_text() to put data in the database, then SQLite3 will not "tamper" > with your data and you may put whatever you wish in there whether it is valid > UTF-8 or not. Similarly if the database encoding is UTF-16 and you use > sqlite3_bind_text16() you can put whatever you wish in there whether it is > valid UTF-16 or not. > > If you use the sqlite3_bind_blob() interface to put data in the database, it > will be stored as a blob and the "affinity" (text) expressed in the create > table statement will be ignored since the "affinity" specified in the table > declaration will never attempt to translate a "blob" into something else. > > Assuming you managed to get data into the database, then how you ask to > retrieve it may also cause explosions to occur. For example, you might have > stored your binary data (that is not valid UTF-8) using sqlite3_bind_text() in > the database where the database encoding is UTF-8. If you try and retrieve it > using sqlite3_column_text16() then SQLite3 will, because *YOU* requested it, > attempt to translate the data, and explosions will occur. Mutatis mutandis if > *you* request other translations that are invalid (ie, from a UTF-16 encoded > database where the data is not valid UTF-16, and you try to retrieve it > translated to UTF-8). > > However, if you attempt to retrieve the data using sqlite3_column_blob() you > will always get back exactly what is in the database without translation. If > you store a blob and ask for some text, explosions may occur. > > So in short, just make sure that you store your "blob" of data using the > appropriate storage type (sqlite3_bind_blob) when you store it, and always > check what storage type it is before retrieving it and use the corresponding > sqlite3_column_(type) to get the data out, and you will be fine. SQLite3 will > do *NOTHING* to your data that you have not explicitly asked it to do. Thanks. I think the above is the clue I needed. I'm not familiar with the C interface; I'm using Xojo for this application. They have an SQLiteBlob class with its own read/write methods. It should therefore suffice for me to ask on their forum what they do. -- Cheers -- Tim _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
