On 2017/09/21 3:56 PM, heribert wrote:
Hello,
i attempt to make a INSERT with a TEXT field containing a string with
\r and \n characters, but it fails (Strings without \r and \n
characters are inserted correct).
The sqlite3_exec results with error... i think the reason are the
special characters.
What is the correct way to store strings containing \r and \n in a
TEXT field?
SQLite has no problems with line breaks and returns, though your base
system probably changes the \r and \n to character codes 10 and 13, and
if it doesn't, you should, SQLite doesn't know what a '\r' is.
This following SQL, for example, will be perfectly replicated by SQLite:
INSERT INTO t(v1) VALUES (
'Hi, this is
a
Multi-line
value.'
);
Alternatively you can add the line breaks in an explicit way (using
SQLite SQL functions) to achieve the same result, like:
INSERT INTO t(v1) VALUES
('Hi, this is' || CHAR(13) || 'a' || CHAR(13) || 'Multi-line' ||
CHAR(13) || 'value.');
When you opt for a way that isn't explicit (like the first example),
then you have to be sure the editor or platform you are using will
correctly convert your mnemonic \r, \n or physical line breaks into the
correct relevant character codes. This is usually very easy to check.
Lastly, because your question implied wanting to know about best
practice, you should really be binding the strings using the API and not
SQL text. But SQLite itself doesn't care and can handle line breaks
perfectly either way.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
