Using the C Language Interface

Now that you have made sure the library is available on your system, let's take a look at the functions that make up the C language interface.

Opening and Closing a Database

The function to open a SQLite database is sqlite_open(). The prototype is

 sqlite *sqlite_open(const char *dbname, int mode, char **errmsg); 

The dbname parameter is the name of the database file on the filesystem. If just a filename is given, it is assumed that the database is in the current directory at runtime. A relative or absolute path can be specified.

The mode parameter is intended for future use, to specify the file mode in which the database file is opened. Typical values would be 0777 for read/write access or 0444 for read-only. However, at the time of writing, this parameter is ignored by the library.

The sqlite structure to which a pointer is returned is an opaque structure that must be passed as the first parameter to all the other SQLite API functions. If the database cannot be opened, the return value will be NULL and errmsg will point to the location of the error message created by sqlite_open(). The memory allocated for errmsg should be freed using sqlite_freemem().

To close the database, use sqlite_close(), which is simply passed the open sqlite structure pointer.

The example in Listing 6.1 shows how to establish a connection to a database called progdb in the current directory, and displays an error message if the database cannot be opened.

Listing 6.1. Connecting to a SQLite Database Using C/C++

 #include <stdio.h> #include <sqlite.h> main() {   char *errmsg;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   printf("Successfully connected to database\n");   sqlite_close(db); } 

We compile listing6.1.c into an executable listing6.1 as follows:

 $ gcc -o listing6.1 listing6.1.c lsqlite 

Executing the new program should tell us that we have been able to connect to the progdb database. The first time you run the program, the database will be created and you will be able to see a file called progdb in the current directory:

 $ ./listing6.1 Successfully connected to database 

If there was a problem opening the database, for example, if you do not have write permission on the current directory, an error will be displayed:

 $ cd / $ /path/to/listing6.1 Could not open database: unable to open database: progdb 

Getting Information About the SQLite Library

Two constants are defined in sqlite.h that allow you to find information about the version and encoding of the library:

 const char sqlite_version[] const char sqlite_encoding[] 

The value of sqlite_version is a string representation of the library version number, for instance 2.8.13.

The encoding method for the library is determined at compile time and the default value of sqlite_encoding is iso8859 for ISO-8859-1 encoding. The alternative encoding method currently provided by SQLite is UTF-8. The encoding method used by a program cannot be changed without recompiling the library.

Executing SQL Statements

The function sqlite_exec() is used to send an SQL statement to the SQLite engine. Its prototype is

 int sqlite_exec(   sqlite *db,   char *sql,   int (*xCallback)(void*,int,char**,char**),   void pArg,   char **errmsg ); 

The five parameters required are as follows:

• db is a valid sqlite structure pointer.

• sql is a null-terminated character string containing one or more SQL statements.

• The third parameter is a pointer to a callback function or may be NULL if no callback is required.

• pArg is a pointer to the first argument of the callback function.

• If the query fails, the error message from SQLite will be pointed to by errmsg.

The callback function is invoked once for each row returned by the query, and we will examine this shortly. An SQL statement that is not a SELECT does not return any rows, so no callback function is required and the third and fourth parameters can be NULL.

Note

A semicolon is not required to terminate a single SQL query in the sql parameter. However, if multiple statements are passed in one sqlite_exec() call, they must be separated with semicolons.

The example in Listing 6.2 shows how a CREATE TABLE statement is executed using the C interface.

Listing 6.2. Executing a CREATE TABLE Statement with the C Library

 #include <stdio.h> #include <sqlite.h> main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   ret = sqlite_exec(db,           "CREATE TABLE contacts (   \n"           "  id INTEGER PRIMARY KEY, \n"           "  first_name CHAR,        \n"           "  last_name  CHAR,        \n"           "  email      CHAR)",  NULL, NULL, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   sqlite_close(db); } 

The SQL statement in Listing 6.2 is formatted using \n characters and indented for readability. This is not a requirement, but as the schema stored in the database is the exact CREATE TABLE statement executed, the formatting here will make the output of the schema more readablefor instance when using the .schema command in sqlite.

Running the compiled program should be silent, assuming there are no problems accessing progdb, and the table will be created. If you execute the program a second time the table name will already be in use, and the following error message from SQLite will be displayed.

 $ ./listing6.2 SQL error: table contacts already exists 

The constant SQLITE_OK is a zero value and is used to check for successful execution of an SQL statement. Any non-zero value indicates that an error has occurred. The full list of return code constants is shown in Table 6.1.

Table 6.1. Return Code Constants

