sqlite3_prepare_v2 / sqlite3_exec

1. One should always check the return values of SQLite functions, in order to make sure it succeeded, thus the use of the if statement is greatly preferred. And if it failed, one would call sqlite3_errmsg() to retrieve a C string description of the error.

2. One would use sqlite3_prepare_v2 (instead of sqlite3_exec) in any situation in which either:

   • one is returning data and therefore will call sqlite3_step followed by one or more sqlite3_column_xxx functions, repeating that process for each row of data; or

   • one is binding values to the ? placeholders in the SQL with sqlite3_bind_xxx.

   One can infer from the above that one would use sqlite3_exec only when (a) the SQL string has no parameters; and (b) the SQL does not return any data. The sqlite3_exec is simpler, but should only be used in these particular situations.

   Please note: That point regarding the ? placeholders is very important: One should avoid building SQL statements manually (e.g., with stringWithFormat or Swift string interpolation), especially if the values being inserted include end-user input. For example, if you call sqlite3_exec with INSERT, UPDATE, or DELETE statement that was created using user input (e.g., inserting some value provided by user into the database), you inherently risk the very real possibility of problems arising from un-escaped quotation marks and escape symbols, etc. One is also exposed to SQL injection attacks.

   For example, if commentString was provided as a result of user input, this would be inadvisable:

   NSString *sql = [NSString stringWithFormat:@"INSERT INTO COMMENTS (COMMENT) VALUES ('%@')", commentString];
   if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, NULL) != SQLITE_OK) {
       NSLog(@"Insert failure: %s", sqlite3_errmsg(database));
   }
   

   Instead, you should:

   const char *sql = "INSERT INTO COMMENTS (COMMENT) VALUES (?)";
   if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
       NSLog(@"Prepare failure: %s", sqlite3_errmsg(database));
       return;
   }
   if (sqlite3_bind_text(statement, 1, [commentString UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK) {
       NSLog(@"Bind 1 failure: %s", sqlite3_errmsg(database));
       sqlite3_finalize(statement);
       return;
   }
   if (sqlite3_step(statement) != SQLITE_DONE) {
       NSLog(@"Step failure: %s", sqlite3_errmsg(database));
   }
   sqlite3_finalize(statement);
   

   Note, if this proper implementation felt like it was too much work, you could use the FMDB library, which would simplify it to:

   if (![db executeUpdate:@"INSERT INTO COMMENTS (COMMENT) VALUES (?)", commentString]) {
       NSLog(@"Insert failure: %@", [db lastErrorMessage]);
   }
   

   This provides the rigor of sqlite3_prepare_v2 approach, but the simplicity of the sqlite3_exec interface.

3. When retrieving multiple rows of data, one would use:

   while(sqlite3_step(sqlStatement) == SQLITE_ROW) { ... }
   

   Or, better, if you wanted to do the proper error handling, you'd do:

   int rc;
   while ((rc = sqlite3_step(sqlStatement)) == SQLITE_ROW) {
       // process row here
   }
   if (rc != SQLITE_DONE) {
        NSLog(@"Step failure: %s", sqlite3_errmsg(database));
   }
   

   When retrieving a single row of data, one would:

   if (sqlite3_step(sqlStatement) != SQLITE_ROW) {
       NSLog(@"Step failure: %s", sqlite3_errmsg(database));
   }
   

   When performing SQL that will not return any data, one would:

   if (sqlite3_step(sqlStatement) != SQLITE_DONE) {
       NSLog(@"Step failure: %s", sqlite3_errmsg(database));
   }
   

---

When using the SQLite C interface, you can see that it takes a little work to do it properly. There is a thin Objective-C wrapper around this interface called FMDB, which not only simplifies the interaction with the SQLite database and is a little more robust.

For question 1,in most cases, you need to verify that result is equal to SQLITE_OK to make sure your command ran successfully. (SQLITE_OK is int type**). Therefore, the second is preferred.

For question 2, the function sqlite3_exec is used to run any command that doesn't return data, including updates,inserts and deletes. Retrieving data from the database is little more involved. And the function sqlite3_prepare_v2 can used for SELECT (in SQL). In common, create table often use the first one.

For question 3, well, while is for loop, while if is for condition. Generally, if you retrieve dada from db, you need a loop to traverse the *return array**. If you insert a data to db (for instance), you can use SQLITE_DONE to check you operation.

By the way, core data is preferred in IOS for most cases.