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

Question

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];

score 1 · Answer 1 · answered Jul 06 '10 at 04:01

1

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

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

answered Jul 06 '10 at 04:01

Nick Bedford

4,365
30
36

In that case you would want to escape your single-quotes by replacing them with double single-quotes. – Nick Bedford Jul 07 '10 at 03:15

score 1 · Answer 2 · edited May 23 '17 at 10:27

1

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.

edited May 23 '17 at 10:27

Community

1
1

answered Jul 06 '10 at 04:30

Georg Fritzsche

97,545
26
194
236

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

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.
}

edited May 23 '17 at 10:32

Community

1
1

answered Jul 06 '10 at 04:44

sagarkothari

24,520
50
165
235

Hi i tried this and had a success in the NSLOG. But however when i try to read the database again, the values werent there – Kenneth Jul 06 '10 at 05:31
databaseName = @"TestDatabase.sql"; //Get the path to the documents NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDir = [documentPaths objectAtIndex:0]; databasePath = [documentsDir stringByAppendingPathComponent:databaseName]; UIAlertView *view; // Setup the database object sqlite3 *database; appDelegate.dBCount = [[NSMutableArray alloc]init]; // Open the database from the users filessytem if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { – Kenneth Jul 06 '10 at 05:35
// Setup the SQL Statement and compile it for faster access const char *sqlStatement = "Select EntryID From XMLEntryID ORDER BY xeID DESC LIMIT 1 "; sqlite3_stmt *compiledStatement; if(sqlite3_prepare_v2(database, sqlStatement,-1, &compiledStatement, NULL) == SQLITE_OK) { // Loop through the results and add them to the feeds array while(sqlite3_step(compiledStatement) == SQLITE_ROW) { // Read the data from the result row NSString *aEntryID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)]; – Kenneth Jul 06 '10 at 05:36
// Add the user object to the users Array and respectively [appDelegate.dBCount addObject:aEntryID]; view = [[UIAlertView alloc] initWithTitle: @"A Message" message: aEntryID delegate: self cancelButtonTitle: @"Yay.!" otherButtonTitles: nil]; [view show]; [view autorelease]; [aEntryID release]; return; } } // Release the compiled statement from memory sqlite3_finalize(compiledStatement); } sqlite3_close(database); – Kenneth Jul 06 '10 at 05:36
this is the code in my method to read the database for the latest value that i have, however it seems that the values weren't inserted . – Kenneth Jul 06 '10 at 05:37
Oh ! I knew that - you should open the database from documents dir. Go to UserName/Library/Application Support/iPhone Simulator/3.0/Applications/YourAppID/Documents/yourDB.sqlite -> open this database & check weather inserted or not. – sagarkothari Jul 06 '10 at 05:48
I am afraid you might be checking wrong database. Once you copy your database to application's documents directory - you should open & check it from there. Every time when you compile if documents directory doesn't contain database,then it should copy from the main bundle, main bundle should have empty copy of the database. – sagarkothari Jul 06 '10 at 05:50
have you placed code for copying the database from main bundle to documents directory - or just pasted directly above code ? – sagarkothari Jul 06 '10 at 05:51
iv check every database in my pc and there werent any new data , below is the code for the inserting – Kenneth Jul 06 '10 at 06:07
//setup some globals databaseName = @"MedicalBedDatabase.sql"; //Get the path to the documents NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDir = [documentPaths objectAtIndex:0]; databasePath = [documentsDir stringByAppendingPathComponent:databaseName]; – Kenneth Jul 06 '10 at 06:08
sqlite3 *database; if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { NSString *str = [NSString stringWithFormat:@"INSERT INTO XMLEntryID(EntryID) VALUES ('%@')",[appDelegate.xmlLastCount objectAtIndex:0]]; const char *sqlStmt = [str UTF8String]; sqlite3_stmt *cmp_sqlStmt; 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(@"INSERT SUCCESS") : NSLog(@" INSERT Fail")); sqlite3_step(cmp_sqlStmt); } – Kenneth Jul 06 '10 at 06:08
sqlite3_finalize(cmp_sqlStmt); } sqlite3_close(database); – Kenneth Jul 06 '10 at 06:09
where do i implement that? in the appDelegate.m? – Kenneth Jul 06 '10 at 06:15
Ahhh :p it's up to your requirement. It's up to what you prefer. I prefer to place it on appDelegate.m - so, I placed there. Choice is up to you - where you save the database path string. – sagarkothari Jul 06 '10 at 06:17
hmm i think im having the correct database path. However i tried inserting values into the table on the Terminal applications but returns me SQL error: unable to open database file. Is it possible that the file is read-only? If so how can i change it – Kenneth Jul 06 '10 at 06:34
*UPDATE* It works! i found out i was doign something silly in my method that caused the database to be locked – Kenneth Jul 06 '10 at 08:42

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

3 Answers3