Discussion:
[sqlite] SQL logic error or missing database no such table
Rajendra Shirhatti
2016-05-21 20:09:47 UTC
Permalink
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
R.A. Nagy
2016-05-22 11:54:51 UTC
Permalink
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
Post by 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
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Rajendra Shirhatti
2016-05-22 19:02:39 UTC
Permalink
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
Post by R.A. Nagy
.schema [tablename]
Since there is probably nothing there, you probably need to create a table.
https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
Cheers,
-Randall Nagy
President, Soft9000.com
Post by 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
SELECT x, y from [tablename]
This exception is raised while executing SQLiteCommand.ExecuteReader().
The
Post by Rajendra Shirhatti
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql,
Post by Rajendra Shirhatti
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
R.A. Nagy
2016-05-23 16:16:11 UTC
Permalink
Are we sure that data are being stored at-all?

Also, how is the database being encrypted?
Post by 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
Post by R.A. Nagy
.schema [tablename]
Since there is probably nothing there, you probably need to create a
table.
Post by R.A. Nagy
https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
Cheers,
-Randall Nagy
President, Soft9000.com
On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
Post by 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.
Post by R.A. Nagy
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
Post by Rajendra Shirhatti
SELECT x, y from [tablename]
This exception is raised while executing SQLiteCommand.ExecuteReader().
The
Post by Rajendra Shirhatti
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql,
Post by Rajendra Shirhatti
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Rajendra Shirhatti
2016-05-23 18:21:01 UTC
Permalink
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
Post by R.A. Nagy
Are we sure that data are being stored at-all?
Also, how is the database being encrypted?
Post by 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
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
.schema [tablename]
Since there is probably nothing there, you probably need to create a
table.
https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
Post by Rajendra Shirhatti
Post by R.A. Nagy
Cheers,
-Randall Nagy
President, Soft9000.com
On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
Post by 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.
Post by R.A. Nagy
Post by Rajendra Shirhatti
SQLite is intermittently throwing following exceptions while
retrieving
Post by Rajendra Shirhatti
Post by R.A. Nagy
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
Post by Rajendra Shirhatti
SELECT x, y from [tablename]
This exception is raised while executing
SQLiteCommand.ExecuteReader().
Post by Rajendra Shirhatti
Post by R.A. Nagy
The
Post by Rajendra Shirhatti
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql,
Post by Rajendra Shirhatti
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Rajendra Shirhatti
2016-05-24 07:47:29 UTC
Permalink
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
Post by 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
Post by 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 <
Post by 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
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
.schema [tablename]
Since there is probably nothing there, you probably need to create a
table.
https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
Post by Rajendra Shirhatti
Post by R.A. Nagy
Cheers,
-Randall Nagy
President, Soft9000.com
On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
Post by 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.
Post by R.A. Nagy
Post by Rajendra Shirhatti
SQLite is intermittently throwing following exceptions while
retrieving
Post by Rajendra Shirhatti
Post by R.A. Nagy
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
Post by Rajendra Shirhatti
SELECT x, y from [tablename]
This exception is raised while executing
SQLiteCommand.ExecuteReader().
Post by Rajendra Shirhatti
Post by R.A. Nagy
The
Post by Rajendra Shirhatti
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql,
Post by Rajendra Shirhatti
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Post by Rajendra Shirhatti
Post by R.A. Nagy
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
R.A. Nagy
2016-05-24 09:17:45 UTC
Permalink
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.
Post by 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
Post by 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 <
Post by 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
Post by R.A. Nagy
Post by Rajendra Shirhatti
service start. I'm leaning towards the possibility that the tables are
somehow getting deleted or the database file becomes inaccessible when
the
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
.schema [tablename]
Since there is probably nothing there, you probably need to create a
table.
https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
Post by Rajendra Shirhatti
Post by R.A. Nagy
Cheers,
-Randall Nagy
President, Soft9000.com
On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti <
Post by 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.
Post by R.A. Nagy
Post by Rajendra Shirhatti
SQLite is intermittently throwing following exceptions while
retrieving
Post by Rajendra Shirhatti
Post by R.A. Nagy
Post by Rajendra Shirhatti
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
Post by R.A. Nagy
Post by Rajendra Shirhatti
SELECT x, y from [tablename]
This exception is raised while executing
SQLiteCommand.ExecuteReader().
Post by Rajendra Shirhatti
Post by R.A. Nagy
The
Post by Rajendra Shirhatti
stack trace points to
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql,
Post by Rajendra Shirhatti
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.
Post by R.A. Nagy
Post by Rajendra Shirhatti
Post by R.A. Nagy
Post by Rajendra Shirhatti
Any help or guidance would be appreciated.
Thank you,
Raj
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Post by R.A. Nagy
Post by Rajendra Shirhatti
Post by R.A. Nagy
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...