This is well documented. The result of expressions involving strings with embedded NUL characters is undefined. The conversion BLOB -> TEXT will check for and stop at the first embedded NUL; in none occurs, a terminating NUL is added. You may store any sequence of bytes in a BLOB, but please do not expect a data storage layer library to handle opaque objects. If you do need to see inside a blob, use the HEX() function to convert it to a readable form.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Father Chrysostomos Gesendet: Dienstag, 09. Mai 2017 05:02 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] The cli truncates values containing null chars While the SQLite database itself supports embedded nulls without any problems, the command-line utility truncates values at the first null character, which can be very confusing if a field begins with a null character. (I spent an hour debugging my code, trying to figure out why it was not writing the value to the field, only to realize eventually that my code was fine. It was the debugging tool [that’s what I use the sqlite3 cli for] that was buggy.) This excerpt from a terminal sessions demonstrates the problem. You need to have the perl DBI and DBD::SQLite modules installed for it to work: $ perl -MDBI -e 'my $dbh = DBI->connect("dbi:SQLite:dbname=/tmp/foo.sqlite","",""); $dbh->do("create table foo ( bar BLOB )"); $dbh->prepare("insert into foo values (?)")->execute("foo\0bar");' $ sqlite3 /tmp/foo.sqlite SQLite version 3.18.0 2017-03-28 18:48:43 Enter ".help" for usage hints. sqlite> select * from foo; foo sqlite> I don’t know how to get null characters into a database from the sqlite3 shell, if that is even possible. If you do not have the perl modules installed, you can use: $ perl -MCPAN -eshell and then type ‘install DBI DBD::SQLite' at the cpan prompt. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
