sqlite attached database problem

Question

I am getting an "no such table" error when I try and access an attached db file that is on a valid SQLiteConnection with built schema.

After saving some test data on the "main" connection, I attach the file and then try to export data from "main" to the file for later use. Here is the code used to attach the file:

private static void _attachDatabase(SQLiteConnection conn, string dataFile) {
var cmd = new SQLiteCommand(conn)
                  {
                      CommandText = string.Format("ATTACH '{0}' AS {1}", dataFile, ATTACHED_DB)
                  };
_log.Debug(cmd.CommandText);
cmd.ExecuteNonQuery();
}

The log output of the command is

"...\bin\Debug\testData.db3' AS asdf" where testData.db3 is the file.

When I try to insert to the file though, I get the error at the end of this post.

Does any see the problem from the info provided? As an aside, should I be able to see the attached db ("asdf") somewhere in the debugger?

Cheers,
Berryl

Test 'SQLiteTesting.Helpers.QueryTests.Director_if_Tim_Burton_query_should_return_one_movie' failed: TestFixtureSetUp failed in QueryTests

TestFixture failed: System.Data.SQLite.SQLiteException : SQLite error no such table: asdf.ActorRole 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.SQLiteCommand.GetStatement(Int32 index) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() at SQLiteTesting.Helpers.SQLiteLoader._copyTableData(SQLiteConnection conn, String source, String destination) in C:\Users\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers\SQLiteLoader.cs:line 68 at SQLiteTesting.Helpers.SQLiteLoader.ExportData(SQLiteConnection conn, String dataFile) in C:\Users\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers\SQLiteLoader.cs:line 30 at SQLiteTesting.Helpers.QueryTests.OnFixtureSetUp() in C:\Users\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers\QueryTests.cs:line 25 at SQLiteTesting.Helpers.BaseFixture.FixtureSetUp() in C:\Users\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers\BaseFixture.cs:line 23 2010-11-23 18:15:36,835 DEBUG ATTACH '...\bin\Debug\testData.db3' AS asd 2010-11-23 18:18:12,935 DEBUG INSERT INTO asdf.ActorRole SELECT * FROM ActorRole

James Anderson James Anderson · Accepted Answer · 2010-11-24T03:45:29

The clue is:

SQLite error no such table: asdf.ActorRole at

If you actualy have defined the table, then you have suffered the major (probably only!) sqlite gotcha. If the database file doesnt exits it will quietly create an empty database for you.

Double check you file path and look for a new empty sq3 file appearing in your directory.

sqlite attached database problem

2 Answers