/ Check-in [cbe441b2]
Login

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

Overview
Comment:Add the "-unsetnull 1" option to the "sqlite3" command in the TCL interface.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | unsetnull-option
Files: files | file ages | folders
SHA3-256:cbe441b231397a9b775df71373ed5df9b1934a4bc428f9e09274446f9c86b859
User & Date: drh 2017-06-26 16:13:20
Context
2017-06-26
16:13
Add the "-unsetnull 1" option to the "sqlite3" command in the TCL interface. Closed-Leaf check-in: cbe441b2 user: drh tags: unsetnull-option
2017-06-24
19:21
Disable shell tests for the .schema command if virtual tables are not available. check-in: c8186874 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/tclsqlite.c.

160
161
162
163
164
165
166

167
168
169
170
171






172
173
174
175
176
177
178
....
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735





1736
1737
1738
1739
1740
1741
1742
1743
1744
....
3305
3306
3307
3308
3309
3310
3311

3312
3313
3314
3315
3316
3317
3318
....
3395
3396
3397
3398
3399
3400
3401








3402
3403
3404
3405
3406
3407
3408
3409
3410

3411
3412
3413
3414
3415
3416
3417
....
3439
3440
3441
3442
3443
3444
3445

3446
3447
3448
3449
3450
3451
3452
  int maxStmt;               /* The next maximum number of stmtList */
  int nStmt;                 /* Number of statements in stmtList */
  IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
  int nStep, nSort, nIndex;  /* Statistics for most recent operation */
  int nVMStep;               /* Another statistic for most recent operation */
  int nTransaction;          /* Number of nested [transaction] methods */
  int openFlags;             /* Flags used to open.  (SQLITE_OPEN_URI) */

#ifdef SQLITE_TEST
  int bLegacyPrepare;        /* True to use sqlite3_prepare() */
#endif
};







struct IncrblobChannel {
  sqlite3_blob *pBlob;      /* sqlite3 blob handle */
  SqliteDb *pDb;            /* Associated database connection */
  int iSeek;                /* Current seek offset */
  Tcl_Channel channel;      /* Channel identifier */
  IncrblobChannel *pNext;   /* Linked list of all open incrblob channels */
  IncrblobChannel *pPrev;   /* Linked list of all open incrblob channels */
................................................................................

