Introduction to the

SQLite interface elements can be divided into three categories:

  • Object list. This is a list of all the abstract objects and data types used by the SQLite library. There are dozens of objects in total, but the two most important ones are the Database Connection Object (SQlite3) and the Prepared Statement Object (SQlite3_STMT).

  • Constant list. This is a list of numeric constants used by SQLite, represented by the definitions in the sqLite3.h header file. These constants are numeric result codes for various interfaces (e.g., SQLITE_OK) or flags passed into functions to control behavior (e.g., SQLITE_OPEN_READONLY).

  • Feature list. This is a list of all the functions and methods that operate on the object and use and/or return constants. There are many functions, but most applications use only a few.

Database Connection Object

typedef struct sqlite3 sqlite3;
Copy the code

Each open SQLite database is represented by a pointer to an instance of an opaque structure named SQlite3. It’s useful to think of sqlite3 Pointers as objects.

The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces are their constructors

Sqlite3_close () and sqlite3_close_v2() are their destructors.

There are many other interfaces (such as sqlite3_prepare_v2(), sqlite3_create_function(), and sqlite3_busy_timeout()) that are methods on sqlite3 objects.

Prepared Statement Object

typedef struct sqlite3_stmt sqlite3_stmt;
Copy the code

An instance object represents a single SQL statement that has been compiled into binary form and is ready for computation.

Think of each SQL statement as a separate computer program. The original SQL text is the source code. The PREPARED statement object is the compiled object code. All SQL must be converted to a PREPARED statement before it can run.

The lifecycle of a PREPARED statement object is usually as follows:

  1. Use sqlite3_prepare_v2() to create a PREPARED statement object.
  2. Use sqlite3_bind_*() to bind values to parameters
  3. Run the SQL one or more times by calling sqlite3_step()
  4. Use sqlite3_reset() to reset prepared’s statement, and then return to Step 2. Do this zero or more times
  5. Destroy the object with sqlite3_Finalize ()

Constructor:

  • sqlite3_prepare
  • sqlite3_prepare_v2
  • sqlite3_prepare_v3
  • sqlite3_prepare16
  • sqlite3_prepare16_v2
  • sqlite3_prepare16_v3

Destructor:

  • sqlite3_finalize()

Commonly used API

sqlite3_open

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);
Copy the code

These routines open the SQLite database file specified by the filename argument. For sqlite3_open() and sqlite3_open_v2(), filename is UTF-8; For sqlite3_open16(), filename is UTF-16. Database connection handles are usually returned in *ppDb, even if an error occurs. The only exception is if SQLite cannot allocate memory to hold the SQlite3 object, then a NULL is written to *ppDb instead of a pointer to the sqlite3 object. SQLITE_OK is returned if the database was successfully opened (and/or created). Otherwise an error code is returned. The sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to get an English description of any error after the sqlite3_open() routine fails.

For databases created using sqlite3_open() or SQlite3_open_v2 (), the default encoding is UTF-8. The default encoding for a database created using SQlite3_open16 () will be UTF-16 in native byte order.

Regardless of whether an error occurred while opening the database connection handle, the resources associated with the database connection handle should be released by passing it to sqlite3_CLOSE () when it is no longer needed.

The sqlite3_open_v2() interface works in a similar way to sqlite3_open(), except that it accepts two additional parameters to control new database connections. The flags argument for sqlite3_open_v2() must contain at least one of the following combinations of flags:

  • SQLITE_OPEN_READONLY

    The database is opened in read-only mode. If the database does not exist, an error is returned.

  • SQLITE_OPEN_READWRITE

    If possible, the database is opened for reading and writing, or only if the file is write protected by the operating system. In either case, the database must already exist or an error will be returned.

  • SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

    Open the database for read and write operations. If the database does not exist, create the database. This is the behavior always used for sqlite3_open() and sqlite3_open16().