Constant	Value	Description
SQLITE_OK	0	Successful result.
SQLITE_ERROR	1	SQL error or missing database.
SQLITE_INTERNAL	2	An internal logic error in SQLite.
SQLITE_PERM	3	Access permission denied.
SQLITE_ABORT	4	Callback routine requested an abort.
SQLITE_BUSY	5	The database file is locked.
SQLITE_LOCKED	6	A table in the database is locked.
SQLITE_NOMEM	7	A malloc() failed.
SQLITE_READONLY	8	Attempt to write to a read-only database.
SQLITE_INTERRUPT	9	Operation terminated by sqlite_interrupt().
SQLITE_IOERR	10	A disk I/O error occurred.
SQLITE_CORRUPT	11	The database disk image is malformed.
SQLITE_NOTFOUND	12	Table or record not found.
SQLITE_FULL	13	Insertion failed because database is full.
SQLITE_CANTOPEN	14	Unable to open the database file.
SQLITE_PROTOCOL	15	Database lock protocol error.
SQLITE_EMPTY	16	Database table is empty.
SQLITE_SCHEMA	17	The database schema changed.
SQLITE_TOOBIG	18	Too much data for one row of a table.
SQLITE_CONSTRAINT	19	Abort due to constraint violation.
SQLITE_MISMATCH	20	Data type mismatch.
SQLITE_MISUSE	21	Library used incorrectly.
SQLITE_NOLFS	22	Uses OS features not supported on host.
SQLITE_AUTH	23	Authorization denied.
SQLITE_ROW	100	sqlite_step() has another row ready.
SQLITE_DONE	101	sqlite_step() has finished executing.

More information on each of these return codes can be found in Appendix E, "C/C++ Interface Reference."

Note

If you want to validate a string to see if it forms a valid SQL statement, use the sqlite_complete() function. Taking a single character pointer argument, the function returns true when the string supplied forms a complete SQL statement or false if more text is required.

The sqlite_complete() function considers a statement to be complete if it ends with a semicolon, ignoring semicolons that separate statements within a CREATE TRIGGER statement. The function does not attempt to validate the syntax of the SQL statement itself.

Using Commands That Change the Database

Now that we have created a table, let's insert some rows. Listing 6.3 creates a program that reads three values from stdin that are used as the first_name, last_name, and email fields in a subsequent INSERT statement.

Listing 6.3. Inserting Records into the Database

 #include <stdio.h> #include <sqlite.h> #define MAXLEN 32 #define MAXQRY 200 main() {   char *errmsg;   int ret;   char first_name[MAXLEN], last_name[MAXLEN], email[MAXLEN];   char qry[MAXQRY];   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   printf("Enter first name: ");   scanf("%s", first_name);   printf("Enter last name: ");   scanf("%s", last_name);   printf("Enter email: ");   scanf("%s", email);   sprintf(qry, "INSERT INTO CONTACTS (first_name, last_name, email) \n"                "VALUES ('%s', '%s', '%s')", first_name, last_name, email);   ret = sqlite_exec(db, qry, NULL, NULL, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n%s\n", errmsg, qry);   }   else   {     printf("%s %s (%s) was inserted as ID %d\n",             first_name, last_name, email, sqlite_last_insert_rowid(db));   }   sqlite_close(db); } 

As the contacts table includes an INTEGER PRIMARY KEY field, a unique number is assigned to the id column each time an INSERT is performed. The value assigned can be retrieved using the sqlite_last_insert_rowid() function. This statement is used to confirm that the insert took place successfully and shows the assigned id value.

 printf("%s %s (%s) was inserted as ID %d\n",         first_name, last_name, email, sqlite_last_insert_rowid(db)); 

Compile and run Listing 6.3 and enter some details:

 $ ./listing6.3 Enter first name: Chris Enter last name: Newman Enter email: [e-mail] Chris Newman ([e-mail]) was inserted as ID 1 

Be aware that no validation is being done in this program to make sure a valid SQL statement is being submitted to SQLite. The apostrophe or single quote character is the most common cause of problems, as you can see if you attempt to enter a contact name containing an apostrophe.

 [chris@python c]$ ./listing6.3 Enter first name: Paddy Enter last name: O'Brien Enter email: [e-mail] SQL error: unrecognized token: "@" INSERT INTO CONTACTS (first_name, last_name, email) VALUES ('Paddy', 'O'Brien', '[e-mail]') 

