How does SQLite handle \r and \n in values?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

How does SQLite handle \r and \n in values?

heribert
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?

Thx for any hint
heribert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

Bart Smissaert
I needed this recently and for a line break this worked:

'text before linebreak' || char(10) || 'text after linebreak'


RBS

On Thu, Sep 21, 2017 at 2:56 PM, heribert <[hidden email]> 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?
>
> Thx for any hint
> heribert
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

J. King-3
In reply to this post by heribert
More details would help. How are you inserting the data? Are you using a bound parameter (you should be), or quoted text? Are you using something other than the C interface to interact with the database? Which error did you receive? Can you provide a minimal example of the code you used?

On September 21, 2017 9:56:29 AM EDT, heribert <[hidden email]> 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?
>
>Thx for any hint
>heribert
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

Richard Hipp-3
In reply to this post by heribert
On 9/21/17, heribert <[hidden email]> wrote:
>
> 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.

The SQLite core should not care.  Something else is going wrong.  What
programming language are you using?  Can you send example code?
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

heribert
In reply to this post by J. King-3
I'm using c++ and generate the whole insert string via sqlite3_vmprintf
with quoted text. So the '\r' and '\n' are single characters - this will
result in the error.
@J.King: I followed your hint to bind the values. Works now as expected.
The c strings are now correctly inserted.

Am 21.09.2017 um 16:20 schrieb J. King:

> More details would help. How are you inserting the data? Are you using a bound parameter (you should be), or quoted text? Are you using something other than the C interface to interact with the database? Which error did you receive? Can you provide a minimal example of the code you used?
>
> On September 21, 2017 9:56:29 AM EDT, heribert <[hidden email]> 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?
>>
>> Thx for any hint
>> heribert
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

R Smith
In reply to this post by heribert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

Simon Slavin-3
In reply to this post by heribert


On 21 Sep 2017, at 5:00pm, heribert <[hidden email]> wrote:

> I'm using c++ and generate the whole insert string via sqlite3_vmprintf with quoted text. So the '\r' and '\n' are single characters - this will result in the error.

Something is wrong with your development environment.  SQLite has no clue what the characters in its strings are.  You can put returns and newlines and all sorts of other control characters in them.

It’s more likely that your string handling library is messing things up.  For debugging purposes have your code print the string, and the length of the string just before the sqlite3_ call.

Also make sure that your string is encoded for Unicode, not some weird code table.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does SQLite handle \r and \n in values?

heribert
The used developing environment is an old one, this may case this error.
I'm currently adding some new features to application compiled with MS
VC98. The strings i'm using are UTF8-coded.
I will mail a short sample and  the resulting error code today.

Am 21.09.2017 um 20:18 schrieb Simon Slavin:

>
> On 21 Sep 2017, at 5:00pm, heribert <[hidden email]> wrote:
>
>> I'm using c++ and generate the whole insert string via sqlite3_vmprintf with quoted text. So the '\r' and '\n' are single characters - this will result in the error.
> Something is wrong with your development environment.  SQLite has no clue what the characters in its strings are.  You can put returns and newlines and all sorts of other control characters in them.
>
> It’s more likely that your string handling library is messing things up.  For debugging purposes have your code print the string, and the length of the string just before the sqlite3_ call.
>
> Also make sure that your string is encoded for Unicode, not some weird code table.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users