/ Check-in [af1e9299]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional testing and bug fixing with the non-callback API. Updated the C/C++ interface document to describe the non-callback API. (CVS 855)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: af1e9299468aa70d7d91e7a5445ba391ccc8ff8b
User & Date: drh 2003-01-29 22:58:26
Context
2003-01-31
17:16
Allow double-quoted strings as string constants in the IN operator. As a side-efffect, allow the GROUP BY clause to refer to columns by their integer column number. Ticket #237. (CVS 856) check-in: 187d9c40 user: drh tags: trunk
2003-01-29
22:58
Additional testing and bug fixing with the non-callback API. Updated the C/C++ interface document to describe the non-callback API. (CVS 855) check-in: af1e9299 user: drh tags: trunk
18:46
Better error messages on constraint violations. Additional tests and bug fixes for the callback-free API. (CVS 854) check-in: ccc82f1a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
896
897
898
899
900
901
902

903
904
905
906
907
908
909
910
911
912
913
914
915
916
...
955
956
957
958
959
960
961

962
963
964
965
966
967
968
969
970
971
972
973
974
975
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.81 2003/01/24 12:14:20 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
*/
int sqliteBtreeRollback(Btree *pBt){
  int rc;
  BtCursor *pCur;
  if( pBt->inTrans==0 ) return SQLITE_OK;
  pBt->inTrans = 0;
  pBt->inCkpt = 0;

  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }
  rc = pBt->readOnly ? SQLITE_OK : sqlitepager_rollback(pBt->pPager);
  unlockBtreeIfUnused(pBt);
  return rc;
}

/*
** Set the checkpoint for the current transaction.  The checkpoint serves
** as a sub-transaction that can be rolled back independently of the
................................................................................
** to use a cursor that was open at the beginning of this operation
** will result in an error.
*/
int sqliteBtreeRollbackCkpt(Btree *pBt){
  int rc;
  BtCursor *pCur;
  if( pBt->inCkpt==0 || pBt->readOnly ) return SQLITE_OK;

  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }
  rc = sqlitepager_ckpt_rollback(pBt->pPager);
  pBt->inCkpt = 0;
  return rc;
}

/*
** Create a new cursor for the BTree whose root is on the page
** iTable.  The act of acquiring a cursor gets a read lock on 







|







 







>

|




<







 







>

|




<







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
896
897
898
899
900
901
902
903
904
905
906
907
908
909

910
911
912
913
914
915
916
...
955
956
957
958
959
960
961
962
963
964
965
966
967
968

969
970
971
972
973
974
975
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.82 2003/01/29 22:58:26 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
*/
int sqliteBtreeRollback(Btree *pBt){
  int rc;
  BtCursor *pCur;
  if( pBt->inTrans==0 ) return SQLITE_OK;
  pBt->inTrans = 0;
  pBt->inCkpt = 0;
  rc = pBt->readOnly ? SQLITE_OK : sqlitepager_rollback(pBt->pPager);
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage && pCur->pPage->isInit==0 ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }

  unlockBtreeIfUnused(pBt);
  return rc;
}

/*
** Set the checkpoint for the current transaction.  The checkpoint serves
** as a sub-transaction that can be rolled back independently of the
................................................................................
** to use a cursor that was open at the beginning of this operation
** will result in an error.
*/
int sqliteBtreeRollbackCkpt(Btree *pBt){
  int rc;
  BtCursor *pCur;
  if( pBt->inCkpt==0 || pBt->readOnly ) return SQLITE_OK;
  rc = sqlitepager_ckpt_rollback(pBt->pPager);
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage && pCur->pPage->isInit==0 ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }

  pBt->inCkpt = 0;
  return rc;
}

/*
** Create a new cursor for the BTree whose root is on the page
** iTable.  The act of acquiring a cursor gets a read lock on 

Changes to src/pager.c.

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
...
446
447
448
449
450
451
452










453
454

455
456
457
458
459
460
461
...
541
542
543
544
545
546
547

548
549
550
551
552
553
554


555

556
557
558
559
560
561
562
** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.71 2003/01/25 15:43:22 drh Exp $
*/
#include "os.h"         /* Must be first to enable large file support */
#include "sqliteInt.h"
#include "pager.h"
#include <assert.h>
#include <string.h>

................................................................................
  pPg = pager_lookup(pPager, pgRec.pgno);
  if( pPg==0 || pPg->needSync==0 ){
    TRACE2("PLAYBACK %d\n", pgRec.pgno);
    sqliteOsSeek(&pPager->fd, (pgRec.pgno-1)*(off_t)SQLITE_PAGE_SIZE);
    rc = sqliteOsWrite(&pPager->fd, pgRec.aData, SQLITE_PAGE_SIZE);
  }
  if( pPg ){










    memcpy(PGHDR_TO_DATA(pPg), pgRec.aData, SQLITE_PAGE_SIZE);
    memset(PGHDR_TO_EXTRA(pPg), 0, pPager->nExtra);

    pPg->dirty = 0;
    pPg->needSync = 0;
  }
  return rc;
}

/*
................................................................................
  ** This is code is used during testing only.  It is necessary to
  ** compensate for the sqliteOsTruncate() call inside 
  ** sqlitepager_rollback().
  */
  if( rc==SQLITE_OK ){
    PgHdr *pPg;
    for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){

      if( (int)pPg->pgno <= pPager->origDbSize ){
        sqliteOsSeek(&pPager->fd, SQLITE_PAGE_SIZE*(off_t)(pPg->pgno-1));
        rc = sqliteOsRead(&pPager->fd, PGHDR_TO_DATA(pPg), SQLITE_PAGE_SIZE);
        if( rc ) break;
      }else{
        memset(PGHDR_TO_DATA(pPg), 0, SQLITE_PAGE_SIZE);
      }


      memset(PGHDR_TO_EXTRA(pPg), 0, pPager->nExtra);

      pPg->needSync = 0;
      pPg->dirty = 0;
    }
  }
