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

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

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.

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

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().

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

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.

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

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 for 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.

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. The application-defined SQL function implementation will pass this pointer through into calls to sqlite3_result(), sqlite3_aggregate_context(), sqlite3_user_data(), sqlite3_context_db_handle(), sqlite3_get_auxdata(), and/or sqlite3_set_auxdata().

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 *pResOut);
  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 populates an sqlite3_file object (or, more commonly, a subclass of the sqlite3_file object) with a pointer to an instance of this object. This object defines the methods used to perform various operations against the open file represented by the sqlite3_file object.

The flags argument to xSync may be one of SQLITE_SYNC_NORMAL or SQLITE_SYNC_FULL. The first choice is the normal fsync(). The second choice is a Mac OS-X style fullsync. The SQLITE_SYNC_DATAONLY 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 checks whether any database connection, either in this process or in some other process, is holding a RESERVED, PENDING, or EXCLUSIVE lock on the file. It returns true if such a lock exists and false otherwise.

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 intended to point 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 all 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().

Memory Allocation Routines

typedef struct sqlite3_mem_methods sqlite3_mem_methods;
struct sqlite3_mem_methods {
  void *(*xMalloc)(int);         /* Memory allocation function */
  void (*xFree)(void*);          /* Free a prior allocation */
  void *(*xRealloc)(void*,int);  /* Resize an allocation */
  int (*xSize)(void*);           /* Return the size of an allocation */
  int (*xRoundup)(int);          /* Round up request size to allocation size */
  int (*xInit)(void*);           /* Initialize the memory allocator */
  void (*xShutdown)(void*);      /* Deinitialize the memory allocator */
  void *pAppData;                /* Argument to xInit() and xShutdown() */
};

An instance of this object defines the interface between SQLite and low-level memory allocation routines.

This object is used in only one place in the SQLite interface. A pointer to an instance of this object is the argument to sqlite3_config() when the configuration option is SQLITE_CONFIG_MALLOC. By creating an instance of this object and passing it to sqlite3_config() during configuration, an application can specify an alternative memory allocation subsystem for SQLite to use for all of its dynamic memory needs.

Note that SQLite comes with a built-in memory allocator that is perfectly adequate for the overwhelming majority of applications and that this object is only useful to a tiny minority of applications with specialized memory allocation requirements. This object is also used during testing of SQLite in order to specify an alternative memory allocator that simulates memory out-of-memory conditions in order to verify that SQLite recovers gracefully from such conditions.

The xMalloc, xFree, and xRealloc methods must work like the malloc(), free(), and realloc() functions from the standard library.

xSize should return the allocated size of a memory allocation previously obtained from xMalloc or xRealloc. The allocated size is always at least as big as the requested size but may be larger.

The xRoundup method returns what would be the allocated size of a memory allocation given a particular requested size. Most memory allocators round up memory allocations at least to the next multiple of 8. Some allocators round up to a larger multiple or to a power of 2.

