java - sqlite4java no such table error by attaching another database

Question

I'm using sqlite4java to handle with sqlite db. I want now to copy a table from database to dbtest. So I found the code here on stackoverflow. But I get always an error. First of all I open a connection to my new created database and afterwards I attach the other database with the following command:

ATTACH DATABASE database AS database;

This works fine. dbtest has been opended earliere as new database. Then I want to copy the tables: Hence, the table is already created and it is the same as in the other database.

INSERT INTO dbtest.Routing (id, source, destination, metadata, zone_src, zone_dst,cost) SELECT * FROM database.Routing;

But after executing this I get the error:

com.almworks.sqlite4java.SQLiteException: [1] DB[1] exec() no such table: dbtest.Routing

I tried it also with sqlite Studio and there it is working without any problems, but I cannot attach a database there (this is done automatically). Do I have to use another notation to use two databases?

EDIT: I use now the answer from CL. But this leads to the new issue:

com.almworks.sqlite4java.SQLiteException: [1] DB[1] exec() no such table: second.Routing

What did I change?

ATTACH DATABASE database AS second; //new name for the database file and it will be attached as second, because if I use the debug function it says: Database second already in use.

If I use this command I get the error mentioned above.

INSERT INTO Routing (id, source, destination, metadata, zone_src, zone_dst,cost) SELECT * FROM second.Routing;

Problem solved by using the absolute path for the database file.

Answer 1

The name of attached databases is not its file name but whatever you've specified as name in the AS clause of the ATTACH statement.

Neither is the name of the main database (the database to which the other database was attached) its file name. Its name always is main.

To specify the name of a table in your main database, use main.Routing, or just Routing.

---

Please note that SQLite is perfectly happy with creating a new database if the file does not yet exist, so you have to ensure to give the correct file name to the ATTACH statement. If your database file is not named database or if it is not in the current directory, the second database will be empty. It is strongly recommended to always give the complete file path, like this:

ATTACH DATABASE '/some/where/whatever.db' AS second;