#endif
  if( rc!=SQLITE_OK ){
    pager_unwritelock(pPager);







|







 







>
>
>
>
>
>
>
>
>
>
|
|
>







 







>


|


|

>
>
|
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
...
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
...
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.72 2003/01/29 22:58:26 drh Exp $
*/
#include "os.h"         /* Must be first to enable large file support */
#include "sqliteInt.h"
#include "pager.h"
#include <assert.h>
#include <string.h>

................................................................................
  pPg = pager_lookup(pPager, pgRec.pgno);
  if( pPg==0 || pPg->needSync==0 ){
    TRACE2("PLAYBACK %d\n", pgRec.pgno);
    sqliteOsSeek(&pPager->fd, (pgRec.pgno-1)*(off_t)SQLITE_PAGE_SIZE);
    rc = sqliteOsWrite(&pPager->fd, pgRec.aData, SQLITE_PAGE_SIZE);
  }
  if( pPg ){
    if( pPg->nRef==0 ||
        memcmp(PGHDR_TO_DATA(pPg), pgRec.aData, SQLITE_PAGE_SIZE)==0
    ){
      /* Do not update the data on this page if the page is in use
      ** and the page has never been modified.  This avoids resetting
      ** the "extra" data.  That in turn avoids invalidating BTree cursors
      ** in trees that have never been modified.  The end result is that
      ** you can have a SELECT going on in one table and ROLLBACK changes
      ** to a different table and the SELECT is unaffected by the ROLLBACK.
      */
      memcpy(PGHDR_TO_DATA(pPg), pgRec.aData, SQLITE_PAGE_SIZE);
      memset(PGHDR_TO_EXTRA(pPg), 0, pPager->nExtra);
    }
    pPg->dirty = 0;
    pPg->needSync = 0;
  }
  return rc;
}

/*
................................................................................
  ** This is code is used during testing only.  It is necessary to
  ** compensate for the sqliteOsTruncate() call inside 
  ** sqlitepager_rollback().
  */
  if( rc==SQLITE_OK ){
    PgHdr *pPg;
    for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
      char zBuf[SQLITE_PAGE_SIZE];
      if( (int)pPg->pgno <= pPager->origDbSize ){
        sqliteOsSeek(&pPager->fd, SQLITE_PAGE_SIZE*(off_t)(pPg->pgno-1));
        rc = sqliteOsRead(&pPager->fd, zBuf, SQLITE_PAGE_SIZE);
        if( rc ) break;
      }else{
        memset(zBuf, 0, SQLITE_PAGE_SIZE);
      }
      if( pPg->nRef==0 || memcmp(zBuf, PGHDR_TO_DATA(pPg), SQLITE_PAGE_SIZE) ){
        memcpy(PGHDR_TO_DATA(pPg), zBuf, SQLITE_PAGE_SIZE);
        memset(PGHDR_TO_EXTRA(pPg), 0, pPager->nExtra);
      }
      pPg->needSync = 0;
      pPg->dirty = 0;
    }
  }
#endif
  if( rc!=SQLITE_OK ){
    pager_unwritelock(pPager);

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774

775
776
777
778
779
780

781
782
783
784
785
786
787
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.19 2003/01/29 18:46:53 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  char **argv            /* Text of each argument */
){
  sqlite_vm *vm;
  int rc, i;
  const char **azValue;
  const char **azColName;
  int N;
  char *zRc;
  char zBuf[50];
  if( argc!=5 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 
       " VM NVAR VALUEVAR COLNAMEVAR", 0);
    return TCL_ERROR;
  }
  if( getVmPointer(interp, argv[1], &vm) ) return TCL_ERROR;
  rc = sqlite_step(vm, &N, &azValue, &azColName);
  if( rc==SQLITE_DONE || rc==SQLITE_ROW ){
    sprintf(zBuf, "%d", N);
    Tcl_SetVar(interp, argv[2], zBuf, 0);
    Tcl_SetVar(interp, argv[3], "", 0);
    if( rc==SQLITE_ROW ){

      for(i=0; i<N; i++){
        Tcl_SetVar(interp, argv[3], azValue[i] ? azValue[i] : "",
            TCL_APPEND_VALUE | TCL_LIST_ELEMENT);
      }
    }
    Tcl_SetVar(interp, argv[4], "", 0);

    for(i=0; i<N*2; i++){
      Tcl_SetVar(interp, argv[4], azColName[i] ? azColName[i] : "",
          TCL_APPEND_VALUE | TCL_LIST_ELEMENT);
    }
  }
  switch( rc ){
    case SQLITE_DONE:   zRc = "SQLITE_DONE";    break;







|







 







|
|
|









<
|
|
|
<
>
|
|
|
|
|
|
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769

770
771
772

773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.20 2003/01/29 22:58:26 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  char **argv            /* Text of each argument */
){
  sqlite_vm *vm;
  int rc, i;
  const char **azValue = 0;
  const char **azColName = 0;
  int N = 0;
  char *zRc;
  char zBuf[50];
  if( argc!=5 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 
       " VM NVAR VALUEVAR COLNAMEVAR", 0);
    return TCL_ERROR;
  }
  if( getVmPointer(interp, argv[1], &vm) ) return TCL_ERROR;
  rc = sqlite_step(vm, &N, &azValue, &azColName);

  sprintf(zBuf, "%d", N);
  Tcl_SetVar(interp, argv[2], zBuf, 0);
  Tcl_SetVar(interp, argv[3], "", 0);

  if( azValue ){
    for(i=0; i<N; i++){
      Tcl_SetVar(interp, argv[3], azValue[i] ? azValue[i] : "",
          TCL_APPEND_VALUE | TCL_LIST_ELEMENT);
    }
  }
  Tcl_SetVar(interp, argv[4], "", 0);
  if( azColName ){
    for(i=0; i<N*2; i++){
      Tcl_SetVar(interp, argv[4], azColName[i] ? azColName[i] : "",
          TCL_APPEND_VALUE | TCL_LIST_ELEMENT);
    }
  }
  switch( rc ){
    case SQLITE_DONE:   zRc = "SQLITE_DONE";    break;

Changes to src/vdbe.c.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
...
755
756
757
758
759
760
761
762
763
764







765
766
767
768
769
770
771
....
1584
1585
1586
1587
1588
1589
1590
1591

1592
1593
1594
1595
1596
1597
1598
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.202 2003/01/29 18:46:53 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  assert( p && p->pFunc && p->pFunc->xStep );
  return p->cnt;
}