  while( (rc==TCL_OK || rc==TCL_CONTINUE) && TCL_OK==(rc = dbEvalStep(p)) ){
    int i;
    int nCol;
    Tcl_Obj **apColName;
    dbEvalRowInfo(p, &nCol, &apColName);
    for(i=0; i<nCol; i++){
      Tcl_Obj *pVal = dbEvalColumnValue(p, i);
      if( pArray==0 ){
        Tcl_ObjSetVar2(interp, apColName[i], 0, pVal, 0);





      }else{
        Tcl_ObjSetVar2(interp, pArray, apColName[i], pVal, 0);
      }
    }

    /* The required interpreter variables are now populated with the data
    ** from the current row. If using NRE, schedule callbacks to evaluate
    ** script pScript, then to invoke this function again to fetch the next
    ** row (or clean up if there is no next row or the script throws an
................................................................................
  SqliteDb *p;
  const char *zArg;
  char *zErrMsg;
  int i;
  const char *zFile;
  const char *zVfs = 0;
  int flags;

  Tcl_DString translatedFilename;
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
  void *pKey = 0;
  int nKey = 0;
#endif
  int rc;

................................................................................
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i+1], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_URI;
      }else{
        flags &= ~SQLITE_OPEN_URI;
      }








    }else{
      Tcl_AppendResult(interp, "unknown option: ", zArg, (char*)0);
      return TCL_ERROR;
    }
  }
  if( objc<3 || (objc&1)!=1 ){
    Tcl_WrongNumArgs(interp, 1, objv,
      "HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"
      " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"

#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
      " ?-key CODECKEY?"
#endif
    );
    return TCL_ERROR;
  }
  zErrMsg = 0;
................................................................................
    Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
    Tcl_Free((char*)p);
    sqlite3_free(zErrMsg);
    return TCL_ERROR;
  }
  p->maxStmt = NUM_PREPARED_STMTS;
  p->openFlags = flags & SQLITE_OPEN_URI;

  p->interp = interp;
  zArg = Tcl_GetStringFromObj(objv[1], 0);
  if( DbUseNre() ){
    Tcl_NRCreateCommand(interp, zArg, DbObjCmdAdaptor, DbObjCmd,
                        (char*)p, DbDeleteCmd);
  }else{
    Tcl_CreateObjCommand(interp, zArg, DbObjCmd, (char*)p, DbDeleteCmd);







>





>
>
>
>
>
>







 







<

|
>
>
>
>
>

|







 







>







 







>
>
>
>
>
>
>
>









>







 







>







160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
....
1733
1734
1735
1736
1737
1738
1739

1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
....
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
....
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
....
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
  int maxStmt;               /* The next maximum number of stmtList */
  int nStmt;                 /* Number of statements in stmtList */
  IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
  int nStep, nSort, nIndex;  /* Statistics for most recent operation */
  int nVMStep;               /* Another statistic for most recent operation */
  int nTransaction;          /* Number of nested [transaction] methods */
  int openFlags;             /* Flags used to open.  (SQLITE_OPEN_URI) */
  unsigned int modeFlags;    /* Operating mode flags */
#ifdef SQLITE_TEST
  int bLegacyPrepare;        /* True to use sqlite3_prepare() */
#endif
};

/*
** Valid values for SqliteDb.modeFlags
*/
#define SQLITE_TCLMODE_UNSETNULL  0x0001  /* Unset NULL array values in */
                                          /*   db eval ARRAY SQL */

struct IncrblobChannel {
  sqlite3_blob *pBlob;      /* sqlite3 blob handle */
  SqliteDb *pDb;            /* Associated database connection */
  int iSeek;                /* Current seek offset */
  Tcl_Channel channel;      /* Channel identifier */
  IncrblobChannel *pNext;   /* Linked list of all open incrblob channels */
  IncrblobChannel *pPrev;   /* Linked list of all open incrblob channels */
................................................................................

