Re: [sqlite] SQL logic error or missing database no such table

[R.A. Nagy]

We only use .commit when we are using transactions: If any type of "BEGIN
TRANSACTION;" is underway, a failure to "COMMIT;" data will guarantee that
data will not be in a database. Interestingly however, even when not
committed our queries will still work - our data will simply not be
available in the next session if we do not .commit our changes.

FWIW: No matter our language-binding technology, I always advise folks to
use a .close operation when provided for any type of statements, as well as
connections. While we can often get away with not doing so, from a cross
'tech point of view - and from JDBC to those .NET bindings - using .close
it is simply a good thing to get used to doing.





On Mon, May 23, 2016 at 2:21 PM, Rajendra Shirhatti 
wrote:

> Yes, I'm very positive the data is stored otherwise the application would
> throw some exception.
> The database is encrypted by calling passing a private key to the
> connection using ChangePassword().
>
> Thank you, Raj
>
> On Mon, May 23, 2016 at 9:16 AM, R.A. Nagy  wrote:
>
> > Are we sure that data are being stored at-all?
> >
> > Also, how is the database being encrypted?
> >
> >
> > On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti <
> rrshirha...@gmail.com
> > >
> > wrote:
> >
> > > Hi Randall,
> > > Thank you so much for your reply.
> > > I don't have enough data to confirm whether database file is missing or
> > > it's due to some other reason.
> > > According to my initial investigation, it looks like the application is
> > > able to create database and perform all the operations successfully
> upon
> > > service start. I'm leaning towards the possibility that the tables are
> > > somehow getting deleted or the database file becomes inaccessible when
> > the
> > > service is RESTARTED. We open the database connection during service
> > > startup and it stays alive as long as service is running.
> > > Is there any scenario that could corrupt the database because the
> > > connection was not closed successfully?
> > >
> > > Thank you once again.
> > >
> > > -Raj
> > >
> > > On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy  wrote:
> > >
> > > > Try:
> > > >
> > > > .schema [tablename]
> > > >
> > > > Since there is probably nothing there, you probably need to create a
> > > table.
> > > >
> > > > If you are new to SQL / SQLite, then here are some helpful videos:
> > > >
> > > >
> > https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
> > > >
> > > >
> > > > Cheers,
> > > >
> > > > -Randall Nagy
> > > > President, Soft9000.com
> > > >
> > > >
> > > >
> > > >
> > > > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
> > > rrshirha...@gmail.com
> > > > >
> > > > wrote:
> > > >
> > > > > Hi,
> > > > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
> > > > > operations. The SQLite package contains two binaries,
> > > > > system.data.sqlite.dll and sqlite.interop.dll.
> > > > >
> > > > > The database is encrypted and the blob it contains is encrypted as
> > > well.
> > > > > SQLite is intermittently throwing following exceptions while
> > retrieving
> > > > > data from the database.
> > > > >
> > > > >
> > > > >- *SQL logic error or missing database no such table*.
> > > > >
> > > > >  The code is trying to execute a simple query which looks something
> > > like
> > > > > this:
> > > > >  SELECT x, y from [tablename]
> > > > >
> > > > > This exception is raised while executing
> > SQLiteCommand.ExecuteReader().
> > > > The
> > > > > stack trace points to
> > > > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
> > > > strSql,
> > > > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
> > > > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
> > > > > at System.Data.SQLite.SQLiteDataReader.NextResult()
> > > > >
> > > > >
> > > > >
> > > > > * - Data Source cannot be empty. Use :memory: to open an in-memory
> > > > > database- unable to open database file *
> > > > >
> > > > > These two exceptions are raised while trying to open the
> connection.
> > > > >
> > > > >
> > > > >  Any help or guidance would be appreciated.
> > > > >
> > > > > Thank you,
> > > > > Raj
> > > > > ___
> > > > > sqlite-users mailing list
> > > > > sqlite-users@mailinglists.sqlite.org
> > > > >
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > 

Re: [sqlite] SQL logic error or missing database no such table

[Rajendra Shirhatti]