The xInit method initializes the memory allocator. (For example, it might allocate any require mutexes or initialize internal data structures. The xShutdown method is invoked (indirectly) by sqlite3_shutdown() and should deallocate any resources acquired by xInit. The pAppData pointer is used as the only parameter to xInit and xShutdown.

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().

Mutex Methods Object

typedef struct sqlite3_mutex_methods sqlite3_mutex_methods;
struct sqlite3_mutex_methods {
  int (*xMutexInit)(void);
  int (*xMutexEnd)(void);
  sqlite3_mutex *(*xMutexAlloc)(int);
  void (*xMutexFree)(sqlite3_mutex *);
  void (*xMutexEnter)(sqlite3_mutex *);
  int (*xMutexTry)(sqlite3_mutex *);
  void (*xMutexLeave)(sqlite3_mutex *);
  int (*xMutexHeld)(sqlite3_mutex *);
  int (*xMutexNotheld)(sqlite3_mutex *);
};

An instance of this structure defines the low-level routines used to allocate and use mutexes.

Usually, the default mutex implementations provided by SQLite are sufficient, however the user has the option of substituting a custom implementation for specialized deployments or systems for which SQLite does not provide a suitable implementation. In this case, the user creates and populates an instance of this structure to pass to sqlite3_config() along with the SQLITE_CONFIG_MUTEX option. Additionally, an instance of this structure can be used as an output variable when querying the system for the current mutex implementation, using the SQLITE_CONFIG_GETMUTEX option.

The xMutexInit method defined by this structure is invoked as part of system initialization by the sqlite3_initialize() function. The xMutexInit routine shall be called by SQLite once for each effective call to sqlite3_initialize().

The xMutexEnd method defined by this structure is invoked as part of system shutdown by the sqlite3_shutdown() function. The implementation of this method is expected to release all outstanding resources obtained by the mutex methods implementation, especially those obtained by the xMutexInit method. The xMutexEnd() interface shall be invoked once for each call to sqlite3_shutdown().

The remaining seven methods defined by this structure (xMutexAlloc, xMutexFree, xMutexEnter, xMutexTry, xMutexLeave, xMutexHeld and xMutexNotheld) implement the following interfaces (respectively):

The only difference is that the public sqlite3_XXX functions enumerated above silently ignore any invocations that pass a NULL pointer instead of a valid mutex handle. The implementations of the methods defined by this structure are not required to handle this case, the results of passing a NULL pointer instead of a valid mutex handle are undefined (i.e. it is acceptable to provide an implementation that segfaults if it is passed a NULL pointer).

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.k.a. directory), then all temporary files created by SQLite will be placed in that directory. If this variable is a NULL pointer, then SQLite performs 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 *pResOut);
  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*);
  int (*xGetLastError)(sqlite3_vfs*, int, char *);
  /* New fields may be appended in figure versions.  The iVersion
  ** value will increment whenever this happens. */
};

An instance of the sqlite3_vfs 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 value of the iVersion field is initially 1 but may be larger in future versions of SQLite. Additional fields may be appended to this object when the iVersion value is increased. Note that the structure of the sqlite3_vfs object changes in the transaction between SQLite version 3.5.9 and 3.6.0 and yet the iVersion field was not modified.

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. Neither the application code nor the VFS implementation should use the pNext pointer.

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 parameter to xOpen is either a NULL pointer or string obtained from xFullPathname(). SQLite further guarantees that the string will be valid and unchanged until xClose() is called. Becasue of the previous sentense, the sqlite3_file can safely store a pointer to the filename if it needs to remember the filename for some reason. If the zFilename parameter is xOpen is a NULL pointer then xOpen must invite its own temporary name for the file. Whenever the xFilename parameter is NULL it will also be the case that the flags parameter will include SQLITE_OPEN_DELETEONCLOSE.

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 change 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 existence of a file, or SQLITE_ACCESS_READWRITE to test whether a file is readable and writable, or SQLITE_ACCESS_READ to test whether a file is at least readable. The file can be a directory.

SQLite will always allocate at least mxPathname+1 bytes for the output buffer 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. Since 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 called for a particular aggregate, SQLite allocates nBytes of memory, zeroes out 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 allocate N bytes of memory, zero that memory, and return a pointer to the allocated 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 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.

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

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().

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 sqlite3_malloc().

Automatic extensions apply across all threads.

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 placeholders for values that are bound to the parameters at a later time.

This routine actually returns the index of the largest (rightmost) 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 the 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 and are also referred to as "anonymous parameters".

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

Returns the size in bytes of the BLOB accessible via the open [] BLOB handle] 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 *);

Closes 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 shall close 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 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 connection error code and message accessible via sqlite3_errcode() and sqlite3_errmsg().

If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any column of the row is changed, even a column other than the one the BLOB handle is open on. Calls to sqlite3_blob_read() and sqlite3_blob_write() for a expired BLOB handle fail with an return code of SQLITE_ABORT. Changes written into a BLOB prior to the BLOB expiring are not rollback by the expiration of the BLOB. Such changes will eventually commit if the transaction continues to completion.

Invariants:

F17813 A successful invocation of the sqlite3_blob_open(D,B,T,C,R,F,P) interface shall open an sqlite3_blob object P on the BLOB in column C of the table T in the database B on the database connection D.
F17814 A successful invocation of sqlite3_blob_open(D,...) shall start a new transaction on the 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 shall open the BLOB for read and write access if and only if the F parameter is non-zero.
F17819 The sqlite3_blob_open() interface shall return 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) shall return information appropriate for that error.
F17824 If any column in the row that a sqlite3_blob has open is changed by a separate UPDATE or DELETE statement or by an ON CONFLICT side effect, then the sqlite3_blob shall be marked as invalid.

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.

An attempt to read from an expired BLOB handle fails with an error code of SQLITE_ABORT.

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

Invariants:

F17853 A successful invocation of sqlite3_blob_read(P,Z,N,X) shall reads N bytes of data out of the BLOB referenced by BLOB handle P beginning at offset X and store those 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 shall leave the Z buffer unchanged and return SQLITE_ERROR.
F17859 In sqlite3_blob_read(P,Z,N,X) if X or N is less than zero then the function shall leave the Z buffer unchanged and return SQLITE_ERROR.
F17862 The sqlite3_blob_read(P,Z,N,X) interface shall return SQLITE_OK if N bytes are successfully read into buffer Z.
F17863 If the BLOB handle P is expired and X and N are within bounds then sqlite3_blob_read(P,Z,N,X) shall leave the Z buffer unchanged and return SQLITE_ABORT.
F17865 If the requested read could not be completed, the sqlite3_blob_read(P,Z,N,X) interface shall return 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) shall return information appropriate for that error, where D is the database connection that was used to open the 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 caller-supplied buffer. N bytes of data are copied from the buffer 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.

An attempt to write to an expired BLOB handle fails with an error code of SQLITE_ABORT. Writes to the BLOB that occurred before the BLOB handle expired are not rolled back by the expiration of the handle, though of course those changes might have been overwritten by the statement that expired the BLOB handle or by other independent statements.

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

Invariants:

F17873 A successful invocation of sqlite3_blob_write(P,Z,N,X) shall write N bytes of data from buffer Z into the BLOB referenced by BLOB handle P beginning at offset X into the BLOB.
F17874 In the absence of other overridding changes, the changes written to a BLOB by sqlite3_blob_write() shall remain in effect after the associated BLOB handle expires.
F17875 If the BLOB handle P was opened for reading only then an invocation of sqlite3_blob_write(P,Z,N,X) shall leave the referenced BLOB unchanged and return SQLITE_READONLY.
F17876 If the size of the BLOB referenced by BLOB handle P is less than N+X bytes then sqlite3_blob_write(P,Z,N,X) shall leave the BLOB unchanged and return SQLITE_ERROR.
F17877 If the BLOB handle P is expired and X and N are within bounds then sqlite3_blob_read(P,Z,N,X) shall leave the BLOB unchanged and return SQLITE_ABORT.
F17879 If X or N are less than zero then sqlite3_blob_write(P,Z,N,X) shall leave the BLOB referenced by BLOB handle P unchanged and return SQLITE_ERROR.
F17882 The sqlite3_blob_write(P,Z,N,X) interface shall return SQLITE_OK if N bytes where successfully written into the BLOB.
F17885 If the requested write could not be completed, the sqlite3_blob_write(P,Z,N,X) interface shall return 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) shall return information appropriate for that error.

Register A Callback To Handle SQLITE_BUSY Errors

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

This routine sets 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 sqlite3_busy_handler(). The second argument to the handler callback 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 previously set handler. Note that calling sqlite3_busy_timeout() will also set or clear the busy handler.

Invariants:

F12311 The sqlite3_busy_handler(D,C,A) function shall replace busy callback in the database connection D with a new a new busy handler C and application data pointer A.
F12312 Newly created database connections shall have a busy handler of NULL.
F12314 When two or more database connections share a common cache, the busy handler for the database connection currently using the cache shall be invoked when the cache encounters a lock.
F12316 If a busy handler callback returns zero, then the SQLite interface that provoked the locking event shall return SQLITE_BUSY.
F12318 SQLite shall invokes the busy handler with two arguments 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:

A12319 A busy handler must not 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 specified amount of time when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. 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 any any given moment. 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 shall override 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 shall be 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 shall be 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 database 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.

Thus, 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 changes caused by subtriggers since those 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, regardless of the number of elements that were originally in the table. To get an accurate count of the number of rows deleted, use "DELETE FROM table WHERE 1" instead.

