</p><blockquote>nrow = 0<br>ncolumn = 3<br>result[0] = "employee_name"<br>result[1] = "login"<br>result[2] = "host"<br></blockquote><p>Memory to hold the information returned by <b>sqlite_get_table</b>is obtained from malloc().  But the calling function should not tryto free this information directly.  Instead, pass the complete tableto <b>sqlite_free_table</b> when the table is no longer needed.It is safe to call <b>sqlite_free_table</b> with a NULL pointer suchas would be returned if the result set is empty.</p><p>The <b>sqlite_get_table</b> routine returns the same integerresult code as <b>sqlite_exec</b>.</p><h4>3.4 Interrupting an SQLite operation</h4><p>The <b>sqlite_interrupt</b> function can be called from adifferent thread or from a signal handler to cause the current databaseoperation to exit at its first opportunity.  When this happens,the <b>sqlite_exec</b> routine (or the equivalent) that startedthe database operation will return SQLITE_INTERRUPT.</p><h4>3.5 Testing for a complete SQL statement</h4><p>The next interface routine to SQLite is a convenience function usedto test whether or not a string forms a complete SQL statement.If the <b>sqlite_complete</b> function returns true when its inputis 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 <b>sqlite_complete</b>returns false, then more text is required to complete the SQL statement.</p><p>For the purpose of the <b>sqlite_complete</b> function, an SQLstatement is complete if it ends in a semicolon.</p><p>The <b>sqlite</b> command-line utility uses the <b>sqlite_complete</b>function to know when it needs to call <b>sqlite_exec</b>.  After eachline of input is received, <b>sqlite</b> calls <b>sqlite_complete</b>on all input in its buffer.  If <b>sqlite_complete</b> returns true, then <b>sqlite_exec</b> is called and the input buffer is reset.  If<b>sqlite_complete</b> returns false, then the prompt is changed tothe continuation prompt and another line of text is read and added tothe input buffer.</p><h4>3.6 Library version string</h4><p>The SQLite library exports the string constant named<b>sqlite_version</b> which contains the version number of thelibrary.  The header file contains a macro SQLITE_VERSIONwith the same information.  If desired, a program can comparethe SQLITE_VERSION macro against the <b>sqlite_version</b>string constant to verify that the version number of theheader file and the library match.</p> <h4>3.7 Library character encoding</h4><p>By default, SQLite assumes that all data uses a fixed-size8-bit character (iso8859).  But if you give the --enable-utf8 optionto the configure script, then the library assumes UTF-8 variablesized characters.  This makes a difference for the LIKE and GLOBoperators and the LENGTH() and SUBSTR() functions.  The staticstring <b>sqlite_encoding</b> will be set to either "UTF-8" or"iso8859" to indicate how the library was compiled.  In addition,the <b>sqlite.h</b> header file will define one of themacros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p><p>Note that the character encoding mechanism used by SQLite cannotbe changed at run-time.  This is a compile-time option only.  The<b>sqlite_encoding</b> character string just tells you how the librarywas compiled.</p><h4>3.8 Changing the library's response to locked files</h4><p>The <b>sqlite_busy_handler</b> procedure can be used to registera busy callback with an open SQLite database.  The busy callback willbe 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 returnsnon-zero, then SQLite tries again to access the database and the cyclerepeats.  If the callback returns zero, then SQLite aborts the currentoperation and returns SQLITE_BUSY.</p><p>The arguments to <b>sqlite_busy_handler</b> are the opaquestructure returned from <b>sqlite_open</b>, a pointer to the busycallback function, and a generic pointer that will be passed asthe first argument to the busy callback.  When SQLite invokes thebusy callback, it sends it three arguments:  the generic pointerthat was passed in as the third argument to <b>sqlite_busy_handler</b>,the name of the database table or index that the library is tryingto access, and the number of times that the library has attempted toaccess the database table or index.</p><p>For the common case where we want the busy callback to sleep,the SQLite library provides a convenience routine <b>sqlite_busy_timeout</b>.The first argument to <b>sqlite_busy_timeout</b> is a pointer toan open SQLite database and the second argument is a number of milliseconds.After <b>sqlite_busy_timeout</b> has been executed, the SQLite librarywill wait for the lock to clear for at least the number of milliseconds specified before it returns SQLITE_BUSY.  Specifying zero milliseconds forthe timeout restores the default behavior.</p><h4>3.9 Using the <tt>_printf()</tt> wrapper functions</h4><p>The four utility functions</p><p><ul><li><b>sqlite_exec_printf()</b></li><li><b>sqlite_exec_vprintf()</b></li><li><b>sqlite_get_table_printf()</b></li><li><b>sqlite_get_table_vprintf()</b></li></ul></p><p>implement the same query functionality as <b>sqlite_exec</b>and <b>sqlite_get_table</b>.  But instead of taking a completeSQL statement as their second argument, the four <b>_printf</b>routines take a printf-style format string.  The SQL statement tobe executed is generated from this format string and from whateveradditional arguments are attached to the end of the function call.</p><p>There are two advantages to using the SQLite printffunctions instead of <b>sprintf</b>.  First of all, with theSQLite printf routines, there is never a danger of overflowing astatic buffer as there is with <b>sprintf</b>.  The SQLiteprintf routines automatically allocate (and later frees)as much memory as is necessary to hold the SQL statements generated.</p><p>The second advantage the SQLite printf routines have over<b>sprintf</b> are two new formatting options specifically designedto support string literals in SQL.  Within the format string,the %q formatting option works very much like %s in that itreads a null-terminated string from the argument list and insertsit into the result.  But %q translates the inserted string bymaking two copies of every single-quote (') character in thesubstituted string.  This has the effect of escaping the end-of-stringmeaning of single-quote within a string literal. The %Q formattingoption works similar; it translates the single-quotes like %q andadditionally 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.</p><p>Consider an example.  Suppose you are trying to insert a stringvalue into a database table where the string value was obtained fromuser input.  Suppose the string to be inserted is stored in a variablenamed zString.  The code to do the insertion might look like this:</p><blockquote><pre>sqlite_exec_printf(db,  "INSERT INTO table1 VALUES('%s')",  0, 0, 0, zString);</pre></blockquote><p>If the zString variable holds text like "Hello", then this statementwill work just fine.  But suppose the user enters a string like "Hi y'all!".  The SQL statement generated reads as follows:<blockquote><pre>INSERT INTO table1 VALUES('Hi y'all')</pre></blockquote><p>This is not valid SQL because of the apostrophy in the word "y'all".But if the %q formatting option is used instead of %s, like this:</p><blockquote><pre>sqlite_exec_printf(db,  "INSERT INTO table1 VALUES('%q')",  0, 0, 0, zString);</pre></blockquote><p>Then the generated SQL will look like the following:</p><blockquote><pre>INSERT INTO table1 VALUES('Hi y''all')</pre></blockquote><p>Here the apostrophy has been escaped and the SQL statement is well-formed.When generating SQL on-the-fly from data that might contain asingle-quote character ('), it is always a good idea to use theSQLite printf routines and the %q formatting option instead of <b>sprintf</b>.</p><p>If the %Q formatting option is used instead of %q, like this:</p><blockquote><pre>sqlite_exec_printf(db,  "INSERT INTO table1 VALUES(%Q)",  0, 0, 0, zString);</pre></blockquote><p>Then the generated SQL will look like the following:</p><blockquote><pre>INSERT INTO table1 VALUES('Hi y''all')</pre></blockquote><p>If the value of the zString variable is NULL, the generated SQLwill look like the following:</p><blockquote><pre>INSERT INTO table1 VALUES(NULL)</pre></blockquote><p>All of the _printf() routines above are built around the followingtwo functions:</p><blockquote><pre>char *sqlite_mprintf(const char *zFormat, ...);char *sqlite_vmprintf(const char *zFormat, va_list);</pre></blockquote><p>The <b>sqlite_mprintf()</b> routine works like the the standard library<b>sprintf()</b> except that it writes its results into memory obtainedfrom malloc() and returns a pointer to the malloced buffer.  <b>sqlite_mprintf()</b> also understands the %q and %Q extensions describedabove.  The <b>sqlite_vmprintf()</b> is a varargs version of the sameroutine.  The string pointer that these routines return should be freedby passing it to <b>sqlite_freemem()</b>.</p><h4>3.10 Performing background jobs during large queries</h3><p>The <b>sqlite_progress_handler()</b> routine can be used to register acallback routine with an SQLite database to be invoked periodically during longrunning calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the variouswrapper functions.</p><p>The callback is invoked every N virtual machine operations, where N issupplied as the second argument to <b>sqlite_progress_handler()</b>. The thirdand fourth arguments to <b>sqlite_progress_handler()</b> are a pointer to theroutine to be invoked and a void pointer to be passed as the first argument toit.</p><p>The time taken to execute each virtual machine operation can vary based onmany factors.  A typical value for a 1 GHz PC is between half and three millionper second but may be much higher or lower, depending on the query.  As such itis difficult to schedule background operations based on virtual machineoperations. Instead, it is recommended that a callback be scheduled relativelyfrequently (say every 1000 instructions) and external timer routines used todetermine whether or not background jobs need to be run.  </p><a name="cfunc"><h3>4.0 Adding New SQL Functions</h3><p>Beginning with version 2.4.0, SQLite allows the SQL language to beextended with new functions implemented as C code.  The following interfaceis used:</p><blockquote><pre>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*);</pre></blockquote><p>The <b>sqlite_create_function()</b> interface is used to create regular functions and <b>sqlite_create_aggregate()</b> is used tocreate new aggregate functions.  In both cases, the <b>db</b>parameter is an open SQLite database on which the functions shouldbe registered, <b>zName</b> is the name of the new function,<b>nArg</b> is the number of arguments, and <b>pUserData</b> isa pointer which is passed through unchanged to the C implementationof the function.  Both routines return 0 on success and non-zeroif there are any errors.</p><p>The length of a function name may not exceed 255 characters.Any attempt to create a function whose name exceeds 255 charactersin length will result in an error.</p><p>For regular functions, the <b>xFunc</b> callback is invoked oncefor each function call.  The implementation of xFunc should callone of the <b>sqlite_set_result_...</b> interfaces to return itsresult.  The <b>sqlite_user_data()</b> routine can be used toretrieve the <b>pUserData</b> pointer that was passed in when thefunction was registered.</p><p>For aggregate functions, the <b>xStep</b> callback is invoked oncefor each row in the result and then <b>xFinalize</b> is invoked at theend to compute a final answer.  The xStep routine can use the<b>sqlite_aggregate_context()</b> interface to allocate memory thatwill be unique to that particular instance of the SQL function.This memory will be automatically deleted after xFinalize is called.The <b>sqlite_aggregate_count()</b> routine can be used to find outhow many rows of data were passed to the aggregate.  The xFinalizecallback should invoke one of the <b>sqlite_set_result_...</b>interfaces to set the final result of the aggregate.</p><p>SQLite now implements all of its built-in functions using thisinterface.  For additional information and examples on how to createnew SQL functions, review the SQLite source code in the file<b>func.c</b>.</p><h3>5.0 Multi-Threading And SQLite</h3><p>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 processat the same time.  But each thread should have its own <b>sqlite*</b>pointer returned from <b>sqlite_open</b>.  It is never safe for twoor more threads to access the same <b>sqlite*</b> pointer at the same time.</p><p>In precompiled SQLite libraries available on the website, the Unixversions are compiled with THREADSAFE turned off but the windowsversions are compiled with THREADSAFE turned on.  If you need somethingdifferent that this you will have to recompile.</p><p>Under Unix, an <b>sqlite*</b> pointer should not be carried across a<b>fork()</b> system call into the child process.  The child processshould open its own copy of the database after the <b>fork()</b>.</p><h3>6.0 Usage Examples</h3><p>For examples of how the SQLite C/C++ interface can be used,refer to the source code for the <b>sqlite</b> program in thefile <b>src/shell.c</b> of the source tree.Additional information about sqlite is available at<a href="sqlite.html">sqlite.html</a>.See also the sources to the Tcl interface for SQLite inthe source file <b>src/tclsqlite.c</b>.</p>}footer $rcsid