Unable to insert data from one table to another in sqlite in iOS with multiple statements

Question

I have two tables 'table1' and 'table_00'. I want to move some data from table1 to table_00

I have this method to do that:

- (void)moveData {


NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];

// Build the path to the database file

databasePath = [[NSString alloc]
                initWithString: [docsDir stringByAppendingPathComponent:
                                 @"MYDB_3.db"]];

 sqlite3_stmt    *statement;
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {

    NSString *insertSQL=@"INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID=00000" ;
     const char *insert_stmt = [insertSQL UTF8String];

    //i am using only one 'insertSQL', i put two 'insertSQL' strings here for reference 
   //mulitple statements
   NSString *insertSQL=@"INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE UTILITYID='00000';"
                         "INSERT INTO TABLE_01 SELECT * FROM TABLE_1 WHERE UTILITYID='00001';"
                         "INSERT INTO TABLE_02 SELECT * FROM TABLE_1 WHERE UTILITYID='00002';"
    "INSERT INTO TABLE_03 SELECT * FROM TABLE_1 WHERE UTILITYID='00003';";

  //this multiple query statement is not working

    sqlite3_prepare_v2(myDatabase, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE) {
                   NSLog(@"data moved");
    } else {
        NSLog(@"data not moved");
    }
    sqlite3_finalize(statement);
    sqlite3_close(myDatabase);
}

After I call this method ,I can move the data by using a single query but, I am unable to move the data by using multiple statements.Could you please tell me why is this happening?

Thanks in advance.

Peter Pei Guo · Accepted Answer · 2014-10-03 20:38:28Z

1

I think you have one extra from, and it should be:

INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID= '00000'

A better way is to use placeholder:

sqlite3_stmt *compiled = nil;
const char *sql = "INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID= ?";
sqlite3_prepare_v2(database, sql, -1, &compiled, NULL);
sqlite3_bind_text(compiled, 1, [yourId UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_step(compiled);
sqlite3_finalize(compiled);

edited Oct 3, 2014 at 20:38

answered Oct 3, 2014 at 20:15

Peter Pei Guo

7,89018 gold badges37 silver badges54 bronze badges

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

3 Comments

Teja Nandamuri Over a year ago

hi, I did that and it says data moved, but when I check into that table,there is no data

Teja Nandamuri Over a year ago

I did, It worked. But I tried to do the same with the multiple statements ,in this case only the first statement executes. Look at the question, I will modify it again!

Teja Nandamuri Over a year ago

I did execute the multiple statements in creating the tables. I guess then multiple thing doesn't work everywhere.

Teja Nandamuri · Accepted Answer · 2014-10-03 21:25:31Z

I used for-loop to execute the multiple statements. Here is how I did that

- (void)moveDatatoUtilityTables {

NSMutableArray *ID_Table=[[NSMutableArray alloc]init];
NSMutableArray *ID=[[NSMutableArray alloc]init];

[ID_Table addObject:@"TABLE_00"];
[ID_Table addObject:@"TABLE_01"];
[ID_Table addObject:@"TABLE_02"];

[ID addObject:@"00000"];
[ID addObject:@"00001"];
[ID addObject:@"00002"];

NSLog(@"database path is %@",databasePath);

sqlite3_stmt    *statement;
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {

    for (int i=0; i<2; i++) {


        NSString *insertSQL=[NSString stringWithFormat:@" INSERT INTO %@ SELECT * FROM TABLE_1 WHERE ID='%@'",[ID_Table objectAtIndex:i],[ID objectAtIndex:i]];


    const char *insert_stmt = [insertSQL UTF8String];


    sqlite3_prepare_v2(myDatabase, insert_stmt,
                       -1, &statement, NULL);

   // const char *sqlite3_column_name(sqlite3_stmt*, int N);

    if (sqlite3_step(statement) == SQLITE_DONE) {

                   NSLog(@"data moved");


    } else {

        NSLog(@"data not moved");
    }



    sqlite3_finalize(statement);

    }
        sqlite3_close(myDatabase);
}

}

Collectives™ on Stack Overflow

Unable to insert data from one table to another in sqlite in iOS with multiple statements

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related