Re: [sqlite] SQL logic error or missing database no such table
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 <rrshirha...@gmail.com> 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 <r.a.n...@gmail.com> 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 <r.a.n...@gmail.com> 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 wh
[sqlite] SQL logic error or missing database no such table
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
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
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