/*
** Advance the virtual machine to the next output row.
**
** The return vale will be either SQLITE_BUSY, SQLITE_DONE, or
** SQLITE_ROW.
**
** SQLITE_BUSY means that the virtual machine attempted to open
** a locked database and there is no busy callback registered.
** Call sqlite_step() again to retry the open.  *pN is set to 0
** and *pazColName and *pazValue are both set to NULL.
**
** SQLITE_DONE means that the virtual machine has finished
................................................................................
** row of the result set.  *pN is set to the number of columns in
** the row.  *pazColName is set to the names of the columns followed
** by the column datatypes.  *pazValue is set to the values of each
** column in the row.  The value of the i-th column is (*pazValue)[i].
** The name of the i-th column is (*pazColName)[i] and the datatype
** of the i-th column is (*pazColName)[i+*pN].
**
** If a run-time error is encountered, SQLITE_DONE is returned.  You
** can access the error code and error message using the sqlite_finalize()
** routine. 







*/
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 */
){
................................................................................
#ifdef VDBE_PROFILE
  unsigned long long start;  /* CPU clock count at start of opcode */
  int origPc;                /* Program counter at start of opcode */
#endif

  if( p->magic!=VDBE_MAGIC_RUN ) return SQLITE_MISUSE;
  assert( db->magic==SQLITE_MAGIC_BUSY );
  assert( p->rc==SQLITE_OK );

  assert( p->explain==0 );
  if( sqlite_malloc_failed ) goto no_mem;
  if( p->popStack ){
    PopStack(p, p->popStack);
    p->popStack = 0;
  }
  for(pc=p->pc; rc==SQLITE_OK; pc++){







|







 







|
|







 







|
|
|
>
>
>
>
>
>
>







 







|
>







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
...
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
....
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.203 2003/01/29 22:58:26 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  assert( p && p->pFunc && p->pFunc->xStep );
  return p->cnt;
}

/*
** Advance the virtual machine to the next output row.
**
** The return vale will be either SQLITE_BUSY, SQLITE_DONE, 
** SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.
**
** SQLITE_BUSY means that the virtual machine attempted to open
** a locked database and there is no busy callback registered.
** Call sqlite_step() again to retry the open.  *pN is set to 0
** and *pazColName and *pazValue are both set to NULL.
**
** SQLITE_DONE means that the virtual machine has finished
................................................................................
** row of the result set.  *pN is set to the number of columns in
** the row.  *pazColName is set to the names of the columns followed
** by the column datatypes.  *pazValue is set to the values of each
** column in the row.  The value of the i-th column is (*pazValue)[i].
** The name of the i-th column is (*pazColName)[i] and the datatype
** of the i-th column is (*pazColName)[i+*pN].
**
** SQLITE_ERROR means that a run-time error (such as a constraint
** violation) has occurred.  The details of the error will be returned
** by the next call to sqlite_finalize().  sqlite_step() should not
** be called again on the VM.
**
** SQLITE_MISUSE means that the this routine was called inappropriately.
** Perhaps it was called on a virtual machine that had already been
** finalized or on one that had previously returned SQLITE_ERROR or
** SQLITE_DONE.  Or it could be the case the the same database connection
** is being used simulataneously by two or more threads.
*/
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 */
){
................................................................................
#ifdef VDBE_PROFILE
  unsigned long long start;  /* CPU clock count at start of opcode */
  int origPc;                /* Program counter at start of opcode */
#endif

  if( p->magic!=VDBE_MAGIC_RUN ) return SQLITE_MISUSE;
  assert( db->magic==SQLITE_MAGIC_BUSY );
  assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY );
  p->rc = SQLITE_OK;
  assert( p->explain==0 );
  if( sqlite_malloc_failed ) goto no_mem;
  if( p->popStack ){
    PopStack(p, p->popStack);
    p->popStack = 0;
  }
  for(pc=p->pc; rc==SQLITE_OK; pc++){

Changes to test/capi2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
171
172
173
174
175
176
177

















178

179



































































































































































































180
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.2 2003/01/29 18:46:54 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check basic functionality
#
................................................................................
  set N x
  set VALUES y
  set COLNAMES z
  sqlite_step $VM N VALUES COLNAMES
} {SQLITE_MISUSE}
do_test capi2-1.9 {
  list $N $VALUES $COLNAMES
} {x y z}
do_test capi2-1.10 {
  sqlite_finalize $VM
} {}

# Check to make sure that the "tail" of a multi-statement SQL script
# is returned by sqlite_compile.
#
................................................................................
do_test capi2-3.13 {
  set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL]
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.14 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {1 {(19) uniqueness constraint failed}}























































































































































































































finish_test







|







 







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.3 2003/01/29 22:58:27 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check basic functionality
#
................................................................................
  set N x
  set VALUES y
  set COLNAMES z
  sqlite_step $VM N VALUES COLNAMES
} {SQLITE_MISUSE}
do_test capi2-1.9 {
  list $N $VALUES $COLNAMES
} {0 {} {}}
do_test capi2-1.10 {
  sqlite_finalize $VM
} {}

