Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Developers Support

C/C++ Interface For SQLite Version 3

This page defined the C-language interface to SQLite.

This is not a tutorial. These pages are designed to be precise, not easy to read. For a tutorial introduction see SQLite In 3 Minutes Or Less and/or the Introduction To The SQLite C/C++ Interface.

This page contains all C-language interface information in a single HTML file. The same information is also available broken out into lots of small pages for easier viewing, if you prefer.

This document is created by a script which scans comments in the source code files.

Objects:

Constants:

Functions:

Virtual Table Indexing Information

struct sqlite3_index_info {
  /* Inputs */
  int nConstraint;           /* Number of entries in aConstraint */
  struct sqlite3_index_constraint {
     int iColumn;              /* Column on left-hand side of constraint */
     unsigned char op;         /* Constraint operator */
     unsigned char usable;     /* True if this constraint is usable */
     int iTermOffset;          /* Used internally - xBestIndex should ignore */
  } *aConstraint;            /* Table of WHERE clause constraints */
  int nOrderBy;              /* Number of terms in the ORDER BY clause */
  struct sqlite3_index_orderby {
     int iColumn;              /* Column number */
     unsigned char desc;       /* True for DESC.  False for ASC. */
  } *aOrderBy;               /* The ORDER BY clause */

The sqlite3_index_info structure and its substructures is used to pass information into and receive the reply from the xBestIndex method of an sqlite3_module. The fields under **Inputs** are the inputs to xBestIndex and are read-only. xBestIndex inserts its results into the **Outputs** fields.

The aConstraint [] array records WHERE clause constraints of the form:

column OP expr

Where OP is =, <, <=, >, or >=. The particular operator is stored in aConstraint [] .op. The index of the column is stored in aConstraint [] .iColumn. aConstraint [] .usable is TRUE if the expr on the right-hand side can be evaluated (and thus the constraint is usable) and false if it cannot.

The optimizer automatically inverts terms of the form "expr OP column" and makes other simplifications to the WHERE clause in an attempt to get as many WHERE clause terms into the form shown above as possible. The aConstraint [] array only reports WHERE clause terms in the correct form that refer to the particular virtual table being queried.

Information about the ORDER BY clause is stored in aOrderBy [] . Each term of aOrderBy records a column of the ORDER BY clause.

The xBestIndex method must fill aConstraintUsage [] with information about what parameters to pass to xFilter. If argvIndex>0 then the right-hand side of the corresponding aConstraint [] is evaluated and becomes the argvIndex-th entry in argv. If aConstraintUsage [] .omit is true, then the constraint is assumed to be fully handled by the virtual table and is not checked again by SQLite.

The idxNum and idxPtr values are recorded and passed into xFilter. sqlite3_free() is used to free idxPtr if needToFreeIdxPtr is true.

The orderByConsumed means that output from xFilter will occur in the correct order to satisfy the ORDER BY clause so that no separate sorting step is required.

The estimatedCost value is an estimate of the cost of doing the particular lookup. A full scan of a table with N entries should have a cost of N. A binary search of a table of N entries should have a cost of approximately log(N).

Virtual Table Object

struct sqlite3_module {
  int iVersion;
  int (*xCreate)(sqlite3*, void *pAux,
               int argc, const char *const*argv,
               sqlite3_vtab **ppVTab, char**);
  int (*xConnect)(sqlite3*, void *pAux,
               int argc, const char *const*argv,
               sqlite3_vtab **ppVTab, char**);
  int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
  int (*xDisconnect)(sqlite3_vtab *pVTab);
  int (*xDestroy)(sqlite3_vtab *pVTab);
  int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
  int (*xClose)(sqlite3_vtab_cursor*);
  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                int argc, sqlite3_value **argv);
  int (*xNext)(sqlite3_vtab_cursor*);
  int (*xEof)(sqlite3_vtab_cursor*);
  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
  int (*xRowid)(sqlite3_vtab_cursor*, sqlite3_int64 *pRowid);
  int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite3_int64 *);
  int (*xBegin)(sqlite3_vtab *pVTab);
  int (*xSync)(sqlite3_vtab *pVTab);
  int (*xCommit)(sqlite3_vtab *pVTab);
  int (*xRollback)(sqlite3_vtab *pVTab);
  int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
                       void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
                       void **ppArg);

A module is a class of virtual tables. Each module is defined by an instance of the following structure. This structure consists mostly of methods for the module.

Virtual Table Instance Object

struct sqlite3_vtab {
  const sqlite3_module *pModule;  /* The module for this virtual table */
  int nRef;                       /* Used internally */
  char *zErrMsg;                  /* Error message from sqlite3_mprintf() */
  /* Virtual table implementations will typically add additional fields */
};

Every module implementation uses a subclass of the following structure to describe a particular instance of the module. Each subclass will be tailored to the specific needs of the module implementation. The purpose of this superclass is to define certain fields that are common to all module implementations.

Virtual tables methods can set an error message by assigning a string obtained from sqlite3_mprintf() to zErrMsg. The method should take care that any prior string is freed by a call to sqlite3_free() prior to assigning a new string to zErrMsg. After the error message is delivered up to the client application, the string will be automatically freed by sqlite3_free() and the zErrMsg field will be zeroed. Note that sqlite3_mprintf() and sqlite3_free() are used on the zErrMsg field since virtual tables are commonly implemented in loadable extensions which do not have access to sqlite3MPrintf() or sqlite3Free().

Virtual Table Cursor Object

struct sqlite3_vtab_cursor {
  sqlite3_vtab *pVtab;      /* Virtual table of this cursor */
  /* Virtual table implementations will typically add additional fields */
};

Every module implementation uses a subclass of the following structure to describe cursors that point into the virtual table and are used to loop through the virtual table. Cursors are created using the xOpen method of the module. Each module implementation will define the content of a cursor structure to suit its own needs.

This superclass exists in order to define fields of the cursor that are common to all implementations.

Standard File Control Opcodes

#define SQLITE_FCNTL_LOCKSTATE        1

These integer constants are opcodes for the xFileControl method of the sqlite3_io_methods object and to the sqlite3_file_control() interface.

The SQLITE_FCNTL_LOCKSTATE opcode is used for debugging. This opcode causes the xFileControl method to write the current state of the lock (one of SQLITE_LOCK_NONE, SQLITE_LOCK_SHARED, SQLITE_LOCK_RESERVED, SQLITE_LOCK_PENDING, or SQLITE_LOCK_EXCLUSIVE) into an integer that the pArg argument points to. This capability is used during testing and only needs to be supported when SQLITE_TEST is defined.

A Handle To An Open BLOB

typedef struct sqlite3_blob sqlite3_blob;

An instance of this object represents an open BLOB on which incremental I/O can be preformed. Objects of this type are created by sqlite3_blob_open() and destroyed by sqlite3_blob_close(). The sqlite3_blob_read() and sqlite3_blob_write() interfaces can be used to read or write small subsections of the blob. The sqlite3_blob_bytes() interface returns the size of the blob in bytes.

SQL Function Context Object

typedef struct sqlite3_context sqlite3_context;

The context in which an SQL function executes is stored in an sqlite3_context object. A pointer to an sqlite3_context object is always first parameter to application-defined SQL functions.

OS Interface Open File Handle

typedef struct sqlite3_file sqlite3_file;
struct sqlite3_file {
  const struct sqlite3_io_methods *pMethods;  /* Methods for an open file */
};

An sqlite3_file object represents an open file in the OS interface layer. Individual OS interface implementations will want to subclass this object by appending additional fields for their own use. The pMethods entry is a pointer to an sqlite3_io_methods object that defines methods for performing I/O operations on the open file.

OS Interface File Virtual Methods Object

