Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 21 Sep 2017, at 5:00pm, heribert  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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

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

[heribert]

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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 9/21/17, heribert  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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  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
>sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users