Hi, Keith,
On Tue, Jun 5, 2018 at 11:04 AM, Keith Medcalf <kmedc...@dessus.com> 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 <ikoro...@gmail.com>
>>wrote:
>>> Hi, Keith,
>>>
>>> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf <kmedc...@dessus.com>
>>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_<api>(...)" 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
>><kmedc...@dessus.com>
>>>>>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
>>>>><kmedc...@dessus.com>
>>>>>>>wrote:
>>>>>>>>
>>>>>>>> #include "sqlite3.h"
>>>>>>>> #include <stdio.h>
>>>>>>>>
>>>>>>>> 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 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
>>>>>>>>>>1ecb3aa13de5c8dc611b814ff34010de0bd90aae73d88aa37a59c4627be4a
>>lt2
>>>>>>>>>>
>>>>>>>>>>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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> 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
>>_______________________________________________
>>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
