Re: [sqlite] c-api document suggestion

[Tim Streater]

On 23 Sep 2011 at 11:18, Mirek Suk  wrote: 

> Dne 23.9.2011 4:41, Igor Tandetnik napsal(a):

>> Note that I didn't say it was wise to store NUL characters as part of the
>> string - I only said that you could do it if you wanted to. sqlite3_bind_text
>> takes the length parameter at face value, and stores exactly as many bytes as
>> you tell it to store. It's up to you to ensure that the values actually make
>> sense for your application. Garbage in/garbage out and all that.

> I just find entire nul handling in SQLite strange. it's C API why not
> expect C (that is nul terminated) strings.

Not in my case it's not. In my case it's the PHP API and I would be very 
annoyed if strings came back with unexpected NULs at the end, or if any NUL I 
insert in the middle of a string acted as a terminator.

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

Re: [sqlite] c-api document suggestion

[Igor Tandetnik]

Mirek Suk  wrote:
> I just find entire nul handling in SQLite strange. it's C API why not
> expect C (that is nul terminated) strings.

Because some people do want to store strings with embedded NULs, for various 
reasons. If you don't, just pass -1 for length and be done with it.

> man says
> "Strings returned by sqlite3_column_text() and sqlite3_column_text16(),
> even empty strings, are always zero terminated."
> "The values returned by sqlite3_column_bytes()
>  and
> sqlite3_column_bytes16() 
> do not include the zero terminators at the end of the string. For
> clarity: the values returned by sqlite3_column_bytes()
>  and
> sqlite3_column_bytes16() 
> are the number of bytes in the string, not the number of characters."
> 
> If I include my nul does this mean string is my nul terminated or sqlite
> appends another one ?

SQLite appends another one.

> is this auto-added null included in size returned

No. But your own (if any) is.

> are my nuls removed from string size or not ?

No. You get back exactly the sequence of bytes you put in.
-- 
Igor Tandetnik

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

Re: [sqlite] c-api document suggestion

[Simon Slavin]

On 23 Sep 2011, at 11:18am, Mirek Suk wrote:

> I just find entire nul handling in SQLite strange. it's C API why not expect 
> C (that is nul terminated) strings.

That's more or less the problem: C expects 0x00 termination.  But SQLite3 is 
written to support UTF-8 and UTF-16 strings of specified lengths.

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

Re: [sqlite] c-api document suggestion

[Mirek Suk]

Dne 23.9.2011 4:41, Igor Tandetnik napsal(a):

Mira Suk  wrote:

On 9/21/2011 21:22 Igor Tandetnik wrote:


You can include the NUL terminator, if you want it to actually be stored
in the database.

Actually you can't - if you do all SQL string functions will not work.
to be clear -
SELECT TRIM(what ever text column you stored with including null on end of 
string)
will not trim that string.

It does work - it removes all whitespace from the end of the string, up to but 
not including the first non-whitespace character. Which happens to be NUL. It 
follows the spec perfectly, it's your expectations that are wrong.

Note that I didn't say it was wise to store NUL characters as part of the 
string - I only said that you could do it if you wanted to. sqlite3_bind_text 
takes the length parameter at face value, and stores exactly as many bytes as 
you tell it to store. It's up to you to ensure that the values actually make 
sense for your application. Garbage in/garbage out and all that.
I just find entire nul handling in SQLite strange. it's C API why not 
expect C (that is nul terminated) strings.


man says
"Strings returned by sqlite3_column_text() and sqlite3_column_text16(), 
even empty strings, are always zero terminated."
"The values returned by sqlite3_column_bytes() 
 and 
sqlite3_column_bytes16()  
do not include the zero terminators at the end of the string. For 
clarity: the values returned by sqlite3_column_bytes() 
 and 
sqlite3_column_bytes16()  
are the number of bytes in the string, not the number of characters."