typedef struct sqlite3_io_methods sqlite3_io_methods;
struct sqlite3_io_methods {
  int iVersion;
  int (*xClose)(sqlite3_file*);
  int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst);
  int (*xWrite)(sqlite3_file*, const void*, int iAmt, sqlite3_int64 iOfst);
  int (*xTruncate)(sqlite3_file*, sqlite3_int64 size);
  int (*xSync)(sqlite3_file*, int flags);
  int (*xFileSize)(sqlite3_file*, sqlite3_int64 *pSize);
  int (*xLock)(sqlite3_file*, int);
  int (*xUnlock)(sqlite3_file*, int);
  int (*xCheckReservedLock)(sqlite3_file*);
  int (*xFileControl)(sqlite3_file*, int op, void *pArg);
  int (*xSectorSize)(sqlite3_file*);
  int (*xDeviceCharacteristics)(sqlite3_file*);
  /* Additional methods may be added in future releases */
};

Every file opened by the sqlite3_vfs xOpen method contains a pointer to an instance of this object. This object defines the methods used to perform various operations against the open file.

The flags argument to xSync may be one of SQLITE_SYNC_NORMAL or SQLITE_SYNC_FULL. The first choice is the normal fsync(). OS-X style fullsync. The SQLITE_SYNC_DATA flag may be ORed in to indicate that only the data of the file and not its inode needs to be synced.

The integer values to xLock() and xUnlock() are one of

xLock() increases the lock. xUnlock() decreases the lock. The xCheckReservedLock() method looks to see if any database connection, either in this process or in some other process, is holding an RESERVED, PENDING, or EXCLUSIVE lock on the file. It returns true if such a lock exists and false if not.

The xFileControl() method is a generic interface that allows custom VFS implementations to directly control an open file using the sqlite3_file_control() interface. The second "op" argument is an integer opcode. The third argument is a generic pointer which is intended to be a pointer to a structure that may contain arguments or space in which to write return values. Potential uses for xFileControl() might be functions to enable blocking locks with timeouts, to change the locking strategy (for example to use dot-file locks), to inquire about the status of a lock, or to break stale locks. The SQLite core reserves opcodes less than 100 for its own use. A list of opcodes less than 100 is available. Applications that define a custom xFileControl method should use opcodes greater than 100 to avoid conflicts.

The xSectorSize() method returns the sector size of the device that underlies the file. The sector size is the minimum write that can be performed without disturbing other bytes in the file. The xDeviceCharacteristics() method returns a bit vector describing behaviors of the underlying device:

The SQLITE_IOCAP_ATOMIC property means that all writes of any size are atomic. The SQLITE_IOCAP_ATOMICnnn values mean that writes of blocks that are nnn bytes in size and are aligned to an address which is an integer multiple of nnn are atomic. The SQLITE_IOCAP_SAFE_APPEND value means that when data is appended to a file, the data is appended first then the size of the file is extended, never the other way around. The SQLITE_IOCAP_SEQUENTIAL property means that information is written to disk in the same order as calls to xWrite().

Mutex Handle

typedef struct sqlite3_mutex sqlite3_mutex;

The mutex module within SQLite defines sqlite3_mutex to be an abstract type for a mutex object. The SQLite core never looks at the internal representation of an sqlite3_mutex. It only deals with pointers to the sqlite3_mutex object.

Mutexes are created using sqlite3_mutex_alloc().

Name Of The Folder Holding Temporary Files

SQLITE_EXTERN char *sqlite3_temp_directory;

If this global variable is made to point to a string which is the name of a folder (a.ka. directory), then all temporary files created by SQLite will be placed in that directory. If this variable is NULL pointer, then SQLite does a search for an appropriate temporary file directory.

It is not safe to modify this variable once a database connection has been opened. It is intended that this variable be set once as part of process initialization and before any SQLite interface routines have been call and remain unchanged thereafter.

OS Interface Object

typedef struct sqlite3_vfs sqlite3_vfs;
struct sqlite3_vfs {
  int iVersion;            /* Structure version number */
  int szOsFile;            /* Size of subclassed sqlite3_file */
  int mxPathname;          /* Maximum file pathname length */
  sqlite3_vfs *pNext;      /* Next registered VFS */
  const char *zName;       /* Name of this virtual file system */
  void *pAppData;          /* Pointer to application-specific data */
  int (*xOpen)(sqlite3_vfs*, const char *zName, sqlite3_file*,
               int flags, int *pOutFlags);
  int (*xDelete)(sqlite3_vfs*, const char *zName, int syncDir);
  int (*xAccess)(sqlite3_vfs*, const char *zName, int flags);
  int (*xGetTempname)(sqlite3_vfs*, int nOut, char *zOut);
  int (*xFullPathname)(sqlite3_vfs*, const char *zName, int nOut, char *zOut);
  void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
  void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
  void *(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol);
  void (*xDlClose)(sqlite3_vfs*, void*);
  int (*xRandomness)(sqlite3_vfs*, int nByte, char *zOut);
  int (*xSleep)(sqlite3_vfs*, int microseconds);
  int (*xCurrentTime)(sqlite3_vfs*, double*);
  /* New fields may be appended in figure versions.  The iVersion
  ** value will increment whenever this happens. */
};

An instance of this object defines the interface between the SQLite core and the underlying operating system. The "vfs" in the name of the object stands for "virtual file system".

The iVersion field is initially 1 but may be larger for future versions of SQLite. Additional fields may be appended to this object when the iVersion value is increased.

The szOsFile field is the size of the subclassed sqlite3_file structure used by this VFS. mxPathname is the maximum length of a pathname in this VFS.

Registered sqlite3_vfs objects are kept on a linked list formed by the pNext pointer. The sqlite3_vfs_register() and sqlite3_vfs_unregister() interfaces manage this list in a thread-safe way. The sqlite3_vfs_find() interface searches the list.

The pNext field is the only field in the sqlite3_vfs structure that SQLite will ever modify. SQLite will only access or modify this field while holding a particular static mutex. The application should never modify anything within the sqlite3_vfs object once the object has been registered.

The zName field holds the name of the VFS module. The name must be unique across all VFS modules.

SQLite will guarantee that the zFilename string passed to xOpen() is a full pathname as generated by xFullPathname() and that the string will be valid and unchanged until xClose() is called. So the sqlite3_file can store a pointer to the filename if it needs to remember the filename for some reason.

The flags argument to xOpen() includes all bits set in the flags argument to sqlite3_open_v2(). Or if sqlite3_open() or sqlite3_open16() is used, then flags includes at least SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE. If xOpen() opens a file read-only then it sets *pOutFlags to include SQLITE_OPEN_READONLY. Other bits in *pOutFlags may be set.

SQLite will also add one of the following flags to the xOpen() call, depending on the object being opened:

The file I/O implementation can use the object type flags to changes the way it deals with files. For example, an application that does not care about crash recovery or rollback might make the open of a journal file a no-op. Writes to this journal would also be no-ops, and any attempt to read the journal would return SQLITE_IOERR. Or the implementation might recognize that a database file will be doing page-aligned sector reads and writes in a random order and set up its I/O subsystem accordingly.

SQLite might also add one of the following flags to the xOpen method:

The SQLITE_OPEN_DELETEONCLOSE flag means the file should be deleted when it is closed. The SQLITE_OPEN_DELETEONCLOSE will be set for TEMP databases, journals and for subjournals. The SQLITE_OPEN_EXCLUSIVE flag means the file should be opened for exclusive access. This flag is set for all files except for the main database file.

At least szOsFile bytes of memory are allocated by SQLite to hold the sqlite3_file structure passed as the third argument to xOpen. The xOpen method does not have to allocate the structure; it should just fill it in.

The flags argument to xAccess() may be SQLITE_ACCESS_EXISTS to test for the existance of a file, or SQLITE_ACCESS_READWRITE to test to see if a file is readable and writable, or SQLITE_ACCESS_READ to test to see if a file is at least readable. The file can be a directory.