In addition to the required flags, the following optional flags are supported:

  • SQLITE_OPEN_URI

    If this flag is set, the file name can be interpreted as a URI.

  • SQLITE_OPEN_MEMORY

    The database will be opened as an in-memory database. If the shared cache mode is enabled, the database is named by the filename parameter to realize cache sharing. Otherwise, filename is ignored.

  • SQLITE_OPEN_NOMUTEX

    New database connections will use the “multi-threaded” thread mode. This means that separate threads are allowed to use SQLite at the same time, as long as each thread uses a different database connection.

  • SQLITE_OPEN_FULLMUTEX

    New database connections will use the “serialized” thread mode. This means that multiple threads can safely try to use the same database connection at the same time. (The mutex will prevent any actual concurrency, but in this mode, there’s no harm in trying.)

  • SQLITE_OPEN_SHAREDCACHE

    The database has shared cache enabled and enabled, overriding the default shared cache Settings provided by sqlite3_ENABLE_shared_cache ().

  • SQLITE_OPEN_PRIVATECACHE

    The shared cache is disabled when the database is open, overriding the default shared cache Settings provided by sqlite3_ENABLE_shared_cache ().

  • SQLITE_OPEN_NOFOLLOW

    Database file names are not allowed to be symbolic links

This behavior is undefined if the third argument to sqlite3_open_v2() is not one of the required combinations shown above (optionally combined with other SQLITE_OPEN_* bits).

The fourth parameter to sqlite3_open_v2() is the name of the SQlite3_VFS object that defines the operating system interface that the new database connection should use. If the fourth argument is a null pointer, the default SQlite3 VFS object is used.

If the file name is :memory:, a dedicated temporary memory database is created for the connection. When the database connection is closed, the in-memory database disappears. Future versions of SQLite may use other special filenames that begin with a “:” character. To avoid ambiguity, you are advised to add a pathname, such as a./, before the file name when the database file name starts with a “:”.

If the file name is an empty string, a dedicated temporary disk database is created. Once the database connection is closed, this dedicated database will be automatically deleted.

sqlite3_close

int sqlite3_close(sqlite3*);
int sqlite3_close_v2(sqlite3*);
Copy the code

Sqlite3_close () and sqlite3_close_v2() are destructors for sqlite3. Calls to sqlite3_CLOSE () and sqlite3_close_v2() will return SQLITE_OK on successful destruction of the SQlite3 object and the release of all associated resources.

If the database connection is associated with an unfinished PREPARED statement or an unfinished SQlite3_BACKUP object, sqlite3_close() will keep the database connection open and return SQLITE_BUSY. If sqlite3_close v2() is called with incomplete PREPARED statements or incomplete SQlite3_backups, the database connection becomes an unavailable “zombie,” It is automatically released when the last PREPARED statement completes or the last SQlite3_backup completes. The sqlite3_CLOSE_v2 () interface is used as the host language for garbage collection, where the order in which destructors are called is arbitrary.

The application should complete all prepared statements, close all BLOB handles, and complete all sqLite3_BACKUP objects associated with the SQLite3 object before attempting to close the object. If sqlite3_close_v2() is called for database connections with outstanding PREPARED statements, BLOB handles, and/or sqlite3_BACKUP objects, it will return SQLITE_OK and delay the release of resources, Until all the prepared statements, BLOB handles, and sqlite3_BACKUP objects are also destroyed.

If the SQlite3 object is destroyed while the transaction is open, the transaction is automatically rolled back.

The C argument to sqlite3_close (C) and sqlite3_close v2 (C) must be a null pointer, Or a pointer to an sqlite3 object obtained from sqlite3_open (), sqlite3_open16 (), or sqlite3_open v2 () and not previously closed. Calling sqlite3_close () or sqlite3_close_v2 () with a null pointer argument is harmless no-op.

sqlite3_prepare_v2

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
Copy the code

To execute an SQL statement, you must first compile it into a bytecode program using SQLite3_PREPARE_v2. Or, to put it another way, these examples are constructors for prepared statement objects.

  • The first argument, db, is the database connection obtained from previous successful calls to sqlite3_open (), sqlite3_open v2 (), or sqlite3_open16 (). The database connection must not be closed.

  • The second parameter, zSql, is the statement to be compiled, encoded as UTF-8 or UTF-16. Sqlite3_prepare (), sqlite3_prepare_v2 (), and sqlite3_prepare_v3 () interfaces use UTF-8, Sqlite3_prepare16 (), sqlite3_prepare16_v2 (), and sqlite3_prepare16_v3 () use UTF-16.

  • If the nByte argument is negative, zSql is read to the first zero terminator. If nByte is positive, it is the number of bytes read from zSql. If nByte is zero, no prepared statements are generated. Passing an nByte argument (the number of bytes in the input string including terminated) has a small performance advantage if the caller knows that the provided string is terminated in kul-terminated.

  • If pzTail is not empty, then *pzTail will point to the first byte after the end of the first SQL statement in zSql. These routines compile only the first statement in zSql, so *pzTail points to the uncompiled content.

  • *ppStmt left refers to a compiled prepared statement that can be executed using sqlite3_step(). If there is an error, *ppStmt is set to null. If the input text does not contain SQL (if the input is an empty string or comment), then *ppStmt is set to empty. The calling procedure is responsible for removing the compiled SQL statements after compilation using sqlite3_Finalize (). PpStmt cannot be NULL.

