From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- www/c_interface.html | 1241 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1241 insertions(+) create mode 100644 www/c_interface.html (limited to 'www/c_interface.html') diff --git a/www/c_interface.html b/www/c_interface.html new file mode 100644 index 0000000..91574ae --- /dev/null +++ b/www/c_interface.html @@ -0,0 +1,1241 @@ + + + + + +The C language interface to SQLite Version 2 + + + +
+ + + +
+
+Small. Fast. Reliable.
Choose any three. +
+
+ +
+ +
+
+ + + +
+
+
+ + + +

+

+
+Editorial Note: +This document describes SQLite version 2, which was deprecated and +replaced by SQLite3 in 2004. +This document is retained as part of the historical record of SQLite. +Modern programmers should refer to +more up-to-date documentation on SQLite is available elsewhere +on this website. +
+

The C language interface to SQLite Version 2

+ +

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

+ +

1.0 The Core API

+ +

The interface to the SQLite library consists of three core functions, +one opaque data structure, and some constants used as return values. +The core interface is as follows:

+ +
+typedef struct sqlite sqlite;
+#define SQLITE_OK           0   /* Successful result */
+
+sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);
+
+void sqlite_close(sqlite *db);
+
+int sqlite_exec(
+  sqlite *db,
+  char *sql,
+  int (*xCallback)(void*,int,char**,char**),
+  void *pArg,
+  char **errmsg
+);
+
+ +

+The above is all you really need to know in order to use SQLite +in your C or C++ programs. There are other interface functions +available (and described below) but we will begin by describing +the core functions shown above. +

+ + +

1.1 Opening a database

+ +

Use the sqlite_open function to open an existing SQLite +database or to create a new SQLite database. The first argument +is the database name. The second argument is intended to signal +whether the database is going to be used for reading and writing +or just for reading. But in the current implementation, the +second argument to sqlite_open is ignored. +The third argument is a pointer to a string pointer. +If the third argument is not NULL and an error occurs +while trying to open the database, then an error message will be +written to memory obtained from malloc() and *errmsg will be made +to point to this error message. The calling function is responsible +for freeing the memory when it has finished with it.

+ +

The name of an SQLite database is the name of a file that will +contain the database. If the file does not exist, SQLite attempts +to create and initialize it. If the file is read-only (due to +permission bits or because it is located on read-only media like +a CD-ROM) then SQLite opens the database for reading only. The +entire SQL database is stored in a single file on the disk. But +additional temporary files may be created during the execution of +an SQL command in order to store the database rollback journal or +temporary and intermediate results of a query.

+ +

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

+ + +

1.2 Closing the database

+ +

To close an SQLite database, call the sqlite_close +function passing it the sqlite structure pointer that was obtained +from a prior call to sqlite_open. +If a transaction is active when the database is closed, the transaction +is rolled back.

+ + +

1.3 Executing SQL statements

+ +

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

+ +
    +

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

    2. +

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

    3. +

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

    4. +

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

    5. +

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

    6. +
+ +

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

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

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

+
+argv[i][0] == 0
+
+

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

+
+argv[i] == 0
+
+ +

The names of the columns are contained in first argc +entries of the fourth argument. +If the SHOW_DATATYPES pragma +is on (it is off by default) then +the second argc entries in the 4th argument are the datatypes +for the corresponding columns. +

+ +

If the +EMPTY_RESULT_CALLBACKS pragma is set to ON and the result of +a query is an empty set, then the callback is invoked once with the +third parameter (argv) set to 0. In other words +

+argv == 0
+
+The second parameter (argc) +and the fourth parameter (columnNames) are still valid +and can be used to determine the number and names of the result +columns if there had been a result. +The default behavior is not to invoke the callback at all if the +result set is empty.

+ + +

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

+ +

1.4 Error Codes

+ +

+The sqlite_exec function normally returns SQLITE_OK. But +if something goes wrong it can return a different value to indicate +the type of error. Here is a complete list of the return codes: +