Invariants:

F12241 The sqlite3_changes() function shall return the number of row changes caused by the most recent INSERT, UPDATE, or DELETE statement on the same database connection and within the same or higher trigger context, or zero if there have not been any qualifying row changes.
F12243 Statements of the form "DELETE FROM tablename" with no WHERE clause shall cause subsequent calls to sqlite3_changes() to return zero, regardless of the number of rows originally in the table.

Limitations:

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

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 the 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 BLOB handles associated with the sqlite3 object prior to attempting to close the object. The sqlite3_next_stmt() interface can be used to locate all prepared statements associated with a database connection if desired. Typical code might look like this:

sqlite3_stmt *pStmt;
while( (pStmt = sqlite3_next_stmt(db, 0))!=0 ){
    sqlite3_finalize(pStmt);
}

If sqlite3_close() is invoked while a transaction is open, the transaction is automatically rolled back.

Invariants:

F12011 A successful call to sqlite3_close(C) shall destroy the database connection object C.
F12012 A successful call to sqlite3_close(C) shall return SQLITE_OK.
F12013 A successful call to sqlite3_close(C) shall release all memory and system resources associated with database connection C.
F12014 A call to sqlite3_close(C) on a database connection C that has one or more open prepared statements shall fail with an SQLITE_BUSY error code.
F12015 A call to sqlite3_close(C) where C is a NULL pointer shall return SQLITE_OK.
F12019 When sqlite3_close(C) is invoked on a database connection C that has a pending transaction, the transaction shall be rolled back.

Limitations:

A12016 The C parameter to sqlite3_close(C) must be either a NULL pointer or an sqlite3 object pointer previously obtained from sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2(), and not 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.

Configuring The SQLite Library

int sqlite3_config(int, ...);

The sqlite3_config() interface is used to make global configuration changes to SQLite in order to tune SQLite to the specific needs of the application. The default configuration is recommended for most applications and so this routine is usually not necessary. It is provided to support rare applications with unusual needs.

The sqlite3_config() interface is not threadsafe. The application must insure that no other SQLite interfaces are invoked by other threads while sqlite3_config() is running. Furthermore, sqlite3_config() may only be invoked prior to library initialization using sqlite3_initialize() or after shutdown by sqlite3_shutdown(). Note, however, that sqlite3_config() can be called as part of the implementation of an application-defined sqlite3_os_init().

The first argument to sqlite3_config() is an integer configuration option that determines what property of SQLite is to be configured. Subsequent arguments vary depending on the configuration option in the first argument.

When a configuration option is set, sqlite3_config() returns SQLITE_OK. If the option is unknown or SQLite is unable to set the option then this routine returns a non-zero error code.

The sqlite3_config() interface is considered experimental in that new configuration options may be added in future releases and existing configuration options may be discontinued or modified.

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 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 a database connection. Module names must be registered before creating new virtual tables on the module, or before using preexisting virtual tables of the module.

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

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