Sqlite3_prepare_v2 returns SQLITE_OK on success; Otherwise an error code is returned.

sqlite3_bind_parameter_count

int sqlite3_bind_parameter_count(sqlite3_stmt*);
Copy the code

Can be used to find the number of SQL parameters in a Parepared statement. The SQL parameter is “?” , “? Tokens of the form NNN”, “:AAA”, “$AAA”, or “@aaa” are used as placeholders for values that are later bound to the parameter.

sqlite3_bind_parameter_index

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);
Copy the code

Returns the index of the SQL parameter with the given name. The index value returned is suitable as the second argument to sqlite3_bind (). If no matching argument is found, zero is returned. Parameter names must be specified in UTF-8 even if the original statement was prepared from UTF-16 text using sqlite3_PREPARE16_v2 () or SQlite3_PREPARE16_v3 ().

sqlite3_bind_parameter_name

const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);
Copy the code

Sqlite3_bind_parameter_name (P, N) The interface returns the name of the NTH SQL parameter in prepared statement P.

SQL form parameters “? NNN” or :AAA or @aaa or $AAA is the name corresponding to a string, respectively? NNN or :AAA or @aaa or $AAA. In other words, initial letters: or $or @ or? Included in the name. Table parameters? The following integers have no name and are called “nameless” or “anonymous parameters”.

The first argument has an index of 1, not 0. The returned string is always in UTF-8 encoding.

sqlite3_bind_xxx

int sqlite3_bind_blob(sqlite3_stmt*, int.const void*, int n, void(*) (void*));
int sqlite3_bind_blob64(sqlite3_stmt*, int.const void*, sqlite3_uint64,
                        void(*) (void*));
int sqlite3_bind_double(sqlite3_stmt*, int.double);
int sqlite3_bind_int(sqlite3_stmt*, int.int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*,int.const char*,int.void(*) (void*));
int sqlite3_bind_text16(sqlite3_stmt*, int.const void*, int.void(*) (void*));
int sqlite3_bind_text64(sqlite3_stmt*, int.const char*, sqlite3_uint64,
                         void(*) (void*), unsigned char encoding);
int sqlite3_bind_value(sqlite3_stmt*, int.const sqlite3_value*);
int sqlite3_bind_pointer(sqlite3_stmt*, int.void*, const char*,void(*) (void*));
int sqlite3_bind_zeroblob(sqlite3_stmt*, int.int n);
int sqlite3_bind_zeroblob64(sqlite3_stmt*, int, sqlite3_uint64);
Copy the code

In the SQL statement text input to sqlite3_PREPARE_v2 () and its variants, the text can be replaced with an argument that matches one of the following templates:

  • ?
  • ? NNN
  • :VVV
  • @VVV
  • $VVV

