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 · Accepted Answer · 2010-11-24 03:45:29Z

2

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.

answered Nov 24, 2010 at 3:45

James Anderson

27.5k7 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Berryl Over a year ago

Hi James. Is it an incorrect to assume that the attach automatically duplicates the schema from the existing connection into the attached file db? If so and I need to manually replicated all tables what is the best way to do that? Cheers

James Anderson Over a year ago

Thats not what I said! If you specifiy a non existant file, sqlite will create a new one with an empty schema.

Berryl Over a year ago

You did say it would be empty, which did help me realize that ATTACH was not replicating the db as I thought it was. After creating the tables, all works well. I think follow-up questions to clarify new information are a good thing though, don't you agree?

Berryl Over a year ago

This is marked as the answer with the understanding that the full and is that attach does not replicate the db on the existing connection. The gotcha James alludes to isn't really more of a gotcha than any other usage misunderstanding. Cheers

James Anderson Over a year ago

Thanks -- glad to have helped even though it was not exactly the answer to your problem!

Steve · Accepted Answer · 2010-11-24 02:57:18Z

0

Do you need the AS {1} statement? Forcing the database name seems to be confusing it.

answered Nov 24, 2010 at 2:57

Steve

1,1998 silver badges14 bronze badges

1 Comment

Berryl Over a year ago

I think so because the idea is to two copies of the same db with one in memory and one the file. So when a table that will be in both db's is referenced, SQLite has a way to distinguish which is which.

Collectives™ on Stack Overflow

sqlite attached database problem

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related