[sqlite] Multiple Prepared Statements
On Fri, 08 May 2015 14:49:54 -0700, Scott Doctor wrote: > Can I prepare multiple statements then implement them in > arbitrary order (based on some logic)? Yes. > Or do the statements need to be prepared, stepped, finalized > serially? No. You even don't have to _finalize() the statement, you can just _reset() it to finish the _step() loop and keep the statement around for reuse (with new bindings) later. _reset() will free the resources that are allocated at the first _step(). -- Regards, Kees Nuyt
[sqlite] Multiple Prepared Statements
On 8 May 2015, at 11:40pm, Scott Doctor wrote: > So if I have a loop that finds a row with some data (statement1) > then based on values from that row sets fields in other rows > statement2 find a row to set new data > statement3 set column to something, > repeat n times. > then go back and do it all again x times. > I can prepare the 3 statements first then loop until I am done then finalize > the 3 statements. > That sure saves much overhead from preparing and finalizing 3*n*x times. Yep. There's nothing stopping you from doing that. But if the changes you make would modify the result of SELECTs which are still running, you may find it difficult to predict how they would interact. Simon.
[sqlite] Multiple Prepared Statements
On 5/8/15, Scott Doctor wrote: > > Can I prepare multiple statements then implement them in > arbitrary order (based on some logic)? > > Or do the statements need to be prepared, stepped, finalized > serially? > They can be run in any arbitrary order. That is the usual case, actually. -- D. Richard Hipp drh at sqlite.org
[sqlite] Multiple Prepared Statements
So if I have a loop that finds a row with some data (statement1) then based on values from that row sets fields in other rows statement2 find a row to set new data statement3 set column to something, repeat n times. then go back and do it all again x times. I can prepare the 3 statements first then loop until I am done then finalize the 3 statements. That sure saves much overhead from preparing and finalizing 3*n*x times. Scott Doctor scott at scottdoctor.com -- On 5/8/2015 3:20 PM, Richard Hipp wrote: > On 5/8/15, Scott Doctor wrote: >> Can I prepare multiple statements then implement them in >> arbitrary order (based on some logic)? >> >> Or do the statements need to be prepared, stepped, finalized >> serially? >> > They can be run in any arbitrary order. That is the usual case, actually.
[sqlite] Multiple Prepared Statements
Can I prepare multiple statements then implement them in arbitrary order (based on some logic)? Or do the statements need to be prepared, stepped, finalized serially? Scott Doctor scott at scottdoctor.com --
Re: [sqlite] Multiple prepared statements
Yup, my bad. Fixed. On 16/10/2010 12:03 AM, Andrew Davison wrote: > On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >>> Now I decide that I want a second type of insert, so I try to use a >>> prepared statement for that as well. However it always fails. As long as >>> the other prepared statement is hanging round I can't prepare a new one. >>> Does this seem right or am I really soing something wrong? >> >> You are doing something wrong. I always keep like 10 prepared >> statements for each connection and it works perfectly. I work in C++ >> so it's a direct SQLite feature. That's what prepared statements are >> for. >> > > That's what I thought, so something is wrong. > Thanks. > > > ___ > 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] Multiple prepared statements
On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >> Now I decide that I want a second type of insert, so I try to use a >> prepared statement for that as well. However it always fails. As long as >> the other prepared statement is hanging round I can't prepare a new one. >> Does this seem right or am I really soing something wrong? > > You are doing something wrong. I always keep like 10 prepared > statements for each connection and it works perfectly. I work in C++ > so it's a direct SQLite feature. That's what prepared statements are > for. > That's what I thought, so something is wrong. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple prepared statements
> Now I decide that I want a second type of insert, so I try to use a > prepared statement for that as well. However it always fails. As long as > the other prepared statement is hanging round I can't prepare a new one. > Does this seem right or am I really soing something wrong? You are doing something wrong. I always keep like 10 prepared statements for each connection and it works perfectly. I work in C++ so it's a direct SQLite feature. That's what prepared statements are for. Pavel On Fri, Oct 15, 2010 at 9:43 AM, Andrew Davison wrote: > In my database I do lots of inserts, of exactly the same nature so I use > a prepared statement, which I cache, always reseting after use. Works fine. > > Now I decide that I want a second type of insert, so I try to use a > prepared statement for that as well. However it always fails. As long as > the other prepared statement is hanging round I can't prepare a new one. > Does this seem right or am I really soing something wrong? > > Can I not have multiple prepared statements created? > > Regards. > > > ___ > 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] Multiple prepared statements
On Fri, Oct 15, 2010 at 8:43 AM, Andrew Davison wrote: > In my database I do lots of inserts, of exactly the same nature so I use > a prepared statement, which I cache, always reseting after use. Works fine. > > Now I decide that I want a second type of insert, so I try to use a > prepared statement for that as well. However it always fails. As long as > the other prepared statement is hanging round I can't prepare a new one. > Does this seem right or am I really soing something wrong? > Which language? > Can I not have multiple prepared statements created? > At least with Perl DBI I can have as many prepared statements as I want or care. Don't know if that is a Perl capability or sqlite capability. > Regards. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple prepared statements
In my database I do lots of inserts, of exactly the same nature so I use a prepared statement, which I cache, always reseting after use. Works fine. Now I decide that I want a second type of insert, so I try to use a prepared statement for that as well. However it always fails. As long as the other prepared statement is hanging round I can't prepare a new one. Does this seem right or am I really soing something wrong? Can I not have multiple prepared statements created? Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple prepared statements
*argh*
more or less my bad, sqlite3_prepare_v2() instead of sqlite3_prepare()
solved it.
Oyvind.
On Wed, Nov 26, 2008 at 5:29 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Oyvind Idland <[EMAIL PROTECTED]>
> wrote:
> > I am using two prepared statements in my code (that does INSERT),
> > following the pattern
> >
> > prepare(stmt1)
> > prepare(stmt2)
> >
> > while (xx)
> > {
> > bind(stmt1)
> > step(stmt1)
> > reset(stmt1)
> >
> > bind(stmt2)
> > step(stmt2)
> > reset(stmt2)
> > }
> >
> > The first iteration works, but int the second step() fails with code
> > 1.
>
> There's nothing wrong with the pattern. The problem must be in your code
> actually implementing the pattern.
>
> > I am thinking of trying to add both INSERT's inside one statement
> > insted, hopefully that will work better..
>
> That won't work. sqlite3_prepare parses only one statement, up to the
> first semicolon.
>
> Igor Tandetnik
>
>
>
> ___
> 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] Multiple prepared statements
Oyvind Idland <[EMAIL PROTECTED]>
wrote:
> I am using two prepared statements in my code (that does INSERT),
> following the pattern
>
> prepare(stmt1)
> prepare(stmt2)
>
> while (xx)
> {
> bind(stmt1)
> step(stmt1)
> reset(stmt1)
>
> bind(stmt2)
> step(stmt2)
> reset(stmt2)
> }
>
> The first iteration works, but int the second step() fails with code
> 1.
There's nothing wrong with the pattern. The problem must be in your code
actually implementing the pattern.
> I am thinking of trying to add both INSERT's inside one statement
> insted, hopefully that will work better..
That won't work. sqlite3_prepare parses only one statement, up to the
first semicolon.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple prepared statements
Hi,
according to documentation,
"An application is allows to prepare multiple SQL statements in advance and
evaluate them as needed. There is no arbitrary
limit to the number of outstanding prepared statements."
I am using two prepared statements in my code (that does INSERT), following
the pattern
prepare(stmt1)
prepare(stmt2)
while (xx)
{
bind(stmt1)
step(stmt1)
reset(stmt1)
bind(stmt2)
step(stmt2)
reset(stmt2)
}
The first iteration works, but int the second step() fails with code 1. I
have also double-checked that I
give the correct statement objects etc. Commenting out one of them works.
I running 3.6.6.1 on WinXP.
I am thinking of trying to add both INSERT's inside one statement insted,
hopefully that will work better..
-Oyvind
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple prepared statements with INSERT?
On 12/21/06, E Tse <[EMAIL PROTECTED]> wrote: Hi guys, I ran into a strange problem. I have 2 prepared sqlite3_stmt, each inserting to different tables in a sqlite database: sqlite3_stmt* insertA; std::string sql = "insert into tablea(col1) values(?)"; int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), &insertA, NULL); sqlite3_stmt* insertB; std::string sql = "insert into tableb(col1) values(?)"; int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), &insertB, NULL); I intended to keep insertA and insertB through the lifetime of my application/database. I use sqlite3_reset(insertA) before and after each $sqlite3_step() call: sqlite3_reset(insertA); sqlite3_bind_int(insertA, ...); rc1 = sqlite3_step(insertA); sqlite3_reset(insertA); sqlite3_reset(insertB); sqlite3_bind_int(insertB, ...); rc2 = sqlite3_step(insertB); sqlite3_reset(insertB); The strange thing is rc2 will returns SQLITE_ERROR if sqlite3_step(insertA) is executed. The error message from sqlite3_errmsg() is even stranger. It returns "not an error"! It looks like your code written so it will reset() the statement before it's executed the first time. Perhaps you shouldn't do a reset unless it's been executed. like prepare() while ( something ) bind() step() reset() -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multiple prepared statements with INSERT?
Hi guys, I ran into a strange problem. I have 2 prepared sqlite3_stmt, each inserting to different tables in a sqlite database: sqlite3_stmt* insertA; std::string sql = "insert into tablea(col1) values(?)"; int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), &insertA, NULL); sqlite3_stmt* insertB; std::string sql = "insert into tableb(col1) values(?)"; int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), &insertB, NULL); I intended to keep insertA and insertB through the lifetime of my application/database. I use sqlite3_reset(insertA) before and after each $sqlite3_step() call: sqlite3_reset(insertA); sqlite3_bind_int(insertA, ...); rc1 = sqlite3_step(insertA); sqlite3_reset(insertA); sqlite3_reset(insertB); sqlite3_bind_int(insertB, ...); rc2 = sqlite3_step(insertB); sqlite3_reset(insertB); The strange thing is rc2 will returns SQLITE_ERROR if sqlite3_step(insertA) is executed. The error message from sqlite3_errmsg() is even stranger. It returns "not an error"! If I don't execute sqlite3_step(insertA) then rc2 will return SQLITE_OK. Does it seem like I cannot keep 2 prepared statement which is going to do INSERT alive at the same time?! If the insertA prepare statement is just a SELECT, rc2 returns SQLITE_OK. If I use sqlite3_exec() to insert into tableb, then it will work, too. strange! I have tried this on sqlite 3.2.7 and 3.3.8 and the same thing happens. Can anyone shed some light? Thanks, Eric
[sqlite] Multiple prepared statements work on Windows... fails on Mac OS X
Hello,
I'm using two prepared statements in a block of cross platform C++ code
like this (very roughly):
{
sqlite3_stmt * pstmt1 = NULL;
sqlite3_stmt * pstmt2 = NULL;
pstmt1 = PrepareAndBind(...); // Prepare and bind one statement
pstmt2 = PrepareAndBind(...); // Prepare and bind a different
statement
sqlite3_step(pstmt1); // Returns SQLITE_ROW (as expected)
sqlite3_step(pstmt2); // Returns SQLITE_DONE on Mac OS X but
returns SQLITE_ROW on Windows (same database. same queries)
sqlite3_finalize(pstmt1);
sqlite3_finalize(pstmt2);
}
The second step function call returns SQLITE_DONE on Mac OS X (when it
should have found records and returned SQLITE_ROW). On Windows, this
always works (both step function calls return SQLITE_ROW and I can
iterate through both sets of records using step until finished).
To work around this so it works on both platforms, I iterate through the
records using step for pstmt1 and call sqlite3_finalize before calling
sqlite3_step on pstmt2. This solution works on both platforms.
This could easily be a bug in my code (I have built some light wrappers
to accomplish most of this). But I have copied the same database from
Mac to Windows and ran the same queries to make sure the data is in the
db and the queries work fine. And I've commented out one prepared
statement or the other and both return results. But as soon as I
uncomment the other statement, it stops returning rows on the Mac as
I've described above.
Are there any known issues with using multiple prepared statements like
I have done? Am I doing something wrong (maybe I should be calling
sqlite3_reset somewhere along the way?)