+ +
+#define SQLITE_OK           0   /* Successful result */
+#define SQLITE_ERROR        1   /* SQL error or missing database */
+#define SQLITE_INTERNAL     2   /* An internal logic error in SQLite */
+#define SQLITE_PERM         3   /* Access permission denied */
+#define SQLITE_ABORT        4   /* Callback routine requested an abort */
+#define SQLITE_BUSY         5   /* The database file is locked */
+#define SQLITE_LOCKED       6   /* A table in the database is locked */
+#define SQLITE_NOMEM        7   /* A malloc() failed */
+#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
+#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */
+#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
+#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
+#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
+#define SQLITE_FULL        13   /* Insertion failed because database is full */
+#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
+#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
+#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
+#define SQLITE_SCHEMA      17   /* The database schema changed */
+#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
+#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
+#define SQLITE_MISMATCH    20   /* Data type mismatch */
+#define SQLITE_MISUSE      21   /* Library used incorrectly */
+#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
+#define SQLITE_AUTH        23   /* Authorization denied */
+#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
+#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
+
+ +

+The meanings of these various return values are as follows: +

+ +
+
+
SQLITE_OK
+

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

+
SQLITE_INTERNAL
+

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

+
SQLITE_ERROR
+

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

+
SQLITE_PERM
+

This return value says that the access permissions on the database +file are such that the file cannot be opened. +

+
SQLITE_ABORT
+

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

+
SQLITE_BUSY
+

This return code indicates that another program or thread has +the database locked. SQLite allows two or more threads to read the +database at the same time, but only one thread can have the database +open for writing at the same time. Locking in SQLite is on the +entire database.

+

+
SQLITE_LOCKED
+

This return code is similar to SQLITE_BUSY in that it indicates +that the database is locked. But the source of the lock is a recursive +call to sqlite_exec. This return can only occur if you attempt +to invoke sqlite_exec from within a callback routine of a query +from a prior invocation of sqlite_exec. Recursive calls to +sqlite_exec are allowed as long as they do +not attempt to write the same table. +

+
SQLITE_NOMEM
+

This value is returned if a call to malloc fails. +

+
SQLITE_READONLY
+

This return code indicates that an attempt was made to write to +a database file that is opened for reading only. +

+
SQLITE_INTERRUPT
+

This value is returned if a call to sqlite_interrupt +interrupts a database operation in progress. +

+
SQLITE_IOERR
+

This value is returned if the operating system informs SQLite +that it is unable to perform some disk I/O operation. This could mean +that there is no more space left on the disk. +

+
SQLITE_CORRUPT
+

This value is returned if SQLite detects that the database it is +working on has become corrupted. Corruption might occur due to a rogue +process writing to the database file or it might happen due to a +previously undetected logic error in of SQLite. This value is also +returned if a disk I/O error occurs in such a way that SQLite is forced +to leave the database file in a corrupted state. The latter should only +happen due to a hardware or operating system malfunction. +

+
SQLITE_FULL
+

This value is returned if an insertion failed because there is +no space left on the disk, or the database is too big to hold any +more information. The latter case should only occur for databases +that are larger than 2GB in size. +

+
SQLITE_CANTOPEN
+

This value is returned if the database file could not be opened +for some reason. +

+
SQLITE_PROTOCOL
+

This value is returned if some other process is messing with +file locks and has violated the file locking protocol that SQLite uses +on its rollback journal files. +

+
SQLITE_SCHEMA
+

When the database first opened, SQLite reads the database schema +into memory and uses that schema to parse new SQL statements. If another +process changes the schema, the command currently being processed will +abort because the virtual machine code generated assumed the old +schema. This is the return code for such cases. Retrying the +command usually will clear the problem. +

+
SQLITE_TOOBIG
+

SQLite will not store more than about 1 megabyte of data in a single +row of a single table. If you attempt to store more than 1 megabyte +in a single row, this is the return code you get. +

+
SQLITE_CONSTRAINT
+

This constant is returned if the SQL statement would have violated +a database constraint. +

+
SQLITE_MISMATCH
+

This error occurs when there is an attempt to insert non-integer +data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite +ignores the data type and allows any kind of data to be stored. But +an INTEGER PRIMARY KEY column is only allowed to store integer data. +

+
SQLITE_MISUSE
+

