AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for TCL interface to the12# SQLite library.13#14# Actually, all tests are based on the TCL interface, so the main15# interface is pretty well tested. This file contains some addition16# tests for fringe issues that the main test suite does not cover.17#18# $Id: tclsqlite.test,v 1.73 2009/03/16 13:19:36 danielk1977 Exp $19 20catch {sqlite3}21 22set testdir [file dirname $argv0]23source $testdir/tester.tcl24set testprefix tcl25 26# Check the error messages generated by tclsqlite27#28set r "sqlite_orig HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nofollow BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"29if {[sqlite3 -has-codec]} {30 append r " ?-key CODECKEY?"31}32do_test tcl-1.1 {33 set v [catch {sqlite3 -bogus} msg]34 regsub {really_sqlite3} $msg {sqlite3} msg35 lappend v $msg36} [list 1 "wrong # args: should be \"$r\""]37do_test tcl-1.1.1 {38 set v [catch {sqlite3} msg]39 regsub {really_sqlite3} $msg {sqlite3} msg40 lappend v $msg41} [list 1 "wrong # args: should be \"$r\""]42do_test tcl-1.2 {43 set v [catch {db bogus} msg]44 lappend v $msg45} {1 {bad option "bogus": must be authorizer, backup, bind_fallback, busy, cache, changes, close, collate, collation_needed, commit_hook, complete, config, copy, deserialize, enable_load_extension, errorcode, erroroffset, eval, exists, format, function, incrblob, interrupt, last_insert_rowid, nullvalue, onecolumn, preupdate, profile, progress, rekey, restore, rollback_hook, serialize, status, timeout, total_changes, trace, trace_v2, transaction, unlock_notify, update_hook, version, or wal_hook}}46do_test tcl-1.2.1 {47 set v [catch {db cache bogus} msg]48 lappend v $msg49} {1 {bad option "bogus": must be flush or size}}50do_test tcl-1.2.2 {51 set v [catch {db cache} msg]52 lappend v $msg53} {1 {wrong # args: should be "db cache option ?arg?"}}54do_test tcl-1.3 {55 execsql {CREATE TABLE t1(a int, b int)}56 execsql {INSERT INTO t1 VALUES(10,20)}57 set v [catch {58 db eval {SELECT * FROM t1} data {59 error "The error message"60 }61 } msg]62 lappend v $msg63} {1 {The error message}}64do_test tcl-1.4 {65 set v [catch {66 db eval {SELECT * FROM t2} data {67 error "The error message"68 }69 } msg]70 lappend v $msg71} {1 {no such table: t2}}72do_test tcl-1.5 {73 set v [catch {74 db eval {SELECT * FROM t1} data {75 break76 }77 } msg]78 lappend v $msg79} {0 {}}80catch {expr x*} msg81do_test tcl-1.6 {82 set v [catch {83 db eval {SELECT * FROM t1} data {84 expr x*85 }86 } msg]87 lappend v $msg88} [list 1 $msg]89do_test tcl-1.7 {90 set v [catch {db} msg]91 lappend v $msg92} {1 {wrong # args: should be "db SUBCOMMAND ..."}}93if {[catch {db auth {}}]==0} {94 do_test tcl-1.8 {95 set v [catch {db authorizer 1 2 3} msg]96 lappend v $msg97 } {1 {wrong # args: should be "db authorizer ?CALLBACK?"}}98}99do_test tcl-1.9 {100 set v [catch {db busy 1 2 3} msg]101 lappend v $msg102} {1 {wrong # args: should be "db busy CALLBACK"}}103do_test tcl-1.10 {104 set v [catch {db progress 1} msg]105 lappend v $msg106} {1 {wrong # args: should be "db progress N CALLBACK"}}107do_test tcl-1.11 {108 set v [catch {db changes xyz} msg]109 lappend v $msg110} {1 {wrong # args: should be "db changes "}}111do_test tcl-1.12 {112 set v [catch {db commit_hook a b c} msg]113 lappend v $msg114} {1 {wrong # args: should be "db commit_hook ?CALLBACK?"}}115ifcapable {complete} {116 do_test tcl-1.13 {117 set v [catch {db complete} msg]118 lappend v $msg119 } {1 {wrong # args: should be "db complete SQL"}}120}121do_test tcl-1.14 {122 set v [catch {db eval} msg]123 lappend v $msg124} {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?VAR-NAME? ?SCRIPT?"}}125do_test tcl-1.15 {126 set v [catch {db function} msg]127 lappend v $msg128} {1 {wrong # args: should be "db function NAME ?SWITCHES? SCRIPT"}}129do_test tcl-1.16 {130 set v [catch {db last_insert_rowid xyz} msg]131 lappend v $msg132} {1 {wrong # args: should be "db last_insert_rowid "}}133do_test tcl-1.17 {134 set v [catch {db rekey} msg]135 lappend v $msg136} {1 {wrong # args: should be "db rekey KEY"}}137do_test tcl-1.18 {138 set v [catch {db timeout} msg]139 lappend v $msg140} {1 {wrong # args: should be "db timeout MILLISECONDS"}}141do_test tcl-1.19 {142 set v [catch {db collate} msg]143 lappend v $msg144} {1 {wrong # args: should be "db collate NAME SCRIPT"}}145do_test tcl-1.20 {146 set v [catch {db collation_needed} msg]147 lappend v $msg148} {1 {wrong # args: should be "db collation_needed SCRIPT"}}149do_test tcl-1.21 {150 set v [catch {db total_changes xyz} msg]151 lappend v $msg152} {1 {wrong # args: should be "db total_changes "}}153do_test tcl-1.22 {154 set v [catch {db copy} msg]155 lappend v $msg156} {1 {wrong # args: should be "db copy CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"}}157do_test tcl-1.23 {158 set v [catch {sqlite3 db2 test.db -vfs nosuchvfs} msg]159 lappend v $msg160} {1 {no such vfs: nosuchvfs}}161 162catch {unset ::result}163do_test tcl-2.1 {164 execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"165} {}166ifcapable schema_pragmas {167 do_test tcl-2.2 {168 execsql "PRAGMA table_info(t\u0123x)"169 } "0 a INT 0 {} 0 1 b\u1235 float 0 {} 0"170}171do_test tcl-2.3 {172 execsql "INSERT INTO t\u0123x VALUES(1,2.3)"173 db eval "SELECT * FROM t\u0123x" result break174 set result(*)175} "a b\u1235"176 177 178# Test the onecolumn method179#180do_test tcl-3.1 {181 execsql {182 INSERT INTO t1 SELECT a*2, b*2 FROM t1;183 INSERT INTO t1 SELECT a*2+1, b*2+1 FROM t1;184 INSERT INTO t1 SELECT a*2+3, b*2+3 FROM t1;185 }186 set rc [catch {db onecolumn {SELECT * FROM t1 ORDER BY a}} msg]187 lappend rc $msg188} {0 10}189do_test tcl-3.2 {190 db onecolumn {SELECT * FROM t1 WHERE a<0}191} {}192do_test tcl-3.3 {193 set rc [catch {db onecolumn} errmsg]194 lappend rc $errmsg195} {1 {wrong # args: should be "db onecolumn SQL"}}196do_test tcl-3.4 {197 set rc [catch {db onecolumn {SELECT bogus}} errmsg]198 lappend rc $errmsg199} {1 {no such column: bogus}}200ifcapable {tclvar} {201 do_test tcl-3.5 {202 set b 50203 set rc [catch {db one {SELECT * FROM t1 WHERE b>$b}} msg]204 lappend rc $msg205 } {0 41}206 do_test tcl-3.6 {207 set b 500208 set rc [catch {db one {SELECT * FROM t1 WHERE b>$b}} msg]209 lappend rc $msg210 } {0 {}}211 do_test tcl-3.7 {212 set b 500213 set rc [catch {db one {214 INSERT INTO t1 VALUES(99,510);215 SELECT * FROM t1 WHERE b>$b216 }} msg]217 lappend rc $msg218 } {0 99}219}220ifcapable {!tclvar} {221 execsql {INSERT INTO t1 VALUES(99,510)}222}223 224# Turn the busy handler on and off225#226do_test tcl-4.1 {227 proc busy_callback {cnt} {228 break229 }230 db busy busy_callback231 db busy232} {busy_callback}233do_test tcl-4.2 {234 db busy {}235 db busy236} {}237 238ifcapable {tclvar} {239 # Parsing of TCL variable names within SQL into bound parameters.240 #241 do_test tcl-5.1 {242 execsql {CREATE TABLE t3(a,b,c)}243 catch {unset x}244 set x(1) A245 set x(2) B246 execsql {247 INSERT INTO t3 VALUES($::x(1),$::x(2),$::x(3));248 SELECT * FROM t3249 }250 } {A B {}}251 do_test tcl-5.2 {252 execsql {253 SELECT typeof(a), typeof(b), typeof(c) FROM t3254 }255 } {text text null}256 do_test tcl-5.3 {257 catch {unset x}258 set x [binary format h12 686900686f00]259 execsql {260 UPDATE t3 SET a=$::x;261 }262 db eval {263 SELECT a FROM t3264 } break265 binary scan $a h12 adata266 set adata267 } {686900686f00}268 do_test tcl-5.4 {269 execsql {270 SELECT typeof(a), typeof(b), typeof(c) FROM t3271 }272 } {blob text null}273}274 275# Operation of "break" and "continue" within row scripts276#277do_test tcl-6.1 {278 db eval {SELECT * FROM t1} {279 break280 }281 lappend a $b282} {10 20}283do_test tcl-6.2 {284 set cnt 0285 db eval {SELECT * FROM t1} {286 if {$a>40} continue287 incr cnt288 }289 set cnt290} {4}291do_test tcl-6.3 {292 set cnt 0293 db eval {SELECT * FROM t1} {294 if {$a<40} continue295 incr cnt296 }297 set cnt298} {5}299do_test tcl-6.4 {300 proc return_test {x} {301 db eval {SELECT * FROM t1} {302 if {$a==$x} {return $b}303 }304 }305 return_test 10306} 20307do_test tcl-6.5 {308 return_test 20309} 40310do_test tcl-6.6 {311 return_test 99312} 510313do_test tcl-6.7 {314 return_test 0315} {}316 317do_test tcl-7.1 {318 db version319 expr 0320} {0}321 322# modify and reset the NULL representation323#324do_test tcl-8.1 {325 db nullvalue NaN326 execsql {INSERT INTO t1 VALUES(30,NULL)}327 db eval {SELECT * FROM t1 WHERE b IS NULL}328} {30 NaN}329proc concatFunc args {return [join $args {}]}330do_test tcl-8.2 {331 db function concat concatFunc332 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL}333} {aNaNz}334do_test tcl-8.3 {335 db nullvalue NULL336 db nullvalue337} {NULL}338do_test tcl-8.4 {339 db nullvalue {}340 db eval {SELECT * FROM t1 WHERE b IS NULL}341} {30 {}}342do_test tcl-8.5 {343 db function concat concatFunc344 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL}345} {az}346 347# Test the return type of user-defined functions348#349do_test tcl-9.1 {350 db function ret_str {return "hi"}351 execsql {SELECT typeof(ret_str())}352} {text}353do_test tcl-9.2 {354 db function ret_dbl {return [expr {rand()*0.5}]}355 execsql {SELECT typeof(ret_dbl())}356} {real}357do_test tcl-9.3 {358 db function ret_int {return [expr {int(rand()*200)}]}359 execsql {SELECT typeof(ret_int())}360} {integer}361 362proc breakAsNullUdf args {363 if {"1" eq [lindex $args 0]} {return -code break}364}365do_test tcl-9.4 {366 db function banu breakAsNullUdf367 execsql {SELECT typeof(banu()), typeof(banu(1))}368} {text null}369do_test tcl-9.5 {370 db nullvalue banunull371 db eval {SELECT banu(), banu(1)}372} {{} banunull}373 374 375# Recursive calls to the same user-defined function376#377ifcapable tclvar {378 do_test tcl-9.10 {379 proc userfunc_r1 {n} {380 if {$n<=0} {return 0}381 set nm1 [expr {$n-1}]382 return [expr {[db eval {SELECT r1($nm1)}]+$n}]383 }384 db function r1 userfunc_r1385 execsql {SELECT r1(10)}386 } {55}387 # Fails under -fsanitize=address,undefined due to stack overflow388 # do_test tcl-9.11 {389 # execsql {SELECT r1(100)}390 # } {5050}391}392 393# Tests for the new transaction method394#395do_test tcl-10.1 {396 db transaction {}397} {}398do_test tcl-10.2 {399 db transaction deferred {}400} {}401do_test tcl-10.3 {402 db transaction immediate {}403} {}404do_test tcl-10.4 {405 db transaction exclusive {}406} {}407do_test tcl-10.5 {408 set rc [catch {db transaction xyzzy {}} msg]409 lappend rc $msg410} {1 {bad transaction type "xyzzy": must be deferred, exclusive, or immediate}}411do_test tcl-10.6 {412 set rc [catch {db transaction {error test-error}} msg]413 lappend rc $msg414} {1 test-error}415do_test tcl-10.7 {416 db transaction {417 db eval {CREATE TABLE t4(x)}418 db transaction {419 db eval {INSERT INTO t4 VALUES(1)}420 }421 }422 db eval {SELECT * FROM t4}423} 1424do_test tcl-10.8 {425 catch {426 db transaction {427 db eval {INSERT INTO t4 VALUES(2)}428 db eval {INSERT INTO t4 VALUES(3)}429 db eval {INSERT INTO t4 VALUES(4)}430 error test-error431 }432 }433 db eval {SELECT * FROM t4}434} 1435do_test tcl-10.9 {436 db transaction {437 db eval {INSERT INTO t4 VALUES(2)}438 catch {439 db transaction {440 db eval {INSERT INTO t4 VALUES(3)}441 db eval {INSERT INTO t4 VALUES(4)}442 error test-error443 }444 }445 }446 db eval {SELECT * FROM t4}447} {1 2}448do_test tcl-10.10 {449 for {set i 0} {$i<1} {incr i} {450 db transaction {451 db eval {INSERT INTO t4 VALUES(5)}452 continue453 }454 error "This line should not be run"455 }456 db eval {SELECT * FROM t4}457} {1 2 5}458do_test tcl-10.11 {459 for {set i 0} {$i<10} {incr i} {460 db transaction {461 db eval {INSERT INTO t4 VALUES(6)}462 break463 }464 }465 db eval {SELECT * FROM t4}466} {1 2 5 6}467do_test tcl-10.12 {468 set rc [catch {469 for {set i 0} {$i<10} {incr i} {470 db transaction {471 db eval {INSERT INTO t4 VALUES(7)}472 return473 }474 }475 }]476} {2}477do_test tcl-10.13 {478 db eval {SELECT * FROM t4}479} {1 2 5 6 7}480 481# Now test that [db transaction] commands may be nested with482# the expected results.483#484do_test tcl-10.14 {485 db transaction {486 db eval {487 DELETE FROM t4;488 INSERT INTO t4 VALUES('one');489 }490 491 catch {492 db transaction {493 db eval { INSERT INTO t4 VALUES('two') }494 db transaction {495 db eval { INSERT INTO t4 VALUES('three') }496 error "throw an error!"497 }498 }499 }500 }501 502 db eval {SELECT * FROM t4}503} {one}504do_test tcl-10.15 {505 # Make sure a transaction has not been left open.506 db eval {BEGIN ; COMMIT}507} {}508do_test tcl-10.16 {509 db transaction {510 db eval { INSERT INTO t4 VALUES('two'); }511 db transaction {512 db eval { INSERT INTO t4 VALUES('three') }513 db transaction {514 db eval { INSERT INTO t4 VALUES('four') }515 }516 }517 }518 db eval {SELECT * FROM t4}519} {one two three four}520do_test tcl-10.17 {521 catch {522 db transaction {523 db eval { INSERT INTO t4 VALUES('A'); }524 db transaction {525 db eval { INSERT INTO t4 VALUES('B') }526 db transaction {527 db eval { INSERT INTO t4 VALUES('C') }528 error "throw an error!"529 }530 }531 }532 }533 db eval {SELECT * FROM t4}534} {one two three four}535do_test tcl-10.18 {536 # Make sure a transaction has not been left open.537 db eval {BEGIN ; COMMIT}538} {}539 540# Mess up a [db transaction] command by locking the database using a541# second connection when it tries to commit. Make sure the transaction542# is not still open after the "database is locked" exception is thrown.543#544do_test tcl-10.18 {545 sqlite3 db2 test.db546 db2 eval {547 BEGIN;548 SELECT * FROM sqlite_master;549 }550 551 set rc [catch {552 db transaction {553 db eval {INSERT INTO t4 VALUES('five')}554 }555 } msg]556 list $rc $msg557} {1 {database is locked}}558do_test tcl-10.19 {559 db eval {BEGIN ; COMMIT}560} {}561 562# Thwart a [db transaction] command by locking the database using a563# second connection with "BEGIN EXCLUSIVE". Make sure no transaction is 564# open after the "database is locked" exception is thrown.565#566do_test tcl-10.20 {567 db2 eval {568 COMMIT;569 BEGIN EXCLUSIVE;570 }571 set rc [catch {572 db transaction {573 db eval {INSERT INTO t4 VALUES('five')}574 }575 } msg]576 list $rc $msg577} {1 {database is locked}}578do_test tcl-10.21 {579 db2 close580 db eval {BEGIN ; COMMIT}581} {}582do_test tcl-10.22 {583 sqlite3 db2 test.db584 db transaction exclusive {585 catch { db2 eval {SELECT * FROM sqlite_master} } msg586 set msg "db2: $msg"587 }588 set msg589} {db2: database is locked}590db2 close591 592do_test tcl-11.1 {593 db eval {INSERT INTO t4 VALUES(6)}594 db exists {SELECT x,x*2,x+x FROM t4 WHERE x==6}595} {1}596do_test tcl-11.2 {597 db exists {SELECT 0 FROM t4 WHERE x==6}598} {1}599do_test tcl-11.3 {600 db exists {SELECT 1 FROM t4 WHERE x==8}601} {0}602do_test tcl-11.3.1 {603 tcl_objproc db exists {SELECT 1 FROM t4 WHERE x==8}604} {0}605 606do_test tcl-12.1 {607 unset -nocomplain a b c version608 set version [db version]609 scan $version "%d.%d.%d" a b c610 expr $a*1000000 + $b*1000 + $c611} [sqlite3_libversion_number]612 613 614# Check to see that when bindings of the form @aaa are used instead615# of $aaa, that objects are treated as bytearray and are inserted616# as BLOBs.617#618ifcapable tclvar {619 do_test tcl-13.1 {620 db eval {CREATE TABLE t5(x BLOB)}621 set x abc123622 db eval {INSERT INTO t5 VALUES($x)}623 db eval {SELECT typeof(x) FROM t5}624 } {text}625 do_test tcl-13.2 {626 binary scan $x H notUsed627 db eval {628 DELETE FROM t5;629 INSERT INTO t5 VALUES($x);630 SELECT typeof(x) FROM t5;631 }632 } {text}633 do_test tcl-13.3 {634 db eval {635 DELETE FROM t5;636 INSERT INTO t5 VALUES(@x);637 SELECT typeof(x) FROM t5;638 }639 } {blob}640 do_test tcl-13.4 {641 set y 1234642 db eval {643 DELETE FROM t5;644 INSERT INTO t5 VALUES(@y);645 SELECT hex(x), typeof(x) FROM t5646 }647 } {31323334 blob}648}649 650db func xCall xCall651proc xCall {} { return "value" }652do_execsql_test tcl-14.1 {653 CREATE TABLE t6(x);654 INSERT INTO t6 VALUES(1);655}656do_test tcl-14.2 {657 db one {SELECT x FROM t6 WHERE xCall()!='value'}658} {}659 660# Verify that the "exists" and "onecolumn" methods work when661# a "profile" is registered.662#663catch {db close}664sqlite3 db :memory:665proc noop-profile {args} {666 return667}668do_test tcl-15.0 {669 db eval {CREATE TABLE t1(a); INSERT INTO t1 VALUES(1),(2),(3);}670 db onecolumn {SELECT a FROM t1 WHERE a>2}671} {3}672do_test tcl-15.1 {673 db exists {SELECT a FROM t1 WHERE a>2}674} {1}675do_test tcl-15.2 {676 db exists {SELECT a FROM t1 WHERE a>3}677} {0}678db profile noop-profile679do_test tcl-15.3 {680 db onecolumn {SELECT a FROM t1 WHERE a>2}681} {3}682do_test tcl-15.4 {683 db exists {SELECT a FROM t1 WHERE a>2}684} {1}685do_test tcl-15.5 {686 db exists {SELECT a FROM t1 WHERE a>3}687} {0}688 689 690# 2017-06-26: The -withoutnulls flag to "db eval".691#692# In the "db eval -withoutnulls SQL TARGET" form, NULL results cause the693# corresponding target entry to be unset. The default behavior (without694# the -withoutnulls flags) is for the corresponding target value to get695# the [db nullvalue] string.696#697catch {db close}698forcedelete test.db699sqlite3 db test.db700do_execsql_test tcl-16.100 {701 CREATE TABLE t1(a,b);702 INSERT INTO t1 VALUES(1,2),(2,NULL),(3,'xyz');703}704do_test tcl-16.101 {705 set res {}706 unset -nocomplain x707 db eval {SELECT * FROM t1} x {708 lappend res $x(a) [array names x]709 }710 set res711} {1 {a b *} 2 {a b *} 3 {a b *}}712do_test tcl-16.102 {713 set res [catch {714 db eval -unknown {SELECT * FROM t1} x {715 lappend res $x(a) [array names x]716 }717 } rc]718 lappend res $rc719} {1 {unknown option: "-unknown"}}720do_test tcl-16.103 {721 set res {}722 unset -nocomplain x723 db eval -withoutnulls {SELECT * FROM t1} x {724 lappend res $x(a) [array names x]725 }726 set res727} {1 {a b *} 2 {a *} 3 {a b *}}728 729#-------------------------------------------------------------------------730# Test the -type option to [db function].731#732reset_db733proc add {a b} { return [expr $a + $b] }734proc ret {a} { return $a }735 736db function add_i -returntype integer add737db function add_r -ret real add738db function add_t -return text add739db function add_b -returntype blob add740db function add_a -returntype any add741 742db function ret_i -returntype int ret743db function ret_r -returntype real ret744db function ret_t -returntype text ret745db function ret_b -returntype blob ret746db function ret_a -r any ret747 748do_execsql_test 17.0 {749 SELECT quote( add_i(2, 3) );750 SELECT quote( add_r(2, 3) );751 SELECT quote( add_t(2, 3) );752 SELECT quote( add_b(2, 3) );753 SELECT quote( add_a(2, 3) );754} {5 5.0 '5' X'35' 5}755 756do_execsql_test 17.1 {757 SELECT quote( add_i(2.2, 3.3) );758 SELECT quote( add_r(2.2, 3.3) );759 SELECT quote( add_t(2.2, 3.3) );760 SELECT quote( add_b(2.2, 3.3) );761 SELECT quote( add_a(2.2, 3.3) );762} {5.5 5.5 '5.5' X'352E35' 5.5}763 764do_execsql_test 17.2 {765 SELECT quote( ret_i(2.5) );766 SELECT quote( ret_r(2.5) );767 SELECT quote( ret_t(2.5) );768 SELECT quote( ret_b(2.5) );769 SELECT quote( ret_a(2.5) );770} {2.5 2.5 '2.5' X'322E35' 2.5}771 772do_execsql_test 17.3 {773 SELECT quote( ret_i('2.5') );774 SELECT quote( ret_r('2.5') );775 SELECT quote( ret_t('2.5') );776 SELECT quote( ret_b('2.5') );777 SELECT quote( ret_a('2.5') );778} {2.5 2.5 '2.5' X'322E35' '2.5'}779 780do_execsql_test 17.4 {781 SELECT quote( ret_i('abc') );782 SELECT quote( ret_r('abc') );783 SELECT quote( ret_t('abc') );784 SELECT quote( ret_b('abc') );785 SELECT quote( ret_a('abc') );786} {'abc' 'abc' 'abc' X'616263' 'abc'}787 788do_execsql_test 17.5 {789 SELECT quote( ret_i(X'616263') );790 SELECT quote( ret_r(X'616263') );791 SELECT quote( ret_t(X'616263') );792 SELECT quote( ret_b(X'616263') );793 SELECT quote( ret_a(X'616263') );794} {'abc' 'abc' 'abc' X'616263' X'616263'}795 796do_test 17.6.1 {797 list [catch { db function xyz -return object ret } msg] $msg798} {1 {bad type "object": must be integer, real, text, blob, or any}}799 800do_test 17.6.2 {801 list [catch { db function xyz -return ret } msg] $msg802} {1 {option requires an argument: -return}}803 804do_test 17.6.3 {805 list [catch { db function xyz -n object ret } msg] $msg806} {1 {bad option "-n": must be -argcount, -deterministic, -directonly, -innocuous, or -returntype}}807 808# 2019-02-28: The "bind_fallback" command.809#810do_test 18.100 {811 unset -nocomplain bindings abc def ghi jkl mno e01 e02812 set bindings(abc) [expr {1+2}]813 set bindings(def) {hello}814 set bindings(ghi) [expr {3.1415926*1.0}]815 proc bind_callback {nm} {816 global bindings817 set n2 [string range $nm 1 end]818 if {[info exists bindings($n2)]} {819 return $bindings($n2)820 }821 if {[string match e* $n2]} {822 error "no such variable: $nm"823 }824 return -code return {}825 }826 db bind_fallback bind_callback827 db eval {SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi)}828} {3 integer hello text 3.1415926 real}829do_test 18.110 {830 db eval {SELECT quote(@def), typeof(@def)}831} {X'68656C6C6F' blob}832do_execsql_test 18.120 {833 SELECT typeof($mno);834} {null}835do_catchsql_test 18.130 {836 SELECT $e01;837} {1 {no such variable: $e01}}838do_test 18.140 {839 db bind_fallback840} {bind_callback}841do_test 18.200 {842 db bind_fallback {}843 db eval {SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi)}844} {{} null {} null {} null}845do_test 18.300 {846 unset -nocomplain bindings847 proc bind_callback {nm} {lappend ::bindings $nm}848 db bind_fallback bind_callback849 db eval {SELECT $abc, @def, $ghi(123), :mno}850 set bindings851} {{$abc} @def {$ghi(123)} :mno}852do_test 18.900 {853 set rc [catch {db bind_fallback a b} msg]854 lappend rc $msg855} {1 {wrong # args: should be "db bind_fallback ?CALLBACK?"}}856do_test 18.910 {857 db bind_fallback bind_fallback_does_not_exist858} {}859do_catchsql_test 19.911 {860 SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi);861} {1 {invalid command name "bind_fallback_does_not_exist"}}862db bind_fallback {}863 864# 2025-05-05: the -asdict eval flag865#866do_test 20.0 {867 execsql {CREATE TABLE tad(a,b)}868 execsql {INSERT INTO tad(a,b) VALUES('aa','bb'),('AA','BB')}869 db eval -asdict {870 SELECT a, b FROM tad WHERE 0871 } D {}872 set D873} {* {a b}}874 875do_test 20.1 {876 unset D877 set i 0878 set res {}879 set colNames {}880 db eval -asdict {881 SELECT a, b FROM tad ORDER BY a882 } D {883 dict set D i [incr i]884 lappend res $i [dict get $D a] [dict get $D b]885 if {1 == $i} {886 set colNames [dict get $D *]887 }888 }889 lappend res $colNames890 unset D891 set res892} {1 AA BB 2 aa bb {a b}}893 894do_test 20.2 {895 set res {}896 db eval -asdict -withoutnulls {897 SELECT n, a, b FROM (898 SELECT 1 as n, 'aa' as a, NULL as b899 UNION ALL900 SELECT 2 as n, NULL as a, 'bb' as b901 )902 ORDER BY n903 } D {904 dict unset D *905 lappend res [dict values $D]906 }907 unset D908 execsql {DROP TABLE tad}909 set res910} {{1 aa} {2 bb}}911 912#-------------------------------------------------------------------------913do_test 21.0 {914 db transaction {915 db close916 }917} {}918 919do_test 21.1 {920 sqlite3 db test.db921 set rc [catch {922 db eval {SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3} { db close }923 } msg]924 list $rc $msg925} {1 {invalid command name "db"}}926 927 928 929proc closedb {} {930 db close931 return 10932}933proc func1 {} { return 1 }934 935sqlite3 db test.db936db func closedb closedb937db func func1 func1938 939do_test 21.2 {940 set rc [catch {941 db eval {942 SELECT closedb(),func1() UNION ALL SELECT 20,30 UNION ALL SELECT 30,40943 }944 } msg]945 list $rc $msg946} {0 {10 1 20 30 30 40}}947 948sqlite3 db :memory:949do_test 22.1 {950 catch {db eval {SELECT 1 2 3;}} msg951 db erroroffset952} {9}953 954 955finish_test956