iPHONE SDK- sqlite , how do i do an insert statement

Question

Hi guys. i have a database that is in the documents directorys of the Application/iPhoneSimulator/3.2/Applications/etc/Documents

I have this code under my method

    databaseName = @"database.sql";
NSArray *documentsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory.NSUserDomainMask,YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

How do i do an insert with a variable/ array . Something like "INSERT INTO TABLE (COLUMN) VALUES ('%@'),[appDelegate.variable objectAtIndex:0];

Answer 1

+(NSString *)stringWithFormat:(NSString *)format parameters:...];

NSString sql = [NSString stringWithFormat:@"INSERT INTO table VALUES('%@')", @"Hello, world!"];
sqlite3_....

Answer 2

Use either prepared statements in combination with the bind_*() functions (e.g. bind_text()) or the mprintf() function to insert strings, see this question for details.

To get a raw C-string you can pass to these functions use -UTF8String or -cStringUsingEncoding: on a NSString.

Answer 3

I insist you to go through this question. First of all copy the database from main bundle to your application's document dir. You can follow below code to implement it.

    NSString *databaseFile=[[NSBundle mainBundle] pathForResource:kDataBaseName ofType:kDataBaseExt];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *basePath = ([paths count] > 0) ? [paths objectAtIndex:0] : nil;
    NSString *dbPath=[basePath stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.%@",kDataBaseName,kDataBaseExt]];
    NSFileManager *fm=[NSFileManager defaultManager];

    if(![fm fileExistsAtPath:dbPath]){
        [fm copyItemAtPath:databaseFile toPath:dbPath error:nil];
    }

    [fm release];

    self.dataBasePath=dbPath;

I am supplying you directly my project code. Please add comment if any doubts. I have added comments for the explanation.

// function with multiple arguments which is going to be used for inserting into table.
+(void)insertBuilding:(NSString*)BName streetNo:(NSInteger)streetNo streetName:(NSString*)streetName streetDir:(NSString*)streetDir muni:(NSString*)muni province:(NSString*)province bAccess:(NSString*)bAccess bType:(NSString*)bType amnity:(NSString*)amnity latitude:(NSString*)latitude longitude:(NSString*)longitude imageName:(NSString*)imageName {
    // application delegate where I have saved my database path.
    BuildingLocatorAppDelegate *x=(BuildingLocatorAppDelegate *)[[UIApplication sharedApplication]delegate];
    sqlite3 *database; // database pointer
    // verifying if database successfully opened from path or not.
    // you must open database for executing insert query    
    // i have supplied database path in argument 
    // opened database address will be assigned to database pointer.
    if(sqlite3_open([[x dataBasePath] UTF8String],&database) == SQLITE_OK) {
        // creating a simple insert query string with arguments.
        NSString *str=[NSString stringWithFormat:@"insert into buildingDtl(b_name,streetNo,streetName,streetDir,muni,province,b_access,b_type,aminity,latitude,longitude,b_image) values('%@',%i,'%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",BName,streetNo,streetName,streetDir,muni,province,bAccess,bType,amnity,latitude,longitude,imageName];
        // converting query to UTF8string.
        const char *sqlStmt=[str UTF8String];       
        sqlite3_stmt *cmp_sqlStmt;
        // preparing for execution of statement.
        if(sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL)==SQLITE_OK) {
            int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL);
            ((returnValue==SQLITE_OK) ?  NSLog(@"Success") :  NSLog(@"UnSuccess") );
                // if NSLog -> unsuccess - that means - there is some problem with insert query.
            sqlite3_step(cmp_sqlStmt);
        }
        sqlite3_finalize(cmp_sqlStmt);
    }
    sqlite3_close(database);
    // please don't forget to close database.
}