In the above template, NNN represents integer text and VVV represents an alphanumeric identifier. The values of these parameters (also known as “host parameter name” or “SQL parameter”) can be set using sqlite3_bind_*() defined here.

  • The first argument to sqlite3_bind_*() is always a pointer to the sqLite3_stmt object returned from sqlite3_prepare_v2() or a variant of it.

  • The second parameter is the index of the SQL parameter to set. The left-most SQL parameter has an index of 1. When the same named SQL parameter is used multiple times, the second and subsequent occurrences have the same index as the first occurrence. If desired, you can use the sqlite3_bind_parameter_index() API to find the index of the named parameter. The index? The NNN argument is the value of NNN. The NNN value must be between 1 and SQLITE_LIMIT_VARIABLE_NUMBER as the sqlite3_LIMIT () argument (default: 999).

  • The third parameter is the value to be bound to. If the third argument to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_blob() is a null pointer, the fourth argument is ignored and the end result is the same as sqlite3_bind_NULL().

  • In those cases where there is a fourth argument, its value is the number of bytes in the argument. Be clear: a value is the number of bytes in the value, not the number of characters. If the fourth argument of sqlite3_bind_text() or sqlite3_bind_text16() is negative, the length of the string is the number of bytes before the first zero terminator. If the fourth argument to sqlite3_bind_blob() is negative, the behavior is undefined. If a non-negative fourth argument is provided to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_text64(), the argument must be the byte offset that the NUL terminator appears when the string is assumed to end in NUL. If the byte offset of any NUL character is less than the value of the fourth argument, the generated string value will contain the embedded NUL. The result of an expression containing a string embedded in NUL is undefined.

  • The fifth parameter to the BLOB and String binding interface is a destructor that is used to process the BLOB or string after SQLite has finished processing it. The destructor is called to free the BLOB or string, even if the call to the BIND API fails, but if the third argument is null or the fourth argument is negative, the destructor is not called. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the value of the fifth parameter is SQLITE_TRANSIENT, then SQLite creates its own private copy of the data immediately before the sqLITe3_bind_ *() example returns.

  • The sixth argument to sqlite3_bind_text64() must be one of SQLITE_UTF8, SQLITE_UTF16, SQLITE_UTF16BE, or SQLITE_UTF16LE to specify the encoding of the text for the third argument. If the sixth argument to sqlite3_bind_text64() is not one of the allowed values shown above, or if the text encoding is different from that specified by the sixth argument, the behavior is undefined.

The sqlite3_bind_Zeroblob () routine binds a BLOB of length N that is filled with zeros. Zeroblob uses a fixed amount of memory (just an integer to hold its size) for processing. ZeroBlob is used as a placeholder for the BLOB, the contents of which are later written using the incremental BLOB I/O routine. A negative value of zero BLOB results in a zero-length BLOB.

The sqlite3_bind_pointer (S, I, P, T, D) routine makes the SQL value of the ith parameter in the prepared statement S null, but is also associated with a pointer P of type T. D can be either a null pointer or a pointer to the destructor of P. After using P, SQLite will call the destructor with a single argument to P. The DT argument should be a static string, preferably string text. The sqlite3_bind_pointer () routine is part of the pointer passing interface added to sqLite3.20.0.

If any sqlite3_bind_*() routine is called with the null pointer to the Prepared statement or with a prepared statement for which SQlite3_step () has been called more recently than sqlite3_reset (), the call returns SQLITE_MISUSE. If any sqlite3_bind_*() routine is passed a completed PREPARED statement, the result is undefined and potentially hazardous.

Sqlite3_reset () cannot clear the binding. Unbound parameters are interpreted as NULL.

Sqlite3_bind_ * returns SQLITE_OK on success and an error code if there are any errors. SQLITE_TOOBIG may be returned if the size of the string or BLOB exceeds the limit set by sqlite3_LIMIT (SQLITE_LIMIT_LENGTH) or SQLITE_MAX_LENGTH. SQLITE_RANGE is returned if the parameter index is out of range. SQLITE_NOMEM is returned if malloc() fails.

sqlite3_step

int sqlite3_step(sqlite3_stmt*);
Copy the code

After the PREPARED statement is generated by calling interfaces such as sqlite3_prepare_v2(), this function must be called one or more times to evaluate the statement.

On traditional interfaces, the return value will be SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. Using the “v2” interface, you can also return any other result code or extend the result code.

  • SQLITE_BUSY indicates that the database engine cannot acquire the database lock needed to perform its job. If the statement is a commit statement or occurs outside an explicit transaction, you can retry the statement. If the statement is not COMMIT and occurs in an explicit transaction, the transaction should be rolled back before proceeding.

  • SQLITE_DONE indicates that the statement has completed successfully. Sqlite3_step () should not be called again on this vm without first calling sqlite3_reset() to reset the vm back to its original state

  • If the SQL statement being executed returns any data, SQLITE_ROW is returned each time the caller is ready to process a new data row. These values can be accessed using column access functions. Call sqlite3_step() again to retrieve the next row of data.

  • SQLITE_ERROR indicates that a runtime error (such as a constraint conflict) has occurred. Sqlite3_step () should not be called again on the virtual machine. You can find out more by calling sqlite3_errmsg(). For traditional interfaces, you can get more specific error codes (for example, SQLITE_INTERRUPT, SQLITE_SCHEMA, SQLITE_CORRUPT, and so on) by calling sqlite3_reset() on prepared statements. In the “v2” interface, the more specific error code is returned directly by sqlite3_step().

  • SQLITE_MISUSE means that the routine is improperly invoked. It may be called on a completed prepared statement, or on a previous statement that returned SQLITE_ERROR or SQLITE_DONE. Or it may be that the same database connection is being used by two or more threads simultaneously.