This error might occur if one or more of the SQLite API routines +is used incorrectly. Examples of incorrect usage include calling +sqlite_exec after the database has been closed using +sqlite_close or +calling sqlite_exec with the same +database pointer simultaneously from two separate threads. +

+
SQLITE_NOLFS
+

This error means that you have attempts to create or access a file +database file that is larger that 2GB on a legacy Unix machine that +lacks large file support. +

+
SQLITE_AUTH
+

This error indicates that the authorizer callback +has disallowed the SQL you are attempting to execute. +

+
SQLITE_ROW
+

This is one of the return codes from the +sqlite_step routine which is part of the non-callback API. +It indicates that another row of result data is available. +

+
SQLITE_DONE
+

This is one of the return codes from the +sqlite_step routine which is part of the non-callback API. +It indicates that the SQL statement has been completely executed and +the sqlite_finalize routine is ready to be called. +

+
+
+ +

2.0 Accessing Data Without Using A Callback Function

+ +

+The sqlite_exec routine described above used to be the only +way to retrieve data from an SQLite database. But many programmers found +it inconvenient to use a callback function to obtain results. So beginning +with SQLite version 2.7.7, a second access interface is available that +does not use callbacks. +

+ +

+The new interface uses three separate functions to replace the single +sqlite_exec function. +

+ +
+typedef struct sqlite_vm sqlite_vm;
+
+int sqlite_compile(
+  sqlite *db,              /* The open database */
+  const char *zSql,        /* SQL statement to be compiled */
+  const char **pzTail,     /* OUT: uncompiled tail of zSql */
+  sqlite_vm **ppVm,        /* OUT: the virtual machine to execute zSql */
+  char **pzErrmsg          /* OUT: Error message. */
+);
+
+int sqlite_step(
+  sqlite_vm *pVm,          /* The virtual machine to execute */
+  int *pN,                 /* OUT: Number of columns in result */
+  const char ***pazValue,  /* OUT: Column data */
+  const char ***pazColName /* OUT: Column names and datatypes */
+);
+
+int sqlite_finalize(
+  sqlite_vm *pVm,          /* The virtual machine to be finalized */
+  char **pzErrMsg          /* OUT: Error message */
+);
+
+ +

+The strategy is to compile a single SQL statement using +sqlite_compile then invoke sqlite_step multiple times, +once for each row of output, and finally call sqlite_finalize +to clean up after the SQL has finished execution. +

+ +

2.1 Compiling An SQL Statement Into A Virtual Machine

+ +

+The sqlite_compile "compiles" a single SQL statement (specified +by the second parameter) and generates a virtual machine that is able +to execute that statement. +As with must interface routines, the first parameter must be a pointer +to an sqlite structure that was obtained from a prior call to +sqlite_open. + +

+A pointer to the virtual machine is stored in a pointer which is passed +in as the 4th parameter. +Space to hold the virtual machine is dynamically allocated. To avoid +a memory leak, the calling function must invoke +sqlite_finalize on the virtual machine after it has finished +with it. +The 4th parameter may be set to NULL if an error is encountered during +compilation. +

+ +

+If any errors are encountered during compilation, an error message is +written into memory obtained from malloc and the 5th parameter +is made to point to that memory. If the 5th parameter is NULL, then +no error message is generated. If the 5th parameter is not NULL, then +the calling function should dispose of the memory containing the error +message by calling sqlite_freemem. +

+ +

+If the 2nd parameter actually contains two or more statements of SQL, +only the first statement is compiled. (This is different from the +behavior of sqlite_exec which executes all SQL statements +in its input string.) The 3rd parameter to sqlite_compile +is made to point to the first character beyond the end of the first +statement of SQL in the input. If the 2nd parameter contains only +a single SQL statement, then the 3rd parameter will be made to point +to the '\000' terminator at the end of the 2nd parameter. +

+ +

+On success, sqlite_compile returns SQLITE_OK. +Otherwise and error code is returned. +

+ +

2.2 Step-By-Step Execution Of An SQL Statement

+ +

