Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 633
Developer3000 Avatar asked Dec 09 '14 16:12

Developer3000


People also ask

What is sqlite3_prepare_v2?

The sqlite3_prepare_v2() function compiles the SQL query. sqlite3_bind_int(res, 1, 3); The sqlite3_bind_int() binds an integer value to the prepared statement. The placeholder is replaced with integer value 3.

How do I close a SQLite database?

close( conn ) closes the SQLite connection by using the MATLAB® interface to SQLite. The SQLite connection object remains open until you close it using the close function. Always close this object when you finish using it.

What is prepared statement SQLite?

A prepared statement object is the compiled object code. All SQL must be converted into a prepared statement before it can be run. The life-cycle of a prepared statement object usually goes like this: Create the prepared statement object using sqlite3_prepare_v2().


2 Answers

  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.

like image 172
Rob Avatar answered Sep 19 '22 01:09

Rob


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.

like image 32
chenzhongpu Avatar answered Sep 17 '22 01:09

chenzhongpu