sqlite3.h

Overview

The sqlite3.h header file defines the public C-language API for the SQLite library. It provides the interfaces and constants that client programs use to interact with SQLite database connections, execute SQL statements, manage prepared statements, handle errors, configure the SQLite environment, and more. This file is the authoritative source for all stable and experimental API functions, structures, types, and constants that SQLite exposes.

Main Components

1. Database Connection and Statement Handles

• sqlite3
  An opaque structure representing an open database connection. It acts as an object with constructors and destructors:

  • Constructors: sqlite3_open(), sqlite3_open16(), sqlite3_open_v2()

  • Destructors: sqlite3_close(), sqlite3_close_v2()

• sqlite3_stmt
  An opaque structure representing a single compiled SQL statement (prepared statement). The lifecycle includes preparation, parameter binding, execution, reset, and finalization.

2. Result Codes and Extended Result Codes

• Standard result codes such as SQLITE_OK, SQLITE_ERROR, SQLITE_BUSY, etc., are defined to indicate the success or failure of SQLite API calls.

• Extended result codes provide finer-grained error information and are enabled or disabled on a per-connection basis using sqlite3_extended_result_codes().

3. File and VFS Interfaces

• sqlite3_file and sqlite3_io_methods define how SQLite interacts with the underlying filesystem.

• sqlite3_vfs defines the virtual file system interface, allowing SQLite to abstract OS-specific behavior for file operations, randomness, timing, and dynamic loading.

4. Memory Allocation and Mutex Interfaces

• sqlite3_mem_methods allows applications to override SQLite’s default memory allocators.

• sqlite3_mutex is an opaque type representing mutexes used internally by SQLite for thread safety.

5. SQL Execution and Prepared Statements

• Functions for preparing SQL statements:

  • sqlite3_prepare(), sqlite3_prepare_v2(), sqlite3_prepare_v3(), and UTF-16 variants.

• Statement evaluation with sqlite3_step().

• Parameter binding interfaces like sqlite3_bind_int(), sqlite3_bind_text(), sqlite3_bind_blob(), etc.

• Column access functions such as sqlite3_column_int(), sqlite3_column_text(), sqlite3_column_blob(), and others.

• Statement reset and finalization using sqlite3_reset() and sqlite3_finalize().

6. Error Handling

• sqlite3_errcode(), sqlite3_extended_errcode(), sqlite3_errmsg(), and related functions provide access to error codes and messages.

• sqlite3_errstr() returns string descriptions for result codes.

• sqlite3_error_offset() gives the byte offset of the error token in SQL input.

7. Configuration Interfaces

• Global configuration with sqlite3_config().

• Per-connection configuration using sqlite3_db_config().

• Run-time limits via sqlite3_limit().

• Thread safety check with sqlite3_threadsafe().

• Initialization and shutdown via sqlite3_initialize() and sqlite3_shutdown().

8. Utility Functions

• sqlite3_exec() for running multiple SQL statements with optional callback for result rows.

• sqlite3_get_table() and sqlite3_free_table() for convenience in retrieving all results at once.

• String formatting with sqlite3_mprintf(), sqlite3_snprintf(), and variants.

• URI parameter parsing functions like sqlite3_uri_parameter(), sqlite3_uri_boolean(), and sqlite3_uri_int64().

9. SQL Function and Aggregate Creation

• Functions to create or redefine SQL scalar functions, aggregate functions, and window functions:

  • sqlite3_create_function(), sqlite3_create_function16(), sqlite3_create_function_v2(), and sqlite3_create_window_function().

10. Authorizer and Busy Handler

• Authorization callback setting with sqlite3_set_authorizer(), controlling permitted SQL actions.

• Busy handler configuration with sqlite3_busy_handler() and sqlite3_busy_timeout() to control behavior when the database is locked.

11. Trace and Profile Callbacks

• Deprecated tracing functions sqlite3_trace() and sqlite3_profile().

• Modern tracing with sqlite3_trace_v2() supporting multiple event types: statement start, profile, row output, and connection close.

12. Progress Handlers and Interrupts

• sqlite3_progress_handler() to set callbacks periodically during long-running queries.

• sqlite3_interrupt() to abort operations, safe to call from other threads.

Detailed Explanations

sqlite3_open(), sqlite3_open16(), sqlite3_open_v2()

Purpose:
Open a new SQLite database connection.

Parameters:

• filename: UTF-8 or UTF-16 encoded database filename or special values like ":memory:".

• ppDb: Output parameter to receive the database connection pointer.

• flags (only in sqlite3_open_v2): Flags controlling how the database is opened (e.g., read-only, read-write, create).

• zVfs (only in sqlite3_open_v2): Name of the VFS module to use, or NULL for default.

Return:
SQLITE_OK on success or an error code otherwise.

Usage Example:

sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {
    // handle error
}

sqlite3_close(), sqlite3_close_v2()

Purpose:
Close an open SQLite database connection and release resources.

Parameters:

• sqlite3*: The database connection to close.