# Check to make sure that the "tail" of a multi-statement SQL script
# is returned by sqlite_compile.
#
................................................................................
do_test capi2-3.13 {
  set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL]
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.14 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {1 {(19) uniqueness constraint failed}}
do_test capi2-3.15 {
  set VM [sqlite_compile $DB {CREATE TABLE t2(a NOT NULL, b)} TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
do_test capi2-3.17 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {0 {}}
do_test capi2-3.18 {
  set VM [sqlite_compile $DB {INSERT INTO t2 VALUES(NULL,2)} TAIL]
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.19 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {1 {(19) t2.a may not be NULL}}

# Two or more virtual machines exists at the same time.
#
do_test capi2-4.1 {
  set VM1 [sqlite_compile $DB {INSERT INTO t2 VALUES(1,2)} TAIL]
  set TAIL
} {}
do_test capi2-4.2 {
  set VM2 [sqlite_compile $DB {INSERT INTO t2 VALUES(2,3)} TAIL]
  set TAIL
} {}
do_test capi2-4.3 {
  set VM3 [sqlite_compile $DB {INSERT INTO t2 VALUES(3,4)} TAIL]
  set TAIL
} {}
do_test capi2-4.4 {
  list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
do_test capi2-4.5 {
  execsql {SELECT * FROM t2 ORDER BY a}
} {2 3}
do_test capi2-4.6 {
  list [catch {sqlite_finalize $VM2} msg] [set msg]
} {0 {}}  
do_test capi2-4.7 {
  list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
do_test capi2-4.8 {
  execsql {SELECT * FROM t2 ORDER BY a}
} {2 3 3 4}
do_test capi2-4.9 {
  list [catch {sqlite_finalize $VM3} msg] [set msg]
} {0 {}}  
do_test capi2-4.10 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
do_test capi2-4.11 {
  execsql {SELECT * FROM t2 ORDER BY a}
} {1 2 2 3 3 4}
do_test capi2-4.12 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}  

# Interleaved SELECTs
#
do_test capi2-5.1 {
  set VM1 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
  set VM2 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
  set VM3 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {2 3} {a b {} {}}}
do_test capi2-5.2 {
  list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {2 3} {a b {} {}}}
do_test capi2-5.3 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {3 4} {a b {} {}}}
do_test capi2-5.4 {
  list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {2 3} {a b {} {}}}
do_test capi2-5.5 {
  list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {3 4} {a b {} {}}}
do_test capi2-5.6 {
  list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {1 2} {a b {} {}}}
do_test capi2-5.7 {
  list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 2 {} {a b {} {}}}
do_test capi2-5.8 {
  list [catch {sqlite_finalize $VM3} msg] [set msg]
} {0 {}}  
do_test capi2-5.9 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {1 2} {a b {} {}}}
do_test capi2-5.10 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}  
do_test capi2-5.11 {
  list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {3 4} {a b {} {}}}
do_test capi2-5.12 {
  list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 2 {1 2} {a b {} {}}}
do_test capi2-5.11 {
  list [catch {sqlite_finalize $VM2} msg] [set msg]
} {0 {}}  

# Check for proper SQLITE_BUSY returns.
#
do_test capi2-6.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(x counter);
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 VALUES(2);
    INSERT INTO t3 SELECT x+2 FROM t3;
    INSERT INTO t3 SELECT x+4 FROM t3;
    INSERT INTO t3 SELECT x+8 FROM t3;
    COMMIT;
  }
  set VM1 [sqlite_compile $DB {SELECT * FROM t3} TAIL]
  sqlite db2 test.db
  execsql {BEGIN} db2
} {}
do_test capi2-6.2 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_BUSY 0 {} {}}
do_test capi2-6.3 {
  execsql {COMMIT} db2
} {}
do_test capi2-6.4 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 1 {x counter}}
do_test capi2-6.5 {
  catchsql {BEGIN} db2
} {1 {database is locked}}
do_test capi2-6.6 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 2 {x counter}}
do_test capi2-6.7 {
  execsql {SELECT * FROM t2} db2
} {2 3 3 4 1 2}
do_test capi2-6.8 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 3 {x counter}}
do_test capi2-6.9 {
  execsql {SELECT * FROM t2} 
} {2 3 3 4 1 2}
do_test capi2-6.10 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 4 {x counter}}
do_test capi2-6.11 {
  execsql {BEGIN}
} {}
do_test capi2-6.12 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 5 {x counter}}
# execsql {pragma vdbe_trace=on}
do_test capi2-6.13 {
  catchsql {UPDATE t3 SET x=x+1}
} {1 {database table is locked}}
do_test capi2-6.14 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 6 {x counter}}
# puts [list [catch {sqlite_finalize $VM1} msg] [set msg]]; exit
do_test capi2-6.15 {
  execsql {SELECT * FROM t1}
} {1 2 3}
do_test capi2-6.16 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 7 {x counter}}
do_test capi2-6.17 {
  catchsql {UPDATE t1 SET b=b+1}
} {0 {}}
do_test capi2-6.18 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 8 {x counter}}
do_test capi2-6.19 {
  execsql {SELECT * FROM t1}
} {1 3 3}
do_test capi2-6.20 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 9 {x counter}}
do_test capi2-6.21 {
  execsql {ROLLBACK; SELECT * FROM t1}
} {1 2 3}
do_test capi2-6.22 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 10 {x counter}}
do_test capi2-6.23 {
  execsql {BEGIN TRANSACTION ON CONFLICT ROLLBACK;}
} {}
do_test capi2-6.24 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 11 {x counter}}
do_test capi2-6.25 {
  execsql {
    INSERT INTO t1 VALUES(2,3,4);
    SELECT * FROM t1;
  }
} {1 2 3 2 3 4}
do_test capi2-6.26 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 12 {x counter}}
do_test capi2-6.27 {
  catchsql {
    INSERT INTO t1 VALUES(2,4,5);
    SELECT * FROM t1;
  }
} {1 {uniqueness constraint failed}}
do_test capi2-6.28 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}