The SQL query displayed in the error message shows the problem caused by the apostrophe. In this case the @ symbol causes an error, which appears strange at first but look at the pairs of single quotes in the VALUES list. Before SQLite even attempts to perform the insert, it reads three strings from the line'Paddy', 'O' and ', '. The @ sign is not contained in quotes and causes a parser error.

Fortunately the library provides an easy way around this using sqlite_mprintf().

This function works in a similar way to sprintf() except that a pointer to a string buffer is returnedthe memory assigned to the buffer by malloc() should be freed using sqlite_freemem() when it is no longer needed. Using sqlite_mprintf(), the format code %q works like %s, but any single quotes are delimited, so they can be inserted as literal characters.

We could rewrite the statement that builds the SQL query in Listing 6.3 like this:

 char *qry; ... qry = sqlite_mprintf("INSERT INTO CONTACTS (first_name, last_name, email) \n"                "VALUES ('%q', '%q', '%q')", first_name, last_name, email); 

For convenience, the function sqlite_exec_printf() works like sqlite_exec() but accepts a printf-style format string with its arguments given after the usual five parameters. Again the %q format code can be used to make sure quotes are delimited correctly.

We can use this function to combine the sprintf() and sqlite_exec() calls in Listing 6.3 while also protecting against problems with single quotes, as the following example demonstrates.

 ret = sqlite_exec_printf(db,              "INSERT INTO CONTACTS (first_name, last_name, email) \n"              "VALUES ('%q', '%q', '%q')", NULL, NULL, &errmsg,                                           first_name, last_name, email); 

Replace lines 33 to 36 with the preceding statement, and then recompile and execute the program to attempt to insert the contact name that caused us problems before:

 Enter first name: Paddy Enter last name: O'Brien Enter email: [e-mail] Paddy O'Brien ([e-mail]) was inserted as ID 2 

The INSERT statement in the preceding example used an implicit transaction to execute a single change to the database. Where an explicit transaction is required around a group of statements, it can be started simply by executing a BEGIN TRANSACTION command through the SQLite C Library.

 sqlite_exec(db, "BEGIN TRANSACTION"); 

Issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION instruction is done the same way. Be aware that if sqlite_close() is called during an open transaction, an implicit ROLLBACK will take place.

Whenever an UPDATE or DELETE operation is performed, records in the database will be changedassuming of course that the WHERE clause does find some rows that should be affected. The function sqlite_changes() allows us to see just how many matching rows were changed or removed as a result of an SQL command.

Listing 6.4 issues an UPDATE statement converting each first_name and last_name value to uppercase in the contacts table and displays the number of rows affected by this command. Because there is no WHERE clause in the SQL statement, the number of rows affected will be the total number of rows in the contacts table.

Listing 6.4. Performing an UPDATE on the Database

 #include <stdio.h> #include <sqlite.h> main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   ret = sqlite_exec(db,           "UPDATE contacts "           "SET first_name = upper(first_name), \n"           "    last_name  = upper(last_name)", NULL, NULL, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   else   {     printf("%d row(s) were changed\n", sqlite_changes(db));   }   sqlite_close(db); } 

When we run this program, the response tells us how many rows were affected:

 $ ./listing6.4 2 row(s) were changed 

Note

The number returned by sqlite_changes() is the total number of rows that could be altered by the UPDATE regardless of whether or not the new value is different from the old value. Therefore if this program is run a second time SQLite still reports the same number of changes, even though the data effectively remains the same.

Callback Functions for SELECT Queries

In order to process the results of a query from sqlite_exec(), a callback function is used that is invoked once for each row in the dataset. The callback function must have this prototype:

 int callback(void *pArg, int argc, char **argv, char **columnNames) 

The pArg parameter to the callback function is a copy of pArg from the sqlite_exec() call. This can be used to pass arbitrary data to the callback, but may be NULL if not required.

The value of argc is the number of columns returned by the query. The array argv contains pointers to strings for each resultthe size of the array is argc. The fourth parameter is also an array in which the first argc entries are the column names from the query.

If you turn on the SHOW_DATATYPES pragma, the columnNames array will also contain the column types from the database schema, in the next argc elements onwards. The PRAGMA command is discussed in detail in Chapter 10, "General Database Administration," but for now this particular setting is turned on by issuing the following statement.

 PRAGMA SHOW_DATATYPES=ON; 

Listing 6.5 contains how a callback function is used to output the entire contents of a dataset.

