On 28 Aug 2018, at 2:50pm, 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? What does SQLite do to textual data > that I ask it to put into a TEXT column? BLOB data is always handled as a block of a certain number of octets, as you'd think. Internally, some handling of TEXT data assumes that it's UTF-8, or perhaps some other encoding: <https://sqlite.org/pragma.html#pragma_encoding> For instance, without testing it yourself or finding a reference, you're not entirely sure whether length() of a string proceeds past a NUL, are you ? SQlite is written to do the most likely most convenient thing, but it is not completely predictable to a programmer who frequently plays with different encodings. Also, if it's expecting a number, SQLite will sometimes convert a string to a number. However, SQLite has no understanding of BLOB data and will never convert it to anything. See section See section 4.2 of <https://www.sqlite.org/datatype3.html>. There are also some niggles about meta-operations with BLOBs. For instance, the "CREATE TABLE ... AS SELECT" construction handles BLOB values a little weirdly. However, the biggest distinction between these two is not in the SQLite C API but in the countless libraries and shims written to allow languages to make SQLite calls. String handling in those libraries can do all sorts of things to strings, including terminating them at NUL (as C would expect), parsing them as Unicode before returning a value, or internally labelling them as using a certain Windows code page. So most of our questions resulting from unexpected string changes result not from the SQLite API but from what happens when a programming language tries to understand the string. None of this should be done to a BLOB except under explicit programmer control. > How does it know not to do that if I want to send some binary data to a Text > column? See section 3.4 of <https://www.sqlite.org/datatype3.html>. Here you'll find demonstrated what happens if you put values of different types into columns of different types. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
