Re: [sqlite] SQL logic error or missing database no such table
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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-
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 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-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinf
[sqlite] SQL logic error or missing database no such table
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
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
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
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
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