finish_test

Changes to www/c_interface.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51


52
53
54
55
56
57
58
59
60
61
..
70
71
72
73
74
75
76
77
78
79
80
81
82

83
84
85
86
87
88
89
90

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
117
118
119
120
121
122
123

124
125
126
127
128
129
130
131
132
...
133
134
135
136
137
138
139

140






141

142
143
144
145
146
147
148
149
150
151
152
153
154

155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
183
184
185
186
187
188
189




190
191
192
193
194
195
196
...
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
294
295
296
297
298
299
300
301

302
303
304




















305
306
307




















































































































































































































308
309
310
311
312
313
314
315
316
317
...
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
...
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
...
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
...
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
...
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
...
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
...
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.35 2002/08/24 18:24:57 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
</p>"

puts {
<p>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.</p>

<h2>The Core API</h2>

<p>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:</p>

<blockquote><pre>
typedef struct sqlite sqlite;
#define SQLITE_OK           0   /* Successful result */

sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);

void sqlite_close(sqlite*);

int sqlite_exec(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);
</pre></blockquote>

<p>
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.
</p>


<h2>Opening a database</h2>

<p>Use the <b>sqlite_open()</b> 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 <b>sqlite_open</b> is ignored.
The third argument is a pointer to a string pointer.
If the third argument is not NULL and an error occurs
................................................................................
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.</p>

<p>The return value of the <b>sqlite_open()</b> function is a
pointer to an opaque <b>sqlite</b> 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.</p>


<h2>Closing the database</h2>

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


<h2>Executing SQL statements</h2>

<p>The <b>sqlite_exec()</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
<li><p>A pointer to the sqlite structure obtained from a prior call
       to <b>sqlite_open()</b>.</p></li>
<li><p>A null-terminated string containing the text of one or more
       SQL statements and/or queries to be processed.</p></li>
<li><p>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.</p></li>
<li><p>A pointer that is forwarded to become the first argument
       to the callback function.</p></li>
................................................................................

<blockquote><pre>
int Callback(void *pArg, int argc, char **argv, char **columnNames){
  return 0;
}
</pre></blockquote>


