Index: VERSION ================================================================== --- VERSION +++ VERSION @@ -1,1 +1,1 @@ -2.0.5 +2.0.6 Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -23,11 +23,11 @@ ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** -** $Id: build.c,v 1.50 2001/10/15 00:44:36 drh Exp $ +** $Id: build.c,v 1.51 2001/10/19 16:44:57 drh Exp $ */ #include "sqliteInt.h" #include /* @@ -1446,10 +1446,18 @@ db->flags |= SQLITE_CountRows; }else{ db->flags &= ~SQLITE_CountRows; } }else + + if( sqliteStrICmp(zLeft, "empty_result_callbacks")==0 ){ + if( getBoolean(zRight) ){ + db->flags |= SQLITE_NullCallback; + }else{ + db->flags &= ~SQLITE_NullCallback; + } + }else if( sqliteStrICmp(zLeft, "table_info")==0 ){ Table *pTab; Vdbe *v; pTab = sqliteFindTable(db, zRight); Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.41 2001/10/18 12:34:47 drh Exp $ +** $Id: select.c,v 1.42 2001/10/19 16:44:57 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that @@ -992,7 +992,15 @@ */ if( pOrderBy ){ generateSortTail(v, pEList->nExpr); } pParse->nTab = base; + + + /* Issue a null callback if that is what the user wants. + */ + if( (pParse->db->flags & SQLITE_NullCallback)!=0 && eDest==SRT_Callback ){ + sqliteVdbeAddOp(v, OP_NullCallback, pEList->nExpr, 0); + } + return 0; } Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains code to implement the "sqlite" command line ** utility for accessing SQLite databases. ** -** $Id: shell.c,v 1.36 2001/10/06 16:33:03 drh Exp $ +** $Id: shell.c,v 1.37 2001/10/19 16:44:57 drh Exp $ */ #include #include #include #include "sqlite.h" @@ -242,10 +242,11 @@ int i; struct callback_data *p = (struct callback_data*)pArg; switch( p->mode ){ case MODE_Line: { int w = 5; + if( azArg==0 ) break; for(i=0; iw ) w = len; } if( p->cnt++>0 ) fprintf(p->out,"\n"); @@ -264,11 +265,11 @@ w = 0; } if( w<=0 ){ w = strlen(azCol[i] ? azCol[i] : ""); if( w<10 ) w = 10; - n = strlen(azArg[i] ? azArg[i] : ""); + n = strlen(azArg && azArg[i] ? azArg[i] : ""); if( wactualWidth) ){ p->actualWidth[i] = w; } @@ -288,10 +289,11 @@ "----------------------------------------------------------", i==nArg-1 ? "\n": " "); } } } + if( azArg==0 ) break; for(i=0; iactualWidth) ){ w = p->actualWidth[i]; }else{ @@ -307,10 +309,11 @@ if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator); } } + if( azArg==0 ) break; for(i=0; iout,"%s",azCol[i]); } fprintf(p->out,"\n"); } + if( azArg==0 ) break; fprintf(p->out,""); for(i=0; iout,""); output_html_string(p->out, azArg[i] ? azArg[i] : ""); fprintf(p->out,"\n"); @@ -350,10 +354,11 @@ } fprintf(p->out,"\n"); break; } case MODE_Insert: { + if( azArg==0 ) break; fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable); for(i=0; i0 ? ",": ""; if( azArg[i]==0 ){ fprintf(p->out,"%sNULL",zSep); @@ -363,10 +368,11 @@ if( zSep[0] ) fprintf(p->out,"%s",zSep); output_quoted_string(p->out, azArg[i]); } } fprintf(p->out,");\n"); + break; } } return 0; } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -9,11 +9,11 @@ ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.63 2001/10/18 12:34:47 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.64 2001/10/19 16:44:57 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" @@ -164,10 +164,12 @@ #define SQLITE_InternChanges 0x00000010 /* Uncommitted Hash table changes */ #define SQLITE_FullColNames 0x00000020 /* Show full column names on SELECT */ #define SQLITE_CountRows 0x00000040 /* Count rows changed by INSERT, */ /* DELETE, or UPDATE and return */ /* the count using a callback. */ +#define SQLITE_NullCallback 0x00000080 /* Invoke the callback once if the */ + /* result set is empty */ /* ** Current file format version */ #define SQLITE_FileFormat 2 Index: src/table.c ================================================================== --- src/table.c +++ src/table.c @@ -45,11 +45,11 @@ char *z; /* Make sure there is enough space in p->azResult to hold everything ** we need to remember from this invocation of the callback. */ - if( p->nRow==0 ){ + if( p->nRow==0 && argv!=0 ){ p->nColumn = nCol; need = nCol*2; }else{ need = nCol; } @@ -81,24 +81,26 @@ } } /* Copy over the row data */ - for(i=0; irc = SQLITE_NOMEM; - return 1; - } - strcpy(z, argv[i]); - } - p->azResult[p->nData++] = z; - } - p->nRow++; + if( argv!=0 ){ + for(i=0; irc = SQLITE_NOMEM; + return 1; + } + strcpy(z, argv[i]); + } + p->azResult[p->nData++] = z; + } + p->nRow++; + } return 0; } /* ** Query the database. But instead of invoking a callback for each row, Index: src/tclsqlite.c ================================================================== --- src/tclsqlite.c +++ src/tclsqlite.c @@ -9,11 +9,11 @@ ** May you share freely, never taking more than you give. ** ************************************************************************* ** A TCL Interface to SQLite ** -** $Id: tclsqlite.c,v 1.25 2001/10/18 12:34:47 drh Exp $ +** $Id: tclsqlite.c,v 1.26 2001/10/19 16:44:57 drh Exp $ */ #ifndef NO_TCL /* Omit this whole file if TCL is unavailable */ #include "sqlite.h" #include "tcl.h" @@ -70,46 +70,48 @@ CallbackData *cbData = (CallbackData*)clientData; int i, rc; #ifdef UTF_TRANSLATION_NEEDED Tcl_DString dCol; #endif - if( cbData->zArray[0] ){ - if( cbData->once ){ - Tcl_SetVar2(cbData->interp, cbData->zArray, "*", "", 0); - for(i=0; iinterp, cbData->zArray, "*", azN[i], - TCL_LIST_ELEMENT|TCL_APPEND_VALUE); - } - } - for(i=0; iinterp, cbData->zArray, azN[i], - Tcl_DStringValue(&dCol), 0); - Tcl_DStringFree(&dCol); -#else - Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], z, 0); -#endif - } - }else{ - for(i=0; iinterp, azN[i], Tcl_DStringValue(&dCol), 0); - Tcl_DStringFree(&dCol); -#else - Tcl_SetVar(cbData->interp, azN[i], z, 0); -#endif - } - } - cbData->once = 0; + if( azCol==0 || (cbData->once && cbData->zArray[0]) ){ + Tcl_SetVar2(cbData->interp, cbData->zArray, "*", "", 0); + for(i=0; iinterp, cbData->zArray, "*", azN[i], + TCL_LIST_ELEMENT|TCL_APPEND_VALUE); + } + cbData->once = 0; + } + if( azCol!=0 ){ + if( cbData->zArray[0] ){ + for(i=0; iinterp, cbData->zArray, azN[i], + Tcl_DStringValue(&dCol), 0); + Tcl_DStringFree(&dCol); +#else + Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], z, 0); +#endif + } + }else{ + for(i=0; iinterp, azN[i], Tcl_DStringValue(&dCol), 0); + Tcl_DStringFree(&dCol); +#else + Tcl_SetVar(cbData->interp, azN[i], z, 0); +#endif + } + } + } rc = Tcl_EvalObj(cbData->interp, cbData->pCode); if( rc==TCL_CONTINUE ) rc = TCL_OK; cbData->tcl_rc = rc; return rc!=TCL_OK; } @@ -126,10 +128,11 @@ char ** azCol, /* Data for each column */ char ** azN /* Name for each column */ ){ Tcl_Obj *pList = (Tcl_Obj*)clientData; int i; + if( azCol==0 ) return 0; for(i=0; i #include @@ -329,11 +329,11 @@ default: { if( !isIdChar[*z] ){ break; } for(i=1; isIdChar[z[i]]; i++){} - *tokenType = sqliteKeywordCode(z, i); + *tokenType = sqliteKeywordCode((char*)z, i); return i; } } *tokenType = TK_ILLEGAL; return 1; Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -28,11 +28,11 @@ ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** -** $Id: vdbe.c,v 1.86 2001/10/18 12:34:48 drh Exp $ +** $Id: vdbe.c,v 1.87 2001/10/19 16:44:57 drh Exp $ */ #include "sqliteInt.h" #include /* @@ -200,10 +200,11 @@ Mem *aMem; /* The memory locations */ Agg agg; /* Aggregate information */ int nSet; /* Number of sets allocated */ Set *aSet; /* An array of sets */ int nFetch; /* Number of OP_Fetch instructions executed */ + int nCallback; /* Number of callbacks invoked so far */ }; /* ** Create a new virtual database engine. */ @@ -809,21 +810,21 @@ "FileRead", "FileColumn", "FileClose", "AggReset", "AggFocus", "AggIncr", "AggNext", "AggSet", "AggGet", "SetInsert", "SetFound", "SetNotFound", "SetClear", "MakeRecord", "MakeKey", "MakeIdxKey", "Goto", "If", "Halt", "ColumnCount", - "ColumnName", "Callback", "Integer", "String", - "Null", "Pop", "Dup", "Pull", - "Add", "AddImm", "Subtract", "Multiply", - "Divide", "Remainder", "BitAnd", "BitOr", - "BitNot", "ShiftLeft", "ShiftRight", "AbsValue", - "Precision", "Min", "Max", "Like", - "Glob", "Eq", "Ne", "Lt", - "Le", "Gt", "Ge", "IsNull", - "NotNull", "Negative", "And", "Or", - "Not", "Concat", "Noop", "Strlen", - "Substr", + "ColumnName", "Callback", "NullCallback", "Integer", + "String", "Null", "Pop", "Dup", + "Pull", "Add", "AddImm", "Subtract", + "Multiply", "Divide", "Remainder", "BitAnd", + "BitOr", "BitNot", "ShiftLeft", "ShiftRight", + "AbsValue", "Precision", "Min", "Max", + "Like", "Glob", "Eq", "Ne", + "Lt", "Le", "Gt", "Ge", + "IsNull", "NotNull", "Negative", "And", + "Or", "Not", "Concat", "Noop", + "Strlen", "Substr", }; /* ** Given the name of an opcode, return its number. Return 0 if ** there is no match. @@ -1192,10 +1193,11 @@ */ case OP_ColumnCount: { p->azColName = sqliteRealloc(p->azColName, (pOp->p1+1)*sizeof(char*)); if( p->azColName==0 ) goto no_mem; p->azColName[pOp->p1] = 0; + p->nCallback = 0; break; } /* Opcode: ColumnName P1 * P3 ** @@ -1205,10 +1207,11 @@ ** hold the column names. Failure to do this will likely result in ** a coredump. */ case OP_ColumnName: { p->azColName[pOp->p1] = pOp->p3 ? pOp->p3 : ""; + p->nCallback = 0; break; } /* Opcode: Callback P1 * * ** @@ -1229,14 +1232,41 @@ zStack[p->tos+1] = 0; if( xCallback!=0 ){ if( xCallback(pArg, pOp->p1, &zStack[i], p->azColName)!=0 ){ rc = SQLITE_ABORT; } + p->nCallback++; } PopStack(p, pOp->p1); break; } + +/* Opcode: NullCallback P1 * * +** +** Invoke the callback function once with the 2nd argument (the +** number of columns) equal to P1 and with the 4th argument (the +** names of the columns) set according to prior OP_ColumnName and +** OP_ColumnCount instructions. This is all like the regular +** OP_Callback or OP_SortCallback opcodes. But the 3rd argument +** which normally contains a pointer to an array of pointers to +** data is NULL. +** +** The callback is only invoked if there have been no prior calls +** to OP_Callback or OP_SortCallback. +** +** This opcode is used to report the number and names of columns +** in cases where the result set is empty. +*/ +case OP_NullCallback: { + if( xCallback!=0 && p->nCallback==0 ){ + if( xCallback(pArg, pOp->p1, 0, p->azColName)!=0 ){ + rc = SQLITE_ABORT; + } + p->nCallback++; + } + break; +} /* Opcode: Concat P1 P2 P3 ** ** Look at the first P1 elements of the stack. Append them all ** together with the lowest element first. Use P3 as a separator. @@ -3374,10 +3404,11 @@ VERIFY( if( i<0 ) goto not_enough_stack; ) if( xCallback!=0 ){ if( xCallback(pArg, pOp->p1, (char**)zStack[i], p->azColName) ){ rc = SQLITE_ABORT; } + p->nCallback++; } POPSTACK; break; } Index: src/vdbe.h ================================================================== --- src/vdbe.h +++ src/vdbe.h @@ -13,11 +13,11 @@ ** ** This header defines the interface to the virtual database engine ** or VDBE. The VDBE implements an abstract machine that runs a ** simple program to access and modify the underlying database. ** -** $Id: vdbe.h,v 1.29 2001/10/13 02:59:09 drh Exp $ +** $Id: vdbe.h,v 1.30 2001/10/19 16:44:57 drh Exp $ */ #ifndef _SQLITE_VDBE_H_ #define _SQLITE_VDBE_H_ #include @@ -153,54 +153,55 @@ #define OP_Halt 71 #define OP_ColumnCount 72 #define OP_ColumnName 73 #define OP_Callback 74 - -#define OP_Integer 75 -#define OP_String 76 -#define OP_Null 77 -#define OP_Pop 78 -#define OP_Dup 79 -#define OP_Pull 80 - -#define OP_Add 81 -#define OP_AddImm 82 -#define OP_Subtract 83 -#define OP_Multiply 84 -#define OP_Divide 85 -#define OP_Remainder 86 -#define OP_BitAnd 87 -#define OP_BitOr 88 -#define OP_BitNot 89 -#define OP_ShiftLeft 90 -#define OP_ShiftRight 91 -#define OP_AbsValue 92 -#define OP_Precision 93 -#define OP_Min 94 -#define OP_Max 95 -#define OP_Like 96 -#define OP_Glob 97 -#define OP_Eq 98 -#define OP_Ne 99 -#define OP_Lt 100 -#define OP_Le 101 -#define OP_Gt 102 -#define OP_Ge 103 -#define OP_IsNull 104 -#define OP_NotNull 105 -#define OP_Negative 106 -#define OP_And 107 -#define OP_Or 108 -#define OP_Not 109 -#define OP_Concat 110 -#define OP_Noop 111 - -#define OP_Strlen 112 -#define OP_Substr 113 - -#define OP_MAX 113 +#define OP_NullCallback 75 + +#define OP_Integer 76 +#define OP_String 77 +#define OP_Null 78 +#define OP_Pop 79 +#define OP_Dup 80 +#define OP_Pull 81 + +#define OP_Add 82 +#define OP_AddImm 83 +#define OP_Subtract 84 +#define OP_Multiply 85 +#define OP_Divide 86 +#define OP_Remainder 87 +#define OP_BitAnd 88 +#define OP_BitOr 89 +#define OP_BitNot 90 +#define OP_ShiftLeft 91 +#define OP_ShiftRight 92 +#define OP_AbsValue 93 +#define OP_Precision 94 +#define OP_Min 95 +#define OP_Max 96 +#define OP_Like 97 +#define OP_Glob 98 +#define OP_Eq 99 +#define OP_Ne 100 +#define OP_Lt 101 +#define OP_Le 102 +#define OP_Gt 103 +#define OP_Ge 104 +#define OP_IsNull 105 +#define OP_NotNull 106 +#define OP_Negative 107 +#define OP_And 108 +#define OP_Or 109 +#define OP_Not 110 +#define OP_Concat 111 +#define OP_Noop 112 + +#define OP_Strlen 113 +#define OP_Substr 114 + +#define OP_MAX 114 /* ** Prototypes for the VDBE interface. See comments on the implementation ** for a description of what each of these routines does. */ Index: test/select1.test ================================================================== --- test/select1.test +++ test/select1.test @@ -9,11 +9,11 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # -# $Id: select1.test,v 1.13 2001/10/18 12:34:48 drh Exp $ +# $Id: select1.test,v 1.14 2001/10/19 16:44:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. @@ -412,6 +412,38 @@ SELECT min(1,2,3), -max(1,2,3) FROM test1 ORDER BY f1 } } {1 -3 1 -3} + +# Check the behavior when the result set is empty +# +do_test select1-9.1 { + catch {unset r} + set r(*) {} + db eval {SELECT * FROM test1 WHERE f1<0} r {} + set r(*) +} {} +do_test select1-9.2 { + execsql {PRAGMA empty_result_callbacks=on} + set r(*) {} + db eval {SELECT * FROM test1 WHERE f1<0} r {} + set r(*) +} {f1 f2} +do_test select1-9.3 { + set r(*) {} + db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} + set r(*) +} {f1 f2} +do_test select1-9.4 { + set r(*) {} + db eval {SELECT * FROM test1 ORDER BY f1} r {} + set r(*) +} {f1 f2} +do_test select1-9.5 { + set r(*) {} + db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} + set r(*) +} {f1 f2} +unset r + finish_test Index: www/c_interface.tcl ================================================================== --- www/c_interface.tcl +++ www/c_interface.tcl @@ -1,9 +1,9 @@ # # Run this Tcl script to generate the sqlite.html file. # -set rcsid {$Id: c_interface.tcl,v 1.17 2001/10/12 17:30:05 drh Exp $} +set rcsid {$Id: c_interface.tcl,v 1.18 2001/10/19 16:44:58 drh Exp $} puts { The C language interface to the SQLite library @@ -18,18 +18,19 @@ puts {

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.