SQLite will always allocate at least mxPathname+1 bytes for the output buffers for xGetTempname and xFullPathname. The exact size of the output buffer is also passed as a parameter to both methods. If the output buffer is not large enough, SQLITE_CANTOPEN should be returned. As this is handled as a fatal error by SQLite, vfs implementations should endeavor to prevent this by setting mxPathname to a sufficiently large value.

The xRandomness(), xSleep(), and xCurrentTime() interfaces are not strictly a part of the filesystem, but they are included in the VFS structure for completeness. The xRandomness() function attempts to return nBytes bytes of good-quality randomness into zOut. The return value is the actual number of bytes of randomness obtained. The xSleep() method causes the calling thread to sleep for at least the number of microseconds given. The xCurrentTime() method returns a Julian Day Number for the current date and time.

Obtain Aggregate Function Context

void *sqlite3_aggregate_context(sqlite3_context*, int nBytes);

The implementation of aggregate SQL functions use this routine to allocate a structure for storing their state. The first time the sqlite3_aggregate_context() routine is is called for a particular aggregate, SQLite allocates nBytes of memory zeros that memory, and returns a pointer to it. On second and subsequent calls to sqlite3_aggregate_context() for the same aggregate function index, the same buffer is returned. The implementation of the aggregate can use the returned buffer to accumulate data.

SQLite automatically frees the allocated buffer when the aggregate query concludes.

The first parameter should be a copy of the SQL function context that is the first parameter to the callback routine that implements the aggregate function.

This routine must be called from the same thread in which the aggregate SQL function is running.

Invariants:

F16211 The first invocation of sqlite3_aggregate_context(C,N) for a particular instance of an aggregate function (for a particular context C) causes SQLite to allocation N bytes of memory, zero that memory, and return a pointer to the allocationed memory.
F16213 If a memory allocation error occurs during sqlite3_aggregate_context(C,N) then the function returns 0.
F16215 Second and subsequent invocations of sqlite3_aggregate_context(C,N) for the same context pointer C ignore the N parameter and return a pointer to the same block of memory returned by the first invocation.
F16217 The memory allocated by sqlite3_aggregate_context(C,N) is automatically freed on the next call to sqlite3_reset() or sqlite3_finalize() for the prepared statement containing the aggregate function associated with context C.

Make Arrangements To Automatically Load An Extension

int sqlite3_auto_extension(void *xEntryPoint);

This function registers an extension entry point that is automatically invoked whenever a new database connection is opened using sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2().

This API can be invoked at program startup in order to register one or more statically linked extensions that will be available to all new database connections.

Duplicate extensions are detected so calling this routine multiple times with the same extension is harmless.

This routine stores a pointer to the extension in an array that is obtained from sqlite_malloc(). If you run a memory leak checker on your program and it reports a leak because of this array, then invoke sqlite3_reset_auto_extension() prior to shutdown to free the memory.

Automatic extensions apply across all threads.

This interface is experimental and is subject to change or removal in future releases of SQLite.

Number Of SQL Parameters

int sqlite3_bind_parameter_count(sqlite3_stmt*);

This routine can be used to find the number of SQL parameters in a prepared statement. SQL parameters are tokens of the form "?", "?NNN", ":AAA", "$AAA", or "@AAA" that serve as place-holders for values that are bound to the parameters at a later time.

This routine actually returns the index of the largest parameter. For all forms except ?NNN, this will correspond to the number of unique parameters. If parameters of the ?NNN are used, there may be gaps in the list.

See also: sqlite3_bind(), sqlite3_bind_parameter_name(), and sqlite3_bind_parameter_index().

Invariants:

F13601 The sqlite3_bind_parameter_count(S) interface returns the largest index of all SQL parameters in the prepared statement S, or 0 if S contains no SQL parameters.

Index Of A Parameter With A Given Name

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);

Return the index of an SQL parameter given its name. The index value returned is suitable for use as the second parameter to sqlite3_bind(). A zero is returned if no matching parameter is found. The parameter name must be given in UTF-8 even if the original statement was prepared from UTF-16 text using sqlite3_prepare16_v2().

See also: sqlite3_bind(), sqlite3_bind_parameter_count(), and sqlite3_bind_parameter_index().

Invariants:

F13641 The sqlite3_bind_parameter_index(S,N) interface returns the index of SQL parameter in prepared statement S whose name matches the UTF-8 string N, or 0 if there is no match.

Name Of A Host Parameter

const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);

This routine returns a pointer to the name of the n-th SQL parameter in a prepared statement. SQL parameters of the form "?NNN" or ":AAA" or "@AAA" or "$AAA" have a name which is the string "?NNN" or ":AAA" or "@AAA" or "$AAA" respectively. In other words, the initial ":" or "$" or "@" or "?" is included as part of the name. Parameters of the form "?" without a following integer have no name.

The first host parameter has an index of 1, not 0.

If the value n is out of range or if the n-th parameter is nameless, then NULL is returned. The returned string is always in the UTF-8 encoding even if the named parameter was originally specified as UTF-16 in sqlite3_prepare16() or sqlite3_prepare16_v2().

See also: sqlite3_bind(), sqlite3_bind_parameter_count(), and sqlite3_bind_parameter_index().

Invariants:

F13621 The sqlite3_bind_parameter_name(S,N) interface returns a UTF-8 rendering of the name of the SQL parameter in prepared statement S having index N, or NULL if there is no SQL parameter with index N or if the parameter with index N is an anonymous parameter "?".

Return The Size Of An Open BLOB

int sqlite3_blob_bytes(sqlite3_blob *);

Return the size in bytes of the blob accessible via the open sqlite3_blob object in its only argument.

Invariants:

F17843 The sqlite3_blob_bytes(P) interface returns the size in bytes of the BLOB that the sqlite3_blob object P refers to.

Close A BLOB Handle

int sqlite3_blob_close(sqlite3_blob *);

Close an open blob handle.

Closing a BLOB shall cause the current transaction to commit if there are no other BLOBs, no pending prepared statements, and the database connection is in autocommit mode. If any writes were made to the BLOB, they might be held in cache until the close operation if they will fit. Closing the BLOB often forces the changes out to disk and so if any I/O errors occur, they will likely occur at the time when the BLOB is closed. Any errors that occur during closing are reported as a non-zero return value.

The BLOB is closed unconditionally. Even if this routine returns an error code, the BLOB is still closed.

Invariants:

F17833 The sqlite3_blob_close(P) interface closes an sqlite3_blob object P previously opened using sqlite3_blob_open().
F17836 Closing an sqlite3_blob object using sqlite3_blob_close() shall cause the current transaction to commit if there are no other open sqlite3_blob objects or prepared statements on the same database connection and the database connection is in autocommit mode.
F17839 The sqlite3_blob_close(P) interfaces closes the sqlite3_blob object P unconditionally, even if sqlite3_blob_close(P) returns something other than SQLITE_OK.

Open A BLOB For Incremental I/O

int sqlite3_blob_open(
  sqlite3*,
  const char *zDb,
  const char *zTable,
  const char *zColumn,
  sqlite3_int64 iRow,
  int flags,
  sqlite3_blob **ppBlob
);

This interfaces opens a handle to the blob located in row iRow, column zColumn, table zTable in database zDb; in other words, the same blob that would be selected by:

SELECT zColumn FROM zDb.zTable WHERE rowid = iRow;

If the flags parameter is non-zero, the blob is opened for read and write access. If it is zero, the blob is opened for read access.

Note that the database name is not the filename that contains the database but rather the symbolic name of the database that is assigned when the database is connected using ATTACH. For the main database file, the database name is "main". For TEMP tables, the database name is "temp".