Hi Randall,
I was googling around and it seems like some people had the same issue
where the data was lost after the connection was closed.
In my case, the connection is a singleton which is used by multiple threads.
I do not close the connection explicitly, let it close automatically when
the service is shutdown.
I'm thinking of making following changes but not sure if that's going to
resolve the issue:
1) Create/Close connection object separately on each thread
2) Call Commit() before closing the connection, is it necessary?
3) Use FailIfMissing flag in connection string

Please advise.

Thank you, Raj



On Mon, May 23, 2016 at 11:21 AM, Rajendra Shirhatti 
wrote:

> Yes, I'm very positive the data is stored otherwise the application would
> throw some exception.
> The database is encrypted by calling passing a private key to the
> connection using ChangePassword().
>
> Thank you, Raj
>
> On Mon, May 23, 2016 at 9:16 AM, R.A. Nagy  wrote:
>
>> Are we sure that data are being stored at-all?
>>
>> Also, how is the database being encrypted?
>>
>>
>> On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti <
>> rrshirha...@gmail.com>
>> wrote:
>>
>> > Hi Randall,
>> > Thank you so much for your reply.
>> > I don't have enough data to confirm whether database file is missing or
>> > it's due to some other reason.
>> > According to my initial investigation, it looks like the application is
>> > able to create database and perform all the operations successfully upon
>> > service start. I'm leaning towards the possibility that the tables are
>> > somehow getting deleted or the database file becomes inaccessible when
>> the
>> > service is RESTARTED. We open the database connection during service
>> > startup and it stays alive as long as service is running.
>> > Is there any scenario that could corrupt the database because the
>> > connection was not closed successfully?
>> >
>> > Thank you once again.
>> >
>> > -Raj
>> >
>> > On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy  wrote:
>> >
>> > > Try:
>> > >
>> > > .schema [tablename]
>> > >
>> > > Since there is probably nothing there, you probably need to create a
>> > table.
>> > >
>> > > If you are new to SQL / SQLite, then here are some helpful videos:
>> > >
>> > >
>> https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
>> > >
>> > >
>> > > Cheers,
>> > >
>> > > -Randall Nagy
>> > > President, Soft9000.com
>> > >
>> > >
>> > >
>> > >
>> > > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
>> > rrshirha...@gmail.com
>> > > >
>> > > wrote:
>> > >
>> > > > Hi,
>> > > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
>> > > > operations. The SQLite package contains two binaries,
>> > > > system.data.sqlite.dll and sqlite.interop.dll.
>> > > >
>> > > > The database is encrypted and the blob it contains is encrypted as
>> > well.
>> > > > SQLite is intermittently throwing following exceptions while
>> retrieving
>> > > > data from the database.
>> > > >
>> > > >
>> > > >- *SQL logic error or missing database no such table*.
>> > > >
>> > > >  The code is trying to execute a simple query which looks something
>> > like
>> > > > this:
>> > > >  SELECT x, y from [tablename]
>> > > >
>> > > > This exception is raised while executing
>> SQLiteCommand.ExecuteReader().
>> > > The
>> > > > stack trace points to
>> > > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
>> > > strSql,
>> > > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
>> > > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
>> > > > at System.Data.SQLite.SQLiteDataReader.NextResult()
>> > > >
>> > > >
>> > > >
>> > > > * - Data Source cannot be empty. Use :memory: to open an in-memory
>> > > > database- unable to open database file *
>> > > >
>> > > > These two exceptions are raised while trying to open the connection.
>> > > >
>> > > >
>> > > >  Any help or guidance would be appreciated.
>> > > >
>> > > > Thank you,
>> > > > Raj
>> > > > ___
>> > > > sqlite-users mailing list
>> > > > sqlite-users@mailinglists.sqlite.org
>> > > >
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> > > >
>> > > ___
>> > > sqlite-users mailing list
>> > > sqlite-users@mailinglists.sqlite.org
>> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> > >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list

[sqlite] SQL logic error or missing database no such table

[R.A. Nagy]

Are we sure that data are being stored at-all?

Also, how is the database being encrypted?


On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti 
wrote:

