Open two sqlite database and execute query in iphone(读写两数据库)

Question

Here i want to execute query using two database and copy data from table of database1 to another database2. here i am able to open one database but getting problem in openning other database. Thanks in advance to all.

CurrentStatus *status = [CurrentStatus sharedCurrentStatus];    sqlite3 *database;    sqlite3 *database1;    sqlite3_stmt *statement;    sqlite3_stmt *statement1;    NSString *dbPath = [status.applicationDocumentDirectory stringByAppendingPathComponent:@"database.sqlite"];    NSString *dbPath1 = [status.applicationDocumentDirectory stringByAppendingPathComponent:@"database1.sqlite"];    if ((sqlite3_open_v2([dbPath UTF8String], &database, SQLITE_OPEN_READWRITE , NULL) == SQLITE_OK) && (sqlite3_open_v2([dbPath1 UTF8String], &database1, SQLITE_OPEN_READWRITE , NULL) == SQLITE_OK)) {        NSString *sqlStr = [[NSString alloc] initWithFormat:@"select * from Login" ];         NSString *sql = [[NSString alloc] initWithString:sqlStr];                if ((sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK)) {                    NSLog(@"DB prepare_v2 Opening successfully");                    if (sqlite3_step(statement) == SQLITE_ROW) {                    }                    else                    {                    }                    sqlite3_finalize(statement);        NSLog(@"DB Opening successfully");               sqlite3_close(database);    }else    {        NSLog(@"else DB Opening successfully");    }        sqlite3_close(database);    }

Close the first one and then try for 2nd Db to Open . & reset the Statement also... — Oct 25 at 5:19
but i want copy data from database1 and paste into database2 table — Oct 25 at 5:20

Kumar Kl 1,592318 · Accepted Answer · 2013-10-25 05:36:31Z

Try something similar to achieve your task ..

First, you are opening the "DB1" database, and then execute the following statement:

ATTACH DATABASE "2ndDB.db" AS 2ndDB;

After that, you can use the CREATE TABLE syntax:

CREATE TABLE newTableInDB1 AS SELECT * FROM 2ndDB.oldTableInMyOtherDB;

This will "copy" the data over to your new database

Great, Yes need to attached both database for executing the query. — Oct 25 at 7:11
2ndDB is not a valid identifier; you have to quote it as "2ndDB" or use something like secondDB. — Oct 25 at 8:05
@Indra The first database (DB1) is already open; only the second one needs to be attached. See thedocumentation. — Oct 25 at 8:07
Thanks for reply, But we can open two database directly, Now i am able to copy data from one table to another table database2 .its working. — Oct 25 at 8:18

TamilKing 78110 · Answer 2 · 2013-10-25 05:28:20Z

Copy which are the data you want it from database1 add each row in NSMutableDictionary and then add it to NSMutableArray. Close and reset your statement then open the database2 copy the data from NSMutableArray, that array act as database for you..

I hope it will work for you..

Rudeboy 49627 · Answer 3 · 2013-10-25 05:48:03Z

You need to You need to Attach databases and then copy tables from one database to another database. You need to do something like this:

NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS %@", sourceDBPath,dbAlias];if (sqlite3_exec(db, [attachSQL UTF8String], NULL, NULL, &errorMessage) != SQLITE_OK)   {        NSString *errorStr = [NSString stringWithFormat:@"The database could not be attached: %@",[NSString stringWithCString:errorMessage encoding:NSUTF8StringEncoding]];          return errorStr;   }

After that you need to copy table , like:

NSString *queryString=[NSString stringWithFormat:@"CREATE TABLE %@ AS SELECT * FROM %@.%@;",newTableName,dbAlias,origTableName];

And you are done, you have the new copy of database.

Open two sqlite database and execute query in iphone(读写两数据库)

3 Answers