On success, SQLITE_OK is returned and the new blob handle is written to *ppBlob. Otherwise an error code is returned and any value written to *ppBlob should not be used by the caller. This function sets the database-handle error code and message accessible via sqlite3_errcode() and sqlite3_errmsg().

Invariants:

F17813 A successful invocation of the sqlite3_blob_open(D,B,T,C,R,F,P) interface opens an sqlite3_blob object P on the blob in column C of table T in database B on database connection D.
F17814 A successful invocation of sqlite3_blob_open(D,...) starts a new transaction on database connection D if that connection is not already in a transaction.
F17816 The sqlite3_blob_open(D,B,T,C,R,F,P) interface opens the blob for read and write access if and only if the F parameter is non-zero.
F17819 The sqlite3_blob_open() interface returns SQLITE_OK on success and an appropriate error code on failure.
F17821 If an error occurs during evaluation of sqlite3_blob_open(D,...) then subsequent calls to sqlite3_errcode(D), sqlite3_errmsg(D), and sqlite3_errmsg16(D) will return information approprate for that error.

Read Data From A BLOB Incrementally

int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int iOffset);

This function is used to read data from an open blob-handle into a caller supplied buffer. N bytes of data are copied into buffer Z from the open blob, starting at offset iOffset.

If offset iOffset is less than N bytes from the end of the blob, SQLITE_ERROR is returned and no data is read. If N or iOffset is less than zero SQLITE_ERROR is returned and no data is read.

On success, SQLITE_OK is returned. Otherwise, an error code or an extended error code is returned.

Invariants:

F17853 The sqlite3_blob_read(P,Z,N,X) interface reads N bytes beginning at offset X from the blob that sqlite3_blob object P refers to and writes those N bytes into buffer Z.
F17856 In sqlite3_blob_read(P,Z,N,X) if the size of the blob is less than N+X bytes, then the function returns SQLITE_ERROR and nothing is read from the blob.
F17859 In sqlite3_blob_read(P,Z,N,X) if X or N is less than zero then the function returns SQLITE_ERROR and nothing is read from the blob.
F17862 The sqlite3_blob_read(P,Z,N,X) interface returns SQLITE_OK if N bytes where successfully read into buffer Z.
F17865 If the requested read could not be completed, the sqlite3_blob_read(P,Z,N,X) interface returns an appropriate error code or extended error code.
F17868 If an error occurs during evaluation of sqlite3_blob_read(P,...) then subsequent calls to sqlite3_errcode(D), sqlite3_errmsg(D), and sqlite3_errmsg16(D) will return information approprate for that error, where D is the database handle that was used to open blob handle P.

Write Data Into A BLOB Incrementally

int sqlite3_blob_write(sqlite3_blob *, const void *z, int n, int iOffset);

This function is used to write data into an open blob-handle from a user supplied buffer. n bytes of data are copied from the buffer pointed to by z into the open blob, starting at offset iOffset.

If the blob-handle passed as the first argument was not opened for writing (the flags parameter to sqlite3_blob_open() was zero), this function returns SQLITE_READONLY.

This function may only modify the contents of the blob; it is not possible to increase the size of a blob using this API. If offset iOffset is less than n bytes from the end of the blob, SQLITE_ERROR is returned and no data is written. If n is less than zero SQLITE_ERROR is returned and no data is written.

On success, SQLITE_OK is returned. Otherwise, an error code or an extended error code is returned.

Invariants:

F17873 The sqlite3_blob_write(P,Z,N,X) interface writes N bytes from buffer Z into the blob that sqlite3_blob object P refers to beginning at an offset of X into the blob.
F17875 The sqlite3_blob_write(P,Z,N,X) interface returns SQLITE_READONLY if the sqlite3_blob object P was opened for reading only.
F17876 In sqlite3_blob_write(P,Z,N,X) if the size of the blob is less than N+X bytes, then the function returns SQLITE_ERROR and nothing is written into the blob.
F17879 In sqlite3_blob_write(P,Z,N,X) if X or N is less than zero then the function returns SQLITE_ERROR and nothing is written into the blob.
F17882 The sqlite3_blob_write(P,Z,N,X) interface returns SQLITE_OK if N bytes where successfully written into blob.
F17885 If the requested write could not be completed, the sqlite3_blob_write(P,Z,N,X) interface returns an appropriate error code or extended error code.
F17888 If an error occurs during evaluation of sqlite3_blob_write(D,...) then subsequent calls to sqlite3_errcode(D), sqlite3_errmsg(D), and sqlite3_errmsg16(D) will return information approprate for that error.

Register A Callback To Handle SQLITE_BUSY Errors

int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);

This routine identifies a callback function that might be invoked whenever an attempt is made to open a database table that another thread or process has locked. If the busy callback is NULL, then SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned immediately upon encountering the lock. If the busy callback is not NULL, then the callback will be invoked with two arguments. The first argument to the handler is a copy of the void* pointer which is the third argument to this routine. The second argument to the handler is the number of times that the busy handler has been invoked for this locking event. If the busy callback returns 0, then no additional attempts are made to access the database and SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned. If the callback returns non-zero, then another attempt is made to open the database for reading and the cycle repeats.

The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed.

The default busy callback is NULL.

The SQLITE_BUSY error is converted to SQLITE_IOERR_BLOCKED when SQLite is in the middle of a large transaction where all the changes will not fit into the in-memory cache. SQLite will already hold a RESERVED lock on the database file, but it needs to promote this lock to EXCLUSIVE so that it can spill cache pages into the database file without harm to concurrent readers. If it is unable to promote the lock, then the in-memory cache will be left in an inconsistent state and so the error code is promoted from the relatively benign SQLITE_BUSY to the more severe SQLITE_IOERR_BLOCKED. This error code promotion forces an automatic rollback of the changes. See the CorruptionFollowingBusyError wiki page for a discussion of why this is important.

There can only be a single busy handler defined for each database connection. Setting a new busy handler clears any previous one. Note that calling sqlite3_busy_timeout() will also set or clear the busy handler.

Invariants:

F12311 The sqlite3_busy_handler() function replaces the busy handler callback in the database connection identified by the 1st parameter with a new busy handler identified by the 2nd and 3rd parameters.
F12312 The default busy handler for new database connections is NULL.
F12314 When two or more database connection share a common cache, the busy handler for the database connection currently using the cache is invoked when the cache encounters a lock.
F12316 If a busy handler callback returns zero, then the SQLite interface that provoked the locking event will return SQLITE_BUSY.
F12318 SQLite will invokes the busy handler with two argument which are a copy of the pointer supplied by the 3rd parameter to sqlite3_busy_handler() and a count of the number of prior invocations of the busy handler for the same locking event.

Limitations:

U12319 A busy handler should not call close the database connection or prepared statement that invoked the busy handler.

Set A Busy Timeout

int sqlite3_busy_timeout(sqlite3*, int ms);

This routine sets a busy handler that sleeps for a while when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have been done. After "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED.

Calling this routine with an argument less than or equal to zero turns off all busy handlers.

There can only be a single busy handler for a particular database connection. If another busy handler was defined (using sqlite3_busy_handler()) prior to calling this routine, that other busy handler is cleared.

Invariants:

F12341 The sqlite3_busy_timeout() function overrides any prior sqlite3_busy_timeout() or sqlite3_busy_handler() setting on the same database connection.
F12343 If the 2nd parameter to sqlite3_busy_timeout() is less than or equal to zero, then the busy handler is cleared so that all subsequent locking events immediately return SQLITE_BUSY.
F12344 If the 2nd parameter to sqlite3_busy_timeout() is a positive number N, then a busy handler is set that repeatedly calls the xSleep() method in the VFS interface until either the lock clears or until the cumulative sleep time reported back by xSleep() exceeds N milliseconds.

Count The Number Of Rows Modified