<p>The first argument to the callback is just a copy of the fourth argument
to <b>sqlite_exec()</b>  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:</p>
................................................................................
<blockquote><pre>
argv[i][0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv[i] == 0
</pre></blockquote>

<p>The names of the columns are contained in the fourth argument.</p>








<p>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
<blockquote><pre>
argv == 0
</pre></blockquote>
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.</p>


<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec()</b> will return SQLITE_ABORT.</p>

<h2>Error Codes</h2>

<p>
The <b>sqlite_exec()</b> 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:
</p>

<blockquote><pre>
#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
................................................................................
#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 */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */




</pre></blockquote>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
................................................................................
<dt>SQLITE_OK</dt>
<dd><p>This value is returned if everything worked and there were no errors.
</p></dd>
<dt>SQLITE_INTERNAL</dt>
<dd><p>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 <b>sqlite_exec()</b> call, please report the problem on the SQLite
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec()</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on the database
file are such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
................................................................................
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.</p>
</p></dd>
<dt>SQLITE_LOCKED</dt>
<dd><p>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 <b>sqlite_exec()</b>.  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.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.
</p></dd>
<dt>SQLITE_INTERRUPT</dt>
<dd><p>This value is returned if a call to <b>sqlite_interrupt()</b>
interrupts a database operation in progress.
</p></dd>
<dt>SQLITE_IOERR</dt>
<dd><p>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.
</p></dd>
................................................................................
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.
</p></dd>
<dt>SQLITE_MISUSE</dt>
<dd><p>This error might occur if one or more of the SQLite API routines
is used incorrectly.  Examples of incorrect usage include calling
<b>sqlite_exec()</b> after the database has been closed using

<b>sqlite_close()</b> or calling <b>sqlite_exec()</b> with the same
database pointer simultaneously from two separate threads.
</p></dd>




















</dl>
</blockquote>





















































































































































































































<h2>The Extended API</h2>

<p>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:
</p>

<blockquote><pre>
int sqlite_last_insert_rowid(sqlite*);

................................................................................
void sqlite_freemem(char*);

</pre></blockquote>

<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h2>The ROWID of the most recent insert</h2>

<p>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_".</p>

<p>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
<b>sqlite_last_insert_rowid()</b> API function.</p>

<h2>The number of rows that changed</h2>

<p>The <b>sqlite_changes()</b> API function returns the number of rows
that were inserted, deleted, or modified during the most recent
<b>sqlite_exec()</b> call.  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 <em>not</em> counted.</p>

<p>SQLite implements the command "<b>DELETE FROM table</b>" (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 <b>sqlite_changes()</b>
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 "<b>DELETE FROM table WHERE 1</b>" instead.</p>

<h2>Querying without using a callback function</h2>

<p>The <b>sqlite_get_table()</b> function is a wrapper around
<b>sqlite_exec()</b> 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.</p>

<p>The main result from <b>sqlite_get_table()</b> 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.</p>

<p>As an example, consider the following query:</p>
................................................................................

<blockquote>
SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%';
</blockquote>

<p>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 <b>sqlite_get_table()</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
................................................................................
result[8] = "zadok"
</blockquote>

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

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table()</b> 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:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
................................................................................
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 try
to free this information directly.  Instead, pass the complete table
to <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 such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table()</b> routine returns the same integer
result code as <b>sqlite_exec()</b>.</p>

<h2>Interrupting an SQLite operation</h2>

<p>The <b>sqlite_interrupt()</b> 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 <b>sqlite_exec()</b> routine (or the equivalent) that started
the database operation will return SQLITE_INTERRUPT.</p>

<h2>Testing for a complete SQL statement</h2>

<p>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 <b>sqlite_complete()</b> 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 <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 SQL
statement 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 each
line 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 to
the continuation prompt and another line of text is read and added to
the input buffer.</p>

<h2>Library version string</h2>

<p>The SQLite library exports the string constant named
<b>sqlite_version</b> 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 <b>sqlite_version</b>
string constant to verify that the version number of the
header file and the library match.</p> 

<h2>Library character encoding</h2>

<p>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 <b>sqlite_encoding</b> will be set to either "UTF-8" or
................................................................................
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>

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

<h2>Changing the library's response to locked files</h2>

<p>The <b>sqlite_busy_handler()</b> 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.</p>

<p>The arguments to <b>sqlite_busy_handler()</b> are the opaque
structure returned from <b>sqlite_open()</b>, 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 <b>sqlite_busy_handler</b>,
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.</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 to
an open SQLite database and the second argument is a number of milliseconds.
After <b>sqlite_busy_timeout()</b> 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.</p>

<h2>Using the <tt>_printf()</tt> wrapper functions</h2>

<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 complete
SQL statement as their second argument, the four <b>_printf</b>
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.</p>

<p>There are two advantages to using the SQLite printf
functions instead of <b>sprintf()</b>.  First of all, with the
SQLite printf routines, there is never a danger of overflowing a
static buffer as there is with <b>sprintf()</b>.  The SQLite
printf 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 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
................................................................................
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<a name="cfunc">
<h2>Adding New SQL Functions</h2>

<p>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:
</p>

<blockquote><pre>
................................................................................
<p>
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
<b>func.c</b>.
</p>

<h2>Multi-Threading And SQLite</h2>

<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 process
at 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 two
or 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 Unix
versions are compiled with THREADSAFE turned off but the windows
versions are compiled with THREADSAFE turned on.  If you need something
................................................................................

<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 process
should open its own copy of the database after the <b>fork()</b>.
</p>

<h2>Usage Examples</h2>

<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 the
file <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 in



|







 







|











|


|

|
|






|



>
>
|

|







 







|





>
|

|





>
|

|




|







 







>

|







 







>
|
>
>
>
>
>
>

>
|












>


|

|


|







 







>
>
>
>







 







|




|







 







|
|
|
|



|






|







 







|
>
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

|







 







|













|

|

|

|






|




|

|
|




|







 







|







 







|







 







|


|
|


|
|

|

|


|


|



|


|


|


|
|
|
|
|
|



|









|







 







|

|








|
|









|
|

|




|












|
|






|

|





|







 







|







 







|





|







 







|







1
2
3
4
5
6
7
8
9
10
11
..
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
...
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
...
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
...
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
...
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
...
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
...
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
...
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
...
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
....
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
....
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.36 2003/01/29 22:58:27 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
</p>"

puts {
<p>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.</p>

<h2>1.0 The Core API</h2>

<p>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:</p>

<blockquote><pre>
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
);
</pre></blockquote>

<p>
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.
</p>

<a name="sqlite_open">
<h3>1.1 Opening a database</h3>

<p>Use the <b>sqlite_open</b> 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 <b>sqlite_open</b> is ignored.
The third argument is a pointer to a string pointer.
If the third argument is not NULL and an error occurs
................................................................................
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.</p>

<p>The return value of the <b>sqlite_open</b> function is a
pointer to an opaque <b>sqlite</b> 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.</p>

<a name="sqlite_close">
<h3>1.2 Closing the database</h3>

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

<a name="sqlite_exec">
<h3>1.3 Executing SQL statements</h3>

<p>The <b>sqlite_exec</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
<li><p>A pointer to the sqlite structure obtained from a prior call
       to <b>sqlite_open</b>.</p></li>
<li><p>A null-terminated string containing the text of one or more
       SQL statements and/or queries to be processed.</p></li>
<li><p>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.</p></li>
<li><p>A pointer that is forwarded to become the first argument
       to the callback function.</p></li>
................................................................................

<blockquote><pre>
int Callback(void *pArg, int argc, char **argv, char **columnNames){
  return 0;
}
</pre></blockquote>

<a name="callback_row_data">
<p>The first argument to the callback is just a copy of the fourth argument
to <b>sqlite_exec</b>  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:</p>
................................................................................
<blockquote><pre>
argv[i][0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv[i] == 0
</pre></blockquote>

<p>The names of the columns are contained in first <i>argc</i>
entries of the fourth argument.
If the <a href="lang.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on (it is off by default) then
the second <i>argc</i> entries in the 4th argument are the datatypes
for the corresponding columns.
</p>

<p>If the <a href="lang.html#pragma_empty_result_callbacks">
EMPTY_RESULT_CALLBACKS</a> 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
<blockquote><pre>
argv == 0
</pre></blockquote>
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.</p>

<a name="callback_returns_nonzero">
<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec</b> will return SQLITE_ABORT.</p>

<h3>1.4 Error Codes</h3>

<p>
The <b>sqlite_exec</b> 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:
</p>

<blockquote><pre>
#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
................................................................................
#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 */
#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 */
</pre></blockquote>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
................................................................................
<dt>SQLITE_OK</dt>
<dd><p>This value is returned if everything worked and there were no errors.
</p></dd>
<dt>SQLITE_INTERNAL</dt>
<dd><p>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 <b>sqlite_exec</b> call, please report the problem on the SQLite
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on the database
file are such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
................................................................................
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.</p>
</p></dd>
<dt>SQLITE_LOCKED</dt>
<dd><p>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 <b>sqlite_exec</b>.  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.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.
</p></dd>
<dt>SQLITE_INTERRUPT</dt>
<dd><p>This value is returned if a call to <b>sqlite_interrupt</b>
interrupts a database operation in progress.
</p></dd>
<dt>SQLITE_IOERR</dt>
<dd><p>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.
</p></dd>
................................................................................
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.
</p></dd>
<dt>SQLITE_MISUSE</dt>
<dd><p>This error might occur if one or more of the SQLite API routines
is used incorrectly.  Examples of incorrect usage include calling
<b>sqlite_exec</b> after the database has been closed using
<b>sqlite_close</b> or 
calling <b>sqlite_exec</b> with the same
database pointer simultaneously from two separate threads.
</p></dd>
<dt>SQLITE_NOLFS</dt>
<dd><p>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.
</p></dd>
<dt>SQLITE_AUTH</dt>
<dd><p>This error indicates that the authorizer callback
has disallowed the SQL you are attempting to execute.
</p></dd>
<dt>SQLITE_ROW</dt>
<dd><p>This is one of the return codes from the
<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that another row of result data is available.
</p></dd>
<dt>SQLITE_DONE</dt>
<dd><p>This is one of the return codes from the
<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that the SQL statement has been completely executed and
the <b>sqlite_finalize</b> routine is ready to be called.
</p></dd>
</dl>
</blockquote>

<h2>2.0 Accessing Data Without Using A Callback Function</h2>

<p>
The <b>sqlite_exec</b> 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.
</p>

<p>
The new interface uses three separate functions to replace the single
<b>sqlite_exec</b> function.
</p>

<blockquote><pre>
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 */
);
</pre></blockquote>

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

<h3>2.1 Compiling An SQL Statement Into A Virtual Machine</h3>

<p>
The <b>sqlite_compile</b> "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
<b>sqlite_open</b>.

<p>
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
<b>sqlite_finalize</b> 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.
</p>

<p>
If any errors are encountered during compilation, an error message is
written into memory obtained from <b>malloc</b> 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 <b>sqlite_freemem</b>.
</p>

<p>
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 <b>sqlite_exec</b> which executes all SQL statements
in its input string.)  The 3rd parameter to <b>sqlite_compile</b>
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.
</p>

<p>
On success, <b>sqlite_compile</b> returns SQLITE_OK.
Otherwise and error code is returned.
</p>

<h3>2.2 Step-By-Step Execution Of An SQL Statement</h3>

<p>
After a virtual machine has been generated using <b>sqlite_compile</b>
it is executed by one or more calls to <b>sqlite_step</b>.  Each
invocation of <b>sqlite_step</b>, 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 <b>sqlite_step</b> convey the
same information as the 2nd through 4th parameters of the
<b>callback</b> routine when using
the <b>sqlite_exec</b> interface.  Except, with <b>sqlite_step</b>
the column datatype information is always included in the in the
4th parameter regardless of whether or not the
<a href="lang.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on or off.
</p>

<p>
Each invocation of <b>sqlite_step</b> 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.
</p>

<p>
If the virtual machine is unable to open the database file because
it is locked by another thread or process, <b>sqlite_step</b>
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 <b>sqlite_step</b> again.  This can
be repeated as many times as desired.
</p>

<p>
Whenever another row of result data is available,
<b>sqlite_step</b> 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.
</p>

<p>
When all processing is complete, <b>sqlite_step</b> 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
<b>sqlite_finalize</b>.)  It is a misuse of the library to attempt
to call <b>sqlite_step</b> again after it has returned SQLITE_DONE
or SQLITE_ERROR.
</p>