+After a virtual machine has been generated using sqlite_compile +it is executed by one or more calls to sqlite_step. Each +invocation of sqlite_step, except the last one, +returns a single row of the result. +The number of columns in the result is stored in the integer that +the 2nd parameter points to. +The pointer specified by the 3rd parameter is made to point +to an array of pointers to column values. +The pointer in the 4th parameter is made to point to an array +of pointers to column names and datatypes. +The 2nd through 4th parameters to sqlite_step convey the +same information as the 2nd through 4th parameters of the +callback routine when using +the sqlite_exec interface. Except, with sqlite_step +the column datatype information is always included in the in the +4th parameter regardless of whether or not the +SHOW_DATATYPES pragma +is on or off. +

+ +

+Each invocation of sqlite_step returns an integer code that +indicates what happened during that step. This code may be +SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or +SQLITE_MISUSE. +

+ +

+If the virtual machine is unable to open the database file because +it is locked by another thread or process, sqlite_step +will return SQLITE_BUSY. The calling function should do some other +activity, or sleep, for a short amount of time to give the lock a +chance to clear, then invoke sqlite_step again. This can +be repeated as many times as desired. +

+ +

+Whenever another row of result data is available, +sqlite_step will return SQLITE_ROW. The row data is +stored in an array of pointers to strings and the 2nd parameter +is made to point to this array. +

+ +

+When all processing is complete, sqlite_step will return +either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the +statement completed successfully and SQLITE_ERROR indicates that there +was a run-time error. (The details of the error are obtained from +sqlite_finalize.) It is a misuse of the library to attempt +to call sqlite_step again after it has returned SQLITE_DONE +or SQLITE_ERROR. +

+ +

+When sqlite_step returns SQLITE_DONE or SQLITE_ERROR, +the *pN and *pazColName values are set to the number of columns +in the result set and to the names of the columns, just as they +are for an SQLITE_ROW return. This allows the calling code to +find the number of result columns and the column names and datatypes +even if the result set is empty. The *pazValue parameter is always +set to NULL when the return codes is SQLITE_DONE or SQLITE_ERROR. +If the SQL being executed is a statement that does not +return a result (such as an INSERT or an UPDATE) then *pN will +be set to zero and *pazColName will be set to NULL. +

+ +

+If you abuse the library by trying to call sqlite_step +inappropriately it will attempt return SQLITE_MISUSE. +This can happen if you call sqlite_step() on the same virtual machine +at the same +time from two or more threads or if you call sqlite_step() +again after it returned SQLITE_DONE or SQLITE_ERROR or if you +pass in an invalid virtual machine pointer to sqlite_step(). +You should not depend on the SQLITE_MISUSE return code to indicate +an error. It is possible that a misuse of the interface will go +undetected and result in a program crash. The SQLITE_MISUSE is +intended as a debugging aid only - to help you detect incorrect +usage prior to a mishap. The misuse detection logic is not guaranteed +to work in every case. +

+ +

2.3 Deleting A Virtual Machine

+ +

+Every virtual machine that sqlite_compile creates should +eventually be handed to sqlite_finalize. The sqlite_finalize() +procedure deallocates the memory and other resources that the virtual +machine uses. Failure to call sqlite_finalize() will result in +resource leaks in your program. +

+ +

+The sqlite_finalize routine also returns the result code +that indicates success or failure of the SQL operation that the +virtual machine carried out. +The value returned by sqlite_finalize() will be the same as would +have been returned had the same SQL been executed by sqlite_exec. +The error message returned will also be the same. +

+ +

+It is acceptable to call sqlite_finalize on a virtual machine +before sqlite_step has returned SQLITE_DONE. Doing so has +the effect of interrupting the operation in progress. Partially completed +changes will be rolled back and the database will be restored to its +original state (unless an alternative recovery algorithm is selected using +an ON CONFLICT clause in the SQL being executed.) The effect is the +same as if a callback function of sqlite_exec had returned +non-zero. +

+ +

+It is also acceptable to call sqlite_finalize on a virtual machine +that has never been passed to sqlite_step even once. +

+ +

3.0 The Extended API

+ +

Only the three core routines described in section 1.0 are required to use +SQLite. But there are many other functions that provide +useful interfaces. These extended routines are as follows: +

