# # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: c_interface.tcl,v 1.5 2000/06/21 13:59:14 drh Exp $} puts { The C language interface to the SQLite library

The C language interface to the SQLite library

} puts "

(This page was last modified on [lrange $rcsid 3 4] GMT)

" puts {

The SQLite library is designed to be very easy to use from a C or C++ program. This document gives an overview of the C/C++ programming interface.

The API

The interface to the SQLite library consists of 4 functions, one opaque data structure, and some constants used as return values from sqlite_exec():

typedef struct sqlite sqlite;

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

void sqlite_close(sqlite*);

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

int sqlite_complete(const char *sql);

#define SQLITE_OK        0    /* Successful result */
#define SQLITE_INTERNAL  1    /* An internal logic error in SQLite */
#define SQLITE_ERROR     2    /* SQL error or missing database */
#define SQLITE_PERM      3    /* Access permission denied */
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */
#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */

All of the above definitions are included in the "sqlite.h" header file that comes in the source tree.

Opening a database

Use the sqlite_open() function to open an existing SQLite database or to create a new SQLite database. The first argument is the database name. The second argument is a constant 0666 to open the database for reading and writing and 0444 to open the database read only. The third argument is a pointer to a string pointer. If the third argument is not NULL and an error occurs while trying to open the database, then an error message will be written to memory obtained from malloc() and *errmsg will be made to point to this error message. The calling function is responsible for freeing the memory when it has finished with it.

An SQLite database is just a directory containing a collection of GDBM files. There is one GDBM file for each table and index in the database. All GDBM files end with the ".tbl" suffix. Every SQLite database also contains a special database table named sqlite_master stored in its own GDBM file. This special table records the database schema.

To create a new SQLite database, all you have to do is call sqlite_open() with the first parameter set to the name of an empty directory and the second parameter set to 0666.

The return value of the sqlite_open() function is a pointer to an opaque sqlite structure. This pointer will be the first argument to all subsequent SQLite function calls that deal with the same database. NULL is returned if the open fails for any reason.

Closing the database

To close an SQLite database, call the sqlite_close() function passing it the sqlite structure pointer that was obtained from a prior call to sqlite_open.

Executing SQL statements

The sqlite_exec() function is used to process SQL statements and queries. This function requires 5 parameters as follows:

  1. A pointer to the sqlite structure obtained from a prior call to sqlite_open().

  2. A null-terminated string containing the text of one or more SQL statements and/or queries to be processed.

  3. A pointer to a callback function which is invoked once for each row in the result of a query. This argument may be NULL, in which case no callbacks will ever be invoked.

  4. A pointer that is forwarded to become the first argument to the callback function.

  5. A pointer to an error string. Error messages are written to space obtained from malloc() and the error string is made to point to the malloced space. The calling function is responsible for freeing this space when it has finished with it. This argument may be NULL, in which case error messages are not reported back to the calling function.

The callback function is used to receive the results of a query. A prototype for the callback function is as follows:

int Callback(void *pArg, int argc, char **argv, char **columnNames){
  return 0;
}

The first argument to the callback is just a copy of the fourth argument to sqlite_exec() This parameter can be used to pass arbitrary information through to the callback function from client code. The second argument is the number columns in the query result. The third argument is an array of pointers to strings where each string is a single column of the result for that record. Note that the callback function reports a NULL value in the database as a NULL pointer, which is very different from an empty string. If the i-th parameter is an empty string, we will get:

argv[i][0] == 0

But if the i-th parameter is NULL we will get:

argv[i] == 0

The names of the columns are contained in the fourth argument.

The callback function should normally return 0. If the callback function returns non-zero, the query is immediately aborted and sqlite_exec() will return SQLITE_ABORT.

The sqlite_exec() function returns an integer to indicate success or failure of the operation. The following are possible return values:

SQLITE_OK

This value is returned if everything worked and there were no errors.

SQLITE_INTERNAL

This value indicates that an internal consistency check within the SQLite library failed. This can only happen if there is a bug in the SQLite library. If you ever get an SQLITE_INTERNAL reply from an sqlite_exec() call, please report the problem on the SQLite mailing list.

SQLITE_ERROR

This return value indicates that there was an error in the SQL that was passed into the sqlite_exec().

SQLITE_PERM

This return value says that the access permissions on one of the GDBM files is such that the file cannot be opened.

SQLITE_ABORT

This value is returned if the callback function returns non-zero.

SQLITE_BUSY

This return code indicates that one of the underlying GDBM files is locked because it is currently being accessed by another thread or process. GDBM allows mutiple readers of the same file, but only one writer. So multiple processes can query an SQLite database at once. But only a single process can write to an SQLite database at one time. If an attempt is made to write to an SQLite database that another process is currently reading, the write is not performed and sqlite_exec() returns SQLITE_BUSY. Similarly, an attempt to read an SQLite database that is currently being written by another process will return SQLITE_BUSY. In both cases, the write or query attempt can be retried after the other process finishes.

Note that locking is done at the file level. One process can write to table ABC (for example) while another process simultaneously reads from a different table XYZ. But you cannot have two processes reading and writing table ABC at the same time.

SQLITE_NOMEM

This value is returned if a call to malloc() fails.

SQLITE_READONLY

This return code indicates that an attempt was made to write to a database file that was originally opened for reading only. This can happen if the callback from a query attempts to update the table being queried.

Testing for a complete SQL statement

The last interface routine to SQLite is a convenience function used to test whether or not a string forms a complete SQL statement. If the sqlite_complete() function returns true when its input is a string, then the argument forms a complete SQL statement. There are no guarantees that the syntax of that statement is correct, but we at least know the statement is complete. If sqlite_complete() returns false, then more text is required to complete the SQL statement.

For the purpose of the sqlite_complete() function, an SQL statement is complete if it ends in a semicolon.

The sqlite command-line utility uses the sqlite_complete() function to know when it needs to call sqlite_exec(). After each line of input is received, sqlite calls sqlite_complete() on all input in its buffer. If sqlite_complete() returns true, then sqlite_exec() is called and the input buffer is reset. If sqlite_complete() returns false, then the prompt is changed to the continuation prompt and another line of text is read and added to the input buffer.

Usage Examples

For examples of how the SQLite C/C++ interface can be used, refer to the source code for the sqlite program in the file src/shell.c of the source tree. Additional information about sqlite is available at sqlite.html. See also the sources to the Tcl interface for SQLite in the source file src/tclsqlite.c.

} puts {


Back to the SQLite Home Page

}