2 # Run this Tcl script to generate the sqlite.html file.
4 set rcsid
{$Id: c_interface.tcl
,v
1.43 2004/11/19 11:59:24 danielk1977 Exp
$}
6 header
{The C language interface to the SQLite library
}
8 <h2
>The C language interface to the SQLite library
</h2
>
10 <p
>The SQLite library is designed to be very easy to use from
11 a C or C
++ program. This document gives an overview of the C
/C
++
12 programming interface.
</p
>
14 <h3
>1.0 The Core API
</h3
>
16 <p
>The interface to the SQLite library consists of three core functions
,
17 one opaque data structure
, and some constants used as
return values.
18 The core interface is as follows
:</p
>
21 typedef struct sqlite sqlite
;
22 #define SQLITE_OK 0 /* Successful result */
24 sqlite
*sqlite_open
(const char
*dbname
, int mode
, char
**errmsg
);
26 void sqlite_close
(sqlite
*db
);
31 int
(*xCallback
)(void
*,int
,char
**,char
**),
38 The above is all you really need to know in order to use SQLite
39 in your C or C
++ programs. There are other interface functions
40 available
(and described below
) but we will begin by describing
41 the core functions shown above.
44 <a name
="sqlite_open">
45 <h4
>1.1 Opening a database
</h4
>
47 <p
>Use the
<b
>sqlite_open
</b
> function to
open an existing SQLite
48 database or to create a new SQLite database. The first argument
49 is the database name. The second argument is intended to signal
50 whether the database is going to be used
for reading and writing
51 or just
for reading. But in the current implementation
, the
52 second argument to
<b
>sqlite_open
</b
> is ignored.
53 The third argument is a pointer to a
string pointer.
54 If the third argument is not NULL and an
error occurs
55 while trying to
open the database
, then an
error message will be
56 written to
memory obtained from malloc
() and
*errmsg will be made
57 to point to this
error message. The calling function is responsible
58 for freeing the
memory when it has finished with it.
</p
>
60 <p
>The name of an SQLite database is the name of a
file that will
61 contain the database. If the
file does not exist
, SQLite attempts
62 to create and initialize it. If the
file is read-only
(due to
63 permission bits or because it is located on read-only media like
64 a CD-ROM
) then SQLite opens the database
for reading only. The
65 entire SQL database is stored in a single
file on the disk. But
66 additional temporary files may be created during the execution of
67 an SQL command in order to store the database rollback journal or
68 temporary and intermediate results of a query.
</p
>
70 <p
>The
return value of the
<b
>sqlite_open
</b
> function is a
71 pointer to an opaque
<b
>sqlite
</b
> structure. This pointer will
72 be the first argument to all subsequent SQLite function calls that
73 deal with the same database. NULL is returned
if the
open fails
76 <a name
="sqlite_close">
77 <h4
>1.2 Closing the database
</h4
>
79 <p
>To
close an SQLite database
, call the
<b
>sqlite_close
</b
>
80 function passing it the sqlite structure pointer that was obtained
81 from a prior call to
<b
>sqlite_open
</b
>.
82 If a transaction is active when the database is closed
, the transaction
85 <a name
="sqlite_exec">
86 <h4
>1.3 Executing SQL statements
</h4
>
88 <p
>The
<b
>sqlite_exec
</b
> function is used to process SQL statements
89 and queries. This function requires
5 parameters as follows
:</p
>
92 <li
><p
>A pointer to the sqlite structure obtained from a prior call
93 to
<b
>sqlite_open
</b
>.
</p
></li
>
94 <li
><p
>A null-terminated
string containing the
text of one or more
95 SQL statements and
/or queries to be processed.
</p
></li
>
96 <li
><p
>A pointer to a callback function which is invoked once
for each
97 row in the result of a query. This argument may be NULL
, in which
98 case no callbacks will ever be invoked.
</p
></li
>
99 <li
><p
>A pointer that is forwarded to become the first argument
100 to the callback function.
</p
></li
>
101 <li
><p
>A pointer to an
error string. Error messages are written to space
102 obtained from malloc
() and the
error string is made to point to
103 the malloced space. The calling function is responsible
for freeing
104 this space when it has finished with it.
105 This argument may be NULL
, in which case
error messages are not
106 reported back to the calling function.
</p
></li
>
110 The callback function is used to receive the results of a query. A
111 prototype
for the callback function is as follows
:</p
>
114 int Callback
(void
*pArg
, int argc
, char
**argv
, char
**columnNames
){
119 <a name
="callback_row_data">
120 <p
>The first argument to the callback is just a copy of the fourth argument
121 to
<b
>sqlite_exec
</b
> This parameter can be used to pass arbitrary
122 information through to the callback function from client code.
123 The second argument is the number of columns in the query result.
124 The third argument is an
array of pointers to strings where each
string
125 is a single column of the result
for that record. Note that the
126 callback function reports a NULL value in the database as a NULL pointer
,
127 which is very different from an empty
string. If the i-th parameter
128 is an empty
string, we will get
:</p
>
132 <p
>But
if the i-th parameter is NULL we will get
:</p
>
137 <p
>The names of the columns are contained in first
<i
>argc
</i
>
138 entries of the fourth argument.
139 If the
<a href
="pragma.html#pragma_show_datatypes">SHOW_DATATYPES
</a
> pragma
140 is on
(it is off by
default) then
141 the second
<i
>argc
</i
> entries in the
4th argument are the datatypes
142 for the corresponding columns.
145 <p
>If the
<a href
="pragma.html#pragma_empty_result_callbacks">
146 EMPTY_RESULT_CALLBACKS
</a
> pragma is
set to ON and the result of
147 a query is an empty
set, then the callback is invoked once with the
148 third parameter
(argv
) set to
0. In other words
152 The second parameter
(argc
)
153 and the fourth parameter
(columnNames
) are still valid
154 and can be used to determine the number and names of the result
155 columns
if there had been a result.
156 The
default behavior is not to invoke the callback at all
if the
157 result
set is empty.
</p
>
159 <a name
="callback_returns_nonzero">
160 <p
>The callback function should normally
return 0. If the callback
161 function returns non-zero
, the query is immediately aborted and
162 <b
>sqlite_exec
</b
> will
return SQLITE_ABORT.
</p
>
164 <h4
>1.4 Error Codes
</h4
>
167 The
<b
>sqlite_exec
</b
> function normally returns SQLITE_OK. But
168 if something goes wrong it can
return a different value to indicate
169 the type of
error. Here is a complete
list of the
return codes
:
173 #define SQLITE_OK 0 /* Successful result */
174 #define SQLITE_ERROR 1 /* SQL error or missing database */
175 #define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */
176 #define SQLITE_PERM 3 /* Access permission denied */
177 #define SQLITE_ABORT 4 /* Callback routine requested an abort */
178 #define SQLITE_BUSY 5 /* The database file is locked */
179 #define SQLITE_LOCKED 6 /* A table in the database is locked */
180 #define SQLITE_NOMEM 7 /* A malloc() failed */
181 #define SQLITE_READONLY 8 /* Attempt to write a readonly database */
182 #define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */
183 #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
184 #define SQLITE_CORRUPT 11 /* The database disk image is malformed */
185 #define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */
186 #define SQLITE_FULL 13 /* Insertion failed because database is full */
187 #define SQLITE_CANTOPEN 14 /* Unable to open the database file */
188 #define SQLITE_PROTOCOL 15 /* Database lock protocol error */
189 #define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */
190 #define SQLITE_SCHEMA 17 /* The database schema changed */
191 #define SQLITE_TOOBIG 18 /* Too much data for one row of a table */
192 #define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */
193 #define SQLITE_MISMATCH 20 /* Data type mismatch */
194 #define SQLITE_MISUSE 21 /* Library used incorrectly */
195 #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
196 #define SQLITE_AUTH 23 /* Authorization denied */
197 #define SQLITE_ROW 100 /* sqlite_step() has another row ready */
198 #define SQLITE_DONE 101 /* sqlite_step() has finished executing */
202 The meanings of these various
return values are as follows
:
208 <dd
><p
>This value is returned
if everything worked and there were no errors.
210 <dt
>SQLITE_INTERNAL
</dt
>
211 <dd
><p
>This value indicates that an internal consistency check within
212 the SQLite library failed. This can only happen
if there is a bug in
213 the SQLite library. If you ever get an SQLITE_INTERNAL reply from
214 an
<b
>sqlite_exec
</b
> call
, please report the problem on the SQLite
217 <dt
>SQLITE_ERROR
</dt
>
218 <dd
><p
>This
return value indicates that there was an
error in the SQL
219 that was passed into the
<b
>sqlite_exec
</b
>.
222 <dd
><p
>This
return value says that the access permissions on the database
223 file are such that the
file cannot be opened.
225 <dt
>SQLITE_ABORT
</dt
>
226 <dd
><p
>This value is returned
if the callback function returns non-zero.
229 <dd
><p
>This
return code indicates that another program or thread has
230 the database locked. SQLite allows two or more threads to
read the
231 database at the same
time, but only one thread can have the database
232 open for writing at the same
time. Locking in SQLite is on the
235 <dt
>SQLITE_LOCKED
</dt
>
236 <dd
><p
>This
return code is similar to SQLITE_BUSY in that it indicates
237 that the database is locked. But the
source of the lock is a recursive
238 call to
<b
>sqlite_exec
</b
>. This
return can only occur
if you attempt
239 to invoke sqlite_exec from within a callback routine of a query
240 from a prior invocation of sqlite_exec. Recursive calls to
241 sqlite_exec are allowed as long as they do
242 not attempt to write the same table.
244 <dt
>SQLITE_NOMEM
</dt
>
245 <dd
><p
>This value is returned
if a call to
<b
>malloc
</b
> fails.
247 <dt
>SQLITE_READONLY
</dt
>
248 <dd
><p
>This
return code indicates that an attempt was made to write to
249 a database
file that is opened
for reading only.
251 <dt
>SQLITE_INTERRUPT
</dt
>
252 <dd
><p
>This value is returned
if a call to
<b
>sqlite_interrupt
</b
>
253 interrupts a database operation in progress.
255 <dt
>SQLITE_IOERR
</dt
>
256 <dd
><p
>This value is returned
if the operating system informs SQLite
257 that it is unable to perform some disk I
/O operation. This could mean
258 that there is no more space left on the disk.
260 <dt
>SQLITE_CORRUPT
</dt
>
261 <dd
><p
>This value is returned
if SQLite detects that the database it is
262 working on has become corrupted. Corruption might occur due to a rogue
263 process writing to the database
file or it might happen due to an
264 perviously undetected logic
error in of SQLite. This value is also
265 returned
if a disk I
/O
error occurs in such a way that SQLite is forced
266 to leave the database
file in a corrupted state. The latter should only
267 happen due to a hardware or operating system malfunction.
270 <dd
><p
>This value is returned
if an insertion failed because there is
271 no space left on the disk
, or the database is too big to hold any
272 more information. The latter case should only occur
for databases
273 that are larger than
2GB in size.
275 <dt
>SQLITE_CANTOPEN
</dt
>
276 <dd
><p
>This value is returned
if the database
file could not be opened
279 <dt
>SQLITE_PROTOCOL
</dt
>
280 <dd
><p
>This value is returned
if some other process is messing with
281 file locks and has violated the
file locking protocol that SQLite uses
282 on its rollback journal files.
284 <dt
>SQLITE_SCHEMA
</dt
>
285 <dd
><p
>When the database first opened
, SQLite reads the database schema
286 into
memory and uses that schema to parse new SQL statements. If another
287 process changes the schema
, the command currently being processed will
288 abort because the virtual machine code generated assumed the old
289 schema. This is the
return code
for such cases. Retrying the
290 command usually will clear the problem.
292 <dt
>SQLITE_TOOBIG
</dt
>
293 <dd
><p
>SQLite will not store more than about
1 megabyte of data in a single
294 row of a single table. If you attempt to store more than
1 megabyte
295 in a single row
, this is the
return code you get.
297 <dt
>SQLITE_CONSTRAINT
</dt
>
298 <dd
><p
>This constant is returned
if the SQL statement would have violated
299 a database constraint.
301 <dt
>SQLITE_MISMATCH
</dt
>
302 <dd
><p
>This
error occurs when there is an attempt to insert non-integer
303 data into a column labeled INTEGER PRIMARY KEY. For most columns
, SQLite
304 ignores the data type and allows any kind of data to be stored. But
305 an INTEGER PRIMARY KEY column is only allowed to store integer data.
307 <dt
>SQLITE_MISUSE
</dt
>
308 <dd
><p
>This
error might occur
if one or more of the SQLite API routines
309 is used incorrectly. Examples of incorrect usage include calling
310 <b
>sqlite_exec
</b
> after the database has been closed using
311 <b
>sqlite_close
</b
> or
312 calling
<b
>sqlite_exec
</b
> with the same
313 database pointer simultaneously from two separate threads.
315 <dt
>SQLITE_NOLFS
</dt
>
316 <dd
><p
>This
error means that you have attempts to create or access a
file
317 database
file that is larger that
2GB on a legacy Unix machine that
318 lacks large
file support.
321 <dd
><p
>This
error indicates that the authorizer callback
322 has disallowed the SQL you are attempting to execute.
325 <dd
><p
>This is one of the
return codes from the
326 <b
>sqlite_step
</b
> routine which is part of the non-callback API.
327 It indicates that another row of result data is available.
330 <dd
><p
>This is one of the
return codes from the
331 <b
>sqlite_step
</b
> routine which is part of the non-callback API.
332 It indicates that the SQL statement has been completely executed and
333 the
<b
>sqlite_finalize
</b
> routine is ready to be called.
338 <h3
>2.0 Accessing Data Without Using A Callback Function
</h3
>
341 The
<b
>sqlite_exec
</b
> routine described above used to be the only
342 way to retrieve data from an SQLite database. But many programmers found
343 it inconvenient to use a callback function to obtain results. So beginning
344 with SQLite version
2.7.7, a second access interface is available that
345 does not use callbacks.
349 The new interface uses three separate functions to replace the single
350 <b
>sqlite_exec
</b
> function.
354 typedef struct sqlite_vm sqlite_vm
;
357 sqlite
*db
, /* The
open database
*/
358 const char
*zSql
, /* SQL statement to be compiled
*/
359 const char
**pzTail
, /* OUT
: uncompiled tail of zSql
*/
360 sqlite_vm
**ppVm
, /* OUT
: the virtual machine to execute zSql
*/
361 char
**pzErrmsg
/* OUT
: Error
message.
*/
365 sqlite_vm
*pVm
, /* The virtual machine to execute
*/
366 int
*pN
, /* OUT
: Number of columns in result
*/
367 const char
***pazValue
, /* OUT
: Column data
*/
368 const char
***pazColName
/* OUT
: Column names and datatypes
*/
372 sqlite_vm
*pVm
, /* The virtual machine to be finalized
*/
373 char
**pzErrMsg
/* OUT
: Error
message */
378 The strategy is to compile a single SQL statement using
379 <b
>sqlite_compile
</b
> then invoke
<b
>sqlite_step
</b
> multiple times
,
380 once
for each row of output
, and finally call
<b
>sqlite_finalize
</b
>
381 to clean up
after the SQL has finished execution.
384 <h4
>2.1 Compiling An SQL Statement Into A Virtual Machine
</h4
>
387 The
<b
>sqlite_compile
</b
> "compiles" a single SQL statement
(specified
388 by the second parameter
) and generates a virtual machine that is able
389 to execute that statement.
390 As with must interface routines
, the first parameter must be a pointer
391 to an sqlite structure that was obtained from a prior call to
395 A pointer to the virtual machine is stored in a pointer which is passed
396 in as the
4th parameter.
397 Space to hold the virtual machine is dynamically allocated. To avoid
398 a
memory leak
, the calling function must invoke
399 <b
>sqlite_finalize
</b
> on the virtual machine
after it has finished
401 The
4th parameter may be
set to NULL
if an
error is encountered during
406 If any errors are encountered during compilation
, an
error message is
407 written into
memory obtained from
<b
>malloc
</b
> and the
5th parameter
408 is made to point to that
memory. If the
5th parameter is NULL
, then
409 no
error message is generated. If the
5th parameter is not NULL
, then
410 the calling function should dispose of the
memory containing the
error
411 message by calling
<b
>sqlite_freemem
</b
>.
415 If the
2nd parameter actually contains two or more statements of SQL
,
416 only the first statement is compiled.
(This is different from the
417 behavior of
<b
>sqlite_exec
</b
> which executes all SQL statements
418 in its input
string.
) The
3rd parameter to
<b
>sqlite_compile
</b
>
419 is made to point to the first character beyond the end of the first
420 statement of SQL in the input. If the
2nd parameter contains only
421 a single SQL statement
, then the
3rd parameter will be made to point
422 to the '
\000' terminator at the end of the
2nd parameter.
426 On
success, <b
>sqlite_compile
</b
> returns SQLITE_OK.
427 Otherwise and
error code is returned.
430 <h4
>2.2 Step-By-Step Execution Of An SQL Statement
</h4
>
433 After a virtual machine has been generated using
<b
>sqlite_compile
</b
>
434 it is executed by one or more calls to
<b
>sqlite_step
</b
>. Each
435 invocation of
<b
>sqlite_step
</b
>, except the last one
,
436 returns a single row of the result.
437 The number of columns in the result is stored in the integer that
438 the
2nd parameter points to.
439 The pointer specified by the
3rd parameter is made to point
440 to an
array of pointers to column values.
441 The pointer in the
4th parameter is made to point to an
array
442 of pointers to column names and datatypes.
443 The
2nd through
4th parameters to
<b
>sqlite_step
</b
> convey the
444 same information as the
2nd through
4th parameters of the
445 <b
>callback
</b
> routine when using
446 the
<b
>sqlite_exec
</b
> interface. Except
, with
<b
>sqlite_step
</b
>
447 the column datatype information is always included in the in the
448 4th parameter regardless of whether or not the
449 <a href
="pragma.html#pragma_show_datatypes">SHOW_DATATYPES
</a
> pragma
454 Each invocation of
<b
>sqlite_step
</b
> returns an integer code that
455 indicates what happened during that step. This code may be
456 SQLITE_BUSY
, SQLITE_ROW
, SQLITE_DONE
, SQLITE_ERROR
, or
461 If the virtual machine is unable to
open the database
file because
462 it is locked by another thread or process
, <b
>sqlite_step
</b
>
463 will
return SQLITE_BUSY. The calling function should do some other
464 activity
, or sleep
, for a short amount of
time to give the lock a
465 chance to clear
, then invoke
<b
>sqlite_step
</b
> again. This can
466 be repeated as many times as desired.
470 Whenever another row of result data is available
,
471 <b
>sqlite_step
</b
> will
return SQLITE_ROW. The row data is
472 stored in an
array of pointers to strings and the
2nd parameter
473 is made to point to this
array.
477 When all processing is complete
, <b
>sqlite_step
</b
> will
return
478 either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the
479 statement completed successfully and SQLITE_ERROR indicates that there
480 was a run-time
error.
(The details of the
error are obtained from
481 <b
>sqlite_finalize
</b
>.
) It is a misuse of the library to attempt
482 to call
<b
>sqlite_step
</b
> again
after it has returned SQLITE_DONE
487 When
<b
>sqlite_step
</b
> returns SQLITE_DONE or SQLITE_ERROR
,
488 the
*pN and
*pazColName values are
set to the number of columns
489 in the result
set and to the names of the columns
, just as they
490 are
for an SQLITE_ROW
return. This allows the calling code to
491 find the number of result columns and the column names and datatypes
492 even
if the result
set is empty. The
*pazValue parameter is always
493 set to NULL when the
return codes is SQLITE_DONE or SQLITE_ERROR.
494 If the SQL being executed is a statement that does not
495 return a result
(such as an INSERT or an UPDATE
) then
*pN will
496 be
set to zero and
*pazColName will be
set to NULL.
500 If you abuse the library by trying to call
<b
>sqlite_step
</b
>
501 inappropriately it will attempt
return SQLITE_MISUSE.
502 This can happen
if you call sqlite_step
() on the same virtual machine
504 time from two or more threads or
if you call sqlite_step
()
505 again
after it returned SQLITE_DONE or SQLITE_ERROR or
if you
506 pass in an invalid virtual machine pointer to sqlite_step
().
507 You should not depend on the SQLITE_MISUSE
return code to indicate
508 an
error. It is possible that a misuse of the interface will go
509 undetected and result in a program crash. The SQLITE_MISUSE is
510 intended as a debugging aid only
- to help you detect incorrect
511 usage prior to a mishap. The misuse detection logic is not guaranteed
512 to work in every case.
515 <h4
>2.3 Deleting A Virtual Machine
</h4
>
518 Every virtual machine that
<b
>sqlite_compile
</b
> creates should
519 eventually be handed to
<b
>sqlite_finalize
</b
>. The sqlite_finalize
()
520 procedure deallocates the
memory and other resources that the virtual
521 machine uses. Failure to call sqlite_finalize
() will result in
522 resource leaks in your program.
526 The
<b
>sqlite_finalize
</b
> routine also returns the result code
527 that indicates
success or
failure of the SQL operation that the
528 virtual machine carried out.
529 The value returned by sqlite_finalize
() will be the same as would
530 have been returned had the same SQL been executed by
<b
>sqlite_exec
</b
>.
531 The
error message returned will also be the same.
535 It is acceptable to call
<b
>sqlite_finalize
</b
> on a virtual machine
536 before
<b
>sqlite_step
</b
> has returned SQLITE_DONE. Doing so has
537 the effect of interrupting the operation in progress. Partially completed
538 changes will be rolled back and the database will be restored to its
539 original state
(unless an alternative recovery algorithm is selected using
540 an ON CONFLICT clause in the SQL being executed.
) The effect is the
541 same as
if a callback function of
<b
>sqlite_exec
</b
> had returned
546 It is also acceptable to call
<b
>sqlite_finalize
</b
> on a virtual machine
547 that has never been passed to
<b
>sqlite_step
</b
> even once.
550 <h3
>3.0 The Extended API
</h3
>
552 <p
>Only the three core routines described in section
1.0 are required to use
553 SQLite. But there are many other functions that provide
554 useful interfaces. These extended routines are as follows
:
558 int sqlite_last_insert_rowid
(sqlite
*);
560 int sqlite_changes
(sqlite
*);
562 int sqlite_get_table
(
571 void sqlite_free_table
(char
**);
573 void sqlite_interrupt
(sqlite
*);
575 int sqlite_complete
(const char
*sql
);
577 void sqlite_busy_handler
(sqlite
*, int
(*)(void
*,const char
*,int
), void
*);
579 void sqlite_busy_timeout
(sqlite
*, int ms
);
581 const char sqlite_version
[];
583 const char sqlite_encoding
[];
585 int sqlite_exec_printf
(
588 int
(*)(void
*,int
,char
**,char
**),
594 int sqlite_exec_vprintf
(
597 int
(*)(void
*,int
,char
**,char
**),
603 int sqlite_get_table_printf
(
613 int sqlite_get_table_vprintf
(
623 char
*sqlite_mprintf
(const char
*zFormat
, ...
);
625 char
*sqlite_vmprintf
(const char
*zFormat
, va_list
);
627 void sqlite_freemem
(char
*);
629 void sqlite_progress_handler
(sqlite
*, int
, int
(*)(void
*), void
*);
633 <p
>All of the above definitions are included in the
"sqlite.h"
634 header
file that comes in the
source tree.
</p
>
636 <h4
>3.1 The ROWID of the most recent insert
</h4
>
638 <p
>Every row of an SQLite table has a unique integer key. If the
639 table has a column labeled INTEGER PRIMARY KEY
, then that column
640 serves as the key. If there is no INTEGER PRIMARY KEY column then
641 the key is a unique integer. The key
for a row can be accessed in
642 a SELECT statement or used in a WHERE or ORDER BY clause using any
643 of the names
"ROWID", "OID", or
"_ROWID_".
</p
>
645 <p
>When you do an insert into a table that does not have an INTEGER PRIMARY
646 KEY column
, or
if the table does have an INTEGER PRIMARY KEY but the value
647 for that column is not specified in the VALUES clause of the insert
, then
648 the key is automatically generated. You can find the value of the key
649 for the most recent INSERT statement using the
650 <b
>sqlite_last_insert_rowid
</b
> API function.
</p
>
652 <h4
>3.2 The number of rows that changed
</h4
>
654 <p
>The
<b
>sqlite_changes
</b
> API function returns the number of rows
655 that have been inserted
, deleted
, or modified since the database was
656 last quiescent. A
"quiescent" database is one in which there are
657 no outstanding calls to
<b
>sqlite_exec
</b
> and no VMs created by
658 <b
>sqlite_compile
</b
> that have not been finalized by
<b
>sqlite_finalize
</b
>.
659 In common usage
, <b
>sqlite_changes
</b
> returns the number
660 of rows inserted
, deleted
, or modified by the most recent
<b
>sqlite_exec
</b
>
661 call or since the most recent
<b
>sqlite_compile
</b
>. But
if you have
662 nested calls to
<b
>sqlite_exec
</b
> (that is
, if the callback routine
663 of one
<b
>sqlite_exec
</b
> invokes another
<b
>sqlite_exec
</b
>) or
if
664 you invoke
<b
>sqlite_compile
</b
> to create a new VM
while there is
665 still another VM in existance
, then
666 the meaning of the number returned by
<b
>sqlite_changes
</b
> is more
668 The number reported includes any changes
669 that were later undone by a ROLLBACK or ABORT. But rows that are
670 deleted because of a DROP TABLE are
<em
>not
</em
> counted.
</p
>
672 <p
>SQLite implements the command
"<b>DELETE FROM table</b>" (without
673 a WHERE clause
) by dropping the table then recreating it.
674 This is much faster than deleting the elements of the table individually.
675 But it also means that the value returned from
<b
>sqlite_changes
</b
>
676 will be zero regardless of the number of elements that were originally
677 in the table. If an accurate count of the number of elements deleted
678 is necessary
, use
"<b>DELETE FROM table WHERE 1</b>" instead.
</p
>
680 <h4
>3.3 Querying into
memory obtained from malloc
()</h4
>
682 <p
>The
<b
>sqlite_get_table
</b
> function is a wrapper around
683 <b
>sqlite_exec
</b
> that collects all the information from successive
684 callbacks and writes it into
memory obtained from malloc
(). This
685 is a convenience function that allows the application to get the
686 entire result of a database query with a single function call.
</p
>
688 <p
>The main result from
<b
>sqlite_get_table
</b
> is an
array of pointers
689 to strings. There is one element in this
array for each column of
690 each row in the result. NULL results are represented by a NULL
691 pointer. In addition to the regular data
, there is an added row at the
692 beginning of the
array that contains the name of each column of the
695 <p
>As an example
, consider the following query
:</p
>
698 SELECT employee_name
, login
, host FROM users WHERE login LIKE 'd
%'
;
701 <p
>This query will
return the name
, login and host computer name
702 for every employee whose login begins with the letter
"d". If this
703 query is submitted to
<b
>sqlite_get_table
</b
> the result might
709 result
[0] = "employee_name"<br
>
710 result
[1] = "login"<br
>
711 result
[2] = "host"<br
>
712 result
[3] = "dummy"<br
>
713 result
[4] = "No such user"<br
>
715 result
[6] = "D. Richard Hipp"<br
>
716 result
[7] = "drh"<br
>
720 <p
>Notice that the
"host" value
for the
"dummy" record is NULL so
721 the result
[] array contains a NULL pointer at that slot.
</p
>
723 <p
>If the result
set of a query is empty
, then by
default
724 <b
>sqlite_get_table
</b
> will
set nrow to
0 and leave its
725 result parameter is
set to NULL. But
if the EMPTY_RESULT_CALLBACKS
726 pragma is ON then the result parameter is initialized to the names
727 of the columns only. For example
, consider this query which has
728 an empty result
set:</p
>
731 SELECT employee_name
, login
, host FROM users WHERE employee_name IS NULL
;
735 The
default behavior gives this results
:
745 But
if the EMPTY_RESULT_CALLBACKS pragma is ON
, then the following
752 result
[0] = "employee_name"<br
>
753 result
[1] = "login"<br
>
754 result
[2] = "host"<br
>
757 <p
>Memory to hold the information returned by
<b
>sqlite_get_table
</b
>
758 is obtained from malloc
(). But the calling function should not try
759 to free this information directly. Instead
, pass the complete table
760 to
<b
>sqlite_free_table
</b
> when the table is no longer needed.
761 It is safe to call
<b
>sqlite_free_table
</b
> with a NULL pointer such
762 as would be returned
if the result
set is empty.
</p
>
764 <p
>The
<b
>sqlite_get_table
</b
> routine returns the same integer
765 result code as
<b
>sqlite_exec
</b
>.
</p
>
767 <h4
>3.4 Interrupting an SQLite operation
</h4
>
769 <p
>The
<b
>sqlite_interrupt
</b
> function can be called from a
770 different thread or from a signal handler to cause the current database
771 operation to
exit at its first opportunity. When this happens
,
772 the
<b
>sqlite_exec
</b
> routine
(or the equivalent
) that started
773 the database operation will
return SQLITE_INTERRUPT.
</p
>
775 <h4
>3.5 Testing
for a complete SQL statement
</h4
>
777 <p
>The next interface routine to SQLite is a convenience function used
778 to test whether or not a
string forms a complete SQL statement.
779 If the
<b
>sqlite_complete
</b
> function returns true when its input
780 is a
string, then the argument forms a complete SQL statement.
781 There are no guarantees that the syntax of that statement is correct
,
782 but we at least know the statement is complete. If
<b
>sqlite_complete
</b
>
783 returns false
, then more
text is required to complete the SQL statement.
</p
>
785 <p
>For the purpose of the
<b
>sqlite_complete
</b
> function
, an SQL
786 statement is complete
if it ends in a semicolon.
</p
>
788 <p
>The
<b
>sqlite
</b
> command-line utility uses the
<b
>sqlite_complete
</b
>
789 function to know when it needs to call
<b
>sqlite_exec
</b
>. After each
790 line of input is received
, <b
>sqlite
</b
> calls
<b
>sqlite_complete
</b
>
791 on all input in its buffer. If
<b
>sqlite_complete
</b
> returns true
,
792 then
<b
>sqlite_exec
</b
> is called and the input buffer is reset. If
793 <b
>sqlite_complete
</b
> returns false
, then the prompt is changed to
794 the continuation prompt and another line of
text is
read and added to
795 the input buffer.
</p
>
797 <h4
>3.6 Library version
string</h4
>
799 <p
>The SQLite library exports the
string constant named
800 <b
>sqlite_version
</b
> which contains the version number of the
801 library. The header
file contains a macro SQLITE_VERSION
802 with the same information. If desired
, a program can compare
803 the SQLITE_VERSION macro against the
<b
>sqlite_version
</b
>
804 string constant to verify that the version number of the
805 header
file and the library match.
</p
>
807 <h4
>3.7 Library character
encoding</h4
>
809 <p
>By
default, SQLite assumes that all data uses a fixed-size
810 8-bit character
(iso8859
). But
if you give the
--enable-utf
8 option
811 to the configure script
, then the library assumes UTF-8
variable
812 sized characters. This makes a difference
for the LIKE and GLOB
813 operators and the LENGTH
() and SUBSTR
() functions. The static
814 string <b
>sqlite_encoding
</b
> will be
set to either
"UTF-8" or
815 "iso8859" to indicate how the library was compiled. In addition
,
816 the
<b
>sqlite.h
</b
> header
file will define one of the
817 macros
<b
>SQLITE_UTF8
</b
> or
<b
>SQLITE_ISO8859
</b
>, as appropriate.
</p
>
819 <p
>Note that the character
encoding mechanism used by SQLite cannot
820 be changed at run-time. This is a compile-time
option only. The
821 <b
>sqlite_encoding
</b
> character
string just tells you how the library
824 <h4
>3.8 Changing the library's response to locked files
</h4
>
826 <p
>The
<b
>sqlite_busy_handler
</b
> procedure can be used to register
827 a busy callback with an
open SQLite database. The busy callback will
828 be invoked whenever SQLite tries to access a database that is locked.
829 The callback will typically do some other useful work
, or perhaps sleep
,
830 in order to give the lock a chance to clear. If the callback returns
831 non-zero
, then SQLite tries again to access the database and the cycle
832 repeats. If the callback returns zero
, then SQLite aborts the current
833 operation and returns SQLITE_BUSY.
</p
>
835 <p
>The arguments to
<b
>sqlite_busy_handler
</b
> are the opaque
836 structure returned from
<b
>sqlite_open
</b
>, a pointer to the busy
837 callback function
, and a generic pointer that will be passed as
838 the first argument to the busy callback. When SQLite invokes the
839 busy callback
, it sends it three arguments
: the generic pointer
840 that was passed in as the third argument to
<b
>sqlite_busy_handler
</b
>,
841 the name of the database table or index that the library is trying
842 to access
, and the number of times that the library has attempted to
843 access the database table or index.
</p
>
845 <p
>For the common case where we want the busy callback to sleep
,
846 the SQLite library provides a convenience routine
<b
>sqlite_busy_timeout
</b
>.
847 The first argument to
<b
>sqlite_busy_timeout
</b
> is a pointer to
848 an
open SQLite database and the second argument is a number of milliseconds.
849 After
<b
>sqlite_busy_timeout
</b
> has been executed
, the SQLite library
850 will wait
for the lock to clear
for at least the number of milliseconds
851 specified before it returns SQLITE_BUSY. Specifying zero milliseconds
for
852 the timeout restores the
default behavior.
</p
>
854 <h4
>3.9 Using the
<tt
>_printf
()</tt
> wrapper functions
</h4
>
856 <p
>The four utility functions
</p
>
860 <li
><b
>sqlite_exec_printf
()</b
></li
>
861 <li
><b
>sqlite_exec_vprintf
()</b
></li
>
862 <li
><b
>sqlite_get_table_printf
()</b
></li
>
863 <li
><b
>sqlite_get_table_vprintf
()</b
></li
>
867 <p
>implement the same query functionality as
<b
>sqlite_exec
</b
>
868 and
<b
>sqlite_get_table
</b
>. But instead of taking a complete
869 SQL statement as their second argument
, the four
<b
>_printf
</b
>
870 routines take a printf-style
format string. The SQL statement to
871 be executed is generated from this
format string and from whatever
872 additional arguments are attached to the end of the function call.
</p
>
874 <p
>There are two advantages to using the SQLite printf
875 functions instead of
<b
>sprintf
</b
>. First of all
, with the
876 SQLite printf routines
, there is never a danger of overflowing a
877 static buffer as there is with
<b
>sprintf
</b
>. The SQLite
878 printf routines automatically allocate
(and later frees
)
880 necessary to hold the SQL statements generated.
</p
>
882 <p
>The second advantage the SQLite printf routines have over
883 <b
>sprintf
</b
> are two new formatting
options specifically designed
884 to support
string literals in SQL. Within the
format string,
885 the
%q formatting
option works very much like
%s in that it
886 reads a null-terminated
string from the argument
list and inserts
887 it into the result. But
%q translates the inserted
string by
888 making two copies of every single-quote
('
) character in the
889 substituted
string. This has the effect of escaping the end-of-string
890 meaning of single-quote within a
string literal. The
%Q formatting
891 option works similar
; it translates the single-quotes like
%q and
892 additionally encloses the resulting
string in single-quotes.
893 If the argument
for the
%Q formatting
options is a NULL pointer
,
894 the resulting
string is NULL without single quotes.
897 <p
>Consider an example. Suppose you are trying to insert a
string
898 value into a database table where the
string value was obtained from
899 user input. Suppose the
string to be inserted is stored in a
variable
900 named zString. The code to do the insertion might look like this
:</p
>
903 sqlite_exec_printf
(db
,
904 "INSERT INTO table1 VALUES('%s')",
908 <p
>If the zString
variable holds
text like
"Hello", then this statement
909 will work just fine. But suppose the user enters a
string like
910 "Hi y'all!". The SQL statement generated reads as follows
:
913 INSERT INTO table1 VALUES
('Hi y'all'
)
916 <p
>This is not valid SQL because of the apostrophy in the word
"y'all".
917 But
if the
%q formatting
option is used instead of
%s
, like this
:</p
>
920 sqlite_exec_printf
(db
,
921 "INSERT INTO table1 VALUES('%q')",
925 <p
>Then the generated SQL will look like the following
:</p
>
928 INSERT INTO table1 VALUES
('Hi y''all'
)
931 <p
>Here the apostrophy has been escaped and the SQL statement is well-formed.
932 When generating SQL on-the-fly from data that might contain a
933 single-quote character
('
), it is always a good idea to use the
934 SQLite printf routines and the
%q formatting
option instead of
<b
>sprintf
</b
>.
937 <p
>If the
%Q formatting
option is used instead of
%q
, like this
:</p
>
940 sqlite_exec_printf
(db
,
941 "INSERT INTO table1 VALUES(%Q)",
945 <p
>Then the generated SQL will look like the following
:</p
>
948 INSERT INTO table1 VALUES
('Hi y''all'
)
951 <p
>If the value of the zString
variable is NULL
, the generated SQL
952 will look like the following
:</p
>
955 INSERT INTO table1 VALUES
(NULL
)
958 <p
>All of the _printf
() routines above are built around the following
962 char
*sqlite_mprintf
(const char
*zFormat
, ...
);
963 char
*sqlite_vmprintf
(const char
*zFormat
, va_list
);
966 <p
>The
<b
>sqlite_mprintf
()</b
> routine works like the the standard library
967 <b
>sprintf
()</b
> except that it writes its results into
memory obtained
968 from malloc
() and returns a pointer to the malloced buffer.
969 <b
>sqlite_mprintf
()</b
> also understands the
%q and
%Q extensions described
970 above. The
<b
>sqlite_vmprintf
()</b
> is a varargs version of the same
971 routine. The
string pointer that these routines
return should be freed
972 by passing it to
<b
>sqlite_freemem
()</b
>.
975 <h4
>3.10 Performing background jobs during large queries
</h3
>
977 <p
>The
<b
>sqlite_progress_handler
()</b
> routine can be used to register a
978 callback routine with an SQLite database to be invoked periodically during long
979 running calls to
<b
>sqlite_exec
()</b
>, <b
>sqlite_step
()</b
> and the various
983 <p
>The callback is invoked every N virtual machine operations
, where N is
984 supplied as the second argument to
<b
>sqlite_progress_handler
()</b
>. The third
985 and fourth arguments to
<b
>sqlite_progress_handler
()</b
> are a pointer to the
986 routine to be invoked and a void pointer to be passed as the first argument to
990 <p
>The
time taken to execute each virtual machine operation can vary based on
991 many factors. A typical value
for a
1 GHz PC is between half and three million
992 per second but may be much higher or
lower, depending on the query. As such it
993 is difficult to schedule background operations based on virtual machine
994 operations. Instead
, it is recommended that a callback be scheduled relatively
995 frequently
(say every
1000 instructions
) and external timer routines used to
996 determine whether or not background jobs need to be run.
1000 <h3
>4.0 Adding New SQL Functions
</h3
>
1002 <p
>Beginning with version
2.4.0, SQLite allows the SQL language to be
1003 extended with new functions implemented as C code. The following interface
1008 typedef struct sqlite_func sqlite_func
;
1010 int sqlite_create_function
(
1014 void
(*xFunc
)(sqlite_func
*,int
,const char
**),
1017 int sqlite_create_aggregate
(
1021 void
(*xStep
)(sqlite_func
*,int
,const char
**),
1022 void
(*xFinalize
)(sqlite_func
*),
1026 char
*sqlite_set_result_string
(sqlite_func
*,const char
*,int
);
1027 void sqlite_set_result_int
(sqlite_func
*,int
);
1028 void sqlite_set_result_double
(sqlite_func
*,double
);
1029 void sqlite_set_result_error
(sqlite_func
*,const char
*,int
);
1031 void
*sqlite_user_data
(sqlite_func
*);
1032 void
*sqlite_aggregate_context
(sqlite_func
*, int nBytes
);
1033 int sqlite_aggregate_count
(sqlite_func
*);
1037 The
<b
>sqlite_create_function
()</b
> interface is used to create
1038 regular functions and
<b
>sqlite_create_aggregate
()</b
> is used to
1039 create new aggregate functions. In both cases
, the
<b
>db
</b
>
1040 parameter is an
open SQLite database on which the functions should
1041 be registered
, <b
>zName
</b
> is the name of the new function
,
1042 <b
>nArg
</b
> is the number of arguments
, and
<b
>pUserData
</b
> is
1043 a pointer which is passed through unchanged to the C implementation
1044 of the function. Both routines
return 0 on
success and non-zero
1045 if there are any errors.
1049 The length of a function name may not exceed
255 characters.
1050 Any attempt to create a function whose name exceeds
255 characters
1051 in length will result in an
error.
1055 For regular functions
, the
<b
>xFunc
</b
> callback is invoked once
1056 for each function call. The implementation of xFunc should call
1057 one of the
<b
>sqlite_set_result_...
</b
> interfaces to
return its
1058 result. The
<b
>sqlite_user_data
()</b
> routine can be used to
1059 retrieve the
<b
>pUserData
</b
> pointer that was passed in when the
1060 function was registered.
1064 For aggregate functions
, the
<b
>xStep
</b
> callback is invoked once
1065 for each row in the result and then
<b
>xFinalize
</b
> is invoked at the
1066 end to compute a final answer. The xStep routine can use the
1067 <b
>sqlite_aggregate_context
()</b
> interface to allocate
memory that
1068 will be unique to that particular instance of the SQL function.
1069 This
memory will be automatically deleted
after xFinalize is called.
1070 The
<b
>sqlite_aggregate_count
()</b
> routine can be used to find out
1071 how many rows of data were passed to the aggregate. The xFinalize
1072 callback should invoke one of the
<b
>sqlite_set_result_...
</b
>
1073 interfaces to
set the final result of the aggregate.
1077 SQLite now implements all of its built-in functions using this
1078 interface. For additional information and examples on how to create
1079 new SQL functions
, review the SQLite
source code in the
file
1083 <h3
>5.0 Multi-Threading And SQLite
</h3
>
1086 If SQLite is compiled with the THREADSAFE preprocessor macro
set to
1,
1087 then it is safe to use SQLite from two or more threads of the same process
1088 at the same
time. But each thread should have its own
<b
>sqlite
*</b
>
1089 pointer returned from
<b
>sqlite_open
</b
>. It is never safe
for two
1090 or more threads to access the same
<b
>sqlite
*</b
> pointer at the same
time.
1094 In precompiled SQLite libraries available on the website
, the Unix
1095 versions are compiled with THREADSAFE turned off but the windows
1096 versions are compiled with THREADSAFE turned on. If you need something
1097 different that this you will have to recompile.
1101 Under Unix
, an
<b
>sqlite
*</b
> pointer should not be carried across a
1102 <b
>fork
()</b
> system call into the child process. The child process
1103 should
open its own copy of the database
after the
<b
>fork
()</b
>.
1106 <h3
>6.0 Usage Examples
</h3
>
1108 <p
>For examples of how the SQLite C
/C
++ interface can be used
,
1109 refer to the
source code
for the
<b
>sqlite
</b
> program in the
1110 file <b
>src
/shell.c
</b
> of the
source tree.
1111 Additional information about sqlite is available at
1112 <a href
="sqlite.html">sqlite.html
</a
>.
1113 See also the sources to the
Tcl interface
for SQLite in
1114 the
source file <b
>src
/tclsqlite.c
</b
>.
</p
>