More SQLite C API

In previous instalment we used sqlite3_open and sqlite3_prepare to connect to db file and execute select statement. Let us take a better look at it again. Those functions are legacy interface, we could use new sqlite3_open_v2 and sqlite3_prepare_v2 which are allowing us more control over execution and they are also recommended by SQLite development team. Let us look at sqlite3_open. This is from SQLite documentation:

int sqlite3_open( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb /* OUT: SQLite db handle */ ); int sqlite3_open16( const void *filename, /* Database filename (UTF-16) */ sqlite3 **ppDb /* OUT: SQLite db handle */ ); int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs /* Name of VFS module to use */ );

In all three cases, the first argument is name of database file to be opened and the second one is handle:

typedef struct sqlite3 sqlite3;

We will need that handle to manipulate db and close it at the end. From comment we see that string with name of db file could be UTF-8 or UTF-16 encoded. What are flags? Here are definitions:

#define SQLITE_OPEN_READONLY 0x00000001 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_READWRITE 0x00000002 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_CREATE 0x00000004 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_URI 0x00000040 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_NOMUTEX 0x00008000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_FULLMUTEX 0x00010000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_SHAREDCACHE 0x00020000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_PRIVATECACHE 0x00040000 /* Ok for sqlite3_open_v2() */

I will wisely skip last four, for now. If we specify SQLITE_OPEN_READWRITE we should be able to read and write, unless OS have marked file as read only, file must exist or error is returned. In order to simulate behavior of sqlite3_open we should use  SQLITE_OPEN_READWRITE |  SQLITE_OPEN_CREATE. The fourth argument of sqlite3_open_v2 function is name of the sqlit3_vfs object that defines the operating system interface that the new database connection should use. If we pass NULL as the fourth argument we get default sqlit3_vfs object.
If  sqlite3_open_v2 doesn't return SQLITE_OK, we have error and we should exit. We still may have proper db handle or NULL handle, calling sqlite3_close with NULL pointer is not problem.
So, we have something like this as template:

#include <sqlite3.h> #include <stdio.h> int main(int argc, char **argv) { char *file = "some.db"; sqlite3 *db = NULL; int rc = 0; rc = sqlite3_open_v2( file, &db, SQLITE_OPEN_READWRITE, NULL ); if ( rc != SQLITE_OK) { printf("Failure to open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } /* do something with connection */ sqlite3_close(db); return 0; }

Call to sqlite3_errmsg should return string with description of message. We want to do resource management and to match  sqlite3_open with sqlite3_close. If we compile this and we do not have some.db file, after execution we should see descriptive error message.

./test
Failure to open database: unable to open database file

While passing to sqlite3_close NULL pointer is not problem, passing to it handle of already closed connection is problem.
Situation with sqlite3_prepare_v2 is similar to  sqlite3_open_v2, new API is preferable to legacy one and it should be used. Here both new and legacy functions are accepting the same number of arguments but behavior is different. Please check in documentation how they differ.

int sqlite3_prepare_v2( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ );

There is also UTF-16 version with about the same arguments. It prepares statement, compiles it to bytecode  and if successful returns pointer to compiled  sqlite3_stmt.

sql = "select * from sqlite_master"; rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); return 1; } rc = sqlite3_step(stmt); col = sqlite3_column_count(stmt); for(i=0; i < col; i++) { printf("%s\t",sqlite3_column_name(stmt, i)); } printf("\n------------------------------------------------------\n"); while(rc == SQLITE_ROW) { for(i=0; i < col; i++) { printf("%s\t", sqlite3_column_text(stmt, i)); } printf("\n"); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt);

After statement is successfully prepared we iterate using sqlite3_step and at the end we finalize statement. In the case of sqlite3_prepare  returning error, we in general want to check is statement handle points to something and do cleanup if it does.
There is convenience wrapper around  prepare_v2-step-finalize, it is sqlite3_exec.

int sqlite3_exec( sqlite3*, /* An open database */ const char *sql, /* SQL to be evaluated */ int (*callback)(void*,int,char**,char**), /* Callback function */ void *, /* 1st argument to callback */ char **errmsg /* Error msg written here */ );

If we are doing insert, we do not really need callback, so we pass NULL pointer as the third and fourth argument.

#include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = NULL, *sql = NULL; int rc = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL); if (rc != SQLITE_OK) { printf("Failure to open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sql = "INSERT INTO jane VALUES(1,'Mr','Smith');" "INSERT INTO jane VALUES(2,'Mrs','Doe');" "INSERT INTO jane VALUES(3,'Mr','Doe');"; rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg); if (rc != SQLITE_OK ) { printf("Exec failure: %s\n", err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; }

If we don't have table jane in test.db exec will return error.

Exec failure: no such table: jane

or if we execute it more than once:

Exec failure: PRIMARY KEY must be unique

Error message is allocated using sqlite3_malloc and we have to free it using sqlite3_free.