> Hi Randall,
> Thank you so much for your reply.
> I don't have enough data to confirm whether database file is missing or
> it's due to some other reason.
> According to my initial investigation, it looks like the application is
> able to create database and perform all the operations successfully upon
> service start. I'm leaning towards the possibility that the tables are
> somehow getting deleted or the database file becomes inaccessible when the
> service is RESTARTED. We open the database connection during service
> startup and it stays alive as long as service is running.
> Is there any scenario that could corrupt the database because the
> connection was not closed successfully?
>
> Thank you once again.
>
> -Raj
>
> On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy  wrote:
>
> > Try:
> >
> > .schema [tablename]
> >
> > Since there is probably nothing there, you probably need to create a
> table.
> >
> > If you are new to SQL / SQLite, then here are some helpful videos:
> >
> > https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
> >
> >
> > Cheers,
> >
> > -Randall Nagy
> > President, Soft9000.com
> >
> >
> >
> >
> > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
> rrshirhatti at gmail.com
> > >
> > wrote:
> >
> > > Hi,
> > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
> > > operations. The SQLite package contains two binaries,
> > > system.data.sqlite.dll and sqlite.interop.dll.
> > >
> > > The database is encrypted and the blob it contains is encrypted as
> well.
> > > SQLite is intermittently throwing following exceptions while retrieving
> > > data from the database.
> > >
> > >
> > >- *SQL logic error or missing database no such table*.
> > >
> > >  The code is trying to execute a simple query which looks something
> like
> > > this:
> > >  SELECT x, y from [tablename]
> > >
> > > This exception is raised while executing SQLiteCommand.ExecuteReader().
> > The
> > > stack trace points to
> > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
> > strSql,
> > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
> > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
> > > at System.Data.SQLite.SQLiteDataReader.NextResult()
> > >
> > >
> > >
> > > * - Data Source cannot be empty. Use :memory: to open an in-memory
> > > database- unable to open database file *
> > >
> > > These two exceptions are raised while trying to open the connection.
> > >
> > >
> > >  Any help or guidance would be appreciated.
> > >
> > > Thank you,
> > > Raj
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQL logic error or missing database no such table

[Rajendra Shirhatti]

Yes, I'm very positive the data is stored otherwise the application would
throw some exception.
The database is encrypted by calling passing a private key to the
connection using ChangePassword().

Thank you, Raj

On Mon, May 23, 2016 at 9:16 AM, R.A. Nagy  wrote:

> Are we sure that data are being stored at-all?
>
> Also, how is the database being encrypted?
>
>
> On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti  >
> wrote:
>
> > Hi Randall,
> > Thank you so much for your reply.
> > I don't have enough data to confirm whether database file is missing or
> > it's due to some other reason.
> > According to my initial investigation, it looks like the application is
> > able to create database and perform all the operations successfully upon
> > service start. I'm leaning towards the possibility that the tables are
> > somehow getting deleted or the database file becomes inaccessible when
> the
> > service is RESTARTED. We open the database connection during service
> > startup and it stays alive as long as service is running.
> > Is there any scenario that could corrupt the database because the
> > connection was not closed successfully?
> >
> > Thank you once again.
> >
> > -Raj
> >
> > On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy  wrote:
> >
> > > Try:
> > >
> > > .schema [tablename]
> > >
> > > Since there is probably nothing there, you probably need to create a
> > table.
> > >
> > > If you are new to SQL / SQLite, then here are some helpful videos:
> > >
> > >
> https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
> > >
> > >
> > > Cheers,
> > >
> > > -Randall Nagy
> > > President, Soft9000.com
> > >
> > >
> > >
> > >
> > > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
> > rrshirhatti at gmail.com
> > > >
> > > wrote:
> > >
> > > > Hi,
> > > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
> > > > operations. The SQLite package contains two binaries,
> > > > system.data.sqlite.dll and sqlite.interop.dll.
> > > >
> > > > The database is encrypted and the blob it contains is encrypted as
> > well.
> > > > SQLite is intermittently throwing following exceptions while
> retrieving
> > > > data from the database.
> > > >
> > > >
> > > >- *SQL logic error or missing database no such table*.
> > > >
> > > >  The code is trying to execute a simple query which looks something
> > like
> > > > this:
> > > >  SELECT x, y from [tablename]
> > > >
> > > > This exception is raised while executing
> SQLiteCommand.ExecuteReader().
> > > The
> > > > stack trace points to
> > > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
> > > strSql,
> > > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
> > > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
> > > > at System.Data.SQLite.SQLiteDataReader.NextResult()
> > > >
> > > >
> > > >
> > > > * - Data Source cannot be empty. Use :memory: to open an in-memory
> > > > database- unable to open database file *
> > > >
> > > > These two exceptions are raised while trying to open the connection.
> > > >
> > > >
> > > >  Any help or guidance would be appreciated.
> > > >
> > > > Thank you,
> > > > Raj
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users at mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQL logic error or missing database no such table