int sqlite3_changes(sqlite3*);

This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted. Auxiliary changes caused by triggers are not counted. Use the sqlite3_total_changes() function to find the total number of changes including changes caused by triggers.

A "row change" is a change to a single row of a single table caused by an INSERT, DELETE, or UPDATE statement. Rows that are changed as side effects of REPLACE constraint resolution, rollback, ABORT processing, DROP TABLE, or by any other mechanisms do not count as direct row changes.

A "trigger context" is a scope of execution that begins and ends with the script of a trigger. Most SQL statements are evaluated outside of any trigger. This is the "top level" trigger context. If a trigger fires from the top level, a new trigger context is entered for the duration of that one trigger. Subtriggers create subcontexts for their duration.

Calling sqlite3_exec() or sqlite3_step() recursively does not create a new trigger context.

This function returns the number of direct row changes in the most recent INSERT, UPDATE, or DELETE statement within the same trigger context.

So when called from the top level, this function returns the number of changes in the most recent INSERT, UPDATE, or DELETE that also occurred at the top level. Within the body of a trigger, the sqlite3_changes() interface can be called to find the number of changes in the most recently completed INSERT, UPDATE, or DELETE statement within the body of the same trigger. However, the number returned does not include in changes caused by subtriggers since they have their own context.

SQLite implements the command "DELETE FROM table" without a WHERE clause by dropping and recreating the table. (This is much faster than going through and deleting individual elements from the table.) Because of this optimization, the deletions in "DELETE FROM table" are not row changes and will not be counted by the sqlite3_changes() or sqlite3_total_changes() functions. To get an accurate count of the number of rows deleted, use "DELETE FROM table WHERE 1" instead.

Invariants:

F12241 The sqlite3_changes() function returns the number of row changes caused by the most recent INSERT, UPDATE, or DELETE statement on the same database connection and within the same trigger context, or zero if there have not been any qualifying row changes.

Limitations:

U12252 If a separate thread makes changes on the same database connection while sqlite3_changes() is running then the value returned is unpredictable and unmeaningful.

Reset All Bindings On A Prepared Statement

int sqlite3_clear_bindings(sqlite3_stmt*);

Contrary to the intuition of many, sqlite3_reset() does not reset the bindings on a prepared statement. Use this routine to reset all host parameters to NULL.

Invariants:

F13661 The sqlite3_clear_bindings(S) interface resets all SQL parameter bindings in prepared statement S back to NULL.

Closing A Database Connection

int sqlite3_close(sqlite3 *);

This routine is the destructor for the sqlite3 object.

Applications should finalize all prepared statements and close all BLOBs associated with the sqlite3 object prior to attempting to close the sqlite3 object.

(TODO: What happens to pending transactions? Are they rolled back, or abandoned?)

Invariants:

F12011 The sqlite3_close() interface destroys an sqlite3 object allocated by a prior call to sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2().
F12012 The sqlite3_close() function releases all memory used by the connection and closes all open files.
F12013 If the database connection contains prepared statements that have not been finalized by sqlite3_finalize(), then sqlite3_close() returns SQLITE_BUSY and leaves the connection open.
F12014 Giving sqlite3_close() a NULL pointer is a harmless no-op.

Limitations:

U12015 The parameter to sqlite3_close() must be an sqlite3 object pointer previously obtained from sqlite3_open() or the equivalent, or NULL.
U12016 The parameter to sqlite3_close() must not have been previously closed.

Number Of Columns In A Result Set

int sqlite3_column_count(sqlite3_stmt *pStmt);

Return the number of columns in the result set returned by the prepared statement. This routine returns 0 if pStmt is an SQL statement that does not return data (for example an UPDATE).

Invariants:

F13711 The sqlite3_column_count(S) interface returns the number of columns in the result set generated by the prepared statement S, or 0 if S does not generate a result set.

Database Connection For Functions

sqlite3 *sqlite3_context_db_handle(sqlite3_context*);

The sqlite3_context_db_handle() interface returns a copy of the pointer to the database connection (the 1st parameter) of the the sqlite3_create_function() and sqlite3_create_function16() routines that originally registered the application defined function.

Invariants:

F16253 The sqlite3_context_db_handle(C) interface returns a copy of the D pointer from the sqlite3_create_function(D,X,N,E,P,F,S,L) or sqlite3_create_function16(D,X,N,E,P,F,S,L) call that registered the SQL function associated with sqlite3_context C.

Register A Virtual Table Implementation

int sqlite3_create_module(
  sqlite3 *db,               /* SQLite connection to register module with */
  const char *zName,         /* Name of the module */
  const sqlite3_module *,    /* Methods for the module */
  void *                     /* Client data for xCreate/xConnect */
);

This routine is used to register a new module name with an SQLite connection. Module names must be registered before creating new virtual tables on the module, or before using preexisting virtual tables of the module.

Register A Virtual Table Implementation

int sqlite3_create_module_v2(
  sqlite3 *db,               /* SQLite connection to register module with */
  const char *zName,         /* Name of the module */
  const sqlite3_module *,    /* Methods for the module */
  void *,                    /* Client data for xCreate/xConnect */
  void(*xDestroy)(void*)     /* Module destructor function */
);

This routine is identical to the sqlite3_create_module() method above, except that it allows a destructor function to be specified. It is even more experimental than the rest of the virtual tables API.

Number of columns in a result set

int sqlite3_data_count(sqlite3_stmt *pStmt);

Return the number of values in the current row of the result set.

Invariants:

F13771 After a call to sqlite3_step(S) that returns SQLITE_ROW, the sqlite3_data_count(S) routine will return the same value as the sqlite3_column_count(S) function.
F13772 After sqlite3_step(S) has returned any value other than SQLITE_ROW or before sqlite3_step(S) has been called on the prepared statement for the first time since it was prepared or reset, the sqlite3_data_count(S) routine returns zero.

Find The Database Handle Of A Prepared Statement

sqlite3 *sqlite3_db_handle(sqlite3_stmt*);

The sqlite3_db_handle interface returns the sqlite3* database handle to which a prepared statement belongs. The database handle returned by sqlite3_db_handle is the same database handle that was the first argument to the sqlite3_prepare_v2() or its variants that was used to create the statement in the first place.

Invariants:

F13123 The sqlite3_db_handle(S) interface returns a pointer to the database connection associated with prepared statement S.

Declare The Schema Of A Virtual Table

int sqlite3_declare_vtab(sqlite3*, const char *zCreateTable);

The xCreate and xConnect methods of a module use the following API to declare the format (the names and datatypes of the columns) of the virtual tables they implement.

Enable Or Disable Extension Loading

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

So as not to open security holes in older applications that are unprepared to deal with extension loading, and as a means of disabling extension loading while evaluating user-entered SQL, the following API is provided to turn the sqlite3_load_extension() mechanism on and off. It is off by default. See ticket #1863.

Call the sqlite3_enable_load_extension() routine with onoff==1 to turn extension loading on and call it with onoff==0 to turn it back off again.

Enable Or Disable Shared Pager Cache

int sqlite3_enable_shared_cache(int);

This routine enables or disables the sharing of the database cache and schema data structures between connections to the same database. Sharing is enabled if the argument is true and disabled if the argument is false.

Cache sharing is enabled and disabled for an entire process. This is a change as of SQLite version 3.5.0. In prior versions of SQLite, sharing was enabled or disabled for each thread separately.

The cache sharing mode set by this interface effects all subsequent calls to sqlite3_open(), sqlite3_open_v2(), and sqlite3_open16(). Existing database connections continue use the sharing mode that was in effect at the time they were opened.

Virtual tables cannot be used with a shared cache. When shared cache is enabled, the sqlite3_create_module() API used to register virtual tables will always return an error.