+ +
+int sqlite_last_insert_rowid(sqlite*);
+
+int sqlite_changes(sqlite*);
+
+int sqlite_get_table(
+  sqlite*,
+  char *sql,
+  char ***result,
+  int *nrow,
+  int *ncolumn,
+  char **errmsg
+);
+
+void sqlite_free_table(char**);
+
+void sqlite_interrupt(sqlite*);
+
+int sqlite_complete(const char *sql);
+
+void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);
+
+void sqlite_busy_timeout(sqlite*, int ms);
+
+const char sqlite_version[];
+
+const char sqlite_encoding[];
+
+int sqlite_exec_printf(
+  sqlite*,
+  char *sql,
+  int (*)(void*,int,char**,char**),
+  void*,
+  char **errmsg,
+  ...
+);
+
+int sqlite_exec_vprintf(
+  sqlite*,
+  char *sql,
+  int (*)(void*,int,char**,char**),
+  void*,
+  char **errmsg,
+  va_list
+);
+
+int sqlite_get_table_printf(
+  sqlite*,
+  char *sql,
+  char ***result,
+  int *nrow,
+  int *ncolumn,
+  char **errmsg,
+  ...
+);
+
+int sqlite_get_table_vprintf(
+  sqlite*,
+  char *sql,
+  char ***result,
+  int *nrow,
+  int *ncolumn,
+  char **errmsg,
+  va_list
+);
+
+char *sqlite_mprintf(const char *zFormat, ...);
+
+char *sqlite_vmprintf(const char *zFormat, va_list);
+
+void sqlite_freemem(char*);
+
+void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);
+
+
+ +

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

+ +

3.1 The ROWID of the most recent insert

+ +

Every row of an SQLite table has a unique integer key. If the +table has a column labeled INTEGER PRIMARY KEY, then that column +serves as the key. If there is no INTEGER PRIMARY KEY column then +the key is a unique integer. The key for a row can be accessed in +a SELECT statement or used in a WHERE or ORDER BY clause using any +of the names "ROWID", "OID", or "_ROWID_".

+ +

When you do an insert into a table that does not have an INTEGER PRIMARY +KEY column, or if the table does have an INTEGER PRIMARY KEY but the value +for that column is not specified in the VALUES clause of the insert, then +the key is automatically generated. You can find the value of the key +for the most recent INSERT statement using the +sqlite_last_insert_rowid API function.

+ +

3.2 The number of rows that changed

+ +

The sqlite_changes API function returns the number of rows +that have been inserted, deleted, or modified since the database was +last quiescent. A "quiescent" database is one in which there are +no outstanding calls to sqlite_exec and no VMs created by +sqlite_compile that have not been finalized by sqlite_finalize. +In common usage, sqlite_changes returns the number +of rows inserted, deleted, or modified by the most recent sqlite_exec +call or since the most recent sqlite_compile. But if you have +nested calls to sqlite_exec (that is, if the callback routine +of one sqlite_exec invokes another sqlite_exec) or if +you invoke sqlite_compile to create a new VM while there is +still another VM in existence, then +the meaning of the number returned by sqlite_changes is more +complex. +The number reported includes any changes +that were later undone by a ROLLBACK or ABORT. But rows that are +deleted because of a DROP TABLE are not counted.

+ +

SQLite implements the command "DELETE FROM table" (without +a WHERE clause) by dropping the table then recreating it. +This is much faster than deleting the elements of the table individually. +But it also means that the value returned from sqlite_changes +will be zero regardless of the number of elements that were originally +in the table. If an accurate count of the number of elements deleted +is necessary, use "DELETE FROM table WHERE 1" instead.

+ +

3.3 Querying into memory obtained from malloc()

+ +

The sqlite_get_table function is a wrapper around +sqlite_exec that collects all the information from successive +callbacks and writes it into memory obtained from malloc(). This +is a convenience function that allows the application to get the +entire result of a database query with a single function call.

+ +

The main result from sqlite_get_table is an array of pointers +to strings. There is one element in this array for each column of +each row in the result. NULL results are represented by a NULL +pointer. In addition to the regular data, there is an added row at the +beginning of the array that contains the name of each column of the +result.

+ +

As an example, consider the following query:

+ +
+SELECT employee_name, login, host FROM users WHERE login LIKE 'd%'; +
+ +