Return:
SQLITE_OK on success. sqlite3_close() returns SQLITE_BUSY if unfinalized statements or unfinished objects exist, while sqlite3_close_v2() marks the connection as zombie and returns SQLITE_OK.

Usage Notes:
Finalize all prepared statements and close all BLOB handles before closing the database to avoid SQLITE_BUSY.

sqlite3_exec()

Purpose:
Execute one or more SQL statements in a single call with optional callback for each result row.

Parameters:

• sqlite3*: Open database connection.

• sql: UTF-8 encoded SQL statement(s).

• callback: Function invoked for each result row, or NULL if results are ignored.

• void *: First argument passed to callback.

• char **errmsg: Pointer to a string where error messages are stored.

Return:
SQLITE_OK on success or an error code.

Usage Example:

int callback(void *data, int argc, char **argv, char **azColName){
    for(int i = 0; i < argc; i++){
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    return 0;
}

char *errmsg = 0;
sqlite3_exec(db, "SELECT * FROM table;", callback, 0, &errmsg);
if (errmsg) {
    // handle error
    sqlite3_free(errmsg);
}

sqlite3_prepare_v2()

Purpose:
Compile SQL text into a prepared statement.

Parameters:

• db: Database connection.

• zSql: UTF-8 SQL query string.

• nByte: Maximum bytes of zSql to read.

• ppStmt: Output pointer to compiled statement.

• pzTail: Pointer to unused portion of zSql.

Return:
SQLITE_OK on success or an error code.

Usage:
A prepared statement can be executed via sqlite3_step() repeatedly, reset with sqlite3_reset(), and finalized with sqlite3_finalize().

sqlite3_step()

Purpose:
Execute a prepared statement or advance it to the next result row.

Parameters:

• sqlite3_stmt*: Prepared statement.

Return:

• SQLITE_ROW: A new row of data is ready.

• SQLITE_DONE: Execution completed successfully.

• SQLITE_BUSY, SQLITE_ERROR, SQLITE_INTERRUPT, or other error codes on failures.

Usage:

while(sqlite3_step(stmt) == SQLITE_ROW){
    // process row
}

sqlite3_column_*() Functions

Retrieve individual column data from the current row of a result set.

• sqlite3_column_int(), sqlite3_column_int64(): Return integer values.

• sqlite3_column_double(): Return floating-point value.

• sqlite3_column_text(): Return UTF-8 encoded text string.

• sqlite3_column_blob(): Return BLOB data pointer.

• sqlite3_column_type(): Return the datatype code of the column.

sqlite3_bind_*() Functions

Bind parameters to SQL statements before execution.

• sqlite3_bind_int(), sqlite3_bind_int64(): Bind integer values.

• sqlite3_bind_double(): Bind floating-point values.

• sqlite3_bind_text(), sqlite3_bind_text16(), sqlite3_bind_text64(): Bind text values.

• sqlite3_bind_blob(): Bind BLOB data.

• sqlite3_bind_null(): Bind NULL value.

• sqlite3_bind_zeroblob(): Bind a BLOB of zeros of specified length.

Parameter Indexing:
Parameters are 1-indexed. Named parameters can be queried with sqlite3_bind_parameter_index().

sqlite3_finalize()

Purpose:
Delete a prepared statement and release associated resources.

Parameters:

• sqlite3_stmt*: Prepared statement to finalize.

Return:
SQLITE_OK on successful finalization, or error code if last run failed.

sqlite3_reset()

Purpose:
Reset a prepared statement to its initial state for re-execution.

Parameters:

• sqlite3_stmt*: Prepared statement.

Return:
SQLITE_OK if reset successful.

sqlite3_create_function(), sqlite3_create_function_v2()

Purpose:
Register or redefine SQL scalar, aggregate, or window functions.

Parameters:

• db: Database connection.

• zFunctionName: SQL function name (UTF-8 or UTF-16).

• nArg: Number of arguments or -1 for variable.

• eTextRep: Preferred text encoding and flags (e.g., SQLITE_UTF8 | SQLITE_DETERMINISTIC).

• pApp: Application data pointer passed to callbacks.

• xFunc, xStep, xFinal: Callback functions for scalar, aggregate step/final, respectively.

• xDestroy (only in _v2): Destructor for pApp.

sqlite3_set_authorizer()

Purpose:
Set a callback to authorize or deny certain SQL operations during statement compilation.

Parameters:

• db: Database connection.

• xAuth: Callback function receiving action codes and details.

• pUserData: Application data passed to callback.

Authorizer Return Codes:

• SQLITE_OK: Permit action.

• SQLITE_IGNORE: Disallow specific action but continue compilation.

• SQLITE_DENY: Abort statement with error.

sqlite3_busy_handler() and sqlite3_busy_timeout()

Purpose:
Set handlers to respond when database is locked (SQLITE_BUSY).

• sqlite3_busy_handler(): Register a custom retry callback.

• sqlite3_busy_timeout(): Set a timeout in milliseconds to retry automatically.

sqlite3_trace_v2()

Purpose:
Register a callback for tracing SQL statement execution and profiling.

Parameters:

• db: Database connection.

• uMask: Bitmask of events to trace (SQLITE_TRACE_STMT, SQLITE_TRACE_PROFILE, etc.).

• xCallback: Callback function.

• pCtx: User data pointer.

sqlite3_progress_handler()

Purpose:
Invoke a callback periodically during long-running SQL operations to enable UI updates or cancellation.

Parameters:

• db: Database connection.

• N: Number of virtual machine instructions between callback invocations.

• X: Callback function.

• P: User data pointer.

sqlite3_interrupt()

Purpose:
Request immediate abort of any currently running SQL operation on a database connection.

Parameters:

• db: Database connection.

Important Implementation Details

• Thread Safety:
  The sqlite3_threadsafe() function indicates whether SQLite was compiled with mutexes enabled. Mutexing behavior can be configured with sqlite3_config().

• Extended Result Codes:
  Extended error codes provide more detailed error information and can be toggled per database connection.

• URI Filenames:
  SQLite supports opening database files using URI syntax with special query parameters for mode, cache, VFS selection, and other options.

• VFS Mechanism:
  The virtual file system interface allows SQLite to operate on different storage backends and platforms by abstracting file operations and OS interactions.

• Memory Management:
  SQLite uses custom memory allocators but allows applications to override them. All memory allocated by SQLite API functions must be freed using SQLite's corresponding free functions.

Interaction with Other System Components

• Database Connections (sqlite3):
  Central to all database operations, managing state, schema, cache, and configuration.

• Prepared Statements (sqlite3_stmt):
  Represent compiled SQL ready for execution and interact closely with connections and query engines.

• VFS Layer (sqlite3_vfs and sqlite3_file):
  Interfaces SQLite uses to perform file operations, randomness, and timing, enabling platform abstraction.

• Memory and Mutex Systems:
  Used internally by SQLite to ensure efficient and safe resource management, configurable by the application.

• Application-defined SQL Functions:
  Extend SQL capabilities and integrate with SQLite execution through function registration APIs.

Visual Diagram: Flowchart of Key API Interactions

flowchart TD
A["sqlite3_open()"] --> B[sqlite3 database connection]
B --> C["sqlite3_prepare_v2()"]
C --> D[sqlite3_stmt prepared statement]
D --> E["sqlite3_bind_*() parameter binding"]
D --> F["sqlite3_step() execute statement"]
F -- if SQLITE_ROW --> G["Access column data (sqlite3_column_*)"]
F -- if SQLITE_DONE --> H["sqlite3_reset() or sqlite3_finalize()"]
B --> I["sqlite3_exec() convenience interface"]
B --> J["sqlite3_close()"]
B --> K["sqlite3_set_authorizer()"]
B --> L["sqlite3_busy_handler() / sqlite3_busy_timeout()"]
B --> M["sqlite3_trace_v2()"]
B --> N["sqlite3_progress_handler()"]
B --> O["sqlite3_interrupt()"]

This flowchart shows how a typical SQLite usage scenario flows from opening a connection, preparing statements, binding parameters, executing queries, accessing results, and finally cleaning up, as well as how auxiliary features like authorizers, busy handlers, tracing, progress callbacks, and interrupts integrate with the connection.

Selected Constants and Macros

• Result Codes:
  SQLITE_OK, SQLITE_ERROR, SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, etc.

• File Open Flags:
  SQLITE_OPEN_READONLY, SQLITE_OPEN_READWRITE, SQLITE_OPEN_CREATE, SQLITE_OPEN_URI, etc.

• Mutex and Threading Modes:
  SQLITE_CONFIG_SINGLETHREAD, SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED.

• Column Data Types:
  SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, SQLITE_NULL.

• Text Encodings:
  SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, SQLITE_UTF16.

• Authorizer Actions:
  SQLITE_CREATE_INDEX, SQLITE_INSERT, SQLITE_READ, SQLITE_UPDATE, SQLITE_DELETE, etc.

• Function Flags:
  SQLITE_DETERMINISTIC, SQLITE_DIRECTONLY, SQLITE_INNOCUOUS, SQLITE_SUBTYPE, etc.

Usage Notes

• Applications should finalize all prepared statements and close database connections to avoid resource leaks.

• Use sqlite3_prepare_v2() or sqlite3_prepare_v3() rather than legacy sqlite3_prepare().

• Bind parameters using sqlite3_bind_*() before calling sqlite3_step().

• Use sqlite3_errmsg() to get human-readable error messages after API failures.

• Register authorizer and busy handlers to customize security and locking behavior respectively.

• Use sqlite3_trace_v2() for detailed tracing and profiling of SQL execution.

• The VFS interface allows SQLite to be ported or extended to various underlying storage mechanisms.

This file is fundamental to the SQLite C API and interacts with many other components such as virtual tables, custom VFS implementations, and user-defined SQL functions. It covers the core functionality for database connection management, SQL statement preparation and execution, error handling, configuration, and extension mechanisms.

For further details, see related topics such as Prepared Statements, Virtual File System (VFS), Error Handling in SQLite, and User-Defined Functions.