sqlite3_reset

int sqlite3_reset(sqlite3_stmt *pStmt);
Copy the code

A call to the sqlite3_reset() function resets the Prepared statement object back to its original state, ready for re-execution. Any SQL statement variables that bind values to them using the SQLite3_bind_ *() API will retain their values. Reset bindings using sqlite3_clear_bindings().

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

Sqlite3_reset returns SQLITE_OK if the last call to sqlite3_step for PREPARED statement S returned SQLITE_ROW or SQLITE_DONE, or if SQlite3_step has never been called on S before.

If the latest call to sqlite3_step(S) of the PREPARED statement indicates an error, sqlite3_reset will return the appropriate error code.

The sqlite3_reset (S) interface does not change any values bound on the PREPARED statement S.

sqlite3_column_count

int sqlite3_column_count(sqlite3_stmt *pStmt);
Copy the code

Returns the number of columns in the result set returned by the PREPARED statement. If this function returns 0, then the PREPARED statement does not return data (for example, UPDATE). However, just because a positive number is returned does not mean that one or more rows of data will be returned. The SELECT statement will always have positive SQLITe3_COLUMN_count (), but it may not return any rows, depending on WHERE clause constraints and table contents.

sqlite3_data_count

int sqlite3_data_count(sqlite3_stmt *pStmt);
Copy the code

The sqLITe3_DATA_COUNT (P) interface returns the number of columns in the current row in the result set prepared Statement P. Sqlite3_data_count (P) returns 0 if prepared Statement P is not ready to return the result (by calling the sqlite3_column () interface series). The sqlite3_data_count (P) routine also returns 0 if P is a null pointer. If the previous call to the sqlite3 step (P) returned SQLITE_DONE, the sqLITe3_datA_Count (P) routine returns 0. If a previous call to SQlite3_step (P) returned SQLITE_ROW, sqlite3_datA_count (P) would return non-zero, unless PRAGMA incremental_VACUUM always returns zero, since each step of the multi-step PRAGMA returns 0 columns of data.

sqlite3_column_name

const char *sqlite3_column_name(sqlite3_stmt*, int N);
const void *sqlite3_column_name16(sqlite3_stmt*, int N);
Copy the code

These routines return the name specified for a particular column in the result set of the SELECT statement. The sqlite3_COLUMN_name () interface returns a pointer to a zero-terminated UTF-8 string, and the sqlite3_COLUMN_name16 () interface returns a pointer to a zero-terminated UTF-16 string. The first parameter is the PREPARED statement that implements the SELECT statement. The second parameter is the column number. The leftmost column is 0.

The returned string pointer is valid until the prepared statement is destroyed by sqlite3_Finalize (), or until the first call to sqlite3_step() automatically reprepares the statement for a particular run, Or until the next call to sqlite3_COLUMN_name () or sqlite3_COLUMN_name16 () on the same column.

Sqlite3_malloc () returns a null pointer if it fails during the processing of any example, such as during the conversion from UTF-8 to UTF-16.

The name of the resulting column is the value of the column’s “AS” clause, if any. If there is no AS clause, the name of the column is not specified and may change from the next release SQLite version.

sqlite3_column_xxx

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
Copy the code
Sqlite3_column_blob → BLOB result sqlite3_column_double → REAL result sqlite3_column_int →32-bit INTEGER result
sqlite3_column_int64	→	64-bit INTEGER result
sqlite3_column_text	→	UTF- 8 - TEXT result
sqlite3_column_text16	→	UTF- 16 TEXT result
sqlite3_column_value	→	The result as an unprotected sqlite3_value object.
 	 	 
sqlite3_column_bytes	→	Size of a BLOB or a UTF- 8 - TEXT result in bytes
sqlite3_column_bytes16  	→  	Size of UTF- 16 TEXT in bytes
sqlite3_column_type	→	Default datatype of the result
Copy the code

These routines return information about a single column in the current result row of the query. In any case, the first argument is a pointer to the PREPARED statement being computed (sqlite3_STmt * returned from sqlite3_PREPARE_v2 () or a variant of it), and the second argument is the index of the column for which information should be returned. The leftmost column in the result set has an index of 0. The number of columns in the result can be determined using sqlite3_column_count().

