[sqlite] SQL logic error or missing database with Python sqlite
This appears to have been asked many times online, but none of the threads I've seen help me fix my issue. I am using SQLite through Python 2.5 or 2.7, which is the sqlite3 module. In a desktop application, every now and then, and in a fairly irreproducible way, when committing to the database I get this error: sqlite3.OperationalError: SQL logic error or missing database I thought this was a PySqlite generated error, but now I see the same error is seen with Ruby, PHP, C++ and other languages, so now it seems it is generated by SQLite itself...but I really don't know. If I try additional times in that same instance of my app being open, it gives me the same error every time. If I close the app and re-open it, it probably will not give me this error, with the same or very similar data being written in the same routines. So I know that the code as written is correct (a significant--greater than 90%?--of the time I don't see this error). In terms of what is causing this, I don't know, but I've noticed that on the occasions that this has happened my computer's RAM was pretty bogged down. I am testing all this on a 2004 laptop with a very fragmented HD with 1 GB of RAM, and I had the following processes running with this much RAM consumed: - Firefox.exe: 297,000 K (and climbing...This is FF 17) - plugin-container.exe: 51,260K. - Dropbox.exe: 30,212 K. - explorer.exe: 22,836 K - pythonw.exe: 18,432 K - svchost.exe: 16,992 K - notepad.exe: 5,532 K - and others... When there is this much RAM being used, particularly when FF is hogging it like that, my computer gets quite slow to respond to any action, such as saving a file (sometimes taking more than 10 seconds to return responsiveness). Is it possible that the SQL logic error or missing database error is due to the issue of low available RAM? Or the fragmented HD? Or something like that? (I get the impression from reading online that this is a very general error that could be due to just about anything, and as such gives just about no information). The problem is, I am currently sitting with FF over 400,000 K and I just successfully used my app, so it truly is intermittent. This error might go away if I used a newer/cleaner/more RAM computer, but I want to stress test my application for those who may be using similarly clunky computers--I want to try to avoid it even for older model computers. Any advice appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Do you know that VS2012 has known optimizer bugs?
Pavel
On Apr 6, 2013 5:01 AM, ibrahim ibrahim.a...@googlemail.com wrote:
On 05.04.2013 17:01, Dan Kennedy wrote:
On 04/05/2013 09:08 PM, Rob Collie wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/**Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return
21
too.
This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
flag is not being passed. It seems quite odd that the other code
would do the same though.
__**_
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Thanks Dan I just copied and paste his code.
correction :
returnValue = sqlite3_open_v2 (C:\\Newfolder\\testing.db, oDatabase,
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;
and to make the test just more simple :
returnValue = sqlite3_open (C:\\Newfolder\\testing.xyz, oDatabase) ;
try the different file extension could be a problem on some systems.
__**_
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 05.04.2013 14:54, Rob Collie wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Can you change the file extension into something different ? z45 as an
example. Sometimes registered filetypes create issues on windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 05.04.2013 16:47, Rob Collie wrote: sqlite3_open_v2(file://C:/Newfolder/testing.db, oDatabase, SQLITE_OPEN_CREATE, NULL); Can you try : returnValue = sqlite3_open_v2 (C:\\Newfolder\\testing.db, oDatabase, SQLITE_OPEN_CREATE, NULL) ; Use double backslash also after file:// ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 05.04.2013 17:01, Dan Kennedy wrote:
On 04/05/2013 09:08 PM, Rob Collie wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does
return 21
too.
This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
flag is not being passed. It seems quite odd that the other code
would do the same though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Thanks Dan I just copied and paste his code.
correction :
returnValue = sqlite3_open_v2 (C:\\Newfolder\\testing.db, oDatabase,
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;
and to make the test just more simple :
returnValue = sqlite3_open (C:\\Newfolder\\testing.xyz, oDatabase) ;
try the different file extension could be a problem on some systems.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm going to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Is your filename UTF8 ?
On 5 April 2013 15:02, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
It's a very odd problem. At first I was worried about character
translations between fortran and C, but the following also fails:
returnValue = sqlite3_open_v2(testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
I guess this rules out encoding too?
Perhaps it's something to do with how the library is being created? I'm
pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
project and compiling it as a static lib.
Rob.
On Fri, Apr 5, 2013 at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm going to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
no it should be ok, check the place where testing.db should be created, do
you have write right ?
On 5 April 2013 15:12, Rob Collie rob.col...@gmail.com wrote:
It's a very odd problem. At first I was worried about character
translations between fortran and C, but the following also fails:
returnValue = sqlite3_open_v2(testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
I guess this rules out encoding too?
Perhaps it's something to do with how the library is being created? I'm
pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
project and compiling it as a static lib.
Rob.
On Fri, Apr 5, 2013 at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com
wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy
fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm going
to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
SQLITE_OPEN_READWRITEhttp://sqlite.org/capi3ref.html#SQLITE_OPEN_AUTOPROXY
The database is opened for reading and writing if possible, or reading only
if the file is write protected by the operating system. In either case the
database must already exist, otherwise an error is returned.
Is it your problem ?
On 5 April 2013 15:15, Noel Frankinet noel.franki...@gmail.com wrote:
no it should be ok, check the place where testing.db should be created, do
you have write right ?
On 5 April 2013 15:12, Rob Collie rob.col...@gmail.com wrote:
It's a very odd problem. At first I was worried about character
translations between fortran and C, but the following also fails:
returnValue = sqlite3_open_v2(testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
I guess this rules out encoding too?
Perhaps it's something to do with how the library is being created? I'm
pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
project and compiling it as a static lib.
Rob.
On Fri, Apr 5, 2013 at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com
wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy
fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something
dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm going
to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 5 Apr 2013, at 1:54pm, Rob Collie rob.col...@gmail.com wrote:
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
Try specifying a full path instead of just a filename. There's a good change
that the default directory is not where you think it is.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Yeap. I've tested on the desktop, running as an admin user. I've tried the
full path, with no luck.
Should SQLITE_OPEN_READWRITE not be used with SQLITE_OPEN_CREATE? If I
remove the SQLITE_OPEN_READWRITE flag, I get 'library routine called out of
sequence' instead.
Rob.
On Fri, Apr 5, 2013 at 3:15 PM, Noel Frankinet noel.franki...@gmail.comwrote:
no it should be ok, check the place where testing.db should be created, do
you have write right ?
On 5 April 2013 15:12, Rob Collie rob.col...@gmail.com wrote:
It's a very odd problem. At first I was worried about character
translations between fortran and C, but the following also fails:
returnValue = sqlite3_open_v2(testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
I guess this rules out encoding too?
Perhaps it's something to do with how the library is being created? I'm
pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
project and compiling it as a static lib.
Rob.
On Fri, Apr 5, 2013 at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com
wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy
fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something
dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm going
to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Sqlitge3_close() might be your problem that's masking the real error.
You can't close what never got opened.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
I thought that, as soon as I replied. Shifting the message above the
now-rem'd close (and changing the error box title to double-check the
library is up-to-date) still gets the same error.
Very puzzling. As a student programmer, I should probably be taking notes.
On Fri, Apr 5, 2013 at 3:28 PM, Michael Black mdblac...@yahoo.com wrote:
Sqlitge3_close() might be your problem that's masking the real error.
You can't close what never got opened.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
something odd, check you compilation setup, are you on windows with visual
studio ? create a small console sample
On 5 April 2013 15:27, Rob Collie rob.col...@gmail.com wrote:
Yeap. I've tested on the desktop, running as an admin user. I've tried the
full path, with no luck.
Should SQLITE_OPEN_READWRITE not be used with SQLITE_OPEN_CREATE? If I
remove the SQLITE_OPEN_READWRITE flag, I get 'library routine called out of
sequence' instead.
Rob.
On Fri, Apr 5, 2013 at 3:15 PM, Noel Frankinet noel.franki...@gmail.com
wrote:
no it should be ok, check the place where testing.db should be created,
do
you have write right ?
On 5 April 2013 15:12, Rob Collie rob.col...@gmail.com wrote:
It's a very odd problem. At first I was worried about character
translations between fortran and C, but the following also fails:
returnValue = sqlite3_open_v2(testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
I guess this rules out encoding too?
Perhaps it's something to do with how the library is being created? I'm
pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
project and compiling it as a static lib.
Rob.
On Fri, Apr 5, 2013 at 3:03 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp d...@sqlite.org wrote:
On Fri, Apr 5, 2013 at 8:54 AM, Rob Collie rob.col...@gmail.com
wrote:
Hello there,
For my sins, I'm trying to create a library allowing our legacy
fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL
Logic
error
or missing database'. No file is ever created. Is there something
dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
I think you want just oDatabase, without the prefix operator.
No. Scratch that. I misread the code. Ignore what I said. I'm
going
to
get coffee now.
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open
Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Also change the last arg of open to NULL instead of .
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return 21
too.
On Fri, Apr 5, 2013 at 3:36 PM, Michael Black mdblac...@yahoo.com wrote:
Also change the last arg of open to NULL instead of .
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error, MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
I would remove the file://
On 5 April 2013 16:08, Rob Collie rob.col...@gmail.com wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return 21
too.
On Fri, Apr 5, 2013 at 3:36 PM, Michael Black mdblac...@yahoo.com wrote:
Also change the last arg of open to NULL instead of .
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database
Hello there,
For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.
Calling this from fortran...
CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic
error
or missing database'. No file is ever created. Is there something dumb
I'm
missing here?
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Checking the incoming data from FORTRAN
CStringW wName(dataBase);
MessageBoxW( NULL, wName, LName: , MB_OK );
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(dataBase, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, );
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open Error,
MB_OK);
return;
}
Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return 21
too.
Three(3) forward slashes for the Internet path style in Windows, maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Same problem, I'm afraid. I've tried just about every combination suggested
in http://www.sqlite.org/c3ref/open.html
On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson kevin.m.ben...@gmail.comwrote:
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return
21
too.
Three(3) forward slashes for the Internet path style in Windows, maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
it's time to check your compiler setting, anything weird ? Are you sure you
have the source code of sqlite for windows ? 32 or 64 bits settings ??
On 5 April 2013 16:24, Rob Collie rob.col...@gmail.com wrote:
Same problem, I'm afraid. I've tried just about every combination suggested
in http://www.sqlite.org/c3ref/open.html
On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson kevin.m.ben...@gmail.com
wrote:
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com
wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does
return
21
too.
Three(3) forward slashes for the Internet path style in Windows, maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
This works for me under Visual Studio 2010. I couldn't seem to get a file
uri to work at all either.
#include stdio.h
#include sqlite3.h
main()
{
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(D:/SQlite/testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
printf(%d: %s\n,returnValue,sqlite3_errmsg(oDatabase));
}
else {
printf(Got it\n);
}
}
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database
Same problem, I'm afraid. I've tried just about every combination suggested
in http://www.sqlite.org/c3ref/open.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
yes that's the setup I use too, so I suspect something more complicated at
work (mismatch between h and c file ? check your include path ??)
On 5 April 2013 16:29, Michael Black mdblac...@yahoo.com wrote:
This works for me under Visual Studio 2010. I couldn't seem to get a file
uri to work at all either.
#include stdio.h
#include sqlite3.h
main()
{
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(D:/SQlite/testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
printf(%d: %s\n,returnValue,sqlite3_errmsg(oDatabase));
}
else {
printf(Got it\n);
}
}
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database
Same problem, I'm afraid. I've tried just about every combination suggested
in http://www.sqlite.org/c3ref/open.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Nothing seems to be overly weird, and the console app is a fresh project,
with very little changed.
I'm compiling under 32-bit in VS2012 using the amalgamation files. I can
attach my test project if needed. I'm really rather curious to know what I
broke.
On Fri, Apr 5, 2013 at 4:28 PM, Noel Frankinet noel.franki...@gmail.comwrote:
it's time to check your compiler setting, anything weird ? Are you sure you
have the source code of sqlite for windows ? 32 or 64 bits settings ??
On 5 April 2013 16:24, Rob Collie rob.col...@gmail.com wrote:
Same problem, I'm afraid. I've tried just about every combination
suggested
in http://www.sqlite.org/c3ref/open.html
On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson kevin.m.ben...@gmail.com
wrote:
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com
wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does
return
21
too.
Three(3) forward slashes for the Internet path style in Windows, maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 5 Apr 2013, at 14:12, Rob Collie wrote: I'm pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++ project and compiling it as a static lib. I don't really know anything about Windows, but this looks a bit different to how I do it on Linux. I think you should only include one of sqlite.h and sqlite3ext.h, depending whether the code is being built as part of an sqlite extension. I'm not sure if including both may have some strange effects. Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
I've tried with just the one .h, it's the same result for both. On Fri, Apr 5, 2013 at 4:52 PM, Kevin Martin ke...@khn.org.uk wrote: On 5 Apr 2013, at 14:12, Rob Collie wrote: I'm pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++ project and compiling it as a static lib. I don't really know anything about Windows, but this looks a bit different to how I do it on Linux. I think you should only include one of sqlite.h and sqlite3ext.h, depending whether the code is being built as part of an sqlite extension. I'm not sure if including both may have some strange effects. Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
On 04/05/2013 09:08 PM, Rob Collie wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return 21
too.
This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
flag is not being passed. It seems quite odd that the other code
would do the same though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
How about just posting your COMPLETE code example for your console app.
Some of us can't import a VS2012 project
I just sent you a complete example that works. Are you saying this doesn't
work for you?
#include stdio.h
#include sqlite3.h
main()
{
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(D:/SQlite/testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
printf(%d: %s\n,returnValue,sqlite3_errmsg(oDatabase));
}
else {
printf(Got it\n);
}
}
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database
Nothing seems to be overly weird, and the console app is a fresh project,
with very little changed.
I'm compiling under 32-bit in VS2012 using the amalgamation files. I can
attach my test project if needed. I'm really rather curious to know what I
broke.
On Fri, Apr 5, 2013 at 4:28 PM, Noel Frankinet
noel.franki...@gmail.comwrote:
it's time to check your compiler setting, anything weird ? Are you sure
you
have the source code of sqlite for windows ? 32 or 64 bits settings ??
On 5 April 2013 16:24, Rob Collie rob.col...@gmail.com wrote:
Same problem, I'm afraid. I've tried just about every combination
suggested
in http://www.sqlite.org/c3ref/open.html
On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson kevin.m.ben...@gmail.com
wrote:
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com
wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does
return
21
too.
Three(3) forward slashes for the Internet path style in Windows,
maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
As Dan said, the console app needs the read/write flag. The other app appears
to be using CStringW, but the api takes a const char *, not a wide char
pointer. I'd try CStringA and explicitly cast to LPCSTR.
Michael Stephenson
On Apr 5, 2013, at 11:01 AM, Dan Kennedy danielk1...@gmail.com wrote:
On 04/05/2013 09:08 PM, Rob Collie wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does return 21
too.
This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
flag is not being passed. It seems quite odd that the other code
would do the same though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Logic error or missing database
Originally, the project was created with _tmain, rather than main. Despite
the character set being set to 'Not Set', changing it to main fixed the
problem in the console app. It now returns with '0', so thanks! I've not
seen _tmain before, and presume it's just a macro.
So I wonder whether the library is still using the wrong character set,
despite being set to 'Not set' too. I've moved the project to Visual Studio
2010, and it works better now.
The database is now being created fine, but with the same error on the Exec
statement. However, I need to check the data being passed from Fortran to
this library, as I strongly suspect the problem now is down to the
character encoding (I believe the data needs to be changed slightly to have
a null terminator as the last character).
#include sqlite3.h
#include windows.h
#include atlstr.h
#include atlbase.h
extern C
{
void EXECUTESQL(char *dataBase, char *query, int returnValue)
{
// Create the object
sqlite3 *oDatabase;
// Create the error objects
char *sErrorMessage;
// Open/create the table, if required
returnValue = sqlite3_open_v2(C:/Newfolder/testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
CString t;
t.Format(_T(%d), returnValue);
MessageBoxA(NULL, t, Error, MB_OK);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Open4 Error, MB_OK);
return;
}
// Run the query
returnValue = sqlite3_exec(oDatabase, query, 0, 0, sErrorMessage);
if (returnValue != SQLITE_OK )
{
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), SQL Exec Error, MB_OK);
return;
}
// Finish up
sqlite3_close(oDatabase);
return;
}
}
Once again, thanks for the help. It was an odd issue. I'll compare the
projects and command line options between the old VS2012 project, and the
VS2010 project created today.
And I'll get checking the data being sent from fortran.
Rob.
On Fri, Apr 5, 2013 at 5:02 PM, Michael Black mdblac...@yahoo.com wrote:
How about just posting your COMPLETE code example for your console app.
Some of us can't import a VS2012 project
I just sent you a complete example that works. Are you saying this doesn't
work for you?
#include stdio.h
#include sqlite3.h
main()
{
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(D:/SQlite/testing.db, oDatabase,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
printf(%d: %s\n,returnValue,sqlite3_errmsg(oDatabase));
}
else {
printf(Got it\n);
}
}
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database
Nothing seems to be overly weird, and the console app is a fresh project,
with very little changed.
I'm compiling under 32-bit in VS2012 using the amalgamation files. I can
attach my test project if needed. I'm really rather curious to know what I
broke.
On Fri, Apr 5, 2013 at 4:28 PM, Noel Frankinet
noel.franki...@gmail.comwrote:
it's time to check your compiler setting, anything weird ? Are you sure
you
have the source code of sqlite for windows ? 32 or 64 bits settings ??
On 5 April 2013 16:24, Rob Collie rob.col...@gmail.com wrote:
Same problem, I'm afraid. I've tried just about every combination
suggested
in http://www.sqlite.org/c3ref/open.html
On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson kevin.m.ben...@gmail.com
wrote:
On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie rob.col...@gmail.com
wrote:
Yeap, I'm on Visual Studio 2012. I've created a console app:
sqlite3 *oDatabase;
int returnValue;
returnValue = sqlite3_open_v2(file://C:/Newfolder/testing.db,
oDatabase, SQLITE_OPEN_CREATE, NULL);
if (returnValue != SQLITE_OK )
{
//sqlite3_close(oDatabase);
return returnValue ;
}
int anyKey;
return 0;
It returns 21. Checking the other project, the open actually does
return
21
too.
Three(3) forward slashes for the Internet path style in Windows,
maybe?
file:///C:/Newfolder/testing.db
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080
Re: [sqlite] SQL logic error or missing database
On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche ger...@datamundi.be wrote: All, hoping you can help me. I bumped into an SQL logic error or missing database error and I don't have a clue why this happens. It happens on the first action I take in a series of all the same actions (updates of a table). I removed the triggers, changed the indexes, checked integrity... still this error. the strange thing is that when I copy the command that triggers this error message to the clipboard and execute it at that time on the DB, the error does not occur. Does anyone have an idea what might cause this Hard to tell without looking at the actual code. Perhaps the path/filename in your _open() statement is not correct (does not point to the same file as the command line does), or the open flags/URI arguments conflict with the databasefile. or how I can detect why this is happening? Check the return values of all API statements, like _open_*(), _prepare_*(), _bind_*(), _step(), to pinpoint where an error first occurs. And as usual: Reduce your code to the minimum needed to reproduce the problem and post it here. thanks gert -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Simon, I'm using LUA. I have permissions and I'm using the short string of the full path. I'm now investigating something Kees mentioned: Perhaps the path/filename in your _open() statement is not correct (does not point to the same file as the command line does), or the open flags/URI arguments conflict with the databasefile. This tool is opening the db via a lua socket but for some actions I have to use the command line exe (like import export data -- I don't know how to do this in the luasqlite framework -- or it is a lot of work to parse the tables). I'm not an educated developer, just learned through experience. No doubt I will learn a lot from this strange error. 2012/10/24 Simon Slavin slav...@bigfraud.org On 24 Oct 2012, at 2:53pm, Gert Van Assche ger...@datamundi.be wrote: I bumped into an SQL logic error or missing database error and I don't have a clue why this happens. What language/API are you programming in ? Are you opening the file with permissions which allow it to be created if it doesn't already exist ? Are you specifying the full file path for the file or are you assuming it will be in some default folder ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
On 24 Oct 2012, at 3:42pm, Gert Van Assche ger...@datamundi.be wrote: I have permissions and I'm using the short string of the full path. Just for testing, try specifying a full path and see what happens. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Kees, thanks. I reopened the connection and all is OK now. thanks for your help, gert 2012/10/24 Kees Nuyt k.n...@zonnet.nl On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche ger...@datamundi.be wrote: All, hoping you can help me. I bumped into an SQL logic error or missing database error and I don't have a clue why this happens. It happens on the first action I take in a series of all the same actions (updates of a table). I removed the triggers, changed the indexes, checked integrity... still this error. the strange thing is that when I copy the command that triggers this error message to the clipboard and execute it at that time on the DB, the error does not occur. Does anyone have an idea what might cause this Hard to tell without looking at the actual code. Perhaps the path/filename in your _open() statement is not correct (does not point to the same file as the command line does), or the open flags/URI arguments conflict with the databasefile. or how I can detect why this is happening? Check the return values of all API statements, like _open_*(), _prepare_*(), _bind_*(), _step(), to pinpoint where an error first occurs. And as usual: Reduce your code to the minimum needed to reproduce the problem and post it here. thanks gert -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database error
I get the following error while opening the database after a system reboot. SQL logic error or missing database The problem does not happen with the subsequent open with a new process. Is there any reason why we get these errors? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database error
Reinstall SQLite. On Thu, Aug 16, 2012 at 5:46 PM, Rittick Gupta ritt...@yahoo.com wrote: SQL logic error or missing database ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database error
Why would it work with the subseqyent open if there was a problem with the install ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
At the end of the loop, instead of having 4000 rows I have 3976 rows (it's random, sometimes I have 3972 or 3974). sqlite3_exec doesn't returns any error during the INSERT statement, but I have some errors during the BEGIN IMMEDIATE, errors are all: SQL logic error or missing database (printed with sqlite3_errmsg). Your use of sqlite3_errmsg is itself very likely a race. Between the time you detect an error and the time you retrieve error message, the other thread could have run some statements that modify the error message. Moreover, between the time you call sqlite3_errmsg and the time you actually print the string pointed to by the char* pointer the function returns, the string may be modified or even deallocated. Igor Tandetnik Is it safe to use sqlite3_errmsg() when each thread is using its own connection handle to access the database and get the error messages for that connection? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Alex Ousherovitch alex.ousherovi...@opentv.com wrote: Is it safe to use sqlite3_errmsg() when each thread is using its own connection handle Yes. Each connection allocates its own memory for this string. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database
I have two threads that are writing 2000 rows each to the same
database at the same time.
I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
Each client executes this code (pseudo C code):
void write (sqlite3 *db) {
int i;
for (i=1; i=2000; i++) {
if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, BEGIN
IMMEDIATE;, ...);
sqlite3_exec(db, INSERT INTO, ...);
if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
COMMIT;, ...);
}
}
and db is shared between the two clients.
At the end of the loop, instead of having 4000 rows I have 3976 rows
(it's random, sometimes I have 3972 or 3974).
sqlite3_exec doesn't returns any error during the INSERT statement,
but I have some errors during the BEGIN IMMEDIATE, errors are all:
SQL logic error or missing database (printed with sqlite3_errmsg).
Any explanation of the possible causes for the missed rows?
Thanks.
--
Marco Bambini
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Marco Bambini ma...@sqlabs.net wrote
in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net
I have two threads that are writing 2000 rows each to the same
database at the same time.
I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
Each client executes this code (pseudo C code):
void write (sqlite3 *db) {
int i;
for (i=1; i=2000; i++) {
if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, BEGIN
IMMEDIATE;, ...);
sqlite3_exec(db, INSERT INTO, ...);
if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db, COMMIT;, ...);
}
}
and db is shared between the two clients.
You have a race condition here: between the calls to
sqlite3_get_autocommit and sqlite3_exec, the other thread could very
well have issued a BEGIN or a COMMIT of its own. Between the time you
check the condition and the time you act on it, the condition could have
changed.
Besides, the documentation on sqlite3_get_autocommit has this sentence:
If another thread changes the autocommit status of the database
connection while this routine is running, then the return value is
undefined. In other words, sqlite3_get_autocommit is explicitly not
thread-safe.
Since you only run one INSERT per transaction anyway, why do you feel
you need explicit BEGIN and COMMIT?
At the end of the loop, instead of having 4000 rows I have 3976 rows
(it's random, sometimes I have 3972 or 3974).
sqlite3_exec doesn't returns any error during the INSERT statement,
but I have some errors during the BEGIN IMMEDIATE, errors are all:
SQL logic error or missing database (printed with sqlite3_errmsg).
Your use of sqlite3_errmsg is itself very likely a race. Between the
time you detect an error and the time you retrieve error message, the
other thread could have run some statements that modify the error
message. Moreover, between the time you call sqlite3_errmsg and the time
you actually print the string pointed to by the char* pointer the
function returns, the string may be modified or even deallocated.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Thanks a lot for the explanation Igor.
--
Marco Bambini
On May 14, 2009, at 1:55 PM, Igor Tandetnik wrote:
Marco Bambini ma...@sqlabs.net wrote
in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net
I have two threads that are writing 2000 rows each to the same
database at the same time.
I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
Each client executes this code (pseudo C code):
void write (sqlite3 *db) {
int i;
for (i=1; i=2000; i++) {
if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, BEGIN
IMMEDIATE;, ...);
sqlite3_exec(db, INSERT INTO, ...);
if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
COMMIT;, ...);
}
}
and db is shared between the two clients.
You have a race condition here: between the calls to
sqlite3_get_autocommit and sqlite3_exec, the other thread could very
well have issued a BEGIN or a COMMIT of its own. Between the time you
check the condition and the time you act on it, the condition could
have
changed.
Besides, the documentation on sqlite3_get_autocommit has this
sentence:
If another thread changes the autocommit status of the database
connection while this routine is running, then the return value is
undefined. In other words, sqlite3_get_autocommit is explicitly not
thread-safe.
Since you only run one INSERT per transaction anyway, why do you feel
you need explicit BEGIN and COMMIT?
At the end of the loop, instead of having 4000 rows I have 3976 rows
(it's random, sometimes I have 3972 or 3974).
sqlite3_exec doesn't returns any error during the INSERT statement,
but I have some errors during the BEGIN IMMEDIATE, errors are all:
SQL logic error or missing database (printed with sqlite3_errmsg).
Your use of sqlite3_errmsg is itself very likely a race. Between the
time you detect an error and the time you retrieve error message, the
other thread could have run some statements that modify the error
message. Moreover, between the time you call sqlite3_errmsg and the
time
you actually print the string pointed to by the char* pointer the
function returns, the string may be modified or even deallocated.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
I'm back again. I could not ignore such a strong suggestion of not using thread. So I am planning on a single threaded event driven model for my application. I also plan to use sqlite3_progress_handler() to handle query cancellation. I compiled SQLite with the flags SQLITE_THREADSAFE=0. Even then, do I need to link my application with pthread library? I get linking errors like: sqlite3.c:(.text+0x1c737): undefined reference to 'pthread_mutex_trylock' ... Thanks, dbikash D. Richard Hipp wrote: On Nov 3, 2008, at 9:54 AM, Ken wrote: No I would not wrap the querries in a transaction. I think the problem you are encountering is due to thread interaction upon the sqlite structures. Since it was compiled with THREADsafety disabled. If you have two threads that share the same connection. You need to compile with THREADSAFE turned on. Or provide your own mutexing such that neither thread interacts with the sqlite connection concurrently. Note: when SQLITE_THREADSAFE=0 then it is not safe for two threads to call SQLite under any circumstances, even if they are using completely separate database connections. My advice is that you not use threads. Threads are evil. But, recognizing that you are unlikely to heed this warning, at the very least compile with SQLITE_THREADSAFE=1 if you really think you must use threads. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20355543.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
Many Thanks Ken for your guidelines. Following your suggestions I found that indeed there was a silly mistake in the querying thread (I was using a 2-D array of queries, and I missed a comma :-( ). After correcting this mistake, the two threads ran fine when run one at a time. When run together, I did not get any error message in the query thread. However, I did receive the SQL logic error or missing database message (error code 1) during an insertion after a random number of successful insertions and queries. This was accompanied by a seg fault. After many futile tries, I replaced the call to sqlite_prepare by sqlite_prepare_v2. And it seems to work! The program is running for 10 minutes now. What could have gone wrong with prepare? Another question, should I wrap each individual query (not inserts) within a transaction too? Thanks again for all the help. dbikash ken-33 wrote: I think you should consider running the program with one thread at a time. This will help you isolate if this is a problem within a thread or is an interaction among the threads. I'll assume its both. After you fix all of the localized issues. I'd suggest recompiling sqlite with thread safe turned on. Allow each thread to create its own connection. Then Wrap each transaction with a begin immediate. That way you can acquire a lock when needed. Use a loop around the begin immediate testing for success. If you fail with sqlite busy. Simply sleep a short while (hint usleep) loop and try again. HTH Ken --- On Fri, 10/31/08, dbikash [EMAIL PROTECTED] wrote: From: dbikash [EMAIL PROTECTED] Subject: Re: [sqlite] SQL logic error or missing database with multithreaded program To: sqlite-users@sqlite.org Date: Friday, October 31, 2008, 10:24 AM I use both int sqlite3_errcode() and sqlite3_errmsg(). Here is what they return (I get 3 different types of errors in different runs): 1, SQL logic error or missing database. 1, unrecognized token (there's a non printable character like a rectangle within the quotes). 1, SQL logic error or missing database, sqlite3_get_table() called with two or more incompatible queries (this last print is obtained from zErrMsg returned when the return value from sqlite3_get_table() is not SQLITE_OK) Also, the program is written to print an error and then continue in the infinite loop. I can find a few errors in query execution, but it is after an error in insertion (sqlite3_step() != SQLITE_DONE) that the program quits by itself. D. Richard Hipp wrote: On Oct 31, 2008, at 9:46 AM, dbikash wrote: I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? Is that the error message text that SQLite returns: SQL logic error or missing database? Or is that just the meaning of SQLITE_ERROR that you looked up? What error message does sqlite3_errmsg(db) return? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20302902.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
No I would not wrap the querries in a transaction. I think the problem you are encountering is due to thread interaction upon the sqlite structures. Since it was compiled with THREADsafety disabled. If you have two threads that share the same connection. You need to compile with THREADSAFE turned on. Or provide your own mutexing such that neither thread interacts with the sqlite connection concurrently. Steps to take from here: 1. re compile with thread saftey turned on. 2. Each thread should create its own connection independently of the other. 3. Perform test for sqlite busy when starting transactions and the prepare and first calls to step (ie for selects). Hope that helps --- On Mon, 11/3/08, dbikash [EMAIL PROTECTED] wrote: From: dbikash [EMAIL PROTECTED] Subject: Re: [sqlite] SQL logic error or missing database with multithreaded program To: sqlite-users@sqlite.org Date: Monday, November 3, 2008, 7:39 AM Many Thanks Ken for your guidelines. Following your suggestions I found that indeed there was a silly mistake in the querying thread (I was using a 2-D array of queries, and I missed a comma :-( ). After correcting this mistake, the two threads ran fine when run one at a time. When run together, I did not get any error message in the query thread. However, I did receive the SQL logic error or missing database message (error code 1) during an insertion after a random number of successful insertions and queries. This was accompanied by a seg fault. After many futile tries, I replaced the call to sqlite_prepare by sqlite_prepare_v2. And it seems to work! The program is running for 10 minutes now. What could have gone wrong with prepare? Another question, should I wrap each individual query (not inserts) within a transaction too? Thanks again for all the help. dbikash ken-33 wrote: I think you should consider running the program with one thread at a time. This will help you isolate if this is a problem within a thread or is an interaction among the threads. I'll assume its both. After you fix all of the localized issues. I'd suggest recompiling sqlite with thread safe turned on. Allow each thread to create its own connection. Then Wrap each transaction with a begin immediate. That way you can acquire a lock when needed. Use a loop around the begin immediate testing for success. If you fail with sqlite busy. Simply sleep a short while (hint usleep) loop and try again. HTH Ken --- On Fri, 10/31/08, dbikash [EMAIL PROTECTED] wrote: From: dbikash [EMAIL PROTECTED] Subject: Re: [sqlite] SQL logic error or missing database with multithreaded program To: sqlite-users@sqlite.org Date: Friday, October 31, 2008, 10:24 AM I use both int sqlite3_errcode() and sqlite3_errmsg(). Here is what they return (I get 3 different types of errors in different runs): 1, SQL logic error or missing database. 1, unrecognized token (there's a non printable character like a rectangle within the quotes). 1, SQL logic error or missing database, sqlite3_get_table() called with two or more incompatible queries (this last print is obtained from zErrMsg returned when the return value from sqlite3_get_table() is not SQLITE_OK) Also, the program is written to print an error and then continue in the infinite loop. I can find a few errors in query execution, but it is after an error in insertion (sqlite3_step() != SQLITE_DONE) that the program quits by itself. D. Richard Hipp wrote: On Oct 31, 2008, at 9:46 AM, dbikash wrote: I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? Is that the error message text that SQLite returns: SQL logic error or missing database? Or is that just the meaning of SQLITE_ERROR that you looked up? What error message does sqlite3_errmsg(db) return? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20302902.html Sent from the SQLite
Re: [sqlite] SQL logic error or missing database with multithreaded program
On Nov 3, 2008, at 9:54 AM, Ken wrote: No I would not wrap the querries in a transaction. I think the problem you are encountering is due to thread interaction upon the sqlite structures. Since it was compiled with THREADsafety disabled. If you have two threads that share the same connection. You need to compile with THREADSAFE turned on. Or provide your own mutexing such that neither thread interacts with the sqlite connection concurrently. Note: when SQLITE_THREADSAFE=0 then it is not safe for two threads to call SQLite under any circumstances, even if they are using completely separate database connections. My advice is that you not use threads. Threads are evil. But, recognizing that you are unlikely to heed this warning, at the very least compile with SQLITE_THREADSAFE=1 if you really think you must use threads. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database with multithreaded program
Hello, I am writing a program using SQLite that uses two threads performing the following operations in an infinite loop: - Thread 1 inserts 500 records to the database every 30 seconds and then goes to sleep. (I prepare an insert statement, BEGIN TRANSACTION, bind values, step through the statement, reset the statement, and do END TRANSACTION). - Thread 2 makes one of 10 pre-defined queries to the database every 5 seconds (using sqlite3_get_table()) and then goes to sleep. This runs ok for some time, but then I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? In one run, I encountered the error: sqlite3_get_table() called with two or more incompatible queries. The same sqlite* database connection is used in both the threads. SQLite is compiled with the -DSQLITE_ALLOW_XTHREAD_CONNECT option; no SQLITE_THREADSAFE compile time option is provided so I guess the threading mode is serialized. Thanks in advance for your help. dbikash -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20266281.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
dbikash [EMAIL PROTECTED] writes: Hello, ... - Thread 1 inserts 500 records to the database every 30 seconds and then goes to sleep. (I prepare an insert statement, BEGIN TRANSACTION, bind values, step through the statement, reset the statement, and do END TRANSACTION) This runs ok for some time, but then I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? ... Thanks in advance for your help. dbikash Surely your BEGIN TRANSACTION goes /before/ you prepare() the INSERT ? (Or does it not matter as long as it's before the step() ?) Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
I prepare the statement once. But I use transaction for every bunch of 500 record inserts. mikewhit wrote: dbikash [EMAIL PROTECTED] writes: Hello, ... - Thread 1 inserts 500 records to the database every 30 seconds and then goes to sleep. (I prepare an insert statement, BEGIN TRANSACTION, bind values, step through the statement, reset the statement, and do END TRANSACTION) This runs ok for some time, but then I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? ... Thanks in advance for your help. dbikash Surely your BEGIN TRANSACTION goes /before/ you prepare() the INSERT ? (Or does it not matter as long as it's before the step() ?) Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20266828.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
On Oct 31, 2008, at 9:46 AM, dbikash wrote: I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? Is that the error message text that SQLite returns: SQL logic error or missing database? Or is that just the meaning of SQLITE_ERROR that you looked up? What error message does sqlite3_errmsg(db) return? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
I use both int sqlite3_errcode() and sqlite3_errmsg(). Here is what they return (I get 3 different types of errors in different runs): 1, SQL logic error or missing database. 1, unrecognized token (there's a non printable character like a rectangle within the quotes). 1, SQL logic error or missing database, sqlite3_get_table() called with two or more incompatible queries (this last print is obtained from zErrMsg returned when the return value from sqlite3_get_table() is not SQLITE_OK) Also, the program is written to print an error and then continue in the infinite loop. I can find a few errors in query execution, but it is after an error in insertion (sqlite3_step() != SQLITE_DONE) that the program quits by itself. D. Richard Hipp wrote: On Oct 31, 2008, at 9:46 AM, dbikash wrote: I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? Is that the error message text that SQLite returns: SQL logic error or missing database? Or is that just the meaning of SQLITE_ERROR that you looked up? What error message does sqlite3_errmsg(db) return? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database with multithreaded program
I think you should consider running the program with one thread at a time. This will help you isolate if this is a problem within a thread or is an interaction among the threads. I'll assume its both. After you fix all of the localized issues. I'd suggest recompiling sqlite with thread safe turned on. Allow each thread to create its own connection. Then Wrap each transaction with a begin immediate. That way you can acquire a lock when needed. Use a loop around the begin immediate testing for success. If you fail with sqlite busy. Simply sleep a short while (hint usleep) loop and try again. HTH Ken --- On Fri, 10/31/08, dbikash [EMAIL PROTECTED] wrote: From: dbikash [EMAIL PROTECTED] Subject: Re: [sqlite] SQL logic error or missing database with multithreaded program To: sqlite-users@sqlite.org Date: Friday, October 31, 2008, 10:24 AM I use both int sqlite3_errcode() and sqlite3_errmsg(). Here is what they return (I get 3 different types of errors in different runs): 1, SQL logic error or missing database. 1, unrecognized token (there's a non printable character like a rectangle within the quotes). 1, SQL logic error or missing database, sqlite3_get_table() called with two or more incompatible queries (this last print is obtained from zErrMsg returned when the return value from sqlite3_get_table() is not SQLITE_OK) Also, the program is written to print an error and then continue in the infinite loop. I can find a few errors in query execution, but it is after an error in insertion (sqlite3_step() != SQLITE_DONE) that the program quits by itself. D. Richard Hipp wrote: On Oct 31, 2008, at 9:46 AM, dbikash wrote: I get a SQLite error 1 (SQL logic error or missing database). What could be the problem? Is that the error message text that SQLite returns: SQL logic error or missing database? Or is that just the meaning of SQLITE_ERROR that you looked up? What error message does sqlite3_errmsg(db) return? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database in version 3.5.6(Bug???)
This is SQLite version 3 database file. I checked header. And I can read it with version 3.3.5 dll. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, March 18, 2008 5:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL logic error or missing database in version 3.5.6(Bug???) Steve Topov [EMAIL PROTECTED] wrote: Hello, Recently I upgraded SQLite to version 3.5.6 and discovered that my program can't work anymore with some database files. Sqlite3_open returns OK, but when I am trying to execute any SQL statement it returns SQL logic error or missing database. For example sqlite3_prepare returns 1 instead of 0. Same for sqlite3_execute. I do not think it is my code that causes the problem because I can just switch SQLite dll from version 3.5.6 to version 3.3.5 and everything works fine. I do not know the version of SQLite the database file in question was created with. Few database files created with version 3.3.5 does not have this problem. I tried to upgrade to the version 3.5.7 - same result. SQLite versions 3.5.6 and 3.5.7 are suppose to be able to read and write any database written by any prior version of SQLite going back to version 3.0.0. Perhaps you have a version 2 database file? If you database file is named XYZ.db, what does this command show you: od -c XYZ.db | head -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database in version 3.5.6 (Bug???)
Steve Topov [EMAIL PROTECTED] wrote: Hello, Recently I upgraded SQLite to version 3.5.6 and discovered that my program cant work anymore with some database files. Sqlite3_open returns OK, but when I am trying to execute any SQL statement it returns SQL logic error or missing database. For example sqlite3_prepare returns 1 instead of 0. Same for sqlite3_execute. I do not think it is my code that causes the problem because I can just switch SQLite dll from version 3.5.6 to version 3.3.5 and everything works fine. I do not know the version of SQLite the database file in question was created with. Few database files created with version 3.3.5 does not have this problem. I tried to upgrade to the version 3.5.7 same result. SQLite versions 3.5.6 and 3.5.7 are suppose to be able to read and write any database written by any prior version of SQLite going back to version 3.0.0. Perhaps you have a version 2 database file? If you database file is named XYZ.db, what does this command show you: od -c XYZ.db | head -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database
Hey all,
i've an SQLite 3 database coming from a mobile phone (e2831) which
works well on the phone itself :
BEGIN TRANSACTION;
CREATE TABLE devicephonebook( UID INTEGER PRIMARY KEY
AUTOINCREMENT ,lastName TEXT NOT NULL DEFAULT(''),firstName TEXT
NOT NULL DEFAULT(''), lastChar INTEGER , fullLastName TEXT ,
ZYFLName TEXT , PYFLName TEXT , hasPhoto INTEGER DEFAULT(0) ,
timeStamp TEXT);
CREATE INDEX nameIndex ON devicephonebook (PYFLName, lastName,
firstName, lastChar, hasPhoto);
CREATE TRIGGER DELETE_EXT_TRIGGER AFTER DELETE ON devicephonebook
BEGIN DELETE FROM extraphonebook WHERE extraphonebook.UID =
OLD.UID; SELECT delete_from_cache(OLD.UID); END;
CREATE TRIGGER INSERT_PBK_TRIGGER AFTER INSERT ON devicephonebook
BEGIN UPDATE devicephonebook SET fullLastName=NEW.lastName||
NEW.firstName ,ZYFLName=get_zy_string(NEW.lastName||
NEW.firstName) ,PYFLName=get_py_string(NEW.lastName||
NEW.firstName) ,lastChar=get_first_char(NEW.lastName|| NEW.firstName)
WHERE UID=NEW.UID; END;
COMMIT;
if i want to insert a value like that :
INSERT INTO devicephonebook ( lastName, firstName ) VALUES( 'Smith',
'John' );
i get an error :
SQL logic error or missing database
Obviously i've implemented the custom functions get_zy_string,
get_py_string and get_first_char, those are returning the correct type.
if i insert all the needed values, such as :
INSERT INTO devicephonebook ( lastName, firstName, lastChar,
fullLastName, ZYFLName, PYFLName, hasPhoto, timeStamp ) VALUES
( 'Smith','John', 20, '', '', '', 0, 'dimanche' );
clearly, i don't understand this error.
in case you have some light...
best,
Yvon
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQL logic error or missing database
Actually, the multiple connections are created from different threads. But
those threads did not access the database in the same moment when the
problem occured, so I assumed (correctly) that the bug happens also when the
connections are done from the same thread. And this is how I created the
script that demonstrated the problem.
Currently the problem is fixed according to the guidelines I got from all of
you. This is how I now step:
Have a prepared statement.
while (true) {
try to step the prepared statement.
If failed to step {
reset the prepared statement to get the correct error.
if the error is not SQLITE_SCHEMA {
deal with the error and break from the loop.
}
if the error is SQLITE_SCHEMA {
finalize the prepared statement.
prepare the statement again.
continue in the loop (so step again with the newly prepared
statement).
}
}
if the step was successful - break from the loop and continue as usuall
after a successful
step.
}
I write it here because I could not find an explanation like this in the
documentation (did I miss it somewhere?). Obviously, if anyone still finds
mistakes above, I will be happy if those mistakes are explained.
In addition, I think it might be helpful for others to add the comment that
sqlite3_errmsg() does not return the correct text till sqlite3_reset() or
sqlite3_finalize() are called.
Thanks again,
Ran
On 5/23/06, John Stanton [EMAIL PROTECTED] wrote:
Why do you connect twice to the DB? You then run into synchronization
issues.
JS
[sqlite] SQL logic error or missing database
Hi all,
Could someone help me with the script below? I get an SQL logic error or
missing database and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.
What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the database.
4. Create table foo in a transaction using the second connection.
5. Try to delete from the table bla using the first connection. == this
gives SQL logic error or missing database.
Here is how it runs (without arguments, the second connection is not opened,
and the table foo is not created - and this runs OK):
./bug
Opened the database.
deleted all from bla successfully
./bug 1
Opened the database.
Opened the database.
Failed to step statement: SQL logic error or missing database
Here is the script:
// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include stdio.h
#include stdlib.h
#include unistd.h
#include sqlite3.h
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc) {
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside a transaction.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the transaction.
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, commit, 0, 0, 0);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_finalize(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
}
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Ran [EMAIL PROTECTED] wrote:
Hi all,
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the transaction.
You forgot the reset here:
int sqlite3_reset(sqlite3_stmt *pStmt);
http://sqlite.org/capi3ref.html#sqlite3_reset
Why are you preparing this statement?
Just sqlite3_exec() it.
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
You still have a valid handle to the open database. Why create a second one?
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, commit, 0, 0, 0);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_finalize(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
}
Again, why prepare something that returns no results and will
not be used more than once?
Re: [sqlite] SQL logic error or missing database
Thanks for your answer.
Actually, I tried to have resets there although I think that finalize is as
good in releasing the locks on tables. It didn't help.
I do it with prepare and step because this script is a demo of a bug I have
in my code, where I use all over a certain function that prepare and step.
It is true that here I could use also exec.
However, if I change the script to have the first two create statements run
by sqlite3_exec, and change the last statement to a select (to justify the
use of prepare) I still get the SQL logic error or missing database. So I
suspect that the problem is because I use two open connections to the
database (again, this is a demo of the bug which happen in much bigger
application where opening two connections to the same database make sense),
and somehow when I create a second table with the second connection it
locks/invalidate or whatever the first connection and I get this error. I
even checked the code with valgrind, but got nothing...
Any help will be appreciated.
Ran
On 5/23/06, Jay Sprenkle [EMAIL PROTECTED] wrote:
On 5/23/06, Ran [EMAIL PROTECTED] wrote:
Hi all,
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the
transaction.
You forgot the reset here:
int sqlite3_reset(sqlite3_stmt *pStmt);
http://sqlite.org/capi3ref.html#sqlite3_reset
Why are you preparing this statement?
Just sqlite3_exec() it.
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same
database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
You still have a valid handle to the open database. Why create a second
one?
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, commit, 0, 0, 0);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_finalize(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
}
Again, why prepare something that returns no results and will
not be used more than once?
Re: [sqlite] SQL logic error or missing database
hmmm...
it gives a schema changed because 'delete * from x' actually drops the table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
program output:
Opened the database.
Opened the database.
Failed to step statement: database schema has changed
finalize failed: database schema has changed
program code:
#include stdio.h
#include stdlib.h
//#include unistd.h
#include sqlite3.h
int main(int argc, char** argv)
{
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc)
{
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside a transaction.
rc = sqlite3_exec(db1, begin, 0, 0, 0);
if (rc != SQLITE_OK)
{
printf(begin failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK)
{
printf(prepare failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE)
{ // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
if (rc != SQLITE_OK)
{
printf(finalize failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the transaction.
if (rc != SQLITE_OK)
{
printf(commit failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
printf(Opened the database.\n);
// create table foo
rc = sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
if (rc != SQLITE_OK)
{
printf(begin failed: %s\n, sqlite3_errmsg(db2));
exit(1);
}
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused portion
if (rc != SQLITE_OK)
{
printf(prepare failed: %s\n, sqlite3_errmsg(db2));
exit(1);
}
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
if (rc != SQLITE_OK)
{
printf(finalize failed: %s\n, sqlite3_errmsg(db2));
exit(1);
}
sqlite3_exec(db2, commit, 0, 0, 0);
if (rc != SQLITE_OK)
{
printf(commit failed: %s\n, sqlite3_errmsg(db2));
exit(1);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
if (rc != SQLITE_OK)
{
printf(begin failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK)
{
printf(prepare failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_finalize(pStmt3);
if (rc != SQLITE_OK)
{
printf(finalize failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
sqlite3_exec(db1, commit, 0, 0, 0);
if (rc != SQLITE_OK)
{
printf(commit failed: %s\n, sqlite3_errmsg(db1));
exit(1);
}
return 0;
}
Re: [sqlite] SQL logic error or missing database
I don't see any reason why this should not work. I can reproduce it with vc6. You should ask DRH if he can explain what's up.
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Jay Sprenkle [EMAIL PROTECTED] wrote:
hmmm...
it gives a schema changed because 'delete * from x' actually drops the
table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
...
Thanks for your efforts!
I am afraid that the delete is not connected to the problem. Also the
prepare/step is not. Even the reset/finalize are not connected to it. The
script below gives the problem, while all the create statements are run by
sqlite3_exec (so no reset/finalize are used there at all). The problem
happens only when stepping the select. See the updated script below:
// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include stdio.h
#include stdlib.h
#include unistd.h
#include sqlite3.h
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc) {
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside a transaction.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_exec(db1, create table bla(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db1));
}
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the transaction.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_exec(db2, create table foo(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db2));
}
sqlite3_exec(db2, commit, 0, 0, 0);
}
// select from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
select * from bla,
-1, // Length of the statement
pStmt3,// OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK) {
printf(Failed to prepare statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_reset(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
rc = sqlite3_finalize(pStmt3);
}
RE: [sqlite] SQL logic error or missing database
-Original Message-
From: Ran [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 6:38 AM
To: sqlite-users
Subject: [sqlite] SQL logic error or missing database
[snip]
Here is the script:
[snip]
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to
unused portion
// of the statement
rc = sqlite3_step(pStmt1);
[snip]
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the
statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
[snip]
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to
unused portion
// of the statement
rc = sqlite3_step(pStmt3);
[snip]
I strongly suspect the bug is related to you not checking the return code
from sqlite3_prepare(). I believe during the course of execution one of
those prepares is returning a SQLITE_SCHEMA error, at which point you need
to retry the statement. Something like this works:
// Try 3 times to prepare the statement
int n = 0;
while (n 3) {
rc = sqlite3_prepare(db1,
delete from bla,
-1,
pStmt3,
0);
if (rc != SQLITE_SCHEMA) break;
n ++;
}
if (rc) {
// blah blah
exit(1);
}
//
Robert
RE: [sqlite] SQL logic error or missing database
-Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 8:13 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL logic error or missing database hmmm... it gives a schema changed because 'delete * from x' actually drops the table but I'm not sure why it gave an error since the prepare was done after the other change was committed... sqlite3_prepare() will return a SQLITE_SCHEMA error if SQLite detects the connection's schema is outdated. There's arguments for and against transparently handling it in prepare. I figured DRH's logic behind returning the error is maintaining consistency. If some schema change errors were transparently handled and some were not, then a programmer interested in capturing all schema change events would not be able to do so. Robert
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Ran [EMAIL PROTECTED] wrote: Thanks for your replies. Actually, in my last email (probably you got it after sending yours), there is a script which exec the create statements, and check the prepare returned value of a SELECT (and not delete). Still I get the same bug. So the problem must be in other place. I checked all the return codes in the version I posted as well.
RE: [sqlite] SQL logic error or missing database
-Original Message-
From: Ran [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 8:37 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL logic error or missing database
On 5/23/06, Jay Sprenkle [EMAIL PROTECTED] wrote:
hmmm...
it gives a schema changed because 'delete * from x'
actually drops the
table
but I'm not sure why it gave an error since the prepare was
done after
the other change was committed...
...
Thanks for your efforts!
I am afraid that the delete is not connected to the problem.
Also the prepare/step is not. Even the reset/finalize are not
connected to it. The script below gives the problem, while
all the create statements are run by sqlite3_exec (so no
reset/finalize are used there at all). The problem happens
only when stepping the select. See the updated script below:
I pasted your code into my Windows environment and ran it. It completed
successfully with no errors.
// compile with: gcc -g bug.cpp -lsqlite3 -o bug #include
stdio.h #include stdlib.h #include unistd.h #include sqlite3.h
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a
new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc) {
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside
a transaction.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_exec(db1, create table bla(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db1));
}
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the
transaction.
// here we, optionally, create another connection to the
same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second
connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_exec(db2, create table foo(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db2));
}
sqlite3_exec(db2, commit, 0, 0, 0);
}
// select from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
select * from bla,
-1, // Length of the statement
pStmt3,// OUT: Statement handle
0); // OUT: Pointer to
unused portion
// of the statement
if (rc != SQLITE_OK) {
printf(Failed to prepare statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_reset(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
rc = sqlite3_finalize(pStmt3);
}
RE: [sqlite] SQL logic error or missing database
-Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 9:53 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL logic error or missing database On 5/23/06, Ran [EMAIL PROTECTED] wrote: Thanks for your replies. Actually, in my last email (probably you got it after sending yours), there is a script which exec the create statements, and check the prepare returned value of a SELECT (and not delete). Still I get the same bug. So the problem must be in other place. I checked all the return codes in the version I posted as well.=0 I'm always late to the party :(
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote: I pasted your code into my Windows environment and ran it. It completed successfully with no errors. which version of sqlite are you using?
Re: [sqlite] SQL logic error or missing database
Oh! Did you run it with a parameter so:
./bug 1
(otherwise the second connection is not created - sorry, I guess this is
confusing - I just wanted to show that the second connection or the second
table create the problem).
In any case, I reinstalled sqlite-3.3.4 on my Linux - and the bug is still
there. In addition, the bug was found originally on Windows (the same
version of sqlite), so I suspect it should be also there.
Ran
On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote:
-Original Message-
From: Ran [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 8:37 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL logic error or missing database
On 5/23/06, Jay Sprenkle [EMAIL PROTECTED] wrote:
hmmm...
it gives a schema changed because 'delete * from x'
actually drops the
table
but I'm not sure why it gave an error since the prepare was
done after
the other change was committed...
...
Thanks for your efforts!
I am afraid that the delete is not connected to the problem.
Also the prepare/step is not. Even the reset/finalize are not
connected to it. The script below gives the problem, while
all the create statements are run by sqlite3_exec (so no
reset/finalize are used there at all). The problem happens
only when stepping the select. See the updated script below:
I pasted your code into my Windows environment and ran it. It completed
successfully with no errors.
// compile with: gcc -g bug.cpp -lsqlite3 -o bug #include
stdio.h #include stdlib.h #include unistd.h #include sqlite3.h
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a
new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc) {
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside
a transaction.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_exec(db1, create table bla(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db1));
}
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the
transaction.
// here we, optionally, create another connection to the
same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second
connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_exec(db2, create table foo(c int,d int), 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf(Failed to exec statement: %s\n, sqlite3_errmsg(db2));
}
sqlite3_exec(db2, commit, 0, 0, 0);
}
// select from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
select * from bla,
-1, // Length of the statement
pStmt3,// OUT: Statement handle
0); // OUT: Pointer to
unused portion
// of the statement
if (rc != SQLITE_OK) {
printf(Failed to prepare statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_reset(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
rc = sqlite3_finalize(pStmt3);
}
RE: [sqlite] SQL logic error or missing database
-Original Message-
From: Ran [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 10:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL logic error or missing database
Oh! Did you run it with a parameter so:
./bug 1
No I missed that little gem. After I passed in a parameter, it failed.
However, the failure is due to a SQLITE_SCHEMA error. (I am using 3.3.5)
I modified the code slightly to catch the schema error:
rc = sqlite3_prepare(db1,// Database handle
select * from bla,
-1, // Length of the statement
pStmt3,// OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK) {
printf(Failed to prepare statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_step(pStmt3);
if (rc) rc = sqlite3_reset(pStmt3);
if (rc == SQLITE_SCHEMA) {
rc = sqlite3_finalize(pStmt3);
// Todo: re-prepare the statement according to the FAQ at
// http://www.sqlite.org/faq.html#q17
}
After sqlite3_step() fails, you should call sqlite3_reset() on the
statement. This is what will give you the SQLITE_SCHEMA error, indicating
you need to re-prepare your statement.
Robert
RE: [sqlite] SQL logic error or missing database
-Original Message-
From: Robert Simpson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 10:30 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQL logic error or missing database
[snip]
rc = sqlite3_step(pStmt3);
if (rc) rc = sqlite3_reset(pStmt3);
if (rc == SQLITE_SCHEMA) {
rc = sqlite3_finalize(pStmt3);
// Todo: re-prepare the statement according to the FAQ at
// http://www.sqlite.org/faq.html#q17
}
Bah the above code isn't right. Check for 100 and 101 after the first
sqlite3_step() before checking for an error rc code.
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote: After sqlite3_step() fails, you should call sqlite3_reset() on the statement. This is what will give you the SQLITE_SCHEMA error, indicating you need to re-prepare your statement. That doesn't seem right. The change was made and committed then the database statement prepared. The change should have already been written so the prepare should have gotten the latest stuff. Does it need to be closed and reopened for a schema change to be recognized?
RE: [sqlite] SQL logic error or missing database
-Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 10:55 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL logic error or missing database On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote: After sqlite3_step() fails, you should call sqlite3_reset() on the statement. This is what will give you the SQLITE_SCHEMA error, indicating you need to re-prepare your statement. That doesn't seem right. The change was made and committed then the database statement prepared. The change should have already been written so the prepare should have gotten the latest stuff. Does it need to be closed and reopened for a schema change to be recognized?= Beats me, but that's the way it seems to work. You should always reset() a statement after a failed step(), and check the reset()'s error code to see if it's SQLITE_SCHEMA. If it is, you should re-prepare the statement. I don't think it matters when the statement was prepare()'d as long as you follow that rule. Robert
Re: [sqlite] SQL logic error or missing database
On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote: That doesn't seem right. The change was made and committed then the database statement prepared. The change should have already been written so the prepare should have gotten the latest stuff. Does it need to be closed and reopened for a schema change to be recognized?= Beats me, but that's the way it seems to work. You should always reset() a statement after a failed step(), and check the reset()'s error code to see if it's SQLITE_SCHEMA. If it is, you should re-prepare the statement. I don't think it matters when the statement was prepare()'d as long as you follow that rule. It was prepared after the schema was changed and written to disk. That seems pretty unintuitive to me. When you prepare the statement it evidently doesn't use the current schema, though it knows the schema has changed... I guess that implies the call to reset() reloads the schema I'll check that
Re: [sqlite] SQL logic error or missing database
Indeed if I reset after the first step failed, and than prepare again, the select works. But I guess this is not the usual way to do things right? I mean - shouldn't the first prepare be aware of the fact that the database was changed? Or maybe CREATE TABLE is a special case? On 5/23/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 5/23/06, Robert Simpson [EMAIL PROTECTED] wrote: That doesn't seem right. The change was made and committed then the database statement prepared. The change should have already been written so the prepare should have gotten the latest stuff. Does it need to be closed and reopened for a schema change to be recognized?= Beats me, but that's the way it seems to work. You should always reset() a statement after a failed step(), and check the reset()'s error code to see if it's SQLITE_SCHEMA. If it is, you should re-prepare the statement. I don't think it matters when the statement was prepare()'d as long as you follow that rule. It was prepared after the schema was changed and written to disk. That seems pretty unintuitive to me. When you prepare the statement it evidently doesn't use the current schema, though it knows the schema has changed... I guess that implies the call to reset() reloads the schema I'll check that
RE: [sqlite] SQL logic error or missing database
-Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 11:27 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL logic error or missing database It was prepared after the schema was changed and written to disk. That seems pretty unintuitive to me. When you prepare the statement it evidently doesn't use the current schema, though it knows the schema has changed... I guess that implies the call to reset() reloads the schema I'll check that=0 I changed the select statement to read select * from foo which was the new table created on the 2nd connection. When I did that, sqlite3_prepare() returned immediately with SQLITE_SCHEMA. So it appears that the schema is only reloaded on prepare() under certain conditions. Immediately calling prepare() again after getting SQLITE_SCHEMA above resulted in a properly prepared statement that step()'d normally. Robert
Re: [sqlite] SQL logic error or missing database
Ran wrote: Indeed if I reset after the first step failed, and than prepare again, the select works. But I guess this is not the usual way to do things right? I mean - shouldn't the first prepare be aware of the fact that the database was changed? Or maybe CREATE TABLE is a special case? If I were to hazzard a guess, its because there was a second handle opened to the database that modified the schema which the first handle doesn't know about until it does its sanity check before operating on the database. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases. http://www.mtsprofessional.com/ A Win32 email server that works for You.
RE: [sqlite] SQL logic error or missing database
-Original Message- From: Ran [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 11:47 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL logic error or missing database Indeed if I reset after the first step failed, and than prepare again, the select works. But I guess this is not the usual way to do things right? I mean - shouldn't the first prepare be aware of the fact that the database was changed? Or maybe CREATE TABLE is a special case? I suspect that in order to save time, prepare() will not check to see if the schema has changed unless the SQL fails to compile. If the SQL fails to compile, it'll go ahead and check the schema and report the schema failure if there is one. Since your SQL didn't fail to compile, it didn't bother checking to see if the schema had expired. The overhead of checking for expired schema on every prepare would be prohibitive, I would think. Robert
Re: [sqlite] SQL logic error or missing database
Ran [EMAIL PROTECTED] wrote:
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
The sqlite3_errmsg() API does not return the correct error
message text until after you do sqlite3_reset() or
sqlite3_finalize(). So if you change the above to read:
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we show it.
sqlite3_reset(pStmt3); // This line inserted
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
Then the program will give you the correct error message:
database schema has changed
Finalize your statement and rebuild it by a second call to
sqlite3_prepare() and it should work.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] SQL logic error or missing database
Ran [EMAIL PROTECTED] wrote: Indeed if I reset after the first step failed, and than prepare again, the select works. But I guess this is not the usual way to do things right? I mean - shouldn't the first prepare be aware of the fact that the database was changed? Or maybe CREATE TABLE is a special case? Because the schema change happened on a separate database connection, the other connection does not realize that the schema has changed until it accesses the disk again. And no disk access occurs until you run sqlite3_step(). Thus, even though the schema change occurred temporally before the sqlite3_prepare(), the sqlite3_prepare() had no way of knowing about the schema change and used the old schema. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] SQL logic error or missing database
Why do you connect twice to the DB? You then run into synchronization
issues.
JS
Ran wrote:
Hi all,
Could someone help me with the script below? I get an SQL logic error or
missing database and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.
What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the database.
4. Create table foo in a transaction using the second connection.
5. Try to delete from the table bla using the first connection. == this
gives SQL logic error or missing database.
Here is how it runs (without arguments, the second connection is not
opened,
and the table foo is not created - and this runs OK):
./bug
Opened the database.
deleted all from bla successfully
./bug 1
Opened the database.
Opened the database.
Failed to step statement: SQL logic error or missing database
Here is the script:
// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include stdio.h
#include stdlib.h
#include unistd.h
#include sqlite3.h
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;
unlink(bug.db); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open(bug.db, db1);
if (rc) {
printf(Cannot open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
printf(Opened the database.\n);
// create table bla using the first connection db1, inside a transaction.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
create table bla(a int,b int),
-1, // Length of the statement
pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
sqlite3_exec(db1, commit, 0, 0, 0); // here we commit the transaction.
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc 1) {
rc = sqlite3_open(bug.db, db2); // create the second connection.
if (rc) {
printf(Cannot open database again: %s\n, sqlite3_errmsg(db2));
exit(1);
}
else {
printf(Opened the database.\n);
}
// create table foo
sqlite3_exec(db2, begin, 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
create table foo(c int,d int),
-1, // Length of the statement
pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, commit, 0, 0, 0);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, begin, 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
delete from bla,
-1, // Length of the statement
pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf(Failed to step statement: %s\n, sqlite3_errmsg(db1));
}
else {
printf(deleted all from bla successfully\n);
}
rc = sqlite3_finalize(pStmt3);
sqlite3_exec(db1, commit, 0, 0, 0);
}