Returns 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 database connection 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() call (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 the 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.

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

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.

Extension loading 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.

Extension loading is off by default.

One-Step Query Execution Interface

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

The sqlite3_exec() interface is a convenient way of running one or more SQL statements without having to write a lot of C code. The UTF-8 encoded 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 error message passed back through the 5th parameter is held in memory obtained from sqlite3_malloc(). To avoid a memory leak, the calling application should call sqlite3_free() on any error message returned through the 5th parameter when it has finished using the error message.

If the SQL statement in the 2nd parameter is NULL or an empty string or a string containing only whitespace and comments, then no SQL statements are evaluated and the database is not changed.

The sqlite3_exec() interface is implemented in terms of sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(). The sqlite3_exec() routine does nothing to the database that cannot be done by sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize().

Invariants:

F12101 A successful invocation of sqlite3_exec(D,S,C,A,E) shall sequentially evaluate all of the UTF-8 encoded, semicolon-separated SQL statements in the zero-terminated string S within the context of the database connection D.
F12102 If the S parameter to sqlite3_exec(D,S,C,A,E) is NULL then the actions of the interface shall be the same as if the S parameter were an empty string.
F12104 The return value of sqlite3_exec() shall be SQLITE_OK if all SQL statements run successfully and to completion.
F12105 The return value of sqlite3_exec() shall be 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 shall be invoked once for each row of result.
F12110 If the callback returns a non-zero value then sqlite3_exec() shall abort the SQL statement it is currently evaluating, skip all subsequent SQL statements, and return SQLITE_ABORT.
F12113 The sqlite3_exec() routine shall pass its 4th parameter through as the 1st parameter of the callback.
F12116 The sqlite3_exec() routine shall set the 2nd parameter of its callback to be the number of columns in the current row of result.
F12119 The sqlite3_exec() routine shall set 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 shall set 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() shall silently discard query results.
F12131 If an error occurs while parsing or evaluating any of the SQL statements in the S parameter of sqlite3_exec(D,S,C,A,E) and if the E parameter is not NULL, then sqlite3_exec() shall store in *E an appropriate error message written into memory obtained from sqlite3_malloc().
F12134 The sqlite3_exec(D,S,C,A,E) routine shall set the value of *E to NULL if E is not NULL and there are no errors.
F12137 The sqlite3_exec(D,S,C,A,E) function shall set the error code and message accessible via sqlite3_errcode(), sqlite3_errmsg(), and sqlite3_errmsg16().
F12138 If the S parameter to sqlite3_exec(D,S,C,A,E) is NULL or an empty string or contains nothing other than whitespace, comments, and/or semicolons, then results of sqlite3_errcode(), sqlite3_errmsg(), and sqlite3_errmsg16() shall reset to indicate no errors.

Limitations:

A12141 The first parameter to sqlite3_exec() must be an valid and open database connection.
A12142 The database connection must not be closed while sqlite3_exec() is running.
A12143 The calling function should use sqlite3_free() to free the memory that *errmsg is left pointing at once the error message is no longer needed.
A12145 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 considerations.

Invariants:

F12201 Each new database connection shall have the extended result codes feature disabled by default.
F12202 The sqlite3_extended_result_codes(D,F) interface shall 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. Incomplete updates may be rolled back and transactions canceled, 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.

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 operation is very nearly finished at the time when sqlite3_interrupt() is called, then it might not have an opportunity to be interrupted and 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:

A12279 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 in the first argument. If no successful INSERTs have ever occurred on that 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 performed on the same database connection and within the same or higher level trigger context, or zero if there have been no qualifying inserts.
F12223 The sqlite3_last_insert_rowid() function returns the same value when called from the same trigger context immediately before and after a ROLLBACK.

Limitations:

A12232 If a separate thread performs 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 preprocessor 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 attack. 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.

New run-time limit categories may be added in future releases.

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 the database connection D to the lesser 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 the 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 the 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 */
);

This interface loads an SQLite extension library from the named file.

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, otherwise an error will be returned.

Find the next prepared statement

sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt);

This interface returns a pointer to the next prepared statement after pStmt associated with the database connection pDb. If pStmt is NULL then this interface returns a pointer to the first prepared statement associated with the database connection pDb. If no prepared statement satisfies the conditions of this routine, it returns NULL.

Invariants:

F13143 If D is a database connection that holds one or more unfinalized prepared statements and S is a NULL pointer, then sqlite3_next_stmt(D, S) routine shall return a pointer to one of the prepared statements associated with D.
F13146 If D is a database connection that holds no unfinalized prepared statements and S is a NULL pointer, then sqlite3_next_stmt(D, S) routine shall return a NULL pointer.
F13149 If S is a prepared statement in the database connection D and S is not the last prepared statement in D, then sqlite3_next_stmt(D, S) routine shall return a pointer to the next prepared statement in D after S.
F13152 If S is the last prepared statement in the database connection D then the sqlite3_next_stmt(D, S) routine shall return a NULL pointer.

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 placeholder 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 operation 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. If N is less than 1, sqlite3_progress_handler() acts as if a NULL progress handler had been specified.
F12913 The progress callback itself is identified by the third argument to sqlite3_progress_handler().
F12914 The fourth argument to 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 registered 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 applications 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 library. Memory used to cache database pages to improve performance is an example of non-essential memory.