Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the -withoutnulls option to the "db eval" method in the TCL interface. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
18f0616e15684ca327fb10a1d133331a |
| User & Date: | drh 2017-06-26 21:08:32 |
Context
|
2017-06-27
| ||
| 16:39 | Fix a virtual table problem that can occur when the vtab is on the RHS of a LEFT JOIN and there is a MATCH constraint in the ON clause, or when the vtab is in a sub-query that is the RHS of a LEFT JOIN and there is a MATCH constraint in the WHERE clause of the sub-query. (check-in: 87b38166 user: dan tags: trunk) | |
|
2017-06-26
| ||
| 21:08 | Add the -withoutnulls option to the "db eval" method in the TCL interface. (check-in: 18f0616e user: drh tags: trunk) | |
| 18:42 | The ".import" command of the shell, and the csv virtual table extension both ignore a single UTF-8 BOM at the beginning of their input. (check-in: 7c15d762 user: drh tags: trunk) | |
Changes
Changes to src/tclsqlite.c.
1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 .... 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 .... 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 .... 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 .... 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 .... 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 |
typedef struct DbEvalContext DbEvalContext;
struct DbEvalContext {
SqliteDb *pDb; /* Database handle */
Tcl_Obj *pSql; /* Object holding string zSql */
const char *zSql; /* Remaining SQL to execute */
SqlPreparedStmt *pPreStmt; /* Current statement */
int nCol; /* Number of columns returned by pStmt */
Tcl_Obj *pArray; /* Name of array variable */
Tcl_Obj **apColName; /* Array of column names */
};
/*
** Release any cache of column names currently held as part of
** the DbEvalContext structure passed as the first argument.
*/
static void dbReleaseColumnNames(DbEvalContext *p){
if( p->apColName ){
................................................................................
**
** set ${pArray}(*) {a b c}
*/
static void dbEvalInit(
DbEvalContext *p, /* Pointer to structure to initialize */
SqliteDb *pDb, /* Database handle */
Tcl_Obj *pSql, /* Object containing SQL script */
Tcl_Obj *pArray /* Name of Tcl array to set (*) element of */
){
memset(p, 0, sizeof(DbEvalContext));
p->pDb = pDb;
p->zSql = Tcl_GetString(pSql);
p->pSql = pSql;
Tcl_IncrRefCount(pSql);
if( pArray ){
p->pArray = pArray;
Tcl_IncrRefCount(pArray);
}
}
/*
** Obtain information about the row that the DbEvalContext passed as the
** first argument currently points to.
*/
static void dbEvalRowInfo(
................................................................................
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
................................................................................
Tcl_Obj *pResult = 0;
DbEvalContext sEval;
if( objc!=3 ){
Tcl_WrongNumArgs(interp, 2, objv, "SQL");
return TCL_ERROR;
}
dbEvalInit(&sEval, pDb, objv[2], 0);
rc = dbEvalStep(&sEval);
if( choice==DB_ONECOLUMN ){
if( rc==TCL_OK ){
pResult = dbEvalColumnValue(&sEval, 0);
}else if( rc==TCL_BREAK ){
Tcl_ResetResult(interp);
}
................................................................................
if( rc==TCL_BREAK ){
rc = TCL_OK;
}
break;
}
/*
** $db eval $sql ?array? ?{ ...code... }?
**
** The SQL statement in $sql is evaluated. For each row, the values are
** placed in elements of the array named "array" and ...code... is executed.
** If "array" and "code" are omitted, then no callback is every invoked.
** If "array" is an empty string, then the values are placed in variables
** that have the same name as the fields extracted by the query.
*/
case DB_EVAL: {
if( objc<3 || objc>5 ){
Tcl_WrongNumArgs(interp, 2, objv, "SQL ?ARRAY-NAME? ?SCRIPT?");
return TCL_ERROR;
}
if( objc==3 ){
DbEvalContext sEval;
Tcl_Obj *pRet = Tcl_NewObj();
Tcl_IncrRefCount(pRet);
dbEvalInit(&sEval, pDb, objv[2], 0);
while( TCL_OK==(rc = dbEvalStep(&sEval)) ){
int i;
int nCol;
dbEvalRowInfo(&sEval, &nCol, 0);
for(i=0; i<nCol; i++){
Tcl_ListObjAppendElement(interp, pRet, dbEvalColumnValue(&sEval, i));
}
................................................................................
Tcl_DecrRefCount(pRet);
}else{
ClientData cd2[2];
DbEvalContext *p;
Tcl_Obj *pArray = 0;
Tcl_Obj *pScript;
if( objc==5 && *(char *)Tcl_GetString(objv[3]) ){
pArray = objv[3];
}
pScript = objv[objc-1];
Tcl_IncrRefCount(pScript);
p = (DbEvalContext *)Tcl_Alloc(sizeof(DbEvalContext));
dbEvalInit(p, pDb, objv[2], pArray);
cd2[0] = (void *)p;
cd2[1] = (void *)pScript;
rc = DbEvalNextCmd(cd2, interp, TCL_OK);
}
break;
}
|
> > > | > > < | > > > > > | | | > > > > > > > > > > > > > | > | | | |
1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 .... 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 .... 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 .... 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 .... 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 .... 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 |
typedef struct DbEvalContext DbEvalContext;
struct DbEvalContext {
SqliteDb *pDb; /* Database handle */
Tcl_Obj *pSql; /* Object holding string zSql */
const char *zSql; /* Remaining SQL to execute */
SqlPreparedStmt *pPreStmt; /* Current statement */
int nCol; /* Number of columns returned by pStmt */
int evalFlags; /* Flags used */
Tcl_Obj *pArray; /* Name of array variable */
Tcl_Obj **apColName; /* Array of column names */
};
#define SQLITE_EVAL_WITHOUTNULLS 0x00001 /* Unset array(*) for NULL */
/*
** Release any cache of column names currently held as part of
** the DbEvalContext structure passed as the first argument.
*/
static void dbReleaseColumnNames(DbEvalContext *p){
if( p->apColName ){
................................................................................
**
** set ${pArray}(*) {a b c}
*/
static void dbEvalInit(
DbEvalContext *p, /* Pointer to structure to initialize */
SqliteDb *pDb, /* Database handle */
Tcl_Obj *pSql, /* Object containing SQL script */
Tcl_Obj *pArray, /* Name of Tcl array to set (*) element of */
int evalFlags /* Flags controlling evaluation */
){
memset(p, 0, sizeof(DbEvalContext));
p->pDb = pDb;
p->zSql = Tcl_GetString(pSql);
p->pSql = pSql;
Tcl_IncrRefCount(pSql);
if( pArray ){
p->pArray = pArray;
Tcl_IncrRefCount(pArray);
}
p->evalFlags = evalFlags;
}
/*
** Obtain information about the row that the DbEvalContext passed as the
** first argument currently points to.
*/
static void dbEvalRowInfo(
................................................................................
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->evalFlags & SQLITE_EVAL_WITHOUTNULLS)!=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
................................................................................
Tcl_Obj *pResult = 0;
DbEvalContext sEval;
if( objc!=3 ){
Tcl_WrongNumArgs(interp, 2, objv, "SQL");
return TCL_ERROR;
}
dbEvalInit(&sEval, pDb, objv[2], 0, 0);
rc = dbEvalStep(&sEval);
if( choice==DB_ONECOLUMN ){
if( rc==TCL_OK ){
pResult = dbEvalColumnValue(&sEval, 0);
}else if( rc==TCL_BREAK ){
Tcl_ResetResult(interp);
}
................................................................................
if( rc==TCL_BREAK ){
rc = TCL_OK;
}
break;
}
/*
** $db eval ?options? $sql ?array? ?{ ...code... }?
**
** The SQL statement in $sql is evaluated. For each row, the values are
** placed in elements of the array named "array" and ...code... is executed.
** If "array" and "code" are omitted, then no callback is every invoked.
** If "array" is an empty string, then the values are placed in variables
** that have the same name as the fields extracted by the query.
*/
case DB_EVAL: {
int evalFlags = 0;
const char *zOpt;
while( objc>3 && (zOpt = Tcl_GetString(objv[2]))!=0 && zOpt[0]=='-' ){
if( strcmp(zOpt, "-withoutnulls")==0 ){
evalFlags |= SQLITE_EVAL_WITHOUTNULLS;
}
else{
Tcl_AppendResult(interp, "unknown option: \"", zOpt, "\"", (void*)0);
return TCL_ERROR;
}
objc--;
objv++;
}
if( objc<3 || objc>5 ){
Tcl_WrongNumArgs(interp, 2, objv,
"?OPTIONS? SQL ?ARRAY-NAME? ?SCRIPT?");
return TCL_ERROR;
}
if( objc==3 ){
DbEvalContext sEval;
Tcl_Obj *pRet = Tcl_NewObj();
Tcl_IncrRefCount(pRet);
dbEvalInit(&sEval, pDb, objv[2], 0, 0);
while( TCL_OK==(rc = dbEvalStep(&sEval)) ){
int i;
int nCol;
dbEvalRowInfo(&sEval, &nCol, 0);
for(i=0; i<nCol; i++){
Tcl_ListObjAppendElement(interp, pRet, dbEvalColumnValue(&sEval, i));
}
................................................................................
Tcl_DecrRefCount(pRet);
}else{
ClientData cd2[2];
DbEvalContext *p;
Tcl_Obj *pArray = 0;
Tcl_Obj *pScript;
if( objc>=5 && *(char *)Tcl_GetString(objv[3]) ){
pArray = objv[3];
}
pScript = objv[objc-1];
Tcl_IncrRefCount(pScript);
p = (DbEvalContext *)Tcl_Alloc(sizeof(DbEvalContext));
dbEvalInit(p, pDb, objv[2], pArray, evalFlags);
cd2[0] = (void *)p;
cd2[1] = (void *)pScript;
rc = DbEvalNextCmd(cd2, interp, TCL_OK);
}
break;
}
|
Changes to test/tclsqlite.test.
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
661
662
663
664
665
666
667
668
669
670
671
672
673
|
set v [catch {db complete} msg]
lappend v $msg
} {1 {wrong # args: should be "db complete SQL"}}
}
do_test tcl-1.14 {
set v [catch {db eval} msg]
lappend v $msg
} {1 {wrong # args: should be "db eval SQL ?ARRAY-NAME? ?SCRIPT?"}}
do_test tcl-1.15 {
set v [catch {db function} msg]
lappend v $msg
} {1 {wrong # args: should be "db function NAME ?SWITCHES? SCRIPT"}}
do_test tcl-1.16 {
set v [catch {db last_insert_rowid xyz} msg]
lappend v $msg
................................................................................
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
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
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
709
710
711
|
set v [catch {db complete} msg]
lappend v $msg
} {1 {wrong # args: should be "db complete SQL"}}
}
do_test tcl-1.14 {
set v [catch {db eval} msg]
lappend v $msg
} {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?ARRAY-NAME? ?SCRIPT?"}}
do_test tcl-1.15 {
set v [catch {db function} msg]
lappend v $msg
} {1 {wrong # args: should be "db function NAME ?SWITCHES? SCRIPT"}}
do_test tcl-1.16 {
set v [catch {db last_insert_rowid xyz} msg]
lappend v $msg
................................................................................
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: The --withoutnulls flag to "db eval".
#
# In the "db eval --withoutnulls SQL ARRAY" form, NULL results cause the
# corresponding array entry to be unset. The default behavior (without
# the -withoutnulls flags) is for the corresponding array value to get
# the [db nullvalue] string.
#
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 *}}
do_test tcl-16.102 {
set res [catch {
db eval -unknown {SELECT * FROM t1} x {
lappend res $x(a) [array names x]
}
} rc]
lappend res $rc
} {1 {unknown option: "-unknown"}}
do_test tcl-16.103 {
set res {}
unset -nocomplain x
db eval -withoutnulls {SELECT * FROM t1} x {
lappend res $x(a) [array names x]
}
set res
} {1 {a b *} 2 {a *} 3 {a b *}}
finish_test
|