Re: [sqlite] a bunch of questions about SQLite from new user
On Wed, 27 Oct 2004 15:11:09 -0500, Tom <[EMAIL PROTECTED]> wrote: > Vladimir, > Thank you for your responses. One short followup: > > >> A. what happens to the zSql? Is it copied or it is assumed that this > >> is a static text? Suppose I call sqlite3_prepare in one place and > >> immediately release dynamically allocated text. sqlite3_prepare parses > >> only the first statement. Then I call sqlite3_step as many times as > >> needed. Will it crash when trying to parse next statements in next > >> steps? > > zSql is parsed and turned into an internal VM bytecode for later > > execution; you're under no obligation to keep zSql around after > > prepare returns. > This would suggest that the whole string is parsed even if it contains > more than one statement. > According to documentation on sqlite3_prepare(): > "This routine only compiles the first statement in zSql, so *pzTail is > left pointing to what remains uncompiled." Sorry, I was unclear; _prepare will return a stmt corresponding to the bytecode for the first SQL statement in zSql. So, if you want to compile multiple statements, you'll have to keep zSql around and use pzTail appropriately. > >> B. Is pzTail of any use? I can see that inside the library code it is > >> checked against NULL before assigning the output text pointer but the > >> documentation does not state explicitly that this parameter is > >> optional. I don't want to rely on internal implementation (which may > >> change in future release) when the docs don't say it is optional. It > >> would be nice to clarify this. > > > > It's useful if you're reading SQL from the user, and you get, say, two > > INSERT commands in one string. pzTail will let you call > > prepare/step/etc. multiple times to evaluate all the sql. It would be > > good to explicitly state that it may be NULL. > I don't understand something. Suppose you have 2 statements in the SQL > string. Do you have to call sqlite3_prepare() 2 times? I was under > impression that sqlite3_step() will do it for me if I call it multiple > times. I tried to find some sample code via google and I did not see a > single example of calling sqlite3_prepare() multiple times for one SQL > string. If you have two SQL statements in one string, you'll need to create two sqlite3_stmt's by calling prepare twice, with the second one using the pzTail result of the first (to point at the second SQL statement). To execute, you'll have to step both stmt's (step the first until it returns DONE, then step the second until it returns DONE). sqlite3_step only applies to one sqlite3_stmt, which in turn contains only one compiled SQL expression -- step is there to step between result rows, not to step between statements. One somewhat involved example is the implementation of sqlite3_exec in legacy.c in the sqlite3 sources -- it prepares and executes all statements in a string. - Vlad
Re: [sqlite] a bunch of questions about SQLite from new user
Vladimir, Thank you for your responses. One short followup: A. what happens to the zSql? Is it copied or it is assumed that this is a static text? Suppose I call sqlite3_prepare in one place and immediately release dynamically allocated text. sqlite3_prepare parses only the first statement. Then I call sqlite3_step as many times as needed. Will it crash when trying to parse next statements in next steps? zSql is parsed and turned into an internal VM bytecode for later execution; you're under no obligation to keep zSql around after prepare returns. This would suggest that the whole string is parsed even if it contains more than one statement. According to documentation on sqlite3_prepare(): "This routine only compiles the first statement in zSql, so *pzTail is left pointing to what remains uncompiled." B. Is pzTail of any use? I can see that inside the library code it is checked against NULL before assigning the output text pointer but the documentation does not state explicitly that this parameter is optional. I don't want to rely on internal implementation (which may change in future release) when the docs don't say it is optional. It would be nice to clarify this. It's useful if you're reading SQL from the user, and you get, say, two INSERT commands in one string. pzTail will let you call prepare/step/etc. multiple times to evaluate all the sql. It would be good to explicitly state that it may be NULL. I don't understand something. Suppose you have 2 statements in the SQL string. Do you have to call sqlite3_prepare() 2 times? I was under impression that sqlite3_step() will do it for me if I call it multiple times. I tried to find some sample code via google and I did not see a single example of calling sqlite3_prepare() multiple times for one SQL string. Thanks in advance, Tom Abracode http://www.abracode.com
Re: [sqlite] a bunch of questions about SQLite from new user
I can take a crack at some of these...
On Tue, 26 Oct 2004 23:17:00 -0500, Tom <[EMAIL PROTECTED]> wrote:
> 1. sqlite3_prepare(
> sqlite3 *db,
> const char *zSql,
> int nBytes,
> sqlite3_stmt **ppStmt,
> const char **pzTail);
>
> A. what happens to the zSql? Is it copied or it is assumed that this
> is a static text? Suppose I call sqlite3_prepare in one place and
> immediately release dynamically allocated text. sqlite3_prepare parses
> only the first statement. Then I call sqlite3_step as many times as
> needed. Will it crash when trying to parse next statements in next
> steps?
zSql is parsed and turned into an internal VM bytecode for later
execution; you're under no obligation to keep zSql around after
prepare returns.
> B. Is pzTail of any use? I can see that inside the library code it is
> checked against NULL before assigning the output text pointer but the
> documentation does not state explicitly that this parameter is
> optional. I don't want to rely on internal implementation (which may
> change in future release) when the docs don't say it is optional. It
> would be nice to clarify this.
It's useful if you're reading SQL from the user, and you get, say, two
INSERT commands in one string. pzTail will let you call
prepare/step/etc. multiple times to evaluate all the sql. It would be
good to explicitly state that it may be NULL.
> 2. int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
> int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
> In case of text in a column, is it supposed to return the byte count of
> text only or the byte count of text plus the terminating 0 byte (2
> bytes if unicode)?
This bit is in the docs:
"If the result is a BLOB then the sqlite3_column_bytes() routine
returns the number of bytes in that BLOB. No type conversions occur.
If the result is a string (or a number since a number can be converted
into a string) then sqlite3_column_bytes() converts the value into a
UTF-8 string and returns the number of bytes in the resulting string.
The value returned does not include the \000 terminator at the end of
the string. The sqlite3_column_bytes16() routine converts the value
into a UTF-16 encoding and returns the number of bytes (not
characters) in the resulting string. The \u terminator is not
included in this count."
So, no null terminator, and always returns the number of bytes (2*num
of chars in the case of unicode).
> 3. const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
> const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
> I assume that text returned from the above functions are 0-terminated.
> It is not stated explicitly in documentation. Should I use value
> returned from sqlite3_column_bytes()/sqlite3_column_bytes16 or use
> strlen()/16 bit unicode equivalent?
They are null terminated; if you need to duplicate them, the number of
bytes to copy is _bytes()+1 or _bytes16()+2 depending on if you use
_text or _text16.
> 4. Can I use sqlite3_column_xxx family of functions when sqlite3_step()
> returns SQLITE_DONE or SQLITE_OK? I assume it can be done only after
> SQLITE_ROW is returned. It would be good to clarify this. When
> SQLITE_DONE is returned? Suppose I run a query searching for some
> column value in DB. SQLite finds a matching row and returns SQLITE_ROW.
> Then I check for more using sqlite3_step() and suppose there are no
> more results. Will it return SQLITE_DONE and still point to previous
> row? Next, suppose there is one more result and it finds the last
> matching row (different from previous) will it return SQLITE_ROW or
> will it return SQLITE_DONE meaning: "one more row found and I am sure
> it is the last you can get"?
SQLITE_DONE means "There are no more rows"; once you get DONE, the
column_xxx functions are not valid. If there are zero rows, you'll
just get DONE. If you have just one row being returned, you'll get
ROW, then DONE.Two rows, you'll get ROW, ROW, DONE. The _column_*
functions are only valid after a ROW return; usually, in a loop, it's
something like while ((ret = sqlite_step(stmt)) == SQLITE_ROW) { ...
}.
I hope that helps,
- Vlad
[sqlite] a bunch of questions about SQLite from new user
Hello, I am a new SQLite user. I look at the documentation and there are a couple of things which are not clear to me and I could not find info in documentation or google. I could probably write some code to test and find answers to the following questions but it actually might be better to ask them here so hopefully they will be answered and serve as a reference for people with similar questions and maybe find their way into documentation. 1. sqlite3_prepare( sqlite3 *db, const char *zSql, int nBytes, sqlite3_stmt **ppStmt, const char **pzTail); A. what happens to the zSql? Is it copied or it is assumed that this is a static text? Suppose I call sqlite3_prepare in one place and immediately release dynamically allocated text. sqlite3_prepare parses only the first statement. Then I call sqlite3_step as many times as needed. Will it crash when trying to parse next statements in next steps? B. Is pzTail of any use? I can see that inside the library code it is checked against NULL before assigning the output text pointer but the documentation does not state explicitly that this parameter is optional. I don't want to rely on internal implementation (which may change in future release) when the docs don't say it is optional. It would be nice to clarify this. 2. int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); In case of text in a column, is it supposed to return the byte count of text only or the byte count of text plus the terminating 0 byte (2 bytes if unicode)? 3. const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); I assume that text returned from the above functions are 0-terminated. It is not stated explicitly in documentation. Should I use value returned from sqlite3_column_bytes()/sqlite3_column_bytes16 or use strlen()/16 bit unicode equivalent? 4. Can I use sqlite3_column_xxx family of functions when sqlite3_step() returns SQLITE_DONE or SQLITE_OK? I assume it can be done only after SQLITE_ROW is returned. It would be good to clarify this. When SQLITE_DONE is returned? Suppose I run a query searching for some column value in DB. SQLite finds a matching row and returns SQLITE_ROW. Then I check for more using sqlite3_step() and suppose there are no more results. Will it return SQLITE_DONE and still point to previous row? Next, suppose there is one more result and it finds the last matching row (different from previous) will it return SQLITE_ROW or will it return SQLITE_DONE meaning: "one more row found and I am sure it is the last you can get"? Thanks, Tom Abracode http://www.abracode.com/
