Developer3000 Developer3000 - 8 months ago 78
iOS Question

sqlite3_prepare_v2 / sqlite3_exec

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


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

Rob Rob
  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 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));
    if (sqlite3_bind_text(statement, 1, [commentString UTF8String], -1, NULL) != SQLITE_OK) {
        NSLog(@"Bind 1 failure: %s", sqlite3_errmsg(database));
    if (sqlite3_step(statement) != SQLITE_DONE) {
        NSLog(@"Step failure: %s", sqlite3_errmsg(database));

    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.