This routine returns SQLITE_OK if shared cache was enabled or disabled successfully. An error code is returned otherwise.

Shared cache is disabled by default. But this might change in future releases of SQLite. Applications that care about shared cache setting should set it explicitly.

Invariants:

F10331 A successful invocation of sqlite3_enable_shared_cache(B) will enable or disable shared cache mode for any subsequently created database connection in the same process.
F10336 When shared cache is enabled, the sqlite3_create_module() interface will always return an error.
F10337 The sqlite3_enable_shared_cache(B) interface returns SQLITE_OK if shared cache was enabled or disabled successfully.
F10339 Shared cache is disabled by default.

One-Step Query Execution Interface

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

The sqlite3_exec() interface is a convenient way of running one or more SQL statements without a lot of C code. The SQL statements are passed in as the second parameter to sqlite3_exec(). The statements are evaluated one by one until either an error or an interrupt is encountered or until they are all done. The 3rd parameter is an optional callback that is invoked once for each row of any query results produced by the SQL statements. The 5th parameter tells where to write any error messages.

The sqlite3_exec() interface is implemented in terms of sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(). The sqlite3_exec() routine does nothing that cannot be done by sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(). The sqlite3_exec() is just a convenient wrapper.

Invariants:

F12101 The sqlite3_exec() interface evaluates zero or more UTF-8 encoded, semicolon-separated, SQL statements in the zero-terminated string of its 2nd parameter within the context of the sqlite3 object given in the 1st parameter.
F12104 The return value of sqlite3_exec() is SQLITE_OK if all SQL statements run successfully.
F12105 The return value of sqlite3_exec() is an appropriate non-zero error code if any SQL statement fails.
F12107 If one or more of the SQL statements handed to sqlite3_exec() return results and the 3rd parameter is not NULL, then the callback function specified by the 3rd parameter is invoked once for each row of result.
F12110 If the callback returns a non-zero value then sqlite3_exec() will aborted the SQL statement it is currently evaluating, skip all subsequent SQL statements, and return SQLITE_ABORT. (TODO: What happens to *errmsg here? Does the result code for sqlite3_errcode() get set?)
F12113 The sqlite3_exec() routine will pass its 4th parameter through as the 1st parameter of the callback.
F12116 The sqlite3_exec() routine sets the 2nd parameter of its callback to be the number of columns in the current row of result.
F12119 The sqlite3_exec() routine sets the 3rd parameter of its callback to be an array of pointers to strings holding the values for each column in the current result set row as obtained from sqlite3_column_text().
F12122 The sqlite3_exec() routine sets the 4th parameter of its callback to be an array of pointers to strings holding the names of result columns as obtained from sqlite3_column_name().
F12125 If the 3rd parameter to sqlite3_exec() is NULL then sqlite3_exec() never invokes a callback. All query results are silently discarded.
F12128 If an error occurs while parsing or evaluating any of the SQL statements handed to sqlite3_exec() then sqlite3_exec() will return an error code other than SQLITE_OK.
F12131 If an error occurs while parsing or evaluating any of the SQL handed to sqlite3_exec() and if the 5th parameter (errmsg) to sqlite3_exec() is not NULL, then an error message is allocated using the equivalent of sqlite3_mprintf() and *errmsg is made to point to that message.
F12134 The sqlite3_exec() routine does not change the value of *errmsg if errmsg is NULL or if there are no errors.
F12137 The sqlite3_exec() function sets the error code and message accessible via sqlite3_errcode(), sqlite3_errmsg(), and sqlite3_errmsg16().

Limitations:

U12141 The first parameter to sqlite3_exec() must be an valid and open database connection.
U12142 The database connection must not be closed while sqlite3_exec() is running.
U12143 The calling function is should use sqlite3_free() to free the memory that *errmsg is left pointing at once the error message is no longer needed.
U12145 The SQL statement text in the 2nd parameter to sqlite3_exec() must remain unchanged while sqlite3_exec() is running.

Enable Or Disable Extended Result Codes

int sqlite3_extended_result_codes(sqlite3*, int onoff);

The sqlite3_extended_result_codes() routine enables or disables the extended result codes feature of SQLite. The extended result codes are disabled by default for historical compatibility.

Invariants:

F12201 Each new database connection has the extended result codes feature disabled by default.
F12202 The sqlite3_extended_result_codes(D,F) interface will enable extended result codes for the database connection D if the F parameter is true, or disable them if F is false.

Low-Level Control Of Database Files

int sqlite3_file_control(sqlite3*, const char *zDbName, int op, void*);

The sqlite3_file_control() interface makes a direct call to the xFileControl method for the sqlite3_io_methods object associated with a particular database identified by the second argument. The name of the database is the name assigned to the database by the ATTACH SQL command that opened the database. To control the main database file, use the name "main" or a NULL pointer. The third and fourth parameters to this routine are passed directly through to the second and third parameters of the xFileControl method. The return value of the xFileControl method becomes the return value of this routine.

If the second parameter (zDbName) does not match the name of any open database file, then SQLITE_ERROR is returned. This error code is not remembered and will not be recalled by sqlite3_errcode() or sqlite3_errmsg(). The underlying xFileControl method might also return SQLITE_ERROR. There is no way to distinguish between an incorrect zDbName and an SQLITE_ERROR return from the underlying xFileControl method.

See also: SQLITE_FCNTL_LOCKSTATE

Destroy A Prepared Statement Object

int sqlite3_finalize(sqlite3_stmt *pStmt);

The sqlite3_finalize() function is called to delete a prepared statement. If the statement was executed successfully, or not executed at all, then SQLITE_OK is returned. If execution of the statement failed then an error code or extended error code is returned.

This routine can be called at any point during the execution of the prepared statement. If the virtual machine has not completed execution when this routine is called, that is like encountering an error or an interrupt. (See sqlite3_interrupt().) Incomplete updates may be rolled back and transactions cancelled, depending on the circumstances, and the error code returned will be SQLITE_ABORT.

Invariants:

F11302 The sqlite3_finalize(S) interface destroys the prepared statement S and releases all memory and file resources held by that object.
F11304 If the most recent call to sqlite3_step(S) for the prepared statement S returned an error, then sqlite3_finalize(S) returns that same error.

Test To See If The Database Is In Auto-Commit Mode

int sqlite3_get_autocommit(sqlite3*);

The sqlite3_get_autocommit() interfaces returns non-zero or zero if the given database connection is or is not in autocommit mode, respectively. Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN statement. Autocommit mode is reenabled by a COMMIT or ROLLBACK.

If certain kinds of errors occur on a statement within a multi-statement transactions (errors including SQLITE_FULL, SQLITE_IOERR, SQLITE_NOMEM, SQLITE_BUSY, and SQLITE_INTERRUPT) then the transaction might be rolled back automatically. The only way to find out if SQLite automatically rolled back the transaction after an error is to use this function.

Invariants:

F12931 The sqlite3_get_autocommit(D) interface returns non-zero or zero if the database connection D is or is not in autocommit mode, respectively.
F12932 Autocommit mode is on by default.
F12933 Autocommit mode is disabled by a successful BEGIN statement.
F12934 Autocommit mode is enabled by a successful COMMIT or ROLLBACK statement.

Limitations:

U12936 If another thread changes the autocommit status of the database connection while this routine is running, then the return value is undefined.

Interrupt A Long-Running Query

void sqlite3_interrupt(sqlite3*);

This function causes any pending database operation to abort and return at its earliest opportunity. This routine is typically called in response to a user action such as pressing "Cancel" or Ctrl-C where the user wants a long query operation to halt immediately.

It is safe to call this routine from a thread different from the thread that is currently running the database operation. But it is not safe to call this routine with a database connection that is closed or might close before sqlite3_interrupt() returns.