-

The API

+

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*);
 
@@ -38,105 +39,18 @@
   char *sql,
   int (*)(void*,int,char**,char**),
   void*,
   char **errmsg
 );
-
-#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 contraint violation */
-
- -

Only the three core routines shown above are required to use -SQLite. But there are many other functions that provide -useful interfaces. These extended routines are as follows: -

- -
-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
-);
-
-
- -

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

- + + +

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

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 @@ -222,18 +136,62 @@

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

 argv[i] == 0

The names of the columns are contained in the fourth argument.

+ +

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.

-

The sqlite_exec() function returns an integer to indicate -success or failure of the operation. The following are possible -return values:

+

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 contraint violation */
+
+ +

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

SQLITE_OK

This value is returned if everything worked and there were no errors. @@ -330,10 +288,84 @@ a database constraint.

+

The Extended API

+ +

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

+ +
+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
+);
+
+
+ +

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

+

Querying without using a callback function

The sqlite_get_table() function is a wrapper around sqlite_exec() that collects all the information from successive callbacks and write it into memory obtained from malloc(). This @@ -372,15 +404,51 @@ 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.

+to sqlite_free_table() when the table is no longer needed. +It is safe to call sqlite_free_table() will 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().

Interrupting an SQLite operation

Index: www/changes.tcl ================================================================== --- www/changes.tcl +++ www/changes.tcl @@ -15,11 +15,12 @@ proc chng {date desc} { puts "
$date
" puts "

    $desc

" } -chng {2001 Oct ?? (2.0.6)} { +chng {2001 Oct 19 (2.0.6)} { +
  • Added the EMPTY_RESULT_CALLBACKS pragma
  • Support for UTF-8 and ISO8859 characters in column and table names.
  • Bug fix: Compute correct table names with the FULL_COLUMN_NAMES pragma is turned on.
    • } Index: www/lang.tcl ================================================================== --- www/lang.tcl +++ www/lang.tcl @@ -1,9 +1,9 @@ # # Run this Tcl script to generate the sqlite.html file. # -set rcsid {$Id: lang.tcl,v 1.13 2001/10/15 00:44:36 drh Exp $} +set rcsid {$Id: lang.tcl,v 1.14 2001/10/19 16:44:58 drh Exp $} puts { Query Language Understood By SQLite @@ -635,10 +635,20 @@
    PRAGMA count_changes = OFF;

    When on, the COUNT_CHANGES pragma causes the callback function to be invoked once for each DELETE, INSERT, or UPDATE operation. The argument is the number of rows that were changed.

    +

  • PRAGMA empty_result_callbacks = ON; +
    PRAGMA empty_result_callbacks = OFF;

    +

    When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback + function to be invoked once for each query that has an empty result + set. The third "argv" parameter to the callback is set to NULL + because there is no data to report. But the second "argc" and + fourth "columnNames" parameters are valid and can be used to + determine the number and names of the columns that would have been in + the result set had the set not been empty.

    +

  • PRAGMA full_column_names = ON;
    PRAGMA full_column_names = OFF;

    The column names reported in an SQLite callback are normally just the name of the column itself, except for joins when "TABLE.COLUMN" is used. But when full_column_names is turned on, column names are