sqlite3_prepare_v2 / sqlite3_exec

Question

Few questions about sqlite3:

1.When is necessary to use first approach one and when the other ? It is a difference between them?

sqlite3_prepare_v2(_contactDB, sql_stmt_getIdRecepteur, -1, &sqlStatement, NULL);

and

if(sqlite3_prepare_v2(_contactDB, sql_stmt_getIdRecepteur, -1, &sqlStatement, NULL) == SQLITE_OK) {}

2.When is most indicated to use 'sqlite3_exec' than 'sqlite3_prepare_v2' ?

3.When is necessary to use first one, the second or the third:

while(sqlite3_step(sqlStatement) == SQLITE_ROW){}
if(sqlite3_step(sqlStatement) == SQLITE_ROW){}
if(sqlite3_step(sqlStatement) == SQLITE_DONE){}

Thank you in advance

Answer 1

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.

Answer 2

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.

Answer 3

A late answer to #2 that I just found: use sqlite3_exec (or use sqlite3_prepare_v2 in a loop) when sql_stmt_getIdRecepteur actually contains multiple SQL statements. From the docs for sqlite3_prepare_v2:

These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.

sqlite3_exec includes an internal loop that calls sqlite3_prepare_v2 multiple times until the entire input string is compiled. If you don't use sqlite3_exec, and you have multiple SQL statements in a string, you need to check the pzTail return value from sqlite3_prepare_v2.