This query will return the name, login and host computer name +for every employee whose login begins with the letter "d". If this +query is submitted to sqlite_get_table the result might +look like this:

+ +
+nrow = 2
+ncolumn = 3
+result[0] = "employee_name"
+result[1] = "login"
+result[2] = "host"
+result[3] = "dummy"
+result[4] = "No such user"
+result[5] = 0
+result[6] = "D. Richard Hipp"
+result[7] = "drh"
+result[8] = "zadok" +
+ +

Notice that the "host" value for the "dummy" record is NULL so +the result[] array contains a NULL pointer at that slot.

+ +

If the result set of a query is empty, then by default +sqlite_get_table will set nrow to 0 and leave its +result parameter is set to NULL. But if the EMPTY_RESULT_CALLBACKS +pragma is ON then the result parameter is initialized to the names +of the columns only. For example, consider this query which has +an empty result set:

+ +
+SELECT employee_name, login, host FROM users WHERE employee_name IS NULL; +
+ +

+The default behavior gives this results: +

+ +
+nrow = 0
+ncolumn = 0
+result = 0
+
+ +

+But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following +is returned: +

+ +
+nrow = 0
+ncolumn = 3
+result[0] = "employee_name"
+result[1] = "login"
+result[2] = "host"
+
+ +

Memory to hold the information returned by sqlite_get_table +is obtained from malloc(). But the calling function should not try +to free this information directly. Instead, pass the complete table +to sqlite_free_table when the table is no longer needed. +It is safe to call sqlite_free_table with a NULL pointer such +as would be returned if the result set is empty.

+ +

The sqlite_get_table routine returns the same integer +result code as sqlite_exec.

+ +

3.4 Interrupting an SQLite operation

+ +

The sqlite_interrupt function can be called from a +different thread or from a signal handler to cause the current database +operation to exit at its first opportunity. When this happens, +the sqlite_exec routine (or the equivalent) that started +the database operation will return SQLITE_INTERRUPT.

+ +

3.5 Testing for a complete SQL statement

+ +

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

+ +

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

+ +

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

+ +

3.6 Library version string

+ +

The SQLite library exports the string constant named +sqlite_version which contains the version number of the +library. The header file contains a macro SQLITE_VERSION +with the same information. If desired, a program can compare +the SQLITE_VERSION macro against the sqlite_version +string constant to verify that the version number of the +header file and the library match.

+ +

3.7 Library character encoding

+ +

By default, SQLite assumes that all data uses a fixed-size +8-bit character (iso8859). But if you give the --enable-utf8 option +to the configure script, then the library assumes UTF-8 variable +sized characters. This makes a difference for the LIKE and GLOB +operators and the LENGTH() and SUBSTR() functions. The static +string sqlite_encoding will be set to either "UTF-8" or +"iso8859" to indicate how the library was compiled. In addition, +the sqlite.h header file will define one of the +macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.

+ +

Note that the character encoding mechanism used by SQLite cannot +be changed at run-time. This is a compile-time option only. The +sqlite_encoding character string just tells you how the library +was compiled.

+ +

3.8 Changing the library's response to locked files

+ +

The sqlite_busy_handler procedure can be used to register +a busy callback with an open SQLite database. The busy callback will +be invoked whenever SQLite tries to access a database that is locked. +The callback will typically do some other useful work, or perhaps sleep, +in order to give the lock a chance to clear. If the callback returns +non-zero, then SQLite tries again to access the database and the cycle +repeats. If the callback returns zero, then SQLite aborts the current +operation and returns SQLITE_BUSY.

+ +

The arguments to sqlite_busy_handler are the opaque +structure returned from sqlite_open, a pointer to the busy +callback function, and a generic pointer that will be passed as +the first argument to the busy callback. When SQLite invokes the +busy callback, it sends it three arguments: the generic pointer +that was passed in as the third argument to sqlite_busy_handler, +the name of the database table or index that the library is trying +to access, and the number of times that the library has attempted to +access the database table or index.

+ +