If the SQL statement does not currently point to a valid row, or the column index is out of range, the result is returned undefined. These routines can only be called if the most recent call to sqlite3_step() returns SQLITE_ROW and then neither sqlite3_reset() nor sqlite3_Finalize () is called. If these routines are called after sqlite3_reset() or sqlite3_Finalize (), or after sqlite3_step() returns something other than SQLITE_ROW, the result is undefined. If sqlite3_step() or sqlite3_reset() or sqlite3_Finalize () are called from another thread when these routines are suspended, the result is undefined.

The first six interfaces (” blob, “” double,” “int,” “INT64,” “Text,” and “Text16”) all return the value of the result column in a specific data format. Automatic type conversion is performed if the result column is not originally in the requested format (for example, if the query returns an integer but uses the SQLite3_COLUMN_TEXT () interface to extract the value).

The sqLite3_column_type () routine returns the datatype code for the initial datatype of the result column. The value returned is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to determine which of the first six interfaces applies to extracting column values. The value returned by sqlite3_column_type() is meaningful only if the value is not automatically cast. After the cast, the result of calling sqlite3_column_type() is undefined, although harmless. Future versions of SQLite may change the behavior of SQlite3_column_type () after type conversion.

If the result is a BLOB or a text string, the sqLite3_COLUMN_bytes () or SQLite3_COLUMN_Bytes16 () interfaces can be used to determine the size of the BLOB or string.

If the result is a BLOB or UTF-8 string, sqlite3_COLUMN_bytes () returns the number of bytes in the BLOB or string. If the result is a UTF-16 string, sqlite3_COLUMN_bytes () converts the string to UTF-8 and returns the number of bytes. If the result is a numeric value, sqlite3_column_bytes() converts the value to a UTF-8 string using sqlite3_snprintf() and returns the number of bytes in that string. If the result is null, sqlite3_COLUMN_bytes () returns zero.

Sqlite3_column_bytes () and sqlite3_COLUMN_bytes16 () return values that do not include the zero terminator at the end of the string. For clarity: sqlite3_COLUMN_bytes () and sqlite3_COLUMN_bytes16 () return the number of bytes in the string, not the number of characters.

Sqlite3_column_text () and sqlite3_COLUMN_text16 () return strings, even empty ones, that always end in zero. For zero-length blobs, the return value of sqlite3_column_blob () is a null pointer.

These routines may attempt to convert the data type of the result. For example, if the internal representation is FLOAT and the text result is requested, the transformation is automatically performed internally using sqlite3_snprintf(). The following table details the transformations applied:

Internal Type Requested Type Conversion
NULL INTEGER Result is 0
NULL FLOAT The Result is 0.0
NULL TEXT Result is a NULL pointer
NULL BLOB Result is a NULL pointer
INTEGER FLOAT Convert from integer to float
INTEGER TEXT ASCII rendering of the integer
INTEGER BLOB Same as INTEGER->TEXT
FLOAT INTEGER CAST to INTEGER
FLOAT TEXT ASCII rendering of the float
FLOAT BLOB CAST to BLOB
TEXT INTEGER CAST to INTEGER
TEXT FLOAT CAST to REAL
TEXT BLOB No change
BLOB INTEGER CAST to INTEGER
BLOB FLOAT CAST to REAL
BLOB TEXT Add a zero terminator if needed

Note that Pointers returned by previous calls to sqlite3_COLUMN_blob (), sqlite3_COLUMN_TEXT (), and/or sqlite3_COLUMN_text16 () may become invalid when a type conversion occurs. A cast and an invalid pointer may occur in the following cases:

The initial content is BLOB, and sqlite3_COLUMN_TEXT () or sqlite3_COLUMN_text16 () is called. You may need to add a zero terminator to the string.

The initial content is UTF-8 text, and sqlite3_COLUMN_bytes16 () or SQlite3_COLUMN_text16 () is called. The content must be converted to UTF-16.

The initial content is UTF-16 text, and sqlite3_COLUMN_bytes () or SQlite3_COLUMN_TEXT () are called. The content must be converted to UTF-8.

The conversion between UTF-16BE and UTF-16LE is always in place and does not invalidate the previous pointer, and of course the contents of the buffer referenced by the previous pointer are modified. Other types of conversions are possible when possible, but sometimes they are not possible, in which case the previous pointer is invalid.

The safest policy is to call these routines in one of the following ways:

Sqlite3_column_text () followed by sqlite3_column_bytes ()