[Rajendra Shirhatti]

Hi Randall,
Thank you so much for your reply.
I don't have enough data to confirm whether database file is missing or
it's due to some other reason.
According to my initial investigation, it looks like the application is
able to create database and perform all the operations successfully upon
service start. I'm leaning towards the possibility that the tables are
somehow getting deleted or the database file becomes inaccessible when the
service is RESTARTED. We open the database connection during service
startup and it stays alive as long as service is running.
Is there any scenario that could corrupt the database because the
connection was not closed successfully?

Thank you once again.

-Raj

On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy  wrote:

> Try:
>
> .schema [tablename]
>
> Since there is probably nothing there, you probably need to create a table.
>
> If you are new to SQL / SQLite, then here are some helpful videos:
>
> https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
>
>
> Cheers,
>
> -Randall Nagy
> President, Soft9000.com
>
>
>
>
> On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti  >
> wrote:
>
> > Hi,
> > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
> > operations. The SQLite package contains two binaries,
> > system.data.sqlite.dll and sqlite.interop.dll.
> >
> > The database is encrypted and the blob it contains is encrypted as well.
> > SQLite is intermittently throwing following exceptions while retrieving
> > data from the database.
> >
> >
> >- *SQL logic error or missing database no such table*.
> >
> >  The code is trying to execute a simple query which looks something like
> > this:
> >  SELECT x, y from [tablename]
> >
> > This exception is raised while executing SQLiteCommand.ExecuteReader().
> The
> > stack trace points to
> > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
> strSql,
> > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
> > at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
> > at System.Data.SQLite.SQLiteDataReader.NextResult()
> >
> >
> >
> > * - Data Source cannot be empty. Use :memory: to open an in-memory
> > database- unable to open database file *
> >
> > These two exceptions are raised while trying to open the connection.
> >
> >
> >  Any help or guidance would be appreciated.
> >
> > Thank you,
> > Raj
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQL logic error or missing database no such table

[R.A. Nagy]

Try:

.schema [tablename]

Since there is probably nothing there, you probably need to create a table.

If you are new to SQL / SQLite, then here are some helpful videos:

https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy


Cheers,

-Randall Nagy
President, Soft9000.com




On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti 
wrote:

> Hi,
> We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
> operations. The SQLite package contains two binaries,
> system.data.sqlite.dll and sqlite.interop.dll.
>
> The database is encrypted and the blob it contains is encrypted as well.
> SQLite is intermittently throwing following exceptions while retrieving
> data from the database.
>
>
>- *SQL logic error or missing database no such table*.
>
>  The code is trying to execute a simple query which looks something like
> this:
>  SELECT x, y from [tablename]
>
> This exception is raised while executing SQLiteCommand.ExecuteReader(). The
> stack trace points to
> at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql,
> SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
> at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
> at System.Data.SQLite.SQLiteDataReader.NextResult()
>
>
>
> * - Data Source cannot be empty. Use :memory: to open an in-memory
> database- unable to open database file *
>
> These two exceptions are raised while trying to open the connection.
>
>
>  Any help or guidance would be appreciated.
>
> Thank you,
> Raj
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQL logic error or missing database no such table

[Rajendra Shirhatti]

Hi,
We've a .Net Windows Service that uses SQLite 1.0.93.0 for database
operations. The SQLite package contains two binaries,
system.data.sqlite.dll and sqlite.interop.dll.

The database is encrypted and the blob it contains is encrypted as well.
SQLite is intermittently throwing following exceptions while retrieving
data from the database.


   - *SQL logic error or missing database no such table*.

 The code is trying to execute a simple query which looks something like
this:
 SELECT x, y from [tablename]

