Insert multiple tables in same database in sqlite

Question

Creating an Iphone application, I used to perform INSERT QUERY on different databases. But when I tried inserting the data in the same database, it gives me assertion failure. I am reading two different XML URL and trying to create and then insert the data in the table as it is read by creating DB. Does anyone have idea why does this show me error ? I have the following code which does insert on DB using SQLITE:

  -(void) insertProductTable:(NSMutableArray *)Dict{
[Dict retain];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"product.sql"];
NSInteger i;

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

    const char * insert_product_sql = "INSERT OR REPLACE INTO test(ID,KEY ) VALUES (?,?)" ;
    if(sqlite3_prepare_v2(database, insert_product_sql, -1, &product_statement, nil) != SQLITE_OK){
        NSAssert1(0, @"Error: failed to prepare insert_product_sql: '%s'.", sqlite3_errmsg(database));
    }

    for (i = 0; i < [self.array count]; i++) {
     sqlite3_bind_text(product_statement, 1, [[Dict valueForKey:@"ID"] UTF8String] ,-1, SQLITE_TRANSIENT);
     sqlite3_bind_text(product_statement, 2, [[Dict valueForKey:@"KEY"] UTF8String] ,-1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(product_statement) != SQLITE_DONE) {
        NSAssert(0,@"Error updating table");
    }

    [Dict release]; Dict = 0;
    sqlite3_finalize(product_statement);
    sqlite3_close(database);

}
}

I have the following method which does the different insert function but different table but at this stage it doesnot allow me to insert throwing me Assertion Failure. Both the methods are called by different class.

    -(void) insertTable:(NSMutableArray *)Dict{
[Dict retain];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"product.sql"];
NSInteger i;

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

    const char * insert_sql = "INSERT OR REPLACE INTO test1(Term,Value ) VALUES (?,?)" ;
    if(sqlite3_prepare_v2(database, insert_sql, -1, &product_statement, nil) != SQLITE_OK){
        NSAssert1(0, @"Error: failed to prepare insert_sql: '%s'.", sqlite3_errmsg(database));
    }

    for (i = 0; i < [self.arayList count]; i++) {
     sqlite3_bind_text(product_statement, 1, [[Dict valueForKey:@"TERM"] UTF8String] ,-1, SQLITE_TRANSIENT);
     sqlite3_bind_text(product_statement, 2, [[Dict valueForKey:@"VALUE"] UTF8String] ,-1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(product_statement) != SQLITE_DONE) {
        NSAssert(0,@"Error updating table");
    }

    [Dict release]; Dict = 0;

    sqlite3_finalize(product_statement);
    sqlite3_close(database);

}
}

Rahul Sharma · Accepted Answer · 2011-06-09 06:36:34Z

1

@lifemoveson

I used to your code to debug and found that the function

sqlite3_prepare_v2(database, insert_product_sql, -1, &product_statement, nil)

returns 1 everytime and it corresponds to your missing database at the desired location.

What I will suggest to you is make a function such as this

-(void)checkAndCreateDatabase{
    databaseName = @"MasterDB.sqlite";
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
    NSLog(@"database path %@",databasePath);
    BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) return;

    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
    //[fileManager release];

    // Now we can add new table

    sqlite3 *database;
    sqlite3_stmt *statement; 
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

        NSString *sqlQuery = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS tbl_Login (userId integer PRIMARY KEY,userName text,password text);"] ;
        const char *create_stmt = [sqlQuery UTF8String];
        sqlite3_prepare_v2(database, create_stmt, -1, &statement, NULL);

        if(sqlite3_step(statement)==SQLITE_DONE) 
        {
            NSLog(@"Table created");            
        }
        else 
        {
            NSLog(@"Table could not be created");
        }   
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

Which sets your class variable databasePath and ensure that before you run any database query the database is actually present in the applcation's document directory. You can call this function in application delegate

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions{
//Other code 
[self checkAndCreateDatabase];

}

When you make any database call use this

const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath , &database) == SQLITE_OK) {
//Code to perform insert operations
 }

i would suggest you to write a separate class for all the database operation that you perform and instead call the class function to achieve the desired functionality. This will save your efforts from rewriting the code.

You can make use of FMDB wrapper for this which you can find here

Hope this helps :-)

edited Jun 9, 2011 at 6:36

answered Jun 9, 2011 at 4:54

Rahul Sharma

3,0031 gold badge23 silver badges47 bronze badges

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

3 Comments

lifemoveson Over a year ago

Thanks Rahul for pointing the error and the solution. Just one more small question. How can I add the Create table query in checkAndCreateDatabase or Do I need to add new function for the create query. Also, I have two different functions in different class to do two Insert queries in the same DB. How can I achieve that?

lifemoveson Over a year ago

Thanks for the help Rahul. I did use the logic you have suggested but have a quick question. As you can see I have two different insert function above both in different class.How can I point the database to that directory? Also,I havent worked with FMDB before and have no idea of how it works. I am not sure if I am gonna use it.

Rahul Sharma Over a year ago

I suggested FMDB looking at the functions itself. Here you have to manually extract the value for particular keys in your insert functions. What about you calling a function with two argument.. an NSString i.e your query and an NSArray containing the arguments viz objects for keys such as @"TERMS",@"ID"and so on.Rest assignment will be taken care by FMDB.Thats the beauty of FMDB. You can search for a good tutorial to use FMDB .

Collectives™ on Stack Overflow

Insert multiple tables in same database in sqlite

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related