Sqlite3_column_blob () followed by sqlite3_column_bytes ()

Sqlite3_column_text16 () followed by sqlite3_column_bytes16 ()

In other words, you should first call sqlite3_COLUMN_text (), sqlite3_column_blob (), or sqlite3_column_text16 () to force the result into the desired format, Sqlite3_column_bytes () or sqlite3_COLUMN_bytes16 () is then called to find the size of the result. Do not mix calls to sqlite3_COLUMN_text () or sqlite3_column_blob () with calls to sqlite3_column_bytes16 (), Do not mix the call to sqlite3_COLUMN_text16 () with the call to sqlite3_COLUMN_bytes () either.

The returned pointer is valid before casting as described above, or before calling sqlite3_step () or sqlite3_reset () or sqlite3_Finalize (). The memory space used to hold strings and blobs is automatically freed. Do not pass a pointer returned from sqlite3_COLUMN_blob (), sqlite3_column_TEXT (), and so on into sqlite3_free ().

As long as the input parameters are correct, these routines will only fail if an out-of-memory error occurs during format conversion. An out of memory error occurred only for the following subroutine:

sqlite3_column_blob() sqlite3_column_text() sqlite3_column_text16() sqlite3_column_bytes() sqlite3_column_bytes16()

If an out-of-memory error occurs, these routines return the same value as if the column contained AN SQL null value. Valid SQL NULL returns can be distinguished from out-of-memory errors by calling SQLite3_ErrCode () after the suspect return value is obtained and before any other SQLite interface is called on the same database connection.

sqlite3_finalize

int sqlite3_finalize(sqlite3_stmt *pStmt);
Copy the code

Call the sqlite3_Finalize () function to remove the PREPARED statement. Sqlite3_finalize () returns SQLITE_OK if no errors have been encountered in recent evaluation of the statement, or if the statement has never been evaluated. If the latest evaluation of statement S fails, sqlite3_Finalize returns the appropriate error code or extended error code.

Sqlite3_finalize (S) can be called at any time during the lifecycle of prepared statement S: Before the evaluation of statement S, after one or more calls to sqlite3_reset (), or after any calls to sqlite3_step (), whether or not the statement has completed execution.

Calling sqlite3_Finalize () on a null pointer is harmless no-op.

The application must complete each prepared statement to avoid resource leaks. After a Prepared statement, any use of it can result in undefined and undesirable behaviors such as Segfaults and heap corruption.

sqlite3_exec

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

The sqlite3_exec() interface is a convenient wrapper around SQlite3_prepare_v2 (), sqlite3_step(), and sqlite3_Finalize (), allowing applications to run multiple SQL statements without using a lot of C code.

The sqlite3_exec() interface runs zero or more UTF-8-encoded, semicolon-separated SQL statements in the context of the database connection passed in as the first parameter. If the callback function for the third argument of sqlite3_exec() is not empty, it will be called for each result row output from the computed SQL statement. The fourth argument of sqlite3_exec() is forwarded to the first argument of each callback call. If the callback pointer to sqlite3_exec() is empty, no callback is ever called and the resulting line is ignored.

If an error occurs while evaluating the SQL statement passed to sqlite3_exec(), execution of the current statement is stopped and subsequent statements are skipped. If the fifth argument to sqlite3_exec() is not null, any error messages will be written to the memory obtained from sqlite3_malloc() and passed back by the fifth argument. To avoid memory leaks, the application should call sqlite3_free() on the error message string returned through the fifth argument of sqlite3_exec() after the error message string is no longer needed. If the fifth argument of sqlite3_exec() is not null and no error occurs, then sqlite3_exec() sets the pointer in its fifth argument to null before returning.

If the sqlite3_exec() callback returns nonzero, the sqlite3_exec() routine returns SQLITE_ABORT without calling the callback again or running any subsequent SQL statements.

The second argument to the sqlite3_exec() callback is the number of columns in the result. The third argument to the sqlite3_exec() callback is an array of Pointers to the field values, one for each column, obtained from sqlite3_column_text(). If the element of the resulting row is empty, the corresponding string pointer to the sqlite3_exec() callback is a null pointer. The fourth argument to the sqlite3_exec() callback is an array of Pointers to the field names, where each entry represents the name of the corresponding result column obtained from sqlite3_column_name().

If the second argument to sqlite3_exec() is a null pointer, a pointer to an empty string, or a pointer containing only whitespace and/or SQL annotations, the SQL statement is not evaluated and the database is not changed.

