Tip: How to "attach" another Sqlite database

You must Login before you can answer or comment on any questions.

This one was tough to figure out, so I thought I'd pass on how it's done. Say you have 2 databases, and you need to use the standard attach Sqlite syntax to join them. For example, in Sqlite:

sqlite3 db1.sql
>attach 'db2.sql' as db2;
>select * from db1 join db2 on .... etc.
How do we pull this off in Titanium Mobile?

Here's the code:

var db = Titanium.Database.install("db1", "db1");
var dir = Ti.Filesystem.getFile(Titanium.Filesystem.resourcesDirectory);
 
db.execute("attach '" + dir.getParent() +
"/Library/Application Support/database/db2.sql' as db2;");
var rows = db.execute("select * from db1 join db2...");

4 Answers

I've had success doing it like this (Ti Mobile SDK 1.8.2):

var dbOne = Ti.Database.open('db1');    
var dbTwo = Ti.Database.open('db2');
 
var db1path = Ti.Filesystem.getFile(Ti.Filesystem.applicationSupportDirectory, '../Private Documents/db1.sql').nativePath;
var db2path = Ti.Filesystem.getFile(Ti.Filesystem.applicationSupportDirectory, '../Private Documents/db2.sql').nativePath;
 
//In the example at the top of this thread, they are missing "DATABASE" from the ATTACH statement
dbOne.execute("ATTACH DATABASE '"+db1path+"' AS dbOne");
dbTwo.execute("ATTACH DATABASE '"+db2path+"' AS dbTwo");
 
//Ready for queries
var result = dbOne.execute("SELECT * FROM dbTwo.tableName");
Good luck!

— answered 3 years ago by Clifton Labrum
answer permalink
3 Comments
  • Are you sure you don't have dbOne.execute and dbTwo.execute backwards in the above ATTACH DATABASE commands? As is, you appear to be attaching each database to itself, instead of to each other.

    — commented 3 years ago by Doug Handy

  • Yep, you're right. I originally only attached the 2nd database, but I was getting an error. It must have been unrelated because it works now.

    Thanks!

    — commented 3 years ago by Clifton Labrum

  • Actually, I take that back. If I don't ATTACH both databases (even though I'm executing these ATTACH statements from within the first), I get a 'table not found' error on dbOne.myTable

    — commented 3 years ago by Clifton Labrum

Hi Mark

I m trying almost same code. But it does not work for me. When i run my application it crash. Here is my code

var backup_db = Titanium.Database.open('db1'); var dir = Ti.Filesystem.getFile(Titanium.Filesystem.applicationSupportDirectory);

backup_db.execute("attach '" + dir.getParent() + "/database/databaseA.sql' as databaseA;");

I got following error in titanium console.

2011-01-04 15:07:13.886 testVideo[1047:7903] [ERROR] Error occurred calling next on a PLSqliteResultSet. SQLite error: 'unable to open database file' for 'attach '/Users/apple/Library/Application Support/iPhone Simulator/4.1/Applications/BC8B0EC7-1F16-4EE8-B677-A4276F8457A4/Library/database/databaseA.sql' as databaseA;' [ERROR] The application has crashed with an unhandled exception. Stack trace:

— answered 4 years ago by Samavaya Samavaya
answer permalink
2 Comments
  • Hi Mark

    I got success.Earlier i was giving wrong path for database. Thank you for your very useful code.

    Here i m adding code to copy table from database to another var rows =db.execute("INSERT INTO tableName(version) SELECT * FROM databaseA.versioninfo;");

    — commented 4 years ago by Samavaya Samavaya

  • Hi Samavaya, What is the correct path to access the db in android?. Thanks.

    — commented 4 years ago by wk ho

Hi Mark

I m trying almost same code. But it does not work for me. When i run my application it crash. Here is my code

var backup_db = Titanium.Database.open('cellarhanddb1'); var dir = Ti.Filesystem.getFile(Titanium.Filesystem.applicationSupportDirectory);

backup_db.execute("attach '" + dir.getParent() + "/database/databaseA.sql' as databaseA;");

I got following error in titanium console.

2011-01-04 15:07:13.886 testVideo[1047:7903] [ERROR] Error occurred calling next on a PLSqliteResultSet. SQLite error: 'unable to open database file' for 'attach '/Users/apple/Library/Application Support/iPhone Simulator/4.1/Applications/BC8B0EC7-1F16-4EE8-B677-A4276F8457A4/Library/database/databaseA.sql' as databaseA;' [ERROR] The application has crashed with an unhandled exception. Stack trace:

I have tried Mark's approach by using:

var dir = Ti.Filesystem.getFile(Titanium.Filesystem.resourcesDirectory); db.execute("attach '" + dir.getParent()...

But I found that dir.getParent() return null value, therefore the db path is not valid.

I am using Android emulator to perform this test.

Do you have any idea on it?

Also, if my attached DB as around 15000 records. What is the estimated time to complete the inserting to another DB?

Thanks.

Your Answer

Think you can help? Login to answer this question!