<p>
When <b>sqlite_step</b> 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.
</p>

<p>
If you abuse the library by trying to call <b>sqlite_step</b>
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.
</p>

<h3>2.3 Deleting A Virtual Machine</h3>

<p>
Every virtual machine that <b>sqlite_compile</b> creates should
eventually be handed to <b>sqlite_finalize</b>.  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.
</p>

<p>
The <b>sqlite_finalize</b> 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 <b>sqlite_exec</b>.
The error message returned will also be the same.
</p>

<p>
It is acceptable to call <b>sqlite_finalize</b> on a virtual machine
before <b>sqlite_step</b> 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 <b>sqlite_exec</b> had returned
non-zero.
</p>

<p>
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine
that has never been passed to <b>sqlite_step</b> even once.
</p>

<h2>3.0 The Extended API</h2>

<p>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:
</p>

<blockquote><pre>
int sqlite_last_insert_rowid(sqlite*);

................................................................................
void sqlite_freemem(char*);

</pre></blockquote>

<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h3>3.1 The ROWID of the most recent insert</h3>

<p>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_".</p>

<p>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
<b>sqlite_last_insert_rowid</b> API function.</p>

<h3>3.2 The number of rows that changed</h3>

<p>The <b>sqlite_changes</b> API function returns the number of rows
that were inserted, deleted, or modified during the most recent
<b>sqlite_exec</b> call.  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 <em>not</em> counted.</p>