Limitations:

The application must ensure that the first parameter of sqlite3_exec() is a valid and open database connection.

When running sqlite3_exec(), the application cannot close the database connection to sqlite3_exec() specified by the first argument.

When running sqlite3_exec(), the application cannot modify the SQL statement text in the second argument passed to sqlite3_exec().

sqlite3_busy_handler

int sqlite3_busy_handler(sqlite3*,int(*) (void*,int),void*);
Copy the code

The sqlite3_busy_handler (D, X, P) routine sets a callback function X that can be called with the argument P when another thread or process locks a table and attempts to access the database table associated with the database connection D. The sqlite3_busy_handler () interface is used to implement sqlite3_busy_timeout () and PRAGMA busy_timeout.

If the BUSY callback is empty, SQLITE_BUSY is returned immediately when the lock is encountered. If the BUSY callback is not empty, the callback can be invoked with two arguments.

The first argument to the busy handler is a copy of the void* pointer, which is the third argument to sqlite3_busy_handler (). The second argument to the BUSY Handler callback is the number of times the Busy Handler was previously called for the same lock event. If the BUSY callback returns 0, no more attempts are made to access the database and SQLITE_BUSY is returned to the application. If the callback returns non-zero, the database is tried again and the cycle is repeated.

The presence of a busy handler does not guarantee that it will be called when lock contention occurs. If SQLite determines that calling the BUSY handler could cause a deadlock, it will proceed and return SQLITE_BUSY to the application instead of calling the BUSY handler. Consider a scenario where one process holds a read lock that it is trying to upgrade to a reserved lock, and another process holds a reserved lock that it is trying to upgrade to an exclusive lock. The first process cannot continue because it was blocked by the second process; The second process cannot continue because it was blocked by the first process. If both processes call busy handlers, neither process makes any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will cause the first process to release its read lock and allow the second process to continue.

The default callback is empty.

You can define only one busy handler per database connection. Setting a new busy handler will clear any handlers that were previously set. Note that calling sqlite3_busy_timeout () or calculating PRAGMA busy_timeout=N changes the BUSY handler, thus clearing any busy handlers that were previously set.

The BUSY callback should not perform any operations that modify the database connection on which the BUSY handler was invoked. In other words, busy handlers are not reentrant. Any such operation results in undefined behavior.

Busy Handler cannot be turned off by closing the number of database connections or by the PREPARED statement.

sqlite3_wal_checkpoint_v2

int sqlite3_wal_checkpoint_v2(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of attached database (or NULL) */
  int eMode,                      /* SQLITE_CHECKPOINT_* value */
  int *pnLog,                     /* OUT: Size of WAL log in frames */
  int *pnCkpt                     /* OUT: Total number of frames checkpointed */
);
Copy the code

Sqlite3_wal_checkpoint_v2 (D, X, M, L, C) the sqlite3_WAL_CHECKPOINt_v2 (D, X, M, L, C) interface runs a checkpoint operation on database X connected to D in mode M. The state information is written back to the integers pointed to by L and C. The M argument must be a valid checkpoint mode:

  • SQLITE_CHECKPOINT_PASSIVE

    Check as many frames as possible without waiting for any database reader to complete, and synchronize the database file if all frames in the log are selected. The busy handler callback is never called in SQLITE_CHECKPOINT_ passive mode. On the other hand, if there are concurrent readers, the passive mode may leave the checkpoint unfinished.

  • SQLITE_CHECKPOINT_FULL

    This pattern blocks (it calls the Busy Handler callback) until there are no database writers and all card readers are reading the latest database snapshot. It then checks all frames in the log file and synchronizes the database file. This mode prevents new database writes while suspended, but allows new database readers to continue unhindered.

  • SQLITE_CHECKPOINT_RESTART

    This pattern works in the same way as SQLITE_CHECKPOINT_FULL, and in addition, after the log file is examined, it blocks (calling the Busy Handler callback) until all card readers read only from the database file. This will ensure that the next writer restarts the log file from scratch. Similar to SQLITE_CHECKPOINT_FULL, this pattern prevents new database writers from trying while suspended, but does not hinder card readers.

  • SQLITE_CHECKPOINT_TRUNCATE

    This pattern works in the same way as SQLITE_CHECKPOINT_RESTART and also truncates the log file to zero bytes before returning successfully.

The resources

Sqlite C/C ++ API