Re: [sqlite] Reset the cursor
Keith, Thank you. After upgrading everything works as expected. It was just an old library version. And I won't even bother trying to track down the fixing commit. ;-) And thanks to Olivier for a suggestion about the "SELECT.. " statement instead of mprintf(). The issue is closed. On Thu, Jun 7, 2018 at 9:39 PM, Keith Medcalf wrote: > > Yes, 3.24.0 is officially released and the official release page on the > sqlite.org website has been updated. I don't think I saw a release > announcement either though. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Thursday, 7 June, 2018 20:19 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>Hi, Keith, >> >>On Tue, Jun 5, 2018 at 5:09 PM, Keith Medcalf >>wrote: >>> >>> Most of them. In particular those that return (as in SELECT) data >>work either way. Those that set things can only be used as a pragma. >>> >>> Note that the table name is passed differently (in the case of >>pragma's expecting an identifier). >>> It is an identifier in the case of a pragma statement, and a string >>in the case of the table valued function. >>> >>> pragma foreign_key_list(identifier); >>> select * from pragma_foreign_key_list(stringval); >>> >>> ie: >>> >>> pragma foreign_key_list("My Table"); >>> select * from foreign_key_list('My Table'); >>> >>> or, if you do not have stoopid characters in identifiers: >>> >>> pragma foreign_key_list(MyTable); >>> select * from foreign_key_list('MyTable'); >>> >>> >>> Both versions behave identically and reset/auto-reset properly for >>me ... >> >>Here is the session with the SQLite version I'm currently developing: >> >>[code] >>SQLite version 3.13.0 2016-05-18 10:57:30 >>Enter ".help" for usage hints. >>sqlite> SELECT* FROM leagues; >>1|Demo - Roto (Auction)|1|1|1|demo|260|0 >>2|Demo - Roto (Draft)|3|1|1|demo|260|0 >>3|Demo - Points (Auction)|1|2|1|demo|260|0 >>4|Demo - Points (Draft)|3|2|1|demo|260|0 >>sqlite> SELECT * FROM pragma_foreign_key_list(leagues); >>Error: no such table: pragma_foreign_key_list >>sqlite> >>[/code] >> >>This version is a little behind (I believe something like 4 years >>old). >>Now, since I'm still developing I can upgrade that version, but when >>I >>went to the SQLite >>download page I see the SQLite 3.24 version and I don't remember the >>official release >>announcement being sent out by Mr. Hipp. >>Is today sources/shell tool is the released version and I just missed >>the announcement? >> >>Thank you. >> >>> >>> --- >>> The fact that there's a Highway to Hell but only a Stairway to >>Heaven says a lot about anticipated traffic volume. >>> >>> >>>>-Original Message- >>>>From: sqlite-users [mailto:sqlite-users- >>>>boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia >>>>Sent: Tuesday, 5 June, 2018 15:35 >>>>To: SQLite mailing list >>>>Subject: Re: [sqlite] Reset the cursor >>>> >>>>> Le 5 juin 2018 à 22:47, Igor Korot a écrit : >>>>> >>>>> As a side note: is it the case for all PRAGMA's command - they >>can >>>>be >>>>> rewritten this way? >>>> >>>>Full documentation for that is on page >>>>https://www.sqlite.org/pragma.html, see the second title ("PRAGMA >>>>functions"). >>>> >>>>Citing in short: // PRAGMAs that return results and that have no >>>>side-effects can be accessed from ordinary SELECT statements as >>>>table-valued functions. For each participating PRAGMA, the >>>>corresponding table-valued function has the same name as the PRAGMA >>>>with a 7-character "pragma_" prefix. // >>>> >>>>-- >>>>Best Regards, Meilleures salutations, Met vriendelijke groeten, >>>>Olivier Mascia >>>> >>>> >>>>___ >>>>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 >>___ >>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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Yes, 3.24.0 is officially released and the official release page on the sqlite.org website has been updated. I don't think I saw a release announcement either though. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Thursday, 7 June, 2018 20:19 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Hi, Keith, > >On Tue, Jun 5, 2018 at 5:09 PM, Keith Medcalf >wrote: >> >> Most of them. In particular those that return (as in SELECT) data >work either way. Those that set things can only be used as a pragma. >> >> Note that the table name is passed differently (in the case of >pragma's expecting an identifier). >> It is an identifier in the case of a pragma statement, and a string >in the case of the table valued function. >> >> pragma foreign_key_list(identifier); >> select * from pragma_foreign_key_list(stringval); >> >> ie: >> >> pragma foreign_key_list("My Table"); >> select * from foreign_key_list('My Table'); >> >> or, if you do not have stoopid characters in identifiers: >> >> pragma foreign_key_list(MyTable); >> select * from foreign_key_list('MyTable'); >> >> >> Both versions behave identically and reset/auto-reset properly for >me ... > >Here is the session with the SQLite version I'm currently developing: > >[code] >SQLite version 3.13.0 2016-05-18 10:57:30 >Enter ".help" for usage hints. >sqlite> SELECT* FROM leagues; >1|Demo - Roto (Auction)|1|1|1|demo|260|0 >2|Demo - Roto (Draft)|3|1|1|demo|260|0 >3|Demo - Points (Auction)|1|2|1|demo|260|0 >4|Demo - Points (Draft)|3|2|1|demo|260|0 >sqlite> SELECT * FROM pragma_foreign_key_list(leagues); >Error: no such table: pragma_foreign_key_list >sqlite> >[/code] > >This version is a little behind (I believe something like 4 years >old). >Now, since I'm still developing I can upgrade that version, but when >I >went to the SQLite >download page I see the SQLite 3.24 version and I don't remember the >official release >announcement being sent out by Mr. Hipp. >Is today sources/shell tool is the released version and I just missed >the announcement? > >Thank you. > >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says a lot about anticipated traffic volume. >> >> >>>-Original Message- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia >>>Sent: Tuesday, 5 June, 2018 15:35 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] Reset the cursor >>> >>>> Le 5 juin 2018 à 22:47, Igor Korot a écrit : >>>> >>>> As a side note: is it the case for all PRAGMA's command - they >can >>>be >>>> rewritten this way? >>> >>>Full documentation for that is on page >>>https://www.sqlite.org/pragma.html, see the second title ("PRAGMA >>>functions"). >>> >>>Citing in short: // PRAGMAs that return results and that have no >>>side-effects can be accessed from ordinary SELECT statements as >>>table-valued functions. For each participating PRAGMA, the >>>corresponding table-valued function has the same name as the PRAGMA >>>with a 7-character "pragma_" prefix. // >>> >>>-- >>>Best Regards, Meilleures salutations, Met vriendelijke groeten, >>>Olivier Mascia >>> >>> >>>___ >>>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 >___ >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] Reset the cursor
Hi, Keith, On Tue, Jun 5, 2018 at 5:09 PM, Keith Medcalf wrote: > > Most of them. In particular those that return (as in SELECT) data work > either way. Those that set things can only be used as a pragma. > > Note that the table name is passed differently (in the case of pragma's > expecting an identifier). > It is an identifier in the case of a pragma statement, and a string in the > case of the table valued function. > > pragma foreign_key_list(identifier); > select * from pragma_foreign_key_list(stringval); > > ie: > > pragma foreign_key_list("My Table"); > select * from foreign_key_list('My Table'); > > or, if you do not have stoopid characters in identifiers: > > pragma foreign_key_list(MyTable); > select * from foreign_key_list('MyTable'); > > > Both versions behave identically and reset/auto-reset properly for me ... Here is the session with the SQLite version I'm currently developing: [code] SQLite version 3.13.0 2016-05-18 10:57:30 Enter ".help" for usage hints. sqlite> SELECT* FROM leagues; 1|Demo - Roto (Auction)|1|1|1|demo|260|0 2|Demo - Roto (Draft)|3|1|1|demo|260|0 3|Demo - Points (Auction)|1|2|1|demo|260|0 4|Demo - Points (Draft)|3|2|1|demo|260|0 sqlite> SELECT * FROM pragma_foreign_key_list(leagues); Error: no such table: pragma_foreign_key_list sqlite> [/code] This version is a little behind (I believe something like 4 years old). Now, since I'm still developing I can upgrade that version, but when I went to the SQLite download page I see the SQLite 3.24 version and I don't remember the official release announcement being sent out by Mr. Hipp. Is today sources/shell tool is the released version and I just missed the announcement? Thank you. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia >>Sent: Tuesday, 5 June, 2018 15:35 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>> Le 5 juin 2018 à 22:47, Igor Korot a écrit : >>> >>> As a side note: is it the case for all PRAGMA's command - they can >>be >>> rewritten this way? >> >>Full documentation for that is on page >>https://www.sqlite.org/pragma.html, see the second title ("PRAGMA >>functions"). >> >>Citing in short: // PRAGMAs that return results and that have no >>side-effects can be accessed from ordinary SELECT statements as >>table-valued functions. For each participating PRAGMA, the >>corresponding table-valued function has the same name as the PRAGMA >>with a 7-character "pragma_" prefix. // >> >>-- >>Best Regards, Meilleures salutations, Met vriendelijke groeten, >>Olivier Mascia >> >> >>___ >>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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Most of them. In particular those that return (as in SELECT) data work either way. Those that set things can only be used as a pragma. Note that the table name is passed differently (in the case of pragma's expecting an identifier). It is an identifier in the case of a pragma statement, and a string in the case of the table valued function. pragma foreign_key_list(identifier); select * from pragma_foreign_key_list(stringval); ie: pragma foreign_key_list("My Table"); select * from foreign_key_list('My Table'); or, if you do not have stoopid characters in identifiers: pragma foreign_key_list(MyTable); select * from foreign_key_list('MyTable'); Both versions behave identically and reset/auto-reset properly for me ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia >Sent: Tuesday, 5 June, 2018 15:35 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >> Le 5 juin 2018 à 22:47, Igor Korot a écrit : >> >> As a side note: is it the case for all PRAGMA's command - they can >be >> rewritten this way? > >Full documentation for that is on page >https://www.sqlite.org/pragma.html, see the second title ("PRAGMA >functions"). > >Citing in short: // PRAGMAs that return results and that have no >side-effects can be accessed from ordinary SELECT statements as >table-valued functions. For each participating PRAGMA, the >corresponding table-valued function has the same name as the PRAGMA >with a 7-character "pragma_" prefix. // > >-- >Best Regards, Meilleures salutations, Met vriendelijke groeten, >Olivier Mascia > > >___ >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] Reset the cursor
> Le 5 juin 2018 à 22:47, Igor Korot a écrit : > > As a side note: is it the case for all PRAGMA's command - they can be > rewritten this way? Full documentation for that is on page https://www.sqlite.org/pragma.html, see the second title ("PRAGMA functions"). Citing in short: // PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions. For each participating PRAGMA, the corresponding table-valued function has the same name as the PRAGMA with a 7-character "pragma_" prefix. // -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Hmmm. I modified the data to CREATE TABLE test (id integer primary key); CREATE TABLE test2 (data integer not null references test); and using the statement "pragma foreign_key_list(\"test2\");" or "pragma foreign_key_list(\"test\");" both appear to work the same as a standard select, that is, they reset properly both manually and automatically -- in the former (test2) case returning 1 row and in the later (test) returning no rows (SQLITE_DONE on the first call to sqlite3_step). #include "sqlite3.h" #include void main(int argc, char** argv) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; char* rest = 0; int rc = 0; int value = 0; sqlite3_open("test.db", &db); rc = sqlite3_prepare_v2(db, "pragma foreign_key_list(\"test\");", -1, &stmt, (void*)&rest); if (rc != SQLITE_OK) { printf("Error %d during prepare\n", rc); return; } printf("\nLoop 1, no reset, reset at 5\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE | value == 5) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } printf("\nLoop 2, After Reset\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); //rc = sqlite3_reset(stmt); //printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } printf("\nLoop 3, No Reset, Got SQLITE_DONE\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } } >test Loop 1, no reset, reset at 5 ! sqlite3_reset returns 0 Loop 2, After Reset ! Loop 3, No Reset, Got SQLITE_DONE ! sqlite3_reset returns 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Tuesday, 5 June, 2018 10:20 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Hi, Keith, > >On Tue, Jun 5, 2018 at 11:04 AM, Keith Medcalf >wrote: >> >> Hmmm. I replaced the :memory: database with an on-disk database >and created a table with nothing in it. The first run returned >SQLITE_DONE immediately and the resets worked normally. Are you >using a virtual table or a non-select statement? > >No virtual table(s). > >My query is: > >std::string query = "PRAGMA foreign_key_list( \"%w\" )"; > >Then I'm doing this: > >char *y = sqlite3_mprintf( query.c_str(), tableName ); >res = sqlite3_prepare_v2( m_db, y, -1, &stmt, 0 ); > >and then the code follows. > >So are you saying that this PRAGMA is not using the SELECT >internally? > >I'm trying to workaround this by checking if the first loop return >any >records and skip second if it does not. > >Thank you. > >P.S.: Using C++ here, but the interface should be the same. > >> >> SQLite version 3.24.0 2018-06-04 19:24:41 >> Enter ".help" for usage hints. >> sqlite> delete from test; >> sqlite> .dump >> PRAGMA foreign_keys=OFF; >> BEGIN TRANSACTION; >> CREATE TABLE test (id integer primary key); >> COMMIT; >> >> Changed the C code to: >> >> sqlite3_open("test.db", &db); >> rc = sqlite3_prepare_v2(db, "select id fro
Re: [sqlite] Reset the cursor
Hi, Olivier, On Tue, Jun 5, 2018 at 3:15 PM, Olivier Mascia wrote: >> Le 5 juin 2018 à 18:19, Igor Korot a écrit : >> >> My query is: >> >> std::string query = "PRAGMA foreign_key_list( \"%w\" )"; >> >> Then I'm doing this: >> >> char *y = sqlite3_mprintf( query.c_str(), tableName ); >> res = sqlite3_prepare_v2( m_db, y, -1, &stmt, 0 ); >> >> and then the code follows. >> >> So are you saying that this PRAGMA is not using the SELECT internally? > > I haven't dig this discussion thread in details, but you could try: > > std::string query = "SELECT * FROM pragma_foreign_key_list(?)"; > > You will then be able to bind the table name after prepare without using > printf, which is good protection against code injection depending from where > your tableName value comes from and will allow you to bind new table names > and re-run without preparing the statement again. Thank you for the suggestion. I will try that. As a side note: is it the case for all PRAGMA's command - they can be rewritten this way? Thank you. > > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > ___ > 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] Reset the cursor
> Le 5 juin 2018 à 18:19, Igor Korot a écrit : > > My query is: > > std::string query = "PRAGMA foreign_key_list( \"%w\" )"; > > Then I'm doing this: > > char *y = sqlite3_mprintf( query.c_str(), tableName ); > res = sqlite3_prepare_v2( m_db, y, -1, &stmt, 0 ); > > and then the code follows. > > So are you saying that this PRAGMA is not using the SELECT internally? I haven't dig this discussion thread in details, but you could try: std::string query = "SELECT * FROM pragma_foreign_key_list(?)"; You will then be able to bind the table name after prepare without using printf, which is good protection against code injection depending from where your tableName value comes from and will allow you to bind new table names and re-run without preparing the statement again. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Hi, Keith, On Tue, Jun 5, 2018 at 11:04 AM, Keith Medcalf wrote: > > Hmmm. I replaced the :memory: database with an on-disk database and created > a table with nothing in it. The first run returned SQLITE_DONE immediately > and the resets worked normally. Are you using a virtual table or a > non-select statement? No virtual table(s). My query is: std::string query = "PRAGMA foreign_key_list( \"%w\" )"; Then I'm doing this: char *y = sqlite3_mprintf( query.c_str(), tableName ); res = sqlite3_prepare_v2( m_db, y, -1, &stmt, 0 ); and then the code follows. So are you saying that this PRAGMA is not using the SELECT internally? I'm trying to workaround this by checking if the first loop return any records and skip second if it does not. Thank you. P.S.: Using C++ here, but the interface should be the same. > > SQLite version 3.24.0 2018-06-04 19:24:41 > Enter ".help" for usage hints. > sqlite> delete from test; > sqlite> .dump > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE test (id integer primary key); > COMMIT; > > Changed the C code to: > > sqlite3_open("test.db", &db); > rc = sqlite3_prepare_v2(db, "select id from test;", -1, &stmt, > (void*)&rest); > > and running it gets: > >>test > > Loop 1, no reset, reset at 5 > ! > sqlite3_reset returns 0 > > Loop 2, After Reset > ! > > Loop 3, No Reset, Got SQLITE_DONE > ! > sqlite3_reset returns 0 > > which is what I would expect ... > > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > >>-----Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Tuesday, 5 June, 2018 08:54 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>Hi, Keith, >>I'm sorry for that but I just realized what is the difference between >>you program and mine. >> >>Can you modify the query to not return any rows and re-run your test >>program? >>Because when I run it first time the query doesn't return any rows >>and >>n the first iteration sqlite3_step() >>gives SQLITE_DONE. >> >>But I guess the next time it runs it just fails for some in-known >>reason. >>If that's not it - I guess I will have to give you the test case for >>it. >> >>Thank you and sorry for not thinking about this immediately. >> >> >>On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot >>wrote: >>> Hi, Keith, >>> >>> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf >>wrote: >>>> >>>> Perhaps. In order for the sqlite3_errcode(db) to have any meaning >>then the immediately preceding API call must have failed with an >>error (that is, returned a result other than SQLITE_OK, SQLITE_ROW, >>or SQLITE_DONE). The sqlite3_errcode is *ONLY* updated when there is >>an error (ie, the return code from an API call is not OK / ROW / >>DONE). Otherwise calling the sqlite3_errcode function will either >>return the errorcode from the most recently called failing API call >>or whatever garbage happens to be contained in that memory location. >>>> >>>> According to the documentation: >>>> >>>> "If the most recent sqlite3_* API call associated with database >>connection D failed, then the sqlite3_errcode(D) interface returns >>the numeric result code or extended result code for that API call. If >>the most recent API call was successful, then the return value from >>sqlite3_errcode() is undefined." >>>> >>>> where undefined means that the value returned has no meaning ... >>>> >>>> "If an interface fails with SQLITE_MISUSE, that means the >>interface was invoked incorrectly by the application. In that case, >>the error code and message may or may not be set." >>>> >>>> So, if and only if "rc = sqlite3_(...)" returns a code (rc) >>which is *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is >>the sqlite3_errcode meaningful with the caveat that if rc is >>SQLITE_MISUSE (23) then mayhaps yes and mayhaps no be meaningful. It >>also only returns data for the last API call on a connection. >>>> >>>> >>>> so the idea is that you get the return code of the API call ... >>>> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, >>SQLITE_DONE]) should you even look at the s
Re: [sqlite] Reset the cursor
Hmmm. I replaced the :memory: database with an on-disk database and created a table with nothing in it. The first run returned SQLITE_DONE immediately and the resets worked normally. Are you using a virtual table or a non-select statement? SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. sqlite> delete from test; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test (id integer primary key); COMMIT; Changed the C code to: sqlite3_open("test.db", &db); rc = sqlite3_prepare_v2(db, "select id from test;", -1, &stmt, (void*)&rest); and running it gets: >test Loop 1, no reset, reset at 5 ! sqlite3_reset returns 0 Loop 2, After Reset ! Loop 3, No Reset, Got SQLITE_DONE ! sqlite3_reset returns 0 which is what I would expect ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Tuesday, 5 June, 2018 08:54 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Hi, Keith, >I'm sorry for that but I just realized what is the difference between >you program and mine. > >Can you modify the query to not return any rows and re-run your test >program? >Because when I run it first time the query doesn't return any rows >and >n the first iteration sqlite3_step() >gives SQLITE_DONE. > >But I guess the next time it runs it just fails for some in-known >reason. >If that's not it - I guess I will have to give you the test case for >it. > >Thank you and sorry for not thinking about this immediately. > > >On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot >wrote: >> Hi, Keith, >> >> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf >wrote: >>> >>> Perhaps. In order for the sqlite3_errcode(db) to have any meaning >then the immediately preceding API call must have failed with an >error (that is, returned a result other than SQLITE_OK, SQLITE_ROW, >or SQLITE_DONE). The sqlite3_errcode is *ONLY* updated when there is >an error (ie, the return code from an API call is not OK / ROW / >DONE). Otherwise calling the sqlite3_errcode function will either >return the errorcode from the most recently called failing API call >or whatever garbage happens to be contained in that memory location. >>> >>> According to the documentation: >>> >>> "If the most recent sqlite3_* API call associated with database >connection D failed, then the sqlite3_errcode(D) interface returns >the numeric result code or extended result code for that API call. If >the most recent API call was successful, then the return value from >sqlite3_errcode() is undefined." >>> >>> where undefined means that the value returned has no meaning ... >>> >>> "If an interface fails with SQLITE_MISUSE, that means the >interface was invoked incorrectly by the application. In that case, >the error code and message may or may not be set." >>> >>> So, if and only if "rc = sqlite3_(...)" returns a code (rc) >which is *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is >the sqlite3_errcode meaningful with the caveat that if rc is >SQLITE_MISUSE (23) then mayhaps yes and mayhaps no be meaningful. It >also only returns data for the last API call on a connection. >>> >>> >>> so the idea is that you get the return code of the API call ... >>> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, >SQLITE_DONE]) should you even look at the sqlite3_errcode >>> if the original API return code was SQLITE_MISUSE then the result >of sqlite3_errcode may mean something and may not >> >> Removing thay call made no difference. >> I am still getting 1 on the second iteration. >> >> Thank you. >> >>> >>> >>> --- >>> The fact that there's a Highway to Hell but only a Stairway to >Heaven says a lot about anticipated traffic volume. >>> >>> >>>>-Original Message- >>>>From: sqlite-users [mailto:sqlite-users- >>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>>Sent: Monday, 4 June, 2018 12:15 >>>>To: SQLite mailing list >>>>Subject: Re: [sqlite] Reset the cursor >>>> >>>>Keith, >>>> >>>>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf > >>>>wrote: >>>>> >>>>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >&g
Re: [sqlite] Reset the cursor
Hi, Keith, I'm sorry for that but I just realized what is the difference between you program and mine. Can you modify the query to not return any rows and re-run your test program? Because when I run it first time the query doesn't return any rows and n the first iteration sqlite3_step() gives SQLITE_DONE. But I guess the next time it runs it just fails for some in-known reason. If that's not it - I guess I will have to give you the test case for it. Thank you and sorry for not thinking about this immediately. On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot wrote: > Hi, Keith, > > On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf wrote: >> >> Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the >> immediately preceding API call must have failed with an error (that is, >> returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The >> sqlite3_errcode is *ONLY* updated when there is an error (ie, the return >> code from an API call is not OK / ROW / DONE). Otherwise calling the >> sqlite3_errcode function will either return the errorcode from the most >> recently called failing API call or whatever garbage happens to be contained >> in that memory location. >> >> According to the documentation: >> >> "If the most recent sqlite3_* API call associated with database connection D >> failed, then the sqlite3_errcode(D) interface returns the numeric result >> code or extended result code for that API call. If the most recent API call >> was successful, then the return value from sqlite3_errcode() is undefined." >> >> where undefined means that the value returned has no meaning ... >> >> "If an interface fails with SQLITE_MISUSE, that means the interface was >> invoked incorrectly by the application. In that case, the error code and >> message may or may not be set." >> >> So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is >> *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the >> sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) >> then mayhaps yes and mayhaps no be meaningful. It also only returns data >> for the last API call on a connection. >> >> >> so the idea is that you get the return code of the API call ... >> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, >> SQLITE_DONE]) should you even look at the sqlite3_errcode >> if the original API return code was SQLITE_MISUSE then the result of >> sqlite3_errcode may mean something and may not > > Removing thay call made no difference. > I am still getting 1 on the second iteration. > > Thank you. > >> >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to Heaven says a >> lot about anticipated traffic volume. >> >> >>>-Original Message- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>Sent: Monday, 4 June, 2018 12:15 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] Reset the cursor >>> >>>Keith, >>> >>>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf >>>wrote: >>>> >>>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >>>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE >>>error on the 3rd loop because the statement was not reset. >>> >>>One more thing: >>> >>>Is my assumption correct that sqlite3_errcode() returning 0, indicate >>>there was no error? >>> >>>Thank you. >>> >>>> >>>> --- >>>> The fact that there's a Highway to Hell but only a Stairway to >>>Heaven says a lot about anticipated traffic volume. >>>> >>>> >>>>>-Original Message- >>>>>From: sqlite-users [mailto:sqlite-users- >>>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>>>Sent: Monday, 4 June, 2018 11:50 >>>>>To: SQLite mailing list >>>>>Subject: Re: [sqlite] Reset the cursor >>>>> >>>>>Keith, >>>>> >>>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf >>> >>>>>wrote: >>>>>> >>>>>> #include "sqlite3.h" >>>>>> #include >>>>>> >>>>>> void main(int argc, char** argv) >>>>>> { >>>>>> sqlite3* db = 0; >>>>>
Re: [sqlite] Reset the cursor
Hi, Keith, On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf wrote: > > Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the > immediately preceding API call must have failed with an error (that is, > returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The > sqlite3_errcode is *ONLY* updated when there is an error (ie, the return code > from an API call is not OK / ROW / DONE). Otherwise calling the > sqlite3_errcode function will either return the errorcode from the most > recently called failing API call or whatever garbage happens to be contained > in that memory location. > > According to the documentation: > > "If the most recent sqlite3_* API call associated with database connection D > failed, then the sqlite3_errcode(D) interface returns the numeric result code > or extended result code for that API call. If the most recent API call was > successful, then the return value from sqlite3_errcode() is undefined." > > where undefined means that the value returned has no meaning ... > > "If an interface fails with SQLITE_MISUSE, that means the interface was > invoked incorrectly by the application. In that case, the error code and > message may or may not be set." > > So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is > *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the > sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) > then mayhaps yes and mayhaps no be meaningful. It also only returns data for > the last API call on a connection. > > > so the idea is that you get the return code of the API call ... > if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, > SQLITE_DONE]) should you even look at the sqlite3_errcode > if the original API return code was SQLITE_MISUSE then the result of > sqlite3_errcode may mean something and may not Removing thay call made no difference. I am still getting 1 on the second iteration. Thank you. > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Monday, 4 June, 2018 12:15 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>Keith, >> >>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf >>wrote: >>> >>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE >>error on the 3rd loop because the statement was not reset. >> >>One more thing: >> >>Is my assumption correct that sqlite3_errcode() returning 0, indicate >>there was no error? >> >>Thank you. >> >>> >>> --- >>> The fact that there's a Highway to Hell but only a Stairway to >>Heaven says a lot about anticipated traffic volume. >>> >>> >>>>-Original Message- >>>>From: sqlite-users [mailto:sqlite-users- >>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>>Sent: Monday, 4 June, 2018 11:50 >>>>To: SQLite mailing list >>>>Subject: Re: [sqlite] Reset the cursor >>>> >>>>Keith, >>>> >>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf >> >>>>wrote: >>>>> >>>>> #include "sqlite3.h" >>>>> #include >>>>> >>>>> void main(int argc, char** argv) >>>>> { >>>>> sqlite3* db = 0; >>>>> sqlite3_stmt* stmt = 0; >>>>> char* rest = 0; >>>>> int rc = 0; >>>>> int value = 0; >>>>> sqlite3_open(":memory:", &db); >>>>> rc = sqlite3_prepare_v2(db, "select value from >>generate_series >>>>where start=1 and stop=10;", -1, &stmt, (void*)&rest); >>>>> if (rc != SQLITE_OK) >>>>> { >>>>> printf("Error %d during prepare\n", rc); >>>>> return; >>>>> } >>>>> printf("\nLoop 1, no reset, reset at 5\n"); >>>>> for (;;) >>>>> { >>>>> rc = sqlite3_step(stmt); >>>>> if (rc == SQLITE_DONE | value == 5) >>>>> { >>>>> printf("!\n"); >>>
Re: [sqlite] Reset the cursor
On 4 Jun 2018, at 8:09pm, Igor Korot wrote: > The second cycle' sqlite3_step() returns 1, but immediately calling > sqlite3_errcode() returns 0. I don't like that. You should almost never see a result code of 1. It's SQLite telling you "Something went wrong but I don't know what.". It a bad sign. And, as you point out, if you do see 1 then if you immediately do sqlite3_errcode() you should get something other than 1 from it. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
On 4 Jun 2018, at 7:15pm, Igor Korot wrote: > Is my assumption correct that sqlite3_errcode() returning 0, indicate > there was no error? Correct. It might return 0 (SQLITE_OK == "no error") 1 to 99 (primary error code) 100 (SQLITE_ROW == "here's a row of data you asked for") 101 (SQLITE_DONE == "no more rows") 200 upward (extended error code) There are a few codes which are hard to categorise (e.g. SQLITE_ABORT) but that's pretty-much it. There aren't any ranges apart from the above. So there's no way to look at the range of the code and tell whether the error was temporary (e.g. a lock) or permanent; or by parameter or content; or hardware or corruption, unless you look at the exact number. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Keith, On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf wrote: > > Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the > immediately preceding API call must have failed with an error (that is, > returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The > sqlite3_errcode is *ONLY* updated when there is an error (ie, the return code > from an API call is not OK / ROW / DONE). Otherwise calling the > sqlite3_errcode function will either return the errorcode from the most > recently called failing API call or whatever garbage happens to be contained > in that memory location. > > According to the documentation: > > "If the most recent sqlite3_* API call associated with database connection D > failed, then the sqlite3_errcode(D) interface returns the numeric result code > or extended result code for that API call. If the most recent API call was > successful, then the return value from sqlite3_errcode() is undefined." > > where undefined means that the value returned has no meaning ... > > "If an interface fails with SQLITE_MISUSE, that means the interface was > invoked incorrectly by the application. In that case, the error code and > message may or may not be set." > > So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is > *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the > sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) > then mayhaps yes and mayhaps no be meaningful. It also only returns data for > the last API call on a connection. > > > so the idea is that you get the return code of the API call ... > if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, > SQLITE_DONE]) should you even look at the sqlite3_errcode > if the original API return code was SQLITE_MISUSE then the result of > sqlite3_errcode may mean something and may not But then this is even more confusing. As I said in the beginning: The second cycle' sqlite3_step() returns 1, but immediately calling sqlite3_errcode() returns 0. I just don't understand how to interpret those results. Can you? Thank you. > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Monday, 4 June, 2018 12:15 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>Keith, >> >>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf >>wrote: >>> >>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE >>error on the 3rd loop because the statement was not reset. >> >>One more thing: >> >>Is my assumption correct that sqlite3_errcode() returning 0, indicate >>there was no error? >> >>Thank you. >> >>> >>> --- >>> The fact that there's a Highway to Hell but only a Stairway to >>Heaven says a lot about anticipated traffic volume. >>> >>> >>>>-Original Message- >>>>From: sqlite-users [mailto:sqlite-users- >>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>>Sent: Monday, 4 June, 2018 11:50 >>>>To: SQLite mailing list >>>>Subject: Re: [sqlite] Reset the cursor >>>> >>>>Keith, >>>> >>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf >> >>>>wrote: >>>>> >>>>> #include "sqlite3.h" >>>>> #include >>>>> >>>>> void main(int argc, char** argv) >>>>> { >>>>> sqlite3* db = 0; >>>>> sqlite3_stmt* stmt = 0; >>>>> char* rest = 0; >>>>> int rc = 0; >>>>> int value = 0; >>>>> sqlite3_open(":memory:", &db); >>>>> rc = sqlite3_prepare_v2(db, "select value from >>generate_series >>>>where start=1 and stop=10;", -1, &stmt, (void*)&rest); >>>>> if (rc != SQLITE_OK) >>>>> { >>>>> printf("Error %d during prepare\n", rc); >>>>> return; >>>>> } >>>>> printf("\nLoop 1, no reset, reset at 5\n"); >>>>> for (;;) >>>>> { >>>>> rc = sqlite3_step(stmt); >>
Re: [sqlite] Reset the cursor
Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the immediately preceding API call must have failed with an error (that is, returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The sqlite3_errcode is *ONLY* updated when there is an error (ie, the return code from an API call is not OK / ROW / DONE). Otherwise calling the sqlite3_errcode function will either return the errorcode from the most recently called failing API call or whatever garbage happens to be contained in that memory location. According to the documentation: "If the most recent sqlite3_* API call associated with database connection D failed, then the sqlite3_errcode(D) interface returns the numeric result code or extended result code for that API call. If the most recent API call was successful, then the return value from sqlite3_errcode() is undefined." where undefined means that the value returned has no meaning ... "If an interface fails with SQLITE_MISUSE, that means the interface was invoked incorrectly by the application. In that case, the error code and message may or may not be set." So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) then mayhaps yes and mayhaps no be meaningful. It also only returns data for the last API call on a connection. so the idea is that you get the return code of the API call ... if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, SQLITE_DONE]) should you even look at the sqlite3_errcode if the original API return code was SQLITE_MISUSE then the result of sqlite3_errcode may mean something and may not --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Monday, 4 June, 2018 12:15 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Keith, > >On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf >wrote: >> >> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE >error on the 3rd loop because the statement was not reset. > >One more thing: > >Is my assumption correct that sqlite3_errcode() returning 0, indicate >there was no error? > >Thank you. > >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says a lot about anticipated traffic volume. >> >> >>>-Original Message- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>>Sent: Monday, 4 June, 2018 11:50 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] Reset the cursor >>> >>>Keith, >>> >>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf > >>>wrote: >>>> >>>> #include "sqlite3.h" >>>> #include >>>> >>>> void main(int argc, char** argv) >>>> { >>>> sqlite3* db = 0; >>>> sqlite3_stmt* stmt = 0; >>>> char* rest = 0; >>>> int rc = 0; >>>> int value = 0; >>>> sqlite3_open(":memory:", &db); >>>> rc = sqlite3_prepare_v2(db, "select value from >generate_series >>>where start=1 and stop=10;", -1, &stmt, (void*)&rest); >>>> if (rc != SQLITE_OK) >>>> { >>>> printf("Error %d during prepare\n", rc); >>>> return; >>>> } >>>> printf("\nLoop 1, no reset, reset at 5\n"); >>>> for (;;) >>>> { >>>> rc = sqlite3_step(stmt); >>>> if (rc == SQLITE_DONE | value == 5) >>>> { >>>> printf("!\n"); >>>> rc = sqlite3_reset(stmt); >>>> printf("sqlite3_reset returns %d\n", rc); >>>> break; >>>> } >>>> if (rc == SQLITE_ROW) >>>> { >>>> value = sqlite3_column_int(stmt, 0); >>>> printf("%d ", value); >>>> continue; >>>> } >>>> printf("Error during stepping %d\n", rc); >>>> rc = sqlite3_reset(stmt); >>>> printf("sqlite3_reset
Re: [sqlite] Reset the cursor
Keith, On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf wrote: > > Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was compiled with > SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE error on the 3rd loop > because the statement was not reset. One more thing: Is my assumption correct that sqlite3_errcode() returning 0, indicate there was no error? Thank you. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Monday, 4 June, 2018 11:50 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >>Keith, >> >>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf >>wrote: >>> >>> #include "sqlite3.h" >>> #include >>> >>> void main(int argc, char** argv) >>> { >>> sqlite3* db = 0; >>> sqlite3_stmt* stmt = 0; >>> char* rest = 0; >>> int rc = 0; >>> int value = 0; >>> sqlite3_open(":memory:", &db); >>> rc = sqlite3_prepare_v2(db, "select value from generate_series >>where start=1 and stop=10;", -1, &stmt, (void*)&rest); >>> if (rc != SQLITE_OK) >>> { >>> printf("Error %d during prepare\n", rc); >>> return; >>> } >>> printf("\nLoop 1, no reset, reset at 5\n"); >>> for (;;) >>> { >>> rc = sqlite3_step(stmt); >>> if (rc == SQLITE_DONE | value == 5) >>> { >>> printf("!\n"); >>> rc = sqlite3_reset(stmt); >>> printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> if (rc == SQLITE_ROW) >>> { >>> value = sqlite3_column_int(stmt, 0); >>> printf("%d ", value); >>> continue; >>> } >>> printf("Error during stepping %d\n", rc); >>> rc = sqlite3_reset(stmt); >>> printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> printf("\nLoop 2, After Reset\n"); >>> for (;;) >>> { >>> rc = sqlite3_step(stmt); >>> if (rc == SQLITE_DONE) >>> { >>> printf("!\n"); >>> //rc = sqlite3_reset(stmt); >>> //printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> if (rc == SQLITE_ROW) >>> { >>> value = sqlite3_column_int(stmt, 0); >>> printf("%d ", value); >>> continue; >>> } >>> printf("Error during stepping %d\n", rc); >>> rc = sqlite3_reset(stmt); >>> printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> printf("\nLoop 3, No Reset, Got SQLITE_DONE\n"); >>> for (;;) >>> { >>> rc = sqlite3_step(stmt); >>> if (rc == SQLITE_DONE) >>> { >>> printf("!\n"); >>> rc = sqlite3_reset(stmt); >>> printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> if (rc == SQLITE_ROW) >>> { >>> value = sqlite3_column_int(stmt, 0); >>> printf("%d ", value); >>> continue; >>> } >>> printf("Error during stepping %d\n", rc); >>> rc = sqlite3_reset(stmt); >>> printf("sqlite3_reset returns %d\n", rc); >>> break; >>> } >>> } >>> >>> 2018-06-04 11:32:12 MinGW [D:\work] >>>>test >>> >>> Loop 1, no reset, reset at 5 >>> 1 2 3 4 5 ! >>> sqlite3_reset returns 0 >>> >>> Loop 2, After Reset >>> 1 2 3 4 5 6 7 8 9 10 ! >>> >>> Loop 3, No Reset, Got SQLITE_DONE >>> 1 2 3 4 5 6 7 8 9 10 ! >>> sqlite3_reset returns 0 >> >>I will try without this call tonight when I'm back from
Re: [sqlite] Reset the cursor
Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE error on the 3rd loop because the statement was not reset. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Monday, 4 June, 2018 11:50 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Keith, > >On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf >wrote: >> >> #include "sqlite3.h" >> #include >> >> void main(int argc, char** argv) >> { >> sqlite3* db = 0; >> sqlite3_stmt* stmt = 0; >> char* rest = 0; >> int rc = 0; >> int value = 0; >> sqlite3_open(":memory:", &db); >> rc = sqlite3_prepare_v2(db, "select value from generate_series >where start=1 and stop=10;", -1, &stmt, (void*)&rest); >> if (rc != SQLITE_OK) >> { >> printf("Error %d during prepare\n", rc); >> return; >> } >> printf("\nLoop 1, no reset, reset at 5\n"); >> for (;;) >> { >> rc = sqlite3_step(stmt); >> if (rc == SQLITE_DONE | value == 5) >> { >> printf("!\n"); >> rc = sqlite3_reset(stmt); >> printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> if (rc == SQLITE_ROW) >> { >> value = sqlite3_column_int(stmt, 0); >> printf("%d ", value); >> continue; >> } >> printf("Error during stepping %d\n", rc); >> rc = sqlite3_reset(stmt); >> printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> printf("\nLoop 2, After Reset\n"); >> for (;;) >> { >> rc = sqlite3_step(stmt); >> if (rc == SQLITE_DONE) >> { >> printf("!\n"); >> //rc = sqlite3_reset(stmt); >> //printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> if (rc == SQLITE_ROW) >> { >> value = sqlite3_column_int(stmt, 0); >> printf("%d ", value); >> continue; >> } >> printf("Error during stepping %d\n", rc); >> rc = sqlite3_reset(stmt); >> printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> printf("\nLoop 3, No Reset, Got SQLITE_DONE\n"); >> for (;;) >> { >> rc = sqlite3_step(stmt); >> if (rc == SQLITE_DONE) >> { >> printf("!\n"); >> rc = sqlite3_reset(stmt); >> printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> if (rc == SQLITE_ROW) >> { >> value = sqlite3_column_int(stmt, 0); >> printf("%d ", value); >> continue; >> } >> printf("Error during stepping %d\n", rc); >> rc = sqlite3_reset(stmt); >> printf("sqlite3_reset returns %d\n", rc); >> break; >> } >> } >> >> 2018-06-04 11:32:12 MinGW [D:\work] >>>test >> >> Loop 1, no reset, reset at 5 >> 1 2 3 4 5 ! >> sqlite3_reset returns 0 >> >> Loop 2, After Reset >> 1 2 3 4 5 6 7 8 9 10 ! >> >> Loop 3, No Reset, Got SQLITE_DONE >> 1 2 3 4 5 6 7 8 9 10 ! >> sqlite3_reset returns 0 > >I will try without this call tonight when I'm back from work and let >you know. > >But if the system have an older version of SQLite this code will >break right? > >Thank you. > >> >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says a lot about anticipated traffic volume. >> >> >>>-Original Message- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >>>Sent: Monday, 4 June, 2018 11:25 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] Reset the cursor >>> >>> >>>Note also that you d
Re: [sqlite] Reset the cursor
Keith, On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf wrote: > > #include "sqlite3.h" > #include > > void main(int argc, char** argv) > { > sqlite3* db = 0; > sqlite3_stmt* stmt = 0; > char* rest = 0; > int rc = 0; > int value = 0; > sqlite3_open(":memory:", &db); > rc = sqlite3_prepare_v2(db, "select value from generate_series where > start=1 and stop=10;", -1, &stmt, (void*)&rest); > if (rc != SQLITE_OK) > { > printf("Error %d during prepare\n", rc); > return; > } > printf("\nLoop 1, no reset, reset at 5\n"); > for (;;) > { > rc = sqlite3_step(stmt); > if (rc == SQLITE_DONE | value == 5) > { > printf("!\n"); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > if (rc == SQLITE_ROW) > { > value = sqlite3_column_int(stmt, 0); > printf("%d ", value); > continue; > } > printf("Error during stepping %d\n", rc); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > printf("\nLoop 2, After Reset\n"); > for (;;) > { > rc = sqlite3_step(stmt); > if (rc == SQLITE_DONE) > { > printf("!\n"); > //rc = sqlite3_reset(stmt); > //printf("sqlite3_reset returns %d\n", rc); > break; > } > if (rc == SQLITE_ROW) > { > value = sqlite3_column_int(stmt, 0); > printf("%d ", value); > continue; > } > printf("Error during stepping %d\n", rc); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > printf("\nLoop 3, No Reset, Got SQLITE_DONE\n"); > for (;;) > { > rc = sqlite3_step(stmt); > if (rc == SQLITE_DONE) > { > printf("!\n"); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > if (rc == SQLITE_ROW) > { > value = sqlite3_column_int(stmt, 0); > printf("%d ", value); > continue; > } > printf("Error during stepping %d\n", rc); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > } > > 2018-06-04 11:32:12 MinGW [D:\work] >>test > > Loop 1, no reset, reset at 5 > 1 2 3 4 5 ! > sqlite3_reset returns 0 > > Loop 2, After Reset > 1 2 3 4 5 6 7 8 9 10 ! > > Loop 3, No Reset, Got SQLITE_DONE > 1 2 3 4 5 6 7 8 9 10 ! > sqlite3_reset returns 0 I will try without this call tonight when I'm back from work and let you know. But if the system have an older version of SQLite this code will break right? Thank you. > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >>Sent: Monday, 4 June, 2018 11:25 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >> >>Note also that you do not need to do an sqlite3_reset after >>sqlite3_step returns SQLITE_DONE as reset is called automatically the >>next time you call sqlite3_step. You only need to call sqlite3_reset >>if you want to reset the statement before all the rows have been >>retrieved (this is documented somewhere, and I believe there is a >>compile time #define to turn off the auto-reset). Yes, it is >>documented in the sqlite3_step documentation >> >>"For all versions of SQLite up to and including 3.6.23.1, a call to >>sqlite3_reset() was required after sqlite3_step() returned anything >>other than SQLITE_ROW before any subsequent invocation of >>sqlite3_step(). Failure to reset the prepared statement using >>sqlite3_reset() would result in an SQLITE_MISUSE return from >>sqlite3_step(). But after version 3.6.23.1 (2010-03-26, >>sqlite3_step() began calling sqlite3_reset() automatically in this >>circumstance rather than returning SQLITE_MISUSE. This is not >>considered a co
Re: [sqlite] Reset the cursor
#include "sqlite3.h" #include void main(int argc, char** argv) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; char* rest = 0; int rc = 0; int value = 0; sqlite3_open(":memory:", &db); rc = sqlite3_prepare_v2(db, "select value from generate_series where start=1 and stop=10;", -1, &stmt, (void*)&rest); if (rc != SQLITE_OK) { printf("Error %d during prepare\n", rc); return; } printf("\nLoop 1, no reset, reset at 5\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE | value == 5) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } printf("\nLoop 2, After Reset\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); //rc = sqlite3_reset(stmt); //printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } printf("\nLoop 3, No Reset, Got SQLITE_DONE\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } } 2018-06-04 11:32:12 MinGW [D:\work] >test Loop 1, no reset, reset at 5 1 2 3 4 5 ! sqlite3_reset returns 0 Loop 2, After Reset 1 2 3 4 5 6 7 8 9 10 ! Loop 3, No Reset, Got SQLITE_DONE 1 2 3 4 5 6 7 8 9 10 ! sqlite3_reset returns 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Monday, 4 June, 2018 11:25 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > > >Note also that you do not need to do an sqlite3_reset after >sqlite3_step returns SQLITE_DONE as reset is called automatically the >next time you call sqlite3_step. You only need to call sqlite3_reset >if you want to reset the statement before all the rows have been >retrieved (this is documented somewhere, and I believe there is a >compile time #define to turn off the auto-reset). Yes, it is >documented in the sqlite3_step documentation > >"For all versions of SQLite up to and including 3.6.23.1, a call to >sqlite3_reset() was required after sqlite3_step() returned anything >other than SQLITE_ROW before any subsequent invocation of >sqlite3_step(). Failure to reset the prepared statement using >sqlite3_reset() would result in an SQLITE_MISUSE return from >sqlite3_step(). But after version 3.6.23.1 (2010-03-26, >sqlite3_step() began calling sqlite3_reset() automatically in this >circumstance rather than returning SQLITE_MISUSE. This is not >considered a compatibility break because any application that ever >receives an SQLITE_MISUSE error is broken by definition. The >SQLITE_OMIT_AUTORESET compile-time option can be used to restore the >legacy behavior." > >Neither the automatic nor the manual sqlite3_reset reset any bindings >-- if you want to do this I believe you must call the >sqlite3_clear_bindings() > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >>Sent: Monday, 4 June, 2018 11:06 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reset the cursor >> >> >>>Currently running w/MSVC 2010 u
Re: [sqlite] Reset the cursor
Note also that you do not need to do an sqlite3_reset after sqlite3_step returns SQLITE_DONE as reset is called automatically the next time you call sqlite3_step. You only need to call sqlite3_reset if you want to reset the statement before all the rows have been retrieved (this is documented somewhere, and I believe there is a compile time #define to turn off the auto-reset). Yes, it is documented in the sqlite3_step documentation "For all versions of SQLite up to and including 3.6.23.1, a call to sqlite3_reset() was required after sqlite3_step() returned anything other than SQLITE_ROW before any subsequent invocation of sqlite3_step(). Failure to reset the prepared statement using sqlite3_reset() would result in an SQLITE_MISUSE return from sqlite3_step(). But after version 3.6.23.1 (2010-03-26, sqlite3_step() began calling sqlite3_reset() automatically in this circumstance rather than returning SQLITE_MISUSE. This is not considered a compatibility break because any application that ever receives an SQLITE_MISUSE error is broken by definition. The SQLITE_OMIT_AUTORESET compile-time option can be used to restore the legacy behavior." Neither the automatic nor the manual sqlite3_reset reset any bindings -- if you want to do this I believe you must call the sqlite3_clear_bindings() --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Monday, 4 June, 2018 11:06 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > > >>Currently running w/MSVC 2010 under Win 8.1. > >>I also presume you are testing under the latest SQLite source? > >Yes, I believe so ... >SQLite 3.24.0 2018-06-02 19:14:58 >1ecb3aa13de5c8dc611b814ff34010de0bd90aae73d88aa37a59c4627be4alt2 > >Using GCC (MinGW-w64 8.1.0) on Windows 10 Pro for Workstations >version 1803 build 17134.81 (current) > >MSVC (the one I have, I think VS 2008) also works fine ... though the >.dll is still compiled with GCC MinGW-w64 8.1.0 (with -O3 and then >some). > >Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.21022.08 >for 80x86 >Copyright (C) Microsoft Corporation. All rights reserved. > >test.c >Microsoft (R) Incremental Linker Version 9.00.21022.08 >Copyright (C) Microsoft Corporation. All rights reserved. > >/out:test.exe >test.obj >sqlite3.lib > > >2018-06-04 10:59:24 MinGW [D:\work] >>test.exe > >Loop 1, no reset >1 2 3 4 5 6 7 8 9 10 ! >sqlite3_reset returns 0 > >Loop 2, after reset >1 2 3 4 5 6 7 8 9 10 ! >sqlite3_reset returns 0 > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > > >___ >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] Reset the cursor
>Currently running w/MSVC 2010 under Win 8.1. >I also presume you are testing under the latest SQLite source? Yes, I believe so ... SQLite 3.24.0 2018-06-02 19:14:58 1ecb3aa13de5c8dc611b814ff34010de0bd90aae73d88aa37a59c4627be4alt2 Using GCC (MinGW-w64 8.1.0) on Windows 10 Pro for Workstations version 1803 build 17134.81 (current) MSVC (the one I have, I think VS 2008) also works fine ... though the .dll is still compiled with GCC MinGW-w64 8.1.0 (with -O3 and then some). Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.21022.08 for 80x86 Copyright (C) Microsoft Corporation. All rights reserved. test.c Microsoft (R) Incremental Linker Version 9.00.21022.08 Copyright (C) Microsoft Corporation. All rights reserved. /out:test.exe test.obj sqlite3.lib 2018-06-04 10:59:24 MinGW [D:\work] >test.exe Loop 1, no reset 1 2 3 4 5 6 7 8 9 10 ! sqlite3_reset returns 0 Loop 2, after reset 1 2 3 4 5 6 7 8 9 10 ! sqlite3_reset returns 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Hi, Keith, On Mon, Jun 4, 2018 at 11:45 AM, Keith Medcalf wrote: > Works just fine for me ... > > > #include "sqlite3.h" > #include > > void main(int argc, char** argv) > { > sqlite3* db = 0; > sqlite3_stmt* stmt = 0; > char* rest = 0; > int rc = 0; > int value = 0; > sqlite3_open(":memory:", &db); > rc = sqlite3_prepare_v2(db, "select value from generate_series where > start=1 and stop=10;", -1, &stmt, (void*)&rest); > if (rc != SQLITE_OK) > { > printf("Error %d during prepare\n", rc); > return; > } > printf("\nLoop 1, no reset\n"); > for (;;) > { > rc = sqlite3_step(stmt); > if (rc == SQLITE_DONE) > { > printf("!\n"); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > if (rc == SQLITE_ROW) > { > value = sqlite3_column_int(stmt, 0); > printf("%d ", value); > continue; > } > printf("Error during stepping %d\n", rc); > break; > } > printf("\nLoop 2, after reset\n"); > for (;;) > { > rc = sqlite3_step(stmt); > if (rc == SQLITE_DONE) > { > printf("!\n"); > rc = sqlite3_reset(stmt); > printf("sqlite3_reset returns %d\n", rc); > break; > } > if (rc == SQLITE_ROW) > { > value = sqlite3_column_int(stmt, 0); > printf("%d ", value); > continue; > } > printf("Error during stepping %d\n", rc); > break; > } > } > > gcc test.c -L. -lsqlite3 -o test.exe Currently running w/MSVC 2010 under Win 8.1. I also presume you are testing under the latest SQLite source? Thank you. > > 2018-06-04 10:41:10 MinGW [D:\work] >>test > > Loop 1, no reset > 1 2 3 4 5 6 7 8 9 10 ! > sqlite3_reset returns 0 > > Loop 2, after reset > 1 2 3 4 5 6 7 8 9 10 ! > sqlite3_reset returns 0 > > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >>Sent: Sunday, 3 June, 2018 22:32 >>To: Discussion of SQLite Database; General Discussion of SQLite >>Database >>Subject: [sqlite] Reset the cursor >> >>Hi, All, >>After executing the following: >> >>int res = sqlite3_prepare_v2( ... stmt ); >>while( ; ; ) >>{ >>res = sqlite3_step( stmt ); >>if( res == SQLITE_ROW ) >>{ >>// process the record >>} >>else if( res == SQLITE_DONE ) >>break; >>else >>{ >>// error procressing >>} >>} >> >>Now I'd like the cursor in the recordset of the "stmt" to go to the >>record 1 >>so I can process those records again. >> >>I thought that this will be a job of sqlite_reset(), but when I >>called >>it and started re-processing the recordset I got SQLITE_DONE on the >>very first iteration. >> >>So, how do I reset the cursor to the first record? >> >>Thank you. >>___ >>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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Works just fine for me ... #include "sqlite3.h" #include void main(int argc, char** argv) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; char* rest = 0; int rc = 0; int value = 0; sqlite3_open(":memory:", &db); rc = sqlite3_prepare_v2(db, "select value from generate_series where start=1 and stop=10;", -1, &stmt, (void*)&rest); if (rc != SQLITE_OK) { printf("Error %d during prepare\n", rc); return; } printf("\nLoop 1, no reset\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); break; } printf("\nLoop 2, after reset\n"); for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) { printf("!\n"); rc = sqlite3_reset(stmt); printf("sqlite3_reset returns %d\n", rc); break; } if (rc == SQLITE_ROW) { value = sqlite3_column_int(stmt, 0); printf("%d ", value); continue; } printf("Error during stepping %d\n", rc); break; } } gcc test.c -L. -lsqlite3 -o test.exe 2018-06-04 10:41:10 MinGW [D:\work] >test Loop 1, no reset 1 2 3 4 5 6 7 8 9 10 ! sqlite3_reset returns 0 Loop 2, after reset 1 2 3 4 5 6 7 8 9 10 ! sqlite3_reset returns 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Sunday, 3 June, 2018 22:32 >To: Discussion of SQLite Database; General Discussion of SQLite >Database >Subject: [sqlite] Reset the cursor > >Hi, All, >After executing the following: > >int res = sqlite3_prepare_v2( ... stmt ); >while( ; ; ) >{ >res = sqlite3_step( stmt ); >if( res == SQLITE_ROW ) >{ >// process the record >} >else if( res == SQLITE_DONE ) >break; >else >{ >// error procressing >} >} > >Now I'd like the cursor in the recordset of the "stmt" to go to the >record 1 >so I can process those records again. > >I thought that this will be a job of sqlite_reset(), but when I >called >it and started re-processing the recordset I got SQLITE_DONE on the >very first iteration. > >So, how do I reset the cursor to the first record? > >Thank you. >___ >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] Reset the cursor
x, On Mon, Jun 4, 2018 at 10:54 AM, x wrote: > If the first loop exits with res3 == SQLITE_DONE then !result will be true > and the second loop should process exactly the same (assuming underlying data > is unchanged). I can’t see why the code below wouldn’t work although I’m > confused by the fact you say that sqlite3_step(stmt3) returns SQLITE_DONE > immediately after the sqlite3_reset(stmt3) but later say it’s returning 1 > (SQLITE_ERROR). Yes, first loop exits with the SQLITE_DONE. The call to sqlite3_reset() return 0 (success). But the very first call to sqlite3_step() returns 1 (error). Then the code goes to execute error handling branch where it calls sqlite3_errcode(). This function returns 0 - which I think means no error is encountered during the previous SQLite call. I can try to get an external error code or the error message though using the appropriate function. Thank you. > > > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { > // initial processing > } > else if( res3 == SQLITE_DONE ) > break; > else > { > // error handling > result = 1; > } > } > if( !result ) > { > res3 = sqlite3_reset( stmt3 ); > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { >// actual processing > } > else if( res3 == SQLITE_DONE ) >break; > else > { >// error handling > } > } > } > > Not sure where this code belongs > > if( res3 != SQLITE_DONE ) > break; > } > > ___ > 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] Reset the cursor
ooops... last answer mail was on the wrong topic. Am 04.06.2018 um 17:57 schrieb heribert: I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 04.06.2018 um 17:54 schrieb x: If the first loop exits with res3 == SQLITE_DONE then !result will be true and the second loop should process exactly the same (assuming underlying data is unchanged). I can’t see why the code below wouldn’t work although I’m confused by the fact you say that sqlite3_step(stmt3) returns SQLITE_DONE immediately after the sqlite3_reset(stmt3) but later say it’s returning 1 (SQLITE_ERROR). int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // initial processing } else if( res3 == SQLITE_DONE ) break; else { // error handling result = 1; } } if( !result ) { res3 = sqlite3_reset( stmt3 ); for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // actual processing } else if( res3 == SQLITE_DONE ) break; else { // error handling } } } Not sure where this code belongs if( res3 != SQLITE_DONE ) break; } ___ 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 04.06.2018 um 17:54 schrieb x: If the first loop exits with res3 == SQLITE_DONE then !result will be true and the second loop should process exactly the same (assuming underlying data is unchanged). I can’t see why the code below wouldn’t work although I’m confused by the fact you say that sqlite3_step(stmt3) returns SQLITE_DONE immediately after the sqlite3_reset(stmt3) but later say it’s returning 1 (SQLITE_ERROR). int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // initial processing } else if( res3 == SQLITE_DONE ) break; else { // error handling result = 1; } } if( !result ) { res3 = sqlite3_reset( stmt3 ); for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // actual processing } else if( res3 == SQLITE_DONE ) break; else { // error handling } } } Not sure where this code belongs if( res3 != SQLITE_DONE ) break; } ___ 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] Reset the cursor
If the first loop exits with res3 == SQLITE_DONE then !result will be true and the second loop should process exactly the same (assuming underlying data is unchanged). I can’t see why the code below wouldn’t work although I’m confused by the fact you say that sqlite3_step(stmt3) returns SQLITE_DONE immediately after the sqlite3_reset(stmt3) but later say it’s returning 1 (SQLITE_ERROR). int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // initial processing } else if( res3 == SQLITE_DONE ) break; else { // error handling result = 1; } } if( !result ) { res3 = sqlite3_reset( stmt3 ); for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // actual processing } else if( res3 == SQLITE_DONE ) break; else { // error handling } } } Not sure where this code belongs if( res3 != SQLITE_DONE ) break; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Sorry, I didn’t notice res3 was reassigned just before the comparison. I was confused by you setting res3 = SQLITE_OK on the first line as I can see no purpose to that. From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 3:44:57 PM To: SQLite mailing list Subject: Re: [sqlite] Reset the cursor x, On Mon, Jun 4, 2018 at 9:42 AM, x wrote: > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > > > As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK > so > > res3 == SQLITE_ROW is never true. But SQLITE_OK != SQLITE_ROW. Thank you. > > > > > > > From: sqlite-users on behalf > of Igor Korot > Sent: Monday, June 4, 2018 3:33:54 PM > To: SQLite mailing list > Subject: Re: [sqlite] Reset the cursor > > Hi, Igor, > > On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik wrote: >> On 6/4/2018 12:31 AM, Igor Korot wrote: >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record >>> 1 >>> so I can process those records again. >>> >>> I thought that this will be a job of sqlite_reset(), but when I called >>> it and started re-processing the recordset I got SQLITE_DONE on the >>> very first iteration. >> >> >> sqlite_reset definitely works. The problem must be somewhere in the code you >> haven't shown. Can you reproduce in a small complete example? > > Following the exact code taken from y source. > Can you spot an error? > > [code] > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { > // initial processing > } > else if( res3 == SQLITE_DONE ) > break; > else > { > // error handling > result = 1; > } > } > if( !result ) > { > res3 = sqlite3_reset( stmt3 ); > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { > // actual processing > } > else if( res3 == SQLITE_DONE ) > break; > else > { > // error handling > } > } > } > if( res3 != SQLITE_DONE ) > break; > }[/code] > > Thank you. > >> -- >> Igor Tandetnik >> >> >> >> ___ >> 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 > ___ > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
x, On Mon, Jun 4, 2018 at 9:42 AM, x wrote: > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > > > As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK > so > > res3 == SQLITE_ROW is never true. But SQLITE_OK != SQLITE_ROW. Thank you. > > > > > > > From: sqlite-users on behalf > of Igor Korot > Sent: Monday, June 4, 2018 3:33:54 PM > To: SQLite mailing list > Subject: Re: [sqlite] Reset the cursor > > Hi, Igor, > > On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik wrote: >> On 6/4/2018 12:31 AM, Igor Korot wrote: >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record >>> 1 >>> so I can process those records again. >>> >>> I thought that this will be a job of sqlite_reset(), but when I called >>> it and started re-processing the recordset I got SQLITE_DONE on the >>> very first iteration. >> >> >> sqlite_reset definitely works. The problem must be somewhere in the code you >> haven't shown. Can you reproduce in a small complete example? > > Following the exact code taken from y source. > Can you spot an error? > > [code] > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { > // initial processing > } > else if( res3 == SQLITE_DONE ) > break; > else > { > // error handling > result = 1; > } > } > if( !result ) > { > res3 = sqlite3_reset( stmt3 ); > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > { > // actual processing > } > else if( res3 == SQLITE_DONE ) > break; > else > { > // error handling > } > } > } > if( res3 != SQLITE_DONE ) > break; > }[/code] > > Thank you. > >> -- >> Igor Tandetnik >> >> >> >> ___ >> 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 > ___ > 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] Reset the cursor
int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK so res3 == SQLITE_ROW is never true. From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 3:33:54 PM To: SQLite mailing list Subject: Re: [sqlite] Reset the cursor Hi, Igor, On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik wrote: > On 6/4/2018 12:31 AM, Igor Korot wrote: >> >> Now I'd like the cursor in the recordset of the "stmt" to go to the record >> 1 >> so I can process those records again. >> >> I thought that this will be a job of sqlite_reset(), but when I called >> it and started re-processing the recordset I got SQLITE_DONE on the >> very first iteration. > > > sqlite_reset definitely works. The problem must be somewhere in the code you > haven't shown. Can you reproduce in a small complete example? Following the exact code taken from y source. Can you spot an error? [code] int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // initial processing } else if( res3 == SQLITE_DONE ) break; else { // error handling result = 1; } } if( !result ) { res3 = sqlite3_reset( stmt3 ); for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // actual processing } else if( res3 == SQLITE_DONE ) break; else { // error handling } } } if( res3 != SQLITE_DONE ) break; }[/code] Thank you. > -- > Igor Tandetnik > > > > ___ > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
x, On Mon, Jun 4, 2018 at 9:12 AM, x wrote: > Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW > > > > But note that a successful sqlite3_step does not return SQLITE_OK (0). Are > you maybe converting the result to Boolean? Please see the code I posted in reply to Igor's post. Thank you. > > > > > From: sqlite-users on behalf > of Igor Korot > Sent: Monday, June 4, 2018 1:52:05 PM > To: SQLite mailing list > Subject: Re: [sqlite] Reset the cursor > > Hi, > > On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot wrote: >> Hi, Clemens et al, >> >> On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: >>> Igor Korot wrote: >>>> res = sqlite3_step( stmt ); >>>> >>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record >>>> 1 >>>> so I can process those records again. >>> >>> Strictly speaking, it is not possible to go back in the _same_ cursor. >>> You'd have to execute the query again (by calling sqlite3_reset() and >>> sqlite3_step()), and if you're not in a transaction, the data might >>> have been modified between these two calls. >>> >>>> I thought that this will be a job of sqlite_reset(), but when I called >>>> it and started re-processing the recordset I got SQLITE_DONE on the >>>> very first iteration. >>> >>> In theory, executing the same query on the same data should work again. >>> Did you accidentally call sqlite3_clear_bindings()? >> >> No, I didn't clear anything. >> I just call sqlite3_reset() and sqlite3_step() and receive an error. > > In addition: > As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error). > However calling sqlite3_errcode() right after returns 0 (which is > success, right) > > Thank you. > >> >> Thank you. >> >>> >>> >>> Regards, >>> Clemens >>> ___ >>> 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 > ___ > 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] Reset the cursor
Hi, Igor, On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik wrote: > On 6/4/2018 12:31 AM, Igor Korot wrote: >> >> Now I'd like the cursor in the recordset of the "stmt" to go to the record >> 1 >> so I can process those records again. >> >> I thought that this will be a job of sqlite_reset(), but when I called >> it and started re-processing the recordset I got SQLITE_DONE on the >> very first iteration. > > > sqlite_reset definitely works. The problem must be somewhere in the code you > haven't shown. Can you reproduce in a small complete example? Following the exact code taken from y source. Can you spot an error? [code] int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // initial processing } else if( res3 == SQLITE_DONE ) break; else { // error handling result = 1; } } if( !result ) { res3 = sqlite3_reset( stmt3 ); for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) { // actual processing } else if( res3 == SQLITE_DONE ) break; else { // error handling } } } if( res3 != SQLITE_DONE ) break; }[/code] Thank you. > -- > Igor Tandetnik > > > > ___ > 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] Reset the cursor
Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW But note that a successful sqlite3_step does not return SQLITE_OK (0). Are you maybe converting the result to Boolean? From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 1:52:05 PM To: SQLite mailing list Subject: Re: [sqlite] Reset the cursor Hi, On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot wrote: > Hi, Clemens et al, > > On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> res = sqlite3_step( stmt ); >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >>> so I can process those records again. >> >> Strictly speaking, it is not possible to go back in the _same_ cursor. >> You'd have to execute the query again (by calling sqlite3_reset() and >> sqlite3_step()), and if you're not in a transaction, the data might >> have been modified between these two calls. >> >>> I thought that this will be a job of sqlite_reset(), but when I called >>> it and started re-processing the recordset I got SQLITE_DONE on the >>> very first iteration. >> >> In theory, executing the same query on the same data should work again. >> Did you accidentally call sqlite3_clear_bindings()? > > No, I didn't clear anything. > I just call sqlite3_reset() and sqlite3_step() and receive an error. In addition: As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error). However calling sqlite3_errcode() right after returns 0 (which is success, right) Thank you. > > Thank you. > >> >> >> Regards, >> Clemens >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Igor, if sqlite3_step is successful it returns SQLITE_ROW which is 1. From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 1:52:05 PM To: SQLite mailing list Subject: Re: [sqlite] Reset the cursor Hi, On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot wrote: > Hi, Clemens et al, > > On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> res = sqlite3_step( stmt ); >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >>> so I can process those records again. >> >> Strictly speaking, it is not possible to go back in the _same_ cursor. >> You'd have to execute the query again (by calling sqlite3_reset() and >> sqlite3_step()), and if you're not in a transaction, the data might >> have been modified between these two calls. >> >>> I thought that this will be a job of sqlite_reset(), but when I called >>> it and started re-processing the recordset I got SQLITE_DONE on the >>> very first iteration. >> >> In theory, executing the same query on the same data should work again. >> Did you accidentally call sqlite3_clear_bindings()? > > No, I didn't clear anything. > I just call sqlite3_reset() and sqlite3_step() and receive an error. In addition: As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error). However calling sqlite3_errcode() right after returns 0 (which is success, right) Thank you. > > Thank you. > >> >> >> Regards, >> Clemens >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
On 6/4/2018 12:31 AM, Igor Korot wrote: Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 so I can process those records again. I thought that this will be a job of sqlite_reset(), but when I called it and started re-processing the recordset I got SQLITE_DONE on the very first iteration. sqlite_reset definitely works. The problem must be somewhere in the code you haven't shown. Can you reproduce in a small complete example? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
Hi, On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot wrote: > Hi, Clemens et al, > > On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> res = sqlite3_step( stmt ); >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >>> so I can process those records again. >> >> Strictly speaking, it is not possible to go back in the _same_ cursor. >> You'd have to execute the query again (by calling sqlite3_reset() and >> sqlite3_step()), and if you're not in a transaction, the data might >> have been modified between these two calls. >> >>> I thought that this will be a job of sqlite_reset(), but when I called >>> it and started re-processing the recordset I got SQLITE_DONE on the >>> very first iteration. >> >> In theory, executing the same query on the same data should work again. >> Did you accidentally call sqlite3_clear_bindings()? > > No, I didn't clear anything. > I just call sqlite3_reset() and sqlite3_step() and receive an error. In addition: As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error). However calling sqlite3_errcode() right after returns 0 (which is success, right) Thank you. > > Thank you. > >> >> >> Regards, >> Clemens >> ___ >> 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] Reset the cursor
Hi, Clemens et al, On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: > Igor Korot wrote: >> res = sqlite3_step( stmt ); >> >> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >> so I can process those records again. > > Strictly speaking, it is not possible to go back in the _same_ cursor. > You'd have to execute the query again (by calling sqlite3_reset() and > sqlite3_step()), and if you're not in a transaction, the data might > have been modified between these two calls. > >> I thought that this will be a job of sqlite_reset(), but when I called >> it and started re-processing the recordset I got SQLITE_DONE on the >> very first iteration. > > In theory, executing the same query on the same data should work again. > Did you accidentally call sqlite3_clear_bindings()? No, I didn't clear anything. I just call sqlite3_reset() and sqlite3_step() and receive an error. Thank you. > > > Regards, > Clemens > ___ > 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] Reset the cursor
Igor Korot wrote: > res = sqlite3_step( stmt ); > > Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 > so I can process those records again. Strictly speaking, it is not possible to go back in the _same_ cursor. You'd have to execute the query again (by calling sqlite3_reset() and sqlite3_step()), and if you're not in a transaction, the data might have been modified between these two calls. > I thought that this will be a job of sqlite_reset(), but when I called > it and started re-processing the recordset I got SQLITE_DONE on the > very first iteration. In theory, executing the same query on the same data should work again. Did you accidentally call sqlite3_clear_bindings()? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reset the cursor
Hi, All, After executing the following: int res = sqlite3_prepare_v2( ... stmt ); while( ; ; ) { res = sqlite3_step( stmt ); if( res == SQLITE_ROW ) { // process the record } else if( res == SQLITE_DONE ) break; else { // error procressing } } Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 so I can process those records again. I thought that this will be a job of sqlite_reset(), but when I called it and started re-processing the recordset I got SQLITE_DONE on the very first iteration. So, how do I reset the cursor to the first record? Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users