Listing 6.5. Displaying the Results of a SELECT Query Using a Callback Function

 #include <stdio.h> #include <sqlite.h> int callback(void *pArg, int argc, char **argv, char **columnNames) {   int i;   for (i=0; i<argc; i++) {     printf("%-10s %-8s %s\n",                 columnNames[i], columnNames[i+argc], argv[i]);   }   return(0); } main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   ret = sqlite_exec(db, "PRAGMA SHOW_DATATYPES=ON", NULL, NULL, NULL);   ret = sqlite_exec(db, "SELECT * FROM contacts", callback, NULL, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   sqlite_close(db); } 

Let's take a closer look at the callback function. We specify callback as the third parameter to sqlite_exec() on line 32, which causes the function callback() defined in lines 4 to 12 to be invoked for each row returned by the query.

 for (i=0; i<argc; i++) {   printf("%-10s %-8s %s\n",               columnNames[i], columnNames[i+argc], argv[i]); } 

The value in argc is the number of columns returned by the query. In the query from Listing 6.5 this is actually 4the total number of columns in contacts. This loop displays one line for each column, using a format string to printf() to specify a fixed-width column output. We show each column's name (columnNames[i]), type (columnNames[i+argc]), and value (argv[i]).

The callback function ends with

 return(0); 

The return value from the callback function is importanta non-zero value returned from callback() will cause the calling sqlite_exec() function to return an error.

Compiling and executing this program outputs the contents of the database, as shown here:

 $ ./listing6.5 id         INTEGER  1 first_name CHAR     CHRIS last_name  CHAR     NEWMAN email      CHAR     [e-mail] id         INTEGER  2 first_name CHAR     PADDY last_name  CHAR     O'BRIEN email      CHAR     [e-mail] 

Executing SQL Without a Callback Function

Historically, if you wanted to retrieve the results of an SQL query using the C interface, a callback function was the only method. However, most programmers found this way of coding inconvenient, so a new method became available.

The new method allows you to work with SQLite at a lower level by manipulating a SQLite virtual machine. It uses three functions to replace sqlite_exec(), as follows.

• Compile a single SQL statement with sqlite_compile()

• Execute sqlite_step() once for each row of output

• Call sqlite_finalize() to clean up when execution has finished

Compilation is the process that takes an SQL statement and generates a SQLite virtual machine capable of running that statement. The workings of the Virtual Database Engine (VDBE) are discussed in Chapter 10.

The prototype for sqlite_compile() is

 int sqlite_compile(   sqlite *db,   const char *zSql,   const char **pzTail,   sqlite_vm **ppVm,   char **pzErrmsg ); 

As before, the db is an open database resource pointer and the second parameteridentified here as zSqlis the SQL statement itself. The return value is SQLITE_OK if compilation succeeded; otherwise, another error code is returned and pzErrmsg will point to a textual error message.

The behavior of sqlite_compile() differs from sqlite_exec() in that only one SQL statement can be compiled at a time. If two or more statements are passed in zSql, the third parameter pzTail will point to the first character following the end of the compiled statement so that any remaining SQL commands can be handled.

After successful compilation, the pointer passed as the fourth parameter will point to the virtual machine, stored in an opaque sqlite_vm type structure. With a valid virtual machine pointer, sqlite_step() can be called once or more times to return a single row of results. This is the prototype for sqlite_step():

 int sqlite_step (   sqlite_vm *pVm,   int *pN,   const char ***pazValue,   const char ***pazColName ); 

The pVm parameter should be a valid pointer to a virtual machine created with sqlite_compile(). The number of columns in the result set is pointed to by pN. The pazValue and pazColName arguments each point to an array of pointers, which are used to reference the column values and their names and data types respectively.

Note

The data type information is always included in pazColName along with the column names when using sqlite_step() regardless of whether the SHOW_DATATYPES pragma has been turned on.

The return value from each sqlite_step() call is an integer code from a subset of those shown in Table 6.1. Only the following codes will be used:

• SQLITE_BUSY

• SQLITE_ROW

• SQLITE_DONE

• SQLITE_ERROR

• SQLITE_MISUSE

A SQLITE_BUSY code indicates that the database file is locked by another thread or process and the calling function should handle this. Sleeping for a short amount of time to wait for the lock to clear is an acceptable action to take, after which sqlite_step() can be called again. The function sqlite_busy_timeout can be used to specify a number of milliseconds for which SQLite should wait for a lock to clear before returning SQLITE_BUSY.

Note

A custom handler for locked databases can be registered with the sqlite_busy_handler() instruction, to specify a function that will be executed whenever SQLite attempts to access a busy database file. A non-zero return code will cause the interface to attempt to open the file again and the cycle continues until the handler function returns zero.