If an SQL is very nearly finished at the time when sqlite3_interrupt() is called, then it might not have an opportunity to be interrupted. It might continue to completion. An SQL operation that is interrupted will return SQLITE_INTERRUPT. If the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then the entire transaction will be rolled back automatically. A call to sqlite3_interrupt() has no effect on SQL statements that are started after sqlite3_interrupt() returns.

Invariants:

F12271 The sqlite3_interrupt() interface will force all running SQL statements associated with the same database connection to halt after processing at most one additional row of data.
F12272 Any SQL statement that is interrupted by sqlite3_interrupt() will return SQLITE_INTERRUPT.

Limitations:

U12279 If the database connection closes while sqlite3_interrupt() is running then bad things will likely happen.

Last Insert Rowid

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

Each entry in an SQLite table has a unique 64-bit signed integer key called the "rowid". The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

This routine returns the rowid of the most recent successful INSERT into the database from the database connection shown in the first argument. If no successful inserts have ever occurred on this database connection, zero is returned.

If an INSERT occurs within a trigger, then the rowid of the inserted row is returned by this routine as long as the trigger is running. But once the trigger terminates, the value returned by this routine reverts to the last value inserted before the trigger fired.

An INSERT that fails due to a constraint violation is not a successful insert and does not change the value returned by this routine. Thus INSERT OR FAIL, INSERT OR IGNORE, INSERT OR ROLLBACK, and INSERT OR ABORT make no changes to the return value of this routine when their insertion fails. When INSERT OR REPLACE encounters a constraint violation, it does not fail. The INSERT continues to completion after deleting rows that caused the constraint problem so INSERT OR REPLACE will always change the return value of this interface.

For the purposes of this routine, an insert is considered to be successful even if it is subsequently rolled back.

Invariants:

F12221 The sqlite3_last_insert_rowid() function returns the rowid of the most recent successful insert done on the same database connection and within the same trigger context, or zero if there have been no qualifying inserts on that connection.
F12223 The sqlite3_last_insert_rowid() function returns same value when called from the same trigger context immediately before and after a ROLLBACK.

Limitations:

U12232 If a separate thread does a new insert on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid.

Run-time Limits

int sqlite3_limit(sqlite3*, int id, int newVal);

This interface allows the size of various constructs to be limited on a connection by connection basis. The first parameter is the database connection whose limit is to be set or queried. The second parameter is one of the limit categories that define a class of constructs to be size limited. The third parameter is the new limit for that construct. The function returns the old limit.

If the new limit is a negative number, the limit is unchanged. For the limit category of SQLITE_LIMIT_XYZ there is a hard upper bound set by a compile-time C-preprocess macro named SQLITE_MAX_XYZ. (The "_LIMIT_" in the name is changed to "_MAX_".) Attempts to increase a limit above its hard upper bound are silently truncated to the hard upper limit.

Run time limits are intended for use in applications that manage both their own internal database and also databases that are controlled by untrusted external sources. An example application might be a webbrowser that has its own databases for storing history and separate databases controlled by javascript applications downloaded off the internet. The internal databases can be given the large, default limits. Databases managed by external sources can be given much smaller limits designed to prevent a denial of service attach. Developers might also want to use the sqlite3_set_authorizer() interface to further control untrusted SQL. The size of the database created by an untrusted script can be contained using the max_page_count PRAGMA.

This interface is currently considered experimental and is subject to change or removal without prior notice.

Invariants:

F12762 A successful call to sqlite3_limit(D,C,V) where V is positive changes the limit on the size of construct C in database connection D to the lessor of V and the hard upper bound on the size of C that is set at compile-time.
F12766 A successful call to sqlite3_limit(D,C,V) where V is negative leaves the state of database connection D unchanged.
F12769 A successful call to sqlite3_limit(D,C,V) returns the value of the limit on the size of construct C in in database connection D as it was prior to the call.

Load An Extension

int sqlite3_load_extension(
  sqlite3 *db,          /* Load the extension into this database connection */
  const char *zFile,    /* Name of the shared library containing extension */
  const char *zProc,    /* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg       /* Put error message here if not 0 */
);

The sqlite3_load_extension() interface attempts to load an SQLite extension library contained in the file zFile. The entry point is zProc. zProc may be 0 in which case the name of the entry point defaults to "sqlite3_extension_init".

The sqlite3_load_extension() interface shall return SQLITE_OK on success and SQLITE_ERROR if something goes wrong.

If an error occurs and pzErrMsg is not 0, then the sqlite3_load_extension() interface shall attempt to fill *pzErrMsg with error message text stored in memory obtained from sqlite3_malloc(). The calling function should free this memory by calling sqlite3_free().

Extension loading must be enabled using sqlite3_enable_load_extension() prior to calling this API or an error will be returned.

Overload A Function For A Virtual Table

int sqlite3_overload_function(sqlite3*, const char *zFuncName, int nArg);

Virtual tables can provide alternative implementations of functions using the xFindFunction method. But global versions of those functions must exist in order to be overloaded.

This API makes sure a global version of a function with a particular name and number of parameters exists. If no such function exists before this API is called, a new function is created. The implementation of the new function always causes an exception to be thrown. So the new function is not good for anything by itself. Its only purpose is to be a place-holder function that can be overloaded by virtual tables.

This API should be considered part of the virtual table interface, which is experimental and subject to change.

Query Progress Callbacks

void sqlite3_progress_handler(sqlite3*, int, int(*)(void*), void*);

This routine configures a callback function - the progress callback - that is invoked periodically during long running calls to sqlite3_exec(), sqlite3_step() and sqlite3_get_table(). An example use for this interface is to keep a GUI updated during a large query.

If the progress callback returns non-zero, the opertion is interrupted. This feature can be used to implement a "Cancel" button on a GUI dialog box.

Invariants:

F12911 The callback function registered by sqlite3_progress_handler() is invoked periodically during long running calls to sqlite3_step().
F12912 The progress callback is invoked once for every N virtual machine opcodes, where N is the second argument to the sqlite3_progress_handler() call that registered the callback. (TODO: What if N is less than 1?)
F12913 The progress callback itself is identified by the third argument to sqlite3_progress_handler().
F12914 The fourth argument sqlite3_progress_handler() is a void pointer passed to the progress callback function each time it is invoked.
F12915 If a call to sqlite3_step() results in fewer than N opcodes being executed, then the progress callback is never invoked.
F12916 Every call to sqlite3_progress_handler() overwrites any previously registere progress handler.
F12917 If the progress handler callback is NULL then no progress handler is invoked.
F12918 If the progress callback returns a result other than 0, then the behavior is a if sqlite3_interrupt() had been called.

Pseudo-Random Number Generator

void sqlite3_randomness(int N, void *P);

SQLite contains a high-quality pseudo-random number generator (PRNG) used to select random ROWIDs when inserting new records into a table that already uses the largest possible ROWID. The PRNG is also used for the build-in random() and randomblob() SQL functions. This interface allows appliations to access the same PRNG for other purposes.

A call to this routine stores N bytes of randomness into buffer P.

The first time this routine is invoked (either internally or by the application) the PRNG is seeded using randomness obtained from the xRandomness method of the default sqlite3_vfs object. On all subsequent invocations, the pseudo-randomness is generated internally and without recourse to the sqlite3_vfs xRandomness method.

Invariants:

F17392 The sqlite3_randomness(N,P) interface writes N bytes of high-quality pseudo-randomness into buffer P.

Attempt To Free Heap Memory

int sqlite3_release_memory(int);

The sqlite3_release_memory() interface attempts to free N bytes of heap memory by deallocating non-essential memory allocations held by the database labrary. Memory used to cache database pages to improve performance is an example of non-essential memory. Sqlite3_release_memory() returns the number of bytes actually freed, which might be more or less than the amount requested.

Invariants:

F17341 The sqlite3_release_memory(N) interface attempts to free N bytes of heap memory by deallocating non-essential memory allocations held by the database labrary.
F16342 The sqlite3_release_memory(N) returns the number of bytes actually freed, which might be more or less than the amount requested.

Reset A Prepared Statement Object

int sqlite3_reset(sqlite3_stmt *pStmt);

The sqlite3_reset() function is called to reset a prepared statement object. back to its initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings() to reset the bindings.

The sqlite3_reset(S) interface resets the prepared statement S back to the beginning of its program.

If the most recent call to sqlite3_step(S) for prepared statement S returned SQLITE_ROW or SQLITE_DONE, or if sqlite3_step(S) has never before been called on S, then sqlite3_reset(S) returns SQLITE_OK.

If the most recent call to sqlite3_step(S) for prepared statement S indicated an error, then sqlite3_reset(S) returns an appropriate error code.

The sqlite3_reset(S) interface does not change the values of any bindings on prepared statement S.

Reset Automatic Extension Loading

void sqlite3_reset_auto_extension(void);

This function disables all previously registered automatic extensions. This routine undoes the effect of all prior sqlite3_auto_extension() calls.

This call disabled automatic extensions in all threads.

This interface is experimental and is subject to change or removal in future releases of SQLite.

Compile-Time Authorization Callbacks

int sqlite3_set_authorizer(
  sqlite3*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);

This routine registers a authorizer callback with a particular database connection, supplied in the first argument. The authorizer callback is invoked as SQL statements are being compiled by sqlite3_prepare() or its variants sqlite3_prepare_v2(), sqlite3_prepare16() and sqlite3_prepare16_v2(). At various points during the compilation process, as logic is being created to perform various actions, the authorizer callback is invoked to see if those actions are allowed. The authorizer callback should return SQLITE_OK to allow the action, SQLITE_IGNORE to disallow the specific action but allow the SQL statement to continue to be compiled, or SQLITE_DENY to cause the entire SQL statement to be rejected with an error. If the authorizer callback returns any value other than SQLITE_IGNORE, SQLITE_OK, or SQLITE_DENY then sqlite3_prepare_v2() or equivalent call that triggered the authorizer will fail with an error message.

When the callback returns SQLITE_OK, that means the operation requested is ok. When the callback returns SQLITE_DENY, the sqlite3_prepare_v2() or equivalent call that triggered the authorizer will fail with an error message explaining that access is denied. If the authorizer code is SQLITE_READ and the callback returns SQLITE_IGNORE then the prepared statement statement is constructed to substitute a NULL value in place of the table column that would have been read if SQLITE_OK had been returned. The SQLITE_IGNORE return can be used to deny an untrusted user access to individual columns of a table.

The first parameter to the authorizer callback is a copy of the third parameter to the sqlite3_set_authorizer() interface. The second parameter to the callback is an integer action code that specifies the particular action to be authorized. The third through sixth parameters to the callback are zero-terminated strings that contain additional details about the action to be authorized.

An authorizer is used when preparing SQL statements from an untrusted source, to ensure that the SQL statements do not try to access data that they are not allowed to see, or that they do not try to execute malicious statements that damage the database. For example, an application may allow a user to enter arbitrary SQL queries for evaluation by a database. But the application does not want the user to be able to make arbitrary changes to the database. An authorizer could then be put in place while the user-entered SQL is being prepared that disallows everything except SELECT statements.

Applications that need to process SQL from untrusted sources might also consider lowering resource limits using sqlite3_limit() and limiting database size using the max_page_count PRAGMA in addition to using an authorizer.

Only a single authorizer can be in place on a database connection at a time. Each call to sqlite3_set_authorizer overrides the previous call. Disable the authorizer by installing a NULL callback. The authorizer is disabled by default.

Note that the authorizer callback is invoked only during sqlite3_prepare() or its variants. Authorization is not performed during statement evaluation in sqlite3_step().

Invariants:

F12501 The sqlite3_set_authorizer(D,...) interface registers a authorizer callback with database connection D.
F12502 The authorizer callback is invoked as SQL statements are being compiled
F12503 If the authorizer callback returns any value other than SQLITE_IGNORE, SQLITE_OK, or SQLITE_DENY then the sqlite3_prepare_v2() or equivalent call that caused the authorizer callback to run shall fail with an SQLITE_ERROR error code and an appropriate error message.
F12504 When the authorizer callback returns SQLITE_OK, the operation described is coded normally.
F12505 When the authorizer callback returns SQLITE_DENY, the sqlite3_prepare_v2() or equivalent call that caused the authorizer callback to run shall fail with an SQLITE_ERROR error code and an error message explaining that access is denied.
F12506 If the authorizer code (the 2nd parameter to the authorizer callback) is SQLITE_READ and the authorizer callback returns SQLITE_IGNORE then the prepared statement is constructed to insert a NULL value in place of the table column that would have been read if SQLITE_OK had been returned.
F12507 If the authorizer code (the 2nd parameter to the authorizer callback) is anything other than SQLITE_READ, then a return of SQLITE_IGNORE has the same effect as SQLITE_DENY.
F12510 The first parameter to the authorizer callback is a copy of the third parameter to the sqlite3_set_authorizer() interface.
F12511 The second parameter to the callback is an integer action code that specifies the particular action to be authorized.
F12512 The third through sixth parameters to the callback are zero-terminated strings that contain additional details about the action to be authorized.
F12520 Each call to sqlite3_set_authorizer() overrides the any previously installed authorizer.
F12521 A NULL authorizer means that no authorization callback is invoked.
F12522 The default authorizer is NULL.

Suspend Execution For A Short Time

int sqlite3_sleep(int);

The sqlite3_sleep() function causes the current thread to suspend execution for at least a number of milliseconds specified in its parameter.

If the operating system does not support sleep requests with millisecond time resolution, then the time will be rounded up to the nearest second. The number of milliseconds of sleep actually requested from the operating system is returned.

SQLite implements this interface by calling the xSleep() method of the default sqlite3_vfs object.

Invariants:

F10533 The sqlite3_sleep(M) interface invokes the xSleep method of the default VFS in order to suspend execution of the current thread for at least M milliseconds.
F10536 The sqlite3_sleep(M) interface returns the number of milliseconds of sleep actually requested of the operating system, which might be larger than the parameter M.

Impose A Limit On Heap Size

void sqlite3_soft_heap_limit(int);

The sqlite3_soft_heap_limit() interface places a "soft" limit on the amount of heap memory that may be allocated by SQLite. If an internal allocation is requested that would exceed the soft heap limit, sqlite3_release_memory() is invoked one or more times to free up some space before the allocation is made.

The limit is called "soft", because if sqlite3_release_memory() cannot free sufficient memory to prevent the limit from being exceeded, the memory is allocated anyway and the current operation proceeds.

A negative or zero value for N means that there is no soft heap limit and sqlite3_release_memory() will only be called when memory is exhausted. The default value for the soft heap limit is zero.

SQLite makes a best effort to honor the soft heap limit. But if the soft heap limit cannot honored, execution will continue without error or notification. This is why the limit is called a "soft" limit. It is advisory only.

Prior to SQLite version 3.5.0, this routine only constrained the memory allocated by a single thread - the same thread in which this routine runs. Beginning with SQLite version 3.5.0, the soft heap limit is applied to all threads. The value specified for the soft heap limit is an upper bound on the total memory allocation for all threads. In version 3.5.0 there is no mechanism for limiting the heap usage for individual threads.

Invariants:

F16351 The sqlite3_soft_heap_limit(N) interface places a soft limit of N bytes on the amount of heap memory that may be allocated using sqlite3_malloc() or sqlite3_realloc() at any point in time.
F16352 If a call to sqlite3_malloc() or sqlite3_realloc() would cause the total amount of allocated memory to exceed the soft heap limit, then