This exception is raised while executing SQLiteCommand.ExecuteReader(). The
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql,
SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
at System.Data.SQLite.SQLiteDataReader.NextResult()



* - Data Source cannot be empty. Use :memory: to open an in-memory
database- unable to open database file *

These two exceptions are raised while trying to open the connection.


 Any help or guidance would be appreciated.

Thank you,
Raj

[sqlite] SQL logic error or missing database no such table: config

[Alessio Fabriziani]

Hello,
Using SQLite for C# (package from NuGet).
In some cases...I get this exception but database and table are present.

Why I get this exception? Corruption? What?

Thanks
Best Regards
___
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 Python sqlite

[C M]

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

[Pavel Ivanov]

Do you know that VS2012 has known optimizer bugs?

Pavel
On Apr 6, 2013 5:01 AM, "ibrahim"  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",
>>> , 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", ,
> SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;
>
> and to make the test just more simple :
>
> returnValue = sqlite3_open ("C:\\Newfolder\\testing.xyz", ) ;
>
> 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-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

[ibrahim]

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",
, 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", , 
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;


and to make the test just more simple :

returnValue = sqlite3_open ("C:\\Newfolder\\testing.xyz", ) ;

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

Re: [sqlite] SQL Logic error or missing database

[ibrahim]

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", , 
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

[ibrahim]

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, L"Name: ", MB_OK );

   // Create the object
   sqlite3 *oDatabase;

   // Create the error objects
   char *sErrorMessage;
   // Open/create the table, if required
   returnValue = sqlite3_open_v2(dataBase, ,
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

[Rob Collie]

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 
#include 
#include 
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", ,
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, );
  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 