  while( (rc==TCL_OK || rc==TCL_CONTINUE) && TCL_OK==(rc = dbEvalStep(p)) ){
    int i;
    int nCol;
    Tcl_Obj **apColName;
    dbEvalRowInfo(p, &nCol, &apColName);
    for(i=0; i<nCol; i++){

      if( pArray==0 ){
        Tcl_ObjSetVar2(interp, apColName[i], 0, dbEvalColumnValue(p,i), 0);
      }else if( (p->pDb->modeFlags & SQLITE_TCLMODE_UNSETNULL)!=0
             && sqlite3_column_type(p->pPreStmt->pStmt, i)==SQLITE_NULL 
      ){
        Tcl_UnsetVar2(interp, Tcl_GetString(pArray), 
                      Tcl_GetString(apColName[i]), 0);
      }else{
        Tcl_ObjSetVar2(interp, pArray, apColName[i], dbEvalColumnValue(p,i), 0);
      }
    }

    /* The required interpreter variables are now populated with the data
    ** from the current row. If using NRE, schedule callbacks to evaluate
    ** script pScript, then to invoke this function again to fetch the next
    ** row (or clean up if there is no next row or the script throws an
................................................................................
  SqliteDb *p;
  const char *zArg;
  char *zErrMsg;
  int i;
  const char *zFile;
  const char *zVfs = 0;
  int flags;
  unsigned int modeFlags = 0;
  Tcl_DString translatedFilename;
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
  void *pKey = 0;
  int nKey = 0;
#endif
  int rc;

................................................................................
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i+1], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_URI;
      }else{
        flags &= ~SQLITE_OPEN_URI;
      }
    }else if( strcmp(zArg, "-unsetnull")==0 ){
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i+1], &b) ) return TCL_ERROR;
      if( b ){
        modeFlags |= SQLITE_TCLMODE_UNSETNULL;
      }else{
        modeFlags &= ~SQLITE_TCLMODE_UNSETNULL;
      }
    }else{
      Tcl_AppendResult(interp, "unknown option: ", zArg, (char*)0);
      return TCL_ERROR;
    }
  }
  if( objc<3 || (objc&1)!=1 ){
    Tcl_WrongNumArgs(interp, 1, objv,
      "HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"
      " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"
      " ?-unsetnull BOOLEAN?"
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
      " ?-key CODECKEY?"
#endif
    );
    return TCL_ERROR;
  }
  zErrMsg = 0;
................................................................................
    Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
    Tcl_Free((char*)p);
    sqlite3_free(zErrMsg);
    return TCL_ERROR;
  }
  p->maxStmt = NUM_PREPARED_STMTS;
  p->openFlags = flags & SQLITE_OPEN_URI;
  p->modeFlags = modeFlags;
  p->interp = interp;
  zArg = Tcl_GetStringFromObj(objv[1], 0);
  if( DbUseNre() ){
    Tcl_NRCreateCommand(interp, zArg, DbObjCmdAdaptor, DbObjCmd,
                        (char*)p, DbDeleteCmd);
  }else{
    Tcl_CreateObjCommand(interp, zArg, DbObjCmd, (char*)p, DbDeleteCmd);

Changes to test/tclsqlite.test.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
660
661
662
663
664
665
666

667




668






669





670








671








672



673
# $Id: tclsqlite.test,v 1.73 2009/03/16 13:19:36 danielk1977 Exp $

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

# Check the error messages generated by tclsqlite
#
set r "sqlite_orig HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"
if {[sqlite3 -has-codec]} {
  append r " ?-key CODECKEY?"
}
do_test tcl-1.1 {
  set v [catch {sqlite3 bogus} msg]
  regsub {really_sqlite3} $msg {sqlite3} msg
  lappend v $msg
................................................................................
do_test tcl-15.4 {
  db exists {SELECT a FROM t1 WHERE a>2}
} {1}
do_test tcl-15.5 {
  db exists {SELECT a FROM t1 WHERE a>3}
} {0}










































finish_test







|







 







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

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
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
706
707
708
# $Id: tclsqlite.test,v 1.73 2009/03/16 13:19:36 danielk1977 Exp $

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

# Check the error messages generated by tclsqlite
#
set r "sqlite_orig HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN? ?-unsetnull BOOLEAN?"
if {[sqlite3 -has-codec]} {
  append r " ?-key CODECKEY?"
}
do_test tcl-1.1 {
  set v [catch {sqlite3 bogus} msg]
  regsub {really_sqlite3} $msg {sqlite3} msg
  lappend v $msg
................................................................................
do_test tcl-15.4 {
  db exists {SELECT a FROM t1 WHERE a>2}
} {1}
do_test tcl-15.5 {
  db exists {SELECT a FROM t1 WHERE a>3}
} {0}

# 2017-06-26: Different behavior with "-unsetnull 1" versus "-unsetnull 0"
#
# In the "db eval SQL ARRAY" form, NULL results cause the corresponding
# array entry to be unset with -unsetnull 1.  With -unsetnull 0, the NULL
# results cause the array entry to be filled with the [db nullvalue] string.
# The -unsetnull 0 version is the default, for legacy compatibility.
#
catch {db close}
forcedelete test.db
sqlite3 db test.db
do_execsql_test tcl-16.100 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1,2),(2,NULL),(3,'xyz');
}
do_test tcl-16.101 {
  set res {}
  unset -nocomplain x
  db eval {SELECT * FROM t1} x {
    lappend res $x(a) [array names x]
  }
  set res
} {1 {a b *} 2 {a b *} 3 {a b *}}
sqlite3 db test.db -unsetnull 0
do_test tcl-16.102 {
  set res {}
  unset -nocomplain x
  db eval {SELECT * FROM t1} x {
    lappend res $x(a) [array names x]
  }
  set res
} {1 {a b *} 2 {a b *} 3 {a b *}}
sqlite3 db test.db -unsetnull 1
do_test tcl-16.103 {
  set res {}
  unset -nocomplain x
  db eval {SELECT * FROM t1} x {
    lappend res $x(a) [array names x]
  }
  set res
} {1 {a b *} 2 {a *} 3 {a b *}}

finish_test