If a row from the data set has been made available in pazValue and pazColumn, the return code will be SQLITE_ROW. If the last row has already been retrieved, a subsequent call will return SQLITE_DONE, so sqlite_step() will be called in total once more than the actual number of rows in the data set.

Should an error occur, the SQLITE_ERROR code is returned. After either SQLITE_DONE or SQLITE_ERROR it is a misuse of the library to attempt to invoke sqlite_step() a further time, and doing so will generate the SQLITE_MISUSE return code.

Note

The SQLITE_MISUSE error code is intended as a debugging aid, but because of the nature of the problems it attempts to detect, you should not rely on it. In some instances such misuse of the library may result in a program crash.

When all processing is complete, sqlite_finalize() should be called to clean up any memory allocated to the virtual machine. This function can also be used to find details of an error that caused sqlite_step() to return a SQLITE_ERROR. Here's the prototype:

 int sqlite_finalize(   sqlite_vm *pVm,   char **pzErrmsg ); 

The return code of sqlite_finalize() indicates the overall success or failure of the SQL statement and is the same code that would have been returned by sqlite_exec() if the same statement had been executed using that method.

Though it is a requirement to call sqlite_finalize() when either SQLITE_DONE or SQLITE_ERROR is returned, the virtual machine can be deleted at an earlier stage. A premature sqlite_finalize() instruction will interrupt processing immediately and return the database to its previous state.

Listing 6.6 shows how the same query we executed using the callback method in Listing 6.5 can be done with the non-callback interface.

Listing 6.6. Using the Non-Callback Interface to Execute a Query

 #include <stdio.h> #include <sqlite.h> main() {   char **values, **columnNames;   char *errmsg;   int ret, cols, i;   sqlite_vm *vm;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   ret = sqlite_compile(db, "SELECT * FROM contacts", NULL, &vm, &errmsg);   while(sqlite_step(vm, &cols, &values, &columnNames) == SQLITE_ROW) {     for (i=0; i<cols; i++) {       printf("%-10s %s\n", columnNames[i], values[i]);     }   }   ret = sqlite_finalize(vm, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   sqlite_close(db); } 

Fetching a Whole Dataset with sqlite_get_table()

The sqlite_get_table() function is provided for convenience and acts as a wrapper around the core API functions that we have already seen. This function executes an SQL query and returns a pointer to a data structure that contains the entire result at once.

The prototype for sqlite_get_table() is as follows:

 int sqlite_get_table(   sqlite *db,   char *sql,   char ***result,   int *nrow,   int *ncolumn,   char **errmsg ); 

In addition to the usual db and sql parameters, the function also requires passing four pointers of the types shown. The result created is a one-dimensional array of character pointers, and so in order to work out which element corresponds to which data item, the nrow and ncolumn values reference the total number of rows and columns returned respectively.

The result array contains one element for each column of every row in the dataset, plus one additional element for each of the column headings. The column headings are stored first, in result[0] to result[ncol-1]. The value of the first column in the first row returned will be result[ncol], and so on.

The example in Listing 6.7 performs a simple loop to output the contents of the contacts table using sqlite_get_table().

Listing 6.7. Using sqlite_get_table to Fetch an Entire Dataset at One Time

 #include <stdio.h> #include <sqlite.h> main() {   char *errmsg;   char **result;   char buf[80];   int ret, rows, cols, i, j;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   ret = sqlite_get_table(db, "SELECT * FROM contacts",                          &result, &rows, &cols, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   else {     printf("The query returned %d row(s) and %d column(s)\n", rows, cols);     for (i=0; i< cols * (rows+1); i++) {       printf("result[%d] = %s\n", i, result[i]);     }   }   sqlite_close(db); } 

The output from this program helps to show how the elements in result are organized.

 $ ./listing6.7 The query returned 2 row(s) and 4 column(s) result[0] = id result[1] = first_name result[2] = last_name result[3] = email result[4] = 1 result[5] = CHRIS result[6] = NEWMAN result[7] = [e-mail] result[8] = 2 result[9] = PADDY result[10] = O'BRIEN result[11] = [e-mail] 

When you are done with the result of a call to sqlite_get_table(), the memory allocated should be freed using sqlite_free_table(result).

Another function combines the convenience of sqlite_get_table() with sqlite_mprintf() to enable automatic delimiting of single quote characters using the %q format code. sqlite_get_table_printf() takes the same first six arguments as sqlite_get_table(), with the ability to use a printf-style format string in the sql parameter, which uses values from the seventh argument onwards.