> #include "sqlite3.h"
>
> main()
> {
>   sqlite3 *oDatabase;
>   int returnValue;
>   returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
> 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.com>wrote:
>
> > 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 proje

Re: [sqlite] SQL Logic error or missing database

[Michael Stephenson]

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  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",
>> , 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

[Michael Black]

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 
#include "sqlite3.h"

main()
{
  sqlite3 *oDatabase;
  int returnValue;
  returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
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.com>wrote:

> 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

[Dan Kennedy]

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",
, 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

[Rob Collie]

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  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

[Kevin Martin]

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

[Rob Collie]

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 wrote:

> 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  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  > >wrote:
> >
> > > On Fri, Apr 5, 2013 at 10:08 AM, 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.
> > > >
> > >
> > >
> > > 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

[Noel Frankinet]

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 
> #include "sqlite3.h"
>
> main()
> {
>   sqlite3 *oDatabase;
>   int returnValue;
>   returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
> 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

[Michael Black]

This works for me under Visual Studio 2010.  I couldn't seem to get a file
uri to work at all either.

#include 
#include "sqlite3.h"

main()
{
  sqlite3 *oDatabase;
  int returnValue;
  returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
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

[Noel Frankinet]

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  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  >wrote:
>
> > On Fri, Apr 5, 2013 at 10:08 AM, 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.
> > >
> >
> >
> > 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

[Rob Collie]

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 wrote:

> On Fri, Apr 5, 2013 at 10:08 AM, 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.
> >
>
>
> 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

[Kevin Benson]

On Fri, Apr 5, 2013 at 10:08 AM, 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.
>


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

[Noel Frankinet]

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",
> , 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, L"Name: ", MB_OK );
> >
> >   // Create the object
> >   sqlite3 *oDatabase;
> >
> >   // Create the error objects
> >   char *sErrorMessage;
> >   // Open/create the table, if required
> >   returnValue = sqlite3_open_v2(dataBase, ,
> > 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

[Rob Collie]

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",
, 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, L"Name: ", MB_OK );
>
>   // Create the object
>   sqlite3 *oDatabase;
>
>   // Create the error objects
>   char *sErrorMessage;
>   // Open/create the table, if required
>   returnValue = sqlite3_open_v2(dataBase, ,
> 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

[Michael Black]

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, L"Name: ", MB_OK );

  // Create the object
  sqlite3 *oDatabase;

  // Create the error objects
  char *sErrorMessage;
  // Open/create the table, if required
  returnValue = sqlite3_open_v2(dataBase, ,
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

[Noel Frankinet]

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  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  >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  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", ,
> > > 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  wrote:
> > >
> > > > On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:
> > > >
> > > > >
> > > > >
> > > > > On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
> > > > >>
> > > > >>   // Create the object
> > > > >>   sqlite3 *oDatabase;
> > > > >>
> > > > >>   // Create the error objects
> > > > >>   char *sErrorMessage;
> > > > >>   // Open/create the table, if required
> > > > >>   returnValue = sqlite3_open_v2(dataBase, ,
> > > > >>
> > > > >
> > > > > 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

[Rob Collie]

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, L"Name: ", MB_OK );
>
>   // Create the object
>   sqlite3 *oDatabase;
>
>   // Create the error objects
>   char *sErrorMessage;
>   // Open/create the table, if required
>   returnValue = sqlite3_open_v2(dataBase, ,
> 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

[Michael Black]

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, L"Name: ", MB_OK );

  // Create the object
  sqlite3 *oDatabase;

  // Create the error objects
  char *sErrorMessage;
  // Open/create the table, if required
  returnValue = sqlite3_open_v2(dataBase, ,
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

[Rob Collie]

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 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  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", ,
> > 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  wrote:
> >
> > > On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:
> > >
> > > >
> > > >
> > > > On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
> > > >>
> > > >>   // Create the object
> > > >>   sqlite3 *oDatabase;
> > > >>
> > > >>   // Create the error objects
> > > >>   char *sErrorMessage;
> > > >>   // Open/create the table, if required
> > > >>   returnValue = sqlite3_open_v2(dataBase, ,
> > > >>
> > > >
> > > > 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

[Simon Slavin]

On 5 Apr 2013, at 1:54pm, Rob Collie  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

[Noel Frankinet]

SQLITE_OPEN_READWRITE
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  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  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", ,
>> 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  wrote:
>>
>> > On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:
>> >
>> > >
>> > >
>> > > On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
>> > >>
>> > >>   // Create the object
>> > >>   sqlite3 *oDatabase;
>> > >>
>> > >>   // Create the error objects
>> > >>   char *sErrorMessage;
>> > >>   // Open/create the table, if required
>> > >>   returnValue = sqlite3_open_v2(dataBase, ,
>> > >>
>> > >
>> > > 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

[Noel Frankinet]

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  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", ,
> 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  wrote:
>
> > On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:
> >
> > >
> > >
> > > On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
> > >>
> > >>   // Create the object
> > >>   sqlite3 *oDatabase;
> > >>
> > >>   // Create the error objects
> > >>   char *sErrorMessage;
> > >>   // Open/create the table, if required
> > >>   returnValue = sqlite3_open_v2(dataBase, ,
> > >>
> > >
> > > 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

[Rob Collie]

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", ,
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  wrote:

> On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:
>
> >
> >
> > On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
> >>
> >>   // Create the object
> >>   sqlite3 *oDatabase;
> >>
> >>   // Create the error objects
> >>   char *sErrorMessage;
> >>   // Open/create the table, if required
> >>   returnValue = sqlite3_open_v2(dataBase, ,
> >>
> >
> > 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

[Noel Frankinet]

Is your filename UTF8 ?


On 5 April 2013 15:02, Richard Hipp  wrote:

> On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
> >
> >   // Create the object
> >   sqlite3 *oDatabase;
> >
> >   // Create the error objects
> >   char *sErrorMessage;
> >   // Open/create the table, if required
> >   returnValue = sqlite3_open_v2(dataBase, ,
> >
>
> 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

[Richard Hipp]

On Fri, Apr 5, 2013 at 9:02 AM, Richard Hipp  wrote:

>
>
> On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
>>
>>   // Create the object
>>   sqlite3 *oDatabase;
>>
>>   // Create the error objects
>>   char *sErrorMessage;
>>   // Open/create the table, if required
>>   returnValue = sqlite3_open_v2(dataBase, ,
>>
>
> 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

[Richard Hipp]

On Fri, Apr 5, 2013 at 8:54 AM, 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, L"Name: ", MB_OK );
>
>   // Create the object
>   sqlite3 *oDatabase;
>
>   // Create the error objects
>   char *sErrorMessage;
>   // Open/create the table, if required
>   returnValue = sqlite3_open_v2(dataBase, ,
>

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

[sqlite] SQL Logic error or missing database

[Rob Collie]

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, L"Name: ", MB_OK );

  // Create the object
  sqlite3 *oDatabase;

  // Create the error objects
  char *sErrorMessage;
  // Open/create the table, if required
  returnValue = sqlite3_open_v2(dataBase, ,
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

[Gert Van Assche]

Kees, thanks. I reopened the connection and all is OK now.

thanks for your help,

gert


2012/10/24 Kees Nuyt 

> On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche
>  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

Re: [sqlite] SQL logic error or missing database

[Simon Slavin]

On 24 Oct 2012, at 3:42pm, Gert Van Assche  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

[Gert Van Assche]

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 

>
> On 24 Oct 2012, at 2:53pm, Gert Van Assche  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

[Kees Nuyt]

On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche
 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 error

[Rittick Gupta]

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 error

[Brandon Pimenta]

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

[sqlite] SQL logic error or missing database error

[Rittick Gupta]

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

[Igor Tandetnik]

Alex Ousherovitch
 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

Re: [sqlite] SQL logic error or missing database

[Alex Ousherovitch]

>> 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

[Marco Bambini]

Thanks a lot for the explanation Igor.

--  
Marco Bambini



On May 14, 2009, at 1:55 PM, Igor Tandetnik wrote:

> "Marco Bambini"  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

[Igor Tandetnik]

"Marco Bambini"  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] SQL logic error or missing database

[Marco Bambini]

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" with multithreaded program

[dbikash]

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

[D. Richard Hipp]

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

Re: [sqlite] "SQL logic error or missing database" with multithreaded program

[Ken]

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 SQLIT

Re: [sqlite] "SQL logic error or missing database" with multithreaded program

[dbikash]

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

[Ken]

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" with multithreaded program

[dbikash]

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

[D. Richard Hipp]

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

[dbikash]

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

[MikeW]

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

[sqlite] "SQL logic error or missing database" with multithreaded program

[dbikash]

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 in version 3.5.6(Bug???)

[Steve Topov]

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???)

[drh]

"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

[sqlite] SQL logic error or missing database

[Yvon Thoraval]

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"

[Ran]

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

Re: [sqlite] "SQL logic error or missing database"

[John Stanton]

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 
#include 
#include 
#include 

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", );
 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
  ,   // 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", ); // 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
,// 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
  ,   // 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"

[drh]

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"

[drh]

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"

[Robert Simpson]

> -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"

[Craig Morrison]

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"

[Robert Simpson]

> -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]

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"

[Jay Sprenkle]

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"

[Robert Simpson]

> -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"

[Jay Sprenkle]

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"

[Robert Simpson]

> -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"

[Robert Simpson]

> -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
   ,// 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"

[Ran]

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
>  #include  #include  #include 
>
> 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", );
>   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", ); // 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
>,// 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"

[Jay Sprenkle]

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"

[Robert Simpson]

> -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"

[Robert Simpson]

> -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 
>  #include  #include  #include 
> 
> 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", );
>   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", ); // 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
>,// 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"

[Jay Sprenkle]

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"

[Robert Simpson]

> -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"

[Robert Simpson]

> -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
>,   // 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
>  ,// 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
>,   // 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,
,
0);
  if (rc != SQLITE_SCHEMA) break;
  n ++;
}

if (rc) {
  // blah blah
  exit(1);
}
//


Robert

Re: [sqlite] "SQL logic error or missing database"

[Ran]

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 
#include 
#include 
#include 

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", );
 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", ); // 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
  ,// 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"

[Jay Sprenkle]

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"

[Jay Sprenkle]

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 
#include 
//#include 
#include 

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", );
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
 ,   // 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", ); // 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
   ,// 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
 ,   // 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"

[Ran]

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
>,   // 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", ); // 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
>  ,// 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
>,   // 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"

[Jay Sprenkle]

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
   ,   // 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", ); // 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
 ,// 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
   ,   // 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?

[sqlite] "SQL logic error or missing database"

[Ran]

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 
#include 
#include 
#include 

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", );
 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
  ,   // 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", ); // 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
,// 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
  ,   // 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);
}