If I include my nul does this mean string is my nul terminated or sqlite 
appends another one ? is this auto-added null included in size returned 
? or not ? are my nuls removed from string size or not ? (there is man 
obviously wrong as column_bytes returns size including "zero terminators 
at the end of the string" if I set them)  => testing how API behaves is 
required to produce code due to unclear docs IMHO.


As for non-functioning SQL text functions - David Garfield did some 
test. It's some time ago when I found out this does not work as 
expected, and yes, I most likely provided invalid sample. Fact is I wont 
ever store nul in SQLite database, as I don't trust all SQLite string 
functions will respect it.


Maybe as You said, my expectations are wrong, however my expectations 
are based on other database engines. if one behaves differently, maybe 
it's not just my expectations.







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

Re: [sqlite] c-api document suggestion

[Igor Tandetnik]

Mira Suk  wrote:
> On 9/21/2011 21:22 Igor Tandetnik wrote:
> 
>> You can include the NUL terminator, if you want it to actually be stored
>> in the database.
> 
> Actually you can't - if you do all SQL string functions will not work.
> to be clear -
> SELECT TRIM(what ever text column you stored with including null on end of 
> string)
> will not trim that string.

It does work - it removes all whitespace from the end of the string, up to but 
not including the first non-whitespace character. Which happens to be NUL. It 
follows the spec perfectly, it's your expectations that are wrong.

Note that I didn't say it was wise to store NUL characters as part of the 
string - I only said that you could do it if you wanted to. sqlite3_bind_text 
takes the length parameter at face value, and stores exactly as many bytes as 
you tell it to store. It's up to you to ensure that the values actually make 
sense for your application. Garbage in/garbage out and all that.
-- 
Igor Tandetnik

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

Re: [sqlite] c-api document suggestion

[Mira Suk]

On 9/21/2011 21:22 Igor Tandetnik wrote:

You can include the NUL terminator, if you want it to actually be stored 
in the database.



Igor Tandetnik


Actually you can't - if you do all SQL string functions will not work.
to be clear -
SELECT TRIM(what ever text column you stored with including null on end of 
string)
will not trim that string.
 
found out the hard way.

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

Re: [sqlite] c-api document suggestion

[Sean Pieper]

a. This is what confused me-- the documentation for the prepare statement 
mentions this perf boost being quite explicit about including the null, then 
also makes the ambiguous statement that "the zSQL string ends at either the 
first '\000' or '\u' character or the nbyteth byte, whichever comes first." 
Since the null gets ignored, I interpreted this as meaning that the nth byte 
was ignored, and that providing the proper data size was the source of the 
performance boost (possibly by eliminating a search for null termination). 

Thank you for the clarification :-).


-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, September 21, 2011 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c-api document suggestion

On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper <spie...@nvidia.com> wrote:

> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1,
> bind_text expects that this length exclude the null termination, whereas
> prepare apparently expects it to include the null termination.
>

No, sqlite3_prepare_v2() does not *expect* N to be size+1.
sqlite3_prepare_v2() with length N will process all text up to the first
zero-character, or the first N bytes, whichever come first.  So you are
perfectly free to set N equal to the length of the string exclusive of the
zero-terminator, just as in bind_text().

However, if you are willing to make N equal to the length of the string plus
one, then SQLite can avoid a memcpy() and thus give a very small performance
advantage.  But that is purely an optimization, and a minor one at that.  It
will work correctly regardless of whether N is strlen() or strlen()+1.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

[Richard Hipp]

On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper  wrote:

> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1,
> bind_text expects that this length exclude the null termination, whereas
> prepare apparently expects it to include the null termination.
>

No, sqlite3_prepare_v2() does not *expect* N to be size+1.
sqlite3_prepare_v2() with length N will process all text up to the first
zero-character, or the first N bytes, whichever come first.  So you are
perfectly free to set N equal to the length of the string exclusive of the
zero-terminator, just as in bind_text().

However, if you are willing to make N equal to the length of the string plus
one, then SQLite can avoid a memcpy() and thus give a very small performance
advantage.  But that is purely an optimization, and a minor one at that.  It
will work correctly regardless of whether N is strlen() or strlen()+1.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

[Pavel Ivanov]

> If the user supplies the length of the argument rather than using -1, 
> bind_text expects that this length exclude the null termination, whereas 
> prepare apparently expects it to include the null termination.
Can I challenge you in that this conclusion is wrong? Everywhere in
the development world length of string always means "null termination
is not included" because explicitly providing length means there can
be no null termination at all.

Please show examples of code that led you to your conclusion and show
how it fails without null termination.


> leaving the reader to look through the description of how auto-detection of 
> length works in each case.

You are a little inconsistent here. Auto-detection of length doesn't
work when you explicitly provide string length, so you don't have to
look through it.


Pavel


On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper  wrote:
> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1, 
> bind_text expects that this length exclude the null termination, whereas 
> prepare apparently expects it to include the null termination.
>
> Since this is kind of surprising, it would be helpful if the documentation 
> for each function directly stated the desired behavior for explicit 
> declaration of length, rather than leaving the reader to look through the 
> description of how auto-detection of length works in each case.
>
> -sean
>
> ---
> This email message is for the sole use of the intended recipient(s) and may 
> contain
> confidential information.  Any unauthorized review, use, disclosure or 
> distribution
> is prohibited.  If you are not the intended recipient, please contact the 
> sender by
> reply email and destroy all copies of the original message.
> ---
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

[Igor Tandetnik]

On 9/21/2011 3:05 PM, Sean Pieper wrote:

There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
sqlite3_prepare_v2.
If the user supplies the length of the argument rather than using -1,
bind_text expects that this length exclude the null termination,


You can include the NUL terminator, if you want it to actually be stored 
in the database.



whereas prepare apparently expects it to include the null
termination.


What makes you believe that? As far as I know, it should still work if 
you don't include it.

--
Igor Tandetnik

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

[sqlite] c-api document suggestion

[Sean Pieper]

There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
sqlite3_prepare_v2.
If the user supplies the length of the argument rather than using -1, bind_text 
expects that this length exclude the null termination, whereas prepare 
apparently expects it to include the null termination.

Since this is kind of surprising, it would be helpful if the documentation for 
each function directly stated the desired behavior for explicit declaration of 
length, rather than leaving the reader to look through the description of how 
auto-detection of length works in each case.

-sean

---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users