For the common case where we want the busy callback to sleep, +the SQLite library provides a convenience routine sqlite_busy_timeout. +The first argument to sqlite_busy_timeout is a pointer to +an open SQLite database and the second argument is a number of milliseconds. +After sqlite_busy_timeout has been executed, the SQLite library +will wait for the lock to clear for at least the number of milliseconds +specified before it returns SQLITE_BUSY. Specifying zero milliseconds for +the timeout restores the default behavior.

+ +

3.9 Using the _printf() wrapper functions

+ +

The four utility functions

+ +

+

+

+ +

implement the same query functionality as sqlite_exec +and sqlite_get_table. But instead of taking a complete +SQL statement as their second argument, the four _printf +routines take a printf-style format string. The SQL statement to +be executed is generated from this format string and from whatever +additional arguments are attached to the end of the function call.

+ +

There are two advantages to using the SQLite printf +functions instead of sprintf. First of all, with the +SQLite printf routines, there is never a danger of overflowing a +static buffer as there is with sprintf. The SQLite +printf routines automatically allocate (and later frees) +as much memory as is +necessary to hold the SQL statements generated.

+ +

The second advantage the SQLite printf routines have over +sprintf are two new formatting options specifically designed +to support string literals in SQL. Within the format string, +the %q formatting option works very much like %s in that it +reads a null-terminated string from the argument list and inserts +it into the result. But %q translates the inserted string by +making two copies of every single-quote (') character in the +substituted string. This has the effect of escaping the end-of-string +meaning of single-quote within a string literal. The %Q formatting +option works similar; it translates the single-quotes like %q and +additionally encloses the resulting string in single-quotes. +If the argument for the %Q formatting options is a NULL pointer, +the resulting string is NULL without single quotes. +

+ +

Consider an example. Suppose you are trying to insert a string +value into a database table where the string value was obtained from +user input. Suppose the string to be inserted is stored in a variable +named zString. The code to do the insertion might look like this:

+ +
+sqlite_exec_printf(db,
+  "INSERT INTO table1 VALUES('%s')",
+  0, 0, 0, zString);
+
+ +

If the zString variable holds text like "Hello", then this statement +will work just fine. But suppose the user enters a string like +"Hi y'all!". The SQL statement generated reads as follows: + +

+INSERT INTO table1 VALUES('Hi y'all')
+
+ +

This is not valid SQL because of the apostrophe in the word "y'all". +But if the %q formatting option is used instead of %s, like this:

+ +
+sqlite_exec_printf(db,
+  "INSERT INTO table1 VALUES('%q')",
+  0, 0, 0, zString);
+
+ +

Then the generated SQL will look like the following:

+ +
+INSERT INTO table1 VALUES('Hi y''all')
+
+ +

Here the apostrophe has been escaped and the SQL statement is well-formed. +When generating SQL on-the-fly from data that might contain a +single-quote character ('), it is always a good idea to use the +SQLite printf routines and the %q formatting option instead of sprintf. +

+ +

If the %Q formatting option is used instead of %q, like this:

+ +
+sqlite_exec_printf(db,
+  "INSERT INTO table1 VALUES(%Q)",
+  0, 0, 0, zString);
+
+ +

Then the generated SQL will look like the following:

+ +
+INSERT INTO table1 VALUES('Hi y''all')
+
+ +

If the value of the zString variable is NULL, the generated SQL +will look like the following:

+ +
+INSERT INTO table1 VALUES(NULL)
+
+ +

All of the _printf() routines above are built around the following +two functions:

+ +
+char *sqlite_mprintf(const char *zFormat, ...);
+char *sqlite_vmprintf(const char *zFormat, va_list);
+
+ +

The sqlite_mprintf() routine works like the standard library +sprintf() except that it writes its results into memory obtained +from malloc() and returns a pointer to the malloced buffer. +sqlite_mprintf() also understands the %q and %Q extensions described +above. The sqlite_vmprintf() is a varargs version of the same +routine. The string pointer that these routines return should be freed +by passing it to sqlite_freemem(). +

+ +

3.10 Performing background jobs during large queries

+ +

The sqlite_progress_handler() routine can be used to register a +callback routine with an SQLite database to be invoked periodically during long +running calls to sqlite_exec(), sqlite_step() and the various +wrapper functions. +

+ +

The callback is invoked every N virtual machine operations, where N is +supplied as the second argument to sqlite_progress_handler(). The third +and fourth arguments to sqlite_progress_handler() are a pointer to the +routine to be invoked and a void pointer to be passed as the first argument to +it. +

+ +

The time taken to execute each virtual machine operation can vary based on +many factors. A typical value for a 1 GHz PC is between half and three million +per second but may be much higher or lower, depending on the query. As such it +is difficult to schedule background operations based on virtual machine +operations. Instead, it is recommended that a callback be scheduled relatively +frequently (say every 1000 instructions) and external timer routines used to +determine whether or not background jobs need to be run. +

+ + +

4.0 Adding New SQL Functions

+ +

Beginning with version 2.4.0, SQLite allows the SQL language to be +extended with new functions implemented as C code. The following interface +is used: +

+ +
+typedef struct sqlite_func sqlite_func;
+
+int sqlite_create_function(
+  sqlite *db,
+  const char *zName,
+  int nArg,
+  void (*xFunc)(sqlite_func*,int,const char**),
+  void *pUserData
+);
+int sqlite_create_aggregate(
+  sqlite *db,
+  const char *zName,
+  int nArg,
+  void (*xStep)(sqlite_func*,int,const char**),
+  void (*xFinalize)(sqlite_func*),
+  void *pUserData
+);
+
+char *sqlite_set_result_string(sqlite_func*,const char*,int);
+void sqlite_set_result_int(sqlite_func*,int);
+void sqlite_set_result_double(sqlite_func*,double);
+void sqlite_set_result_error(sqlite_func*,const char*,int);
+
+void *sqlite_user_data(sqlite_func*);
+void *sqlite_aggregate_context(sqlite_func*, int nBytes);
+int sqlite_aggregate_count(sqlite_func*);
+
+ +

+The sqlite_create_function() interface is used to create +regular functions and sqlite_create_aggregate() is used to +create new aggregate functions. In both cases, the db +parameter is an open SQLite database on which the functions should +be registered, zName is the name of the new function, +nArg is the number of arguments, and pUserData is +a pointer which is passed through unchanged to the C implementation +of the function. Both routines return 0 on success and non-zero +if there are any errors. +

+ +

+The length of a function name may not exceed 255 characters. +Any attempt to create a function whose name exceeds 255 characters +in length will result in an error. +

+ +

+For regular functions, the xFunc callback is invoked once +for each function call. The implementation of xFunc should call +one of the sqlite_set_result_... interfaces to return its +result. The sqlite_user_data() routine can be used to +retrieve the pUserData pointer that was passed in when the +function was registered. +

+ +

+For aggregate functions, the xStep callback is invoked once +for each row in the result and then xFinalize is invoked at the +end to compute a final answer. The xStep routine can use the +sqlite_aggregate_context() interface to allocate memory that +will be unique to that particular instance of the SQL function. +This memory will be automatically deleted after xFinalize is called. +The sqlite_aggregate_count() routine can be used to find out +how many rows of data were passed to the aggregate. The xFinalize +callback should invoke one of the sqlite_set_result_... +interfaces to set the final result of the aggregate. +

+ +

+SQLite now implements all of its built-in functions using this +interface. For additional information and examples on how to create +new SQL functions, review the SQLite source code in the file +func.c. +

+ +

5.0 Multi-Threading And SQLite

+ +

+If SQLite is compiled with the THREADSAFE preprocessor macro set to 1, +then it is safe to use SQLite from two or more threads of the same process +at the same time. But each thread should have its own sqlite* +pointer returned from sqlite_open. It is never safe for two +or more threads to access the same sqlite* pointer at the same time. +

+ +

+In precompiled SQLite libraries available on the website, the Unix +versions are compiled with THREADSAFE turned off but the Windows +versions are compiled with THREADSAFE turned on. If you need something +different that this you will have to recompile. +

+ +

+Under Unix, an sqlite* pointer should not be carried across a +fork() system call into the child process. The child process +should open its own copy of the database after the fork(). +

+ +

6.0 Usage Examples

+ +

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

+

This page last modified on 2023-01-06 00:45:39 UTC

+ -- cgit v1.2.3