<p>SQLite implements the command "<b>DELETE FROM table</b>" (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 <b>sqlite_changes</b>
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 "<b>DELETE FROM table WHERE 1</b>" instead.</p>

<h3>3.3 Querying into memory obtained from malloc()</h3>

<p>The <b>sqlite_get_table</b> function is a wrapper around
<b>sqlite_exec</b> 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.</p>

<p>The main result from <b>sqlite_get_table</b> 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.</p>

<p>As an example, consider the following query:</p>
................................................................................

<blockquote>
SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%';
</blockquote>

<p>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 <b>sqlite_get_table</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
................................................................................
result[8] = "zadok"
</blockquote>

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

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table</b> 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:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
................................................................................
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 try
to free this information directly.  Instead, pass the complete table
to <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 such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table</b> routine returns the same integer
result code as <b>sqlite_exec</b>.</p>

<h3>3.4 Interrupting an SQLite operation</h3>

<p>The <b>sqlite_interrupt</b> 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 <b>sqlite_exec</b> routine (or the equivalent) that started
the database operation will return SQLITE_INTERRUPT.</p>

<h3>3.5 Testing for a complete SQL statement</h3>

<p>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 <b>sqlite_complete</b> 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 <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 SQL
statement 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 each
line 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 to
the continuation prompt and another line of text is read and added to
the input buffer.</p>

<h3>3.6 Library version string</h3>

<p>The SQLite library exports the string constant named
<b>sqlite_version</b> 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 <b>sqlite_version</b>
string constant to verify that the version number of the
header file and the library match.</p> 

<h3>3.7 Library character encoding</h3>

<p>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 <b>sqlite_encoding</b> will be set to either "UTF-8" or
................................................................................
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>

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

<h3>3.8 Changing the library's response to locked files</h3>

<p>The <b>sqlite_busy_handler</b> 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.</p>

<p>The arguments to <b>sqlite_busy_handler</b> are the opaque
structure returned from <b>sqlite_open</b>, 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 <b>sqlite_busy_handler</b>,
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.</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 to
an open SQLite database and the second argument is a number of milliseconds.
After <b>sqlite_busy_timeout</b> 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.</p>

<h3>3.9 Using the <tt>_printf()</tt> wrapper functions</h3>

<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 complete
SQL statement as their second argument, the four <b>_printf</b>
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.</p>

<p>There are two advantages to using the SQLite printf
functions instead of <b>sprintf</b>.  First of all, with the
SQLite printf routines, there is never a danger of overflowing a
static buffer as there is with <b>sprintf</b>.  The SQLite
printf 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 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
................................................................................
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<a name="cfunc">
<h2>4.0 Adding New SQL Functions</h2>

<p>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:
</p>

<blockquote><pre>
................................................................................
<p>
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
<b>func.c</b>.
</p>

<h2>5.0 Multi-Threading And SQLite</h2>

<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 process
at 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 two
or 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 Unix
versions are compiled with THREADSAFE turned off but the windows
versions are compiled with THREADSAFE turned on.  If you need something
................................................................................

<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 process
should open its own copy of the database after the <b>fork()</b>.
</p>

<h2>6.0 Usage Examples</h2>

<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 the
file <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 in

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
....
1099
1100
1101
1102
1103
1104
1105

1106
1107
1108
1109
1110
1111
1112
....
1141
1142
1143
1144
1145
1146
1147

1148
1149
1150
1151
1152
1153
1154
1155


1156
1157
1158
1159
1160
1161
1162
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.48 2003/01/26 15:28:18 jplyon Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
    operations are as much as 50 or more times faster with synchronous off.
    </p>
    <p>This pragma changes the synchronous mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <b>synchronous</b> pragma does the same thing but only applies the setting
    to the current session.</p>


<li><p><b>PRAGMA empty_result_callbacks = ON;
       <br>PRAGMA empty_result_callbacks = OFF;</b></p>
    <p>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 "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
................................................................................
<li><p><b>PRAGMA integrity_check;</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, and malformed records.
    If any problems are found, then a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.</p>


<li><p><b>PRAGMA show_datatypes = ON;<br>PRAGMA show_datatypes = OFF;</b></p>
    <p>When turned on, the SHOW_DATATYPES pragma causes extra entries containing
    the names of <a href="datatypes.html">datatypes</a> of columns to be
    appended to the 4th ("columnNames") argument to <b>sqlite_exec()</b>
    callbacks.  When
    turned off, the 4th argument to callbacks contains only the column names.
    SQLite <a href="datatypes.html">datatypes</a> are always either "TEXT"
    or "NUMERIC".


    The following chart illustrates the difference for the query
    "SELECT 'xyzzy', 5, NULL AS empty ":</p>

    <blockquote><table border=0>
    <tr><th>show_datatypes=OFF</th><th width=30></th>
        <th>show_datatypes=ON</th></tr>
    <tr><td valign="top">



|







 







>







 







>






|
|
>
>







1
2
3
4
5
6
7
8
9
10
11
....
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
....
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.49 2003/01/29 22:58:27 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
    operations are as much as 50 or more times faster with synchronous off.
    </p>
    <p>This pragma changes the synchronous mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <b>synchronous</b> pragma does the same thing but only applies the setting
    to the current session.</p>

<a name="pragma_empty_result_callbacks">
<li><p><b>PRAGMA empty_result_callbacks = ON;
       <br>PRAGMA empty_result_callbacks = OFF;</b></p>
    <p>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 "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
................................................................................
<li><p><b>PRAGMA integrity_check;</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, and malformed records.
    If any problems are found, then a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.</p>

<a name="pragma_show_datatypes">
<li><p><b>PRAGMA show_datatypes = ON;<br>PRAGMA show_datatypes = OFF;</b></p>
    <p>When turned on, the SHOW_DATATYPES pragma causes extra entries containing
    the names of <a href="datatypes.html">datatypes</a> of columns to be
    appended to the 4th ("columnNames") argument to <b>sqlite_exec()</b>
    callbacks.  When
    turned off, the 4th argument to callbacks contains only the column names.
    The datatype for table columns is taken from the CREATE TABLE statement
    that defines the table.  Columns with an unspecified datatype have a
    datatype of "NUMERIC" and the results of expression have a datatype of
    either "TEXT" or "NUMERIC" depending on the expression.
    The following chart illustrates the difference for the query
    "SELECT 'xyzzy', 5, NULL AS empty ":</p>

    <blockquote><table border=0>
    <tr><th>show_datatypes=OFF</th><th width=30></th>
        <th>show_datatypes=ON</th></tr>
    <tr><td valign="top">