3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this script testing the sqlite_bind API.
14 # $Id: bind.test,v 1.31 2005/03/20 23:18:58 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 proc sqlite_step {stmt N VALS COLS} {
26 set rc [sqlite3_step $stmt]
27 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
28 lappend cols [sqlite3_column_name $stmt $i]
30 for {set i 0} {$i < [sqlite3_data_count $stmt]} {incr i} {
31 lappend vals [sqlite3_column_text $stmt $i]
39 set DB [sqlite3 db test.db]
40 execsql {CREATE TABLE t1(a,b,c);}
41 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL]
45 sqlite3_bind_parameter_count $VM
48 sqlite3_bind_parameter_name $VM 1
51 sqlite3_bind_parameter_name $VM 2
54 sqlite3_bind_parameter_name $VM 3
57 sqlite_step $VM N VALUES COLNAMES
60 execsql {SELECT rowid, * FROM t1}
64 sqlite_bind $VM 1 {test value 1} normal
65 sqlite_step $VM N VALUES COLNAMES
68 execsql {SELECT rowid, * FROM t1}
69 } {1 {} {} {} 2 {test value 1} {} {}}
72 sqlite_bind $VM 3 {'test value 2'} normal
73 sqlite_step $VM N VALUES COLNAMES
76 execsql {SELECT rowid, * FROM t1}
77 } {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}}
80 set sqlite_static_bind_value 123
81 sqlite_bind $VM 1 {} static
82 sqlite_bind $VM 2 {abcdefg} normal
83 sqlite_bind $VM 3 {} null
84 execsql {DELETE FROM t1}
85 sqlite_step $VM N VALUES COLNAMES
86 execsql {SELECT rowid, * FROM t1}
90 sqlite_bind $VM 1 {456} normal
91 sqlite_step $VM N VALUES COLNAMES
92 execsql {SELECT rowid, * FROM t1}
93 } {1 123 abcdefg {} 2 456 abcdefg {}}
99 # Prepare the statement in different ways depending on whether or not
100 # the $var processing is compiled into the library.
107 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,${x{y}z})}\
115 ifcapable {!tclvar} {
120 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX]
129 sqlite3_bind_parameter_count $VM
132 sqlite3_bind_parameter_name $VM 1
135 sqlite3_bind_parameter_name $VM 2
138 sqlite3_bind_parameter_name $VM 3
141 sqlite3_bind_parameter_index $VM $v1
144 sqlite3_bind_parameter_index $VM $v2
147 sqlite3_bind_parameter_index $VM $v3
150 sqlite3_bind_parameter_index $VM {:hi}
155 sqlite3_bind_int $VM 1 123
156 sqlite3_bind_int $VM 2 456
157 sqlite3_bind_int $VM 3 789
158 sqlite_step $VM N VALUES COLNAMES
160 execsql {SELECT rowid, * FROM t1}
163 sqlite3_bind_int $VM 2 -2000000000
164 sqlite3_bind_int $VM 3 2000000000
165 sqlite_step $VM N VALUES COLNAMES
167 execsql {SELECT rowid, * FROM t1}
168 } {1 123 456 789 2 123 -2000000000 2000000000}
170 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
171 } {integer integer integer integer integer integer}
180 sqlite3_bind_int64 $VM 1 32
181 sqlite3_bind_int64 $VM 2 -2000000000000
182 sqlite3_bind_int64 $VM 3 2000000000000
183 sqlite_step $VM N VALUES COLNAMES
185 execsql {SELECT rowid, * FROM t1}
186 } {1 32 -2000000000000 2000000000000}
188 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
189 } {integer integer integer}
198 sqlite3_bind_double $VM 1 1234.1234
199 sqlite3_bind_double $VM 2 0.00001
200 sqlite3_bind_double $VM 3 123456789
201 sqlite_step $VM N VALUES COLNAMES
203 set x [execsql {SELECT rowid, * FROM t1}]
204 regsub {1e-005} $x {1e-05} y
206 } {1 1234.1234 1e-05 123456789.0}
208 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
218 sqlite3_bind_null $VM 1
219 sqlite3_bind_null $VM 2
220 sqlite3_bind_null $VM 3
221 sqlite_step $VM N VALUES COLNAMES
223 execsql {SELECT rowid, * FROM t1}
226 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
236 sqlite3_bind_text $VM 1 hellothere 5
237 sqlite3_bind_text $VM 2 ".." 1
238 sqlite3_bind_text $VM 3 world -1
239 sqlite_step $VM N VALUES COLNAMES
241 execsql {SELECT rowid, * FROM t1}
244 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
255 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
256 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
257 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
258 sqlite_step $VM N VALUES COLNAMES
260 execsql {SELECT rowid, * FROM t1}
263 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
272 # Test that the 'out of range' error works.
274 catch { sqlite3_bind_null $VM 0 }
278 } {bind or column index out of range}
281 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
282 } {bind or column index out of range}
285 sqlite3_bind_null $VM 1
289 catch { sqlite3_bind_null $VM 4 }
293 } {bind or column index out of range}
296 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
297 } {bind or column index out of range}
301 catch { sqlite3_bind_blob $VM 0 "abc" 3 }
304 catch { sqlite3_bind_blob $VM 4 "abc" 3 }
307 catch { sqlite3_bind_text $VM 0 "abc" 3 }
311 catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
315 catch { sqlite3_bind_int $VM 0 5 }
318 catch { sqlite3_bind_int $VM 4 5 }
321 catch { sqlite3_bind_double $VM 0 5.0 }
324 catch { sqlite3_bind_double $VM 4 6.0 }
333 CREATE TABLE t2(a,b,c,d,e,f);
336 sqlite3_prepare $DB {
337 INSERT INTO t2(a) VALUES(?0)
341 } {1 {(1) variable number must be between ?1 and ?999}}
344 sqlite3_prepare $DB {
345 INSERT INTO t2(a) VALUES(?1000)
349 } {1 {(1) variable number must be between ?1 and ?999}}
352 sqlite3_prepare $DB {
353 INSERT INTO t2(a,b) VALUES(?1,?999)
356 sqlite3_bind_parameter_count $VM
358 catch {sqlite3_finalize $VM}
361 sqlite3_prepare $DB {
362 INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?)
365 sqlite3_bind_parameter_count $VM
368 sqlite3_bind_int $VM 1 1
369 sqlite3_bind_int $VM 999 999
370 sqlite3_bind_int $VM 1000 1000
371 sqlite3_bind_int $VM 1001 1001
378 execsql {SELECT * FROM t2}
379 } {1 999 1000 1001 {} {}}
384 sqlite3_prepare $DB {
385 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
388 sqlite3_bind_parameter_count $VM
393 ifcapable {!tclvar} {
396 sqlite3_prepare $DB {
397 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)
400 sqlite3_bind_parameter_count $VM
406 sqlite3_bind_parameter_index $VM :abc
409 sqlite3_bind_parameter_index $VM $v1
412 sqlite3_bind_parameter_index $VM $v2
415 sqlite3_bind_parameter_name $VM 1
418 sqlite3_bind_parameter_name $VM 2
421 sqlite3_bind_parameter_name $VM 3
423 do_test bind-10.7.1 {
424 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL
426 do_test bind-10.7.2 {
427 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small
429 do_test bind-10.7.3 {
430 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big
433 sqlite3_bind_int $VM 1 1
434 sqlite3_bind_int $VM 2 2
435 sqlite3_bind_int $VM 3 3
438 do_test bind-10.8.1 {
439 # Binding attempts after program start should fail
441 sqlite3_bind_int $VM 1 1
449 execsql {SELECT * FROM t2}
450 } {1 999 1000 1001 {} {} 1 2 1 3 2 1}
455 catch {sqlite3_finalize $VM}
457 sqlite3_prepare $DB {
458 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4)
461 sqlite3_bind_parameter_count $VM
463 do_test bind-10.11.1 {
464 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments
467 sqlite3_bind_parameter_index $VM :xyz
470 sqlite3_bind_parameter_index $VM {}
473 sqlite3_bind_parameter_index $VM :pqr
476 sqlite3_bind_parameter_index $VM ?4
479 sqlite3_bind_parameter_name $VM 1
482 sqlite3_bind_parameter_name $VM 2
485 sqlite3_bind_parameter_name $VM 3
488 sqlite3_bind_parameter_name $VM 4
491 sqlite3_bind_parameter_name $VM 5
493 catch {sqlite3_finalize $VM}
495 # Make sure we catch an unterminated "(" in a Tcl-style variable name
499 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;}
500 } {1 {unrecognized token: "$abc(123"}}