On Tuesday, 28 August, 2018 07:50, Tim Streater <t...@clothears.org.uk> wrote:
>What is actually the difference between a column declared as TEXT and >one declared as BLOB in an SQLite database? Not a thing. You are free to store data of any type in any column in any row. The "TEXT" declaration only means that the column affinity is TEXT and if you attempt to put something that is not text in that column (such as an integer or a real) then it will be converted to text before being stored, since you have expressed that you would prefer the data in this column to be text wherever possible. >What does SQLite do to textual data that I ask it to put into a TEXT column? Nothing that you do not ask it to do. >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. >The reason I'm interested is that I have a table with a TEXT column. >For backwards compatibility reasons, I'd like not to change the >column's type or even add another column with BLOB type. Up to now >I've assumed that the data to go in the column was all ASCII or >perhaps UTF-8. Now it seems that it's legal for it to be a mixture of >encodings. So I want to treat it as binary and be able to just use >the existing TEXT column. Anything I need to look out for? Unless you explicitly request translations to occur then none will occur. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
