1# 2003 September 6 2# 3# The author disclaims copyright to this source code. In place of 4# 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 SQLite library. The 12# focus of this script testing the sqlite_bind API. 13# 14# $Id: bind.test,v 1.38 2006/06/27 20:06:45 drh Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20proc sqlite_step {stmt N VALS COLS} { 21 upvar VALS vals 22 upvar COLS cols 23 set vals [list] 24 set cols [list] 25 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] 29 } 30 for {set i 0} {$i < [sqlite3_data_count $stmt]} {incr i} { 31 lappend vals [sqlite3_column_text $stmt $i] 32 } 33 34 return $rc 35} 36 37do_test bind-1.1 { 38 set DB [sqlite3_connection_pointer db] 39 execsql {CREATE TABLE t1(a,b,c);} 40 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL] 41 set TAIL 42} {} 43do_test bind-1.1.1 { 44 sqlite3_bind_parameter_count $VM 45} 3 46do_test bind-1.1.2 { 47 sqlite3_bind_parameter_name $VM 1 48} {:1} 49do_test bind-1.1.3 { 50 sqlite3_bind_parameter_name $VM 2 51} {} 52do_test bind-1.1.4 { 53 sqlite3_bind_parameter_name $VM 3 54} {:abc} 55do_test bind-1.2 { 56 sqlite_step $VM N VALUES COLNAMES 57} {SQLITE_DONE} 58do_test bind-1.3 { 59 execsql {SELECT rowid, * FROM t1} 60} {1 {} {} {}} 61do_test bind-1.4 { 62 sqlite3_reset $VM 63 sqlite_bind $VM 1 {test value 1} normal 64 sqlite_step $VM N VALUES COLNAMES 65} SQLITE_DONE 66do_test bind-1.5 { 67 execsql {SELECT rowid, * FROM t1} 68} {1 {} {} {} 2 {test value 1} {} {}} 69do_test bind-1.6 { 70 sqlite3_reset $VM 71 sqlite_bind $VM 3 {'test value 2'} normal 72 sqlite_step $VM N VALUES COLNAMES 73} SQLITE_DONE 74do_test bind-1.7 { 75 execsql {SELECT rowid, * FROM t1} 76} {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}} 77do_test bind-1.8 { 78 sqlite3_reset $VM 79 set sqlite_static_bind_value 123 80 sqlite_bind $VM 1 {} static 81 sqlite_bind $VM 2 {abcdefg} normal 82 sqlite_bind $VM 3 {} null 83 execsql {DELETE FROM t1} 84 sqlite_step $VM N VALUES COLNAMES 85 execsql {SELECT rowid, * FROM t1} 86} {1 123 abcdefg {}} 87do_test bind-1.9 { 88 sqlite3_reset $VM 89 sqlite_bind $VM 1 {456} normal 90 sqlite_step $VM N VALUES COLNAMES 91 execsql {SELECT rowid, * FROM t1} 92} {1 123 abcdefg {} 2 456 abcdefg {}} 93 94do_test bind-1.99 { 95 sqlite3_finalize $VM 96} SQLITE_OK 97 98# Prepare the statement in different ways depending on whether or not 99# the $var processing is compiled into the library. 100# 101ifcapable {tclvar} { 102 do_test bind-2.1 { 103 execsql { 104 DELETE FROM t1; 105 } 106 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,$x(-z-))}\ 107 -1 TX] 108 set TX 109 } {} 110 set v1 {$one} 111 set v2 {$::two} 112 set v3 {$x(-z-)} 113} 114ifcapable {!tclvar} { 115 do_test bind-2.1 { 116 execsql { 117 DELETE FROM t1; 118 } 119 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX] 120 set TX 121 } {} 122 set v1 {:one} 123 set v2 {:two} 124 set v3 {:_} 125} 126 127do_test bind-2.1.1 { 128 sqlite3_bind_parameter_count $VM 129} 3 130do_test bind-2.1.2 { 131 sqlite3_bind_parameter_name $VM 1 132} $v1 133do_test bind-2.1.3 { 134 sqlite3_bind_parameter_name $VM 2 135} $v2 136do_test bind-2.1.4 { 137 sqlite3_bind_parameter_name $VM 3 138} $v3 139do_test bind-2.1.5 { 140 sqlite3_bind_parameter_index $VM $v1 141} 1 142do_test bind-2.1.6 { 143 sqlite3_bind_parameter_index $VM $v2 144} 2 145do_test bind-2.1.7 { 146 sqlite3_bind_parameter_index $VM $v3 147} 3 148do_test bind-2.1.8 { 149 sqlite3_bind_parameter_index $VM {:hi} 150} 0 151 152# 32 bit Integers 153do_test bind-2.2 { 154 sqlite3_bind_int $VM 1 123 155 sqlite3_bind_int $VM 2 456 156 sqlite3_bind_int $VM 3 789 157 sqlite_step $VM N VALUES COLNAMES 158 sqlite3_reset $VM 159 execsql {SELECT rowid, * FROM t1} 160} {1 123 456 789} 161do_test bind-2.3 { 162 sqlite3_bind_int $VM 2 -2000000000 163 sqlite3_bind_int $VM 3 2000000000 164 sqlite_step $VM N VALUES COLNAMES 165 sqlite3_reset $VM 166 execsql {SELECT rowid, * FROM t1} 167} {1 123 456 789 2 123 -2000000000 2000000000} 168do_test bind-2.4 { 169 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 170} {integer integer integer integer integer integer} 171do_test bind-2.5 { 172 execsql { 173 DELETE FROM t1; 174 } 175} {} 176 177# 64 bit Integers 178do_test bind-3.1 { 179 sqlite3_bind_int64 $VM 1 32 180 sqlite3_bind_int64 $VM 2 -2000000000000 181 sqlite3_bind_int64 $VM 3 2000000000000 182 sqlite_step $VM N VALUES COLNAMES 183 sqlite3_reset $VM 184 execsql {SELECT rowid, * FROM t1} 185} {1 32 -2000000000000 2000000000000} 186do_test bind-3.2 { 187 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 188} {integer integer integer} 189do_test bind-3.3 { 190 execsql { 191 DELETE FROM t1; 192 } 193} {} 194 195# Doubles 196do_test bind-4.1 { 197 sqlite3_bind_double $VM 1 1234.1234 198 sqlite3_bind_double $VM 2 0.00001 199 sqlite3_bind_double $VM 3 123456789 200 sqlite_step $VM N VALUES COLNAMES 201 sqlite3_reset $VM 202 set x [execsql {SELECT rowid, * FROM t1}] 203 regsub {1e-005} $x {1e-05} y 204 set y 205} {1 1234.1234 1e-05 123456789.0} 206do_test bind-4.2 { 207 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 208} {real real real} 209do_test bind-4.3 { 210 execsql { 211 DELETE FROM t1; 212 } 213} {} 214 215# NULL 216do_test bind-5.1 { 217 sqlite3_bind_null $VM 1 218 sqlite3_bind_null $VM 2 219 sqlite3_bind_null $VM 3 220 sqlite_step $VM N VALUES COLNAMES 221 sqlite3_reset $VM 222 execsql {SELECT rowid, * FROM t1} 223} {1 {} {} {}} 224do_test bind-5.2 { 225 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 226} {null null null} 227do_test bind-5.3 { 228 execsql { 229 DELETE FROM t1; 230 } 231} {} 232 233# UTF-8 text 234do_test bind-6.1 { 235 sqlite3_bind_text $VM 1 hellothere 5 236 sqlite3_bind_text $VM 2 ".." 1 237 sqlite3_bind_text $VM 3 world -1 238 sqlite_step $VM N VALUES COLNAMES 239 sqlite3_reset $VM 240 execsql {SELECT rowid, * FROM t1} 241} {1 hello . world} 242do_test bind-6.2 { 243 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 244} {text text text} 245do_test bind-6.3 { 246 execsql { 247 DELETE FROM t1; 248 } 249} {} 250 251# UTF-16 text 252ifcapable {utf16} { 253 do_test bind-7.1 { 254 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10 255 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0 256 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10 257 sqlite_step $VM N VALUES COLNAMES 258 sqlite3_reset $VM 259 execsql {SELECT rowid, * FROM t1} 260 } {1 hello {} world} 261 do_test bind-7.2 { 262 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 263 } {text text text} 264} 265do_test bind-7.3 { 266 execsql { 267 DELETE FROM t1; 268 } 269} {} 270 271# Test that the 'out of range' error works. 272do_test bind-8.1 { 273 catch { sqlite3_bind_null $VM 0 } 274} {1} 275do_test bind-8.2 { 276 sqlite3_errmsg $DB 277} {bind or column index out of range} 278ifcapable {utf16} { 279 do_test bind-8.3 { 280 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 281 } {bind or column index out of range} 282} 283do_test bind-8.4 { 284 sqlite3_bind_null $VM 1 285 sqlite3_errmsg $DB 286} {not an error} 287do_test bind-8.5 { 288 catch { sqlite3_bind_null $VM 4 } 289} {1} 290do_test bind-8.6 { 291 sqlite3_errmsg $DB 292} {bind or column index out of range} 293ifcapable {utf16} { 294 do_test bind-8.7 { 295 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 296 } {bind or column index out of range} 297} 298 299do_test bind-8.8 { 300 catch { sqlite3_bind_blob $VM 0 "abc" 3 } 301} {1} 302do_test bind-8.9 { 303 catch { sqlite3_bind_blob $VM 4 "abc" 3 } 304} {1} 305do_test bind-8.10 { 306 catch { sqlite3_bind_text $VM 0 "abc" 3 } 307} {1} 308ifcapable {utf16} { 309 do_test bind-8.11 { 310 catch { sqlite3_bind_text16 $VM 4 "abc" 2 } 311 } {1} 312} 313do_test bind-8.12 { 314 catch { sqlite3_bind_int $VM 0 5 } 315} {1} 316do_test bind-8.13 { 317 catch { sqlite3_bind_int $VM 4 5 } 318} {1} 319do_test bind-8.14 { 320 catch { sqlite3_bind_double $VM 0 5.0 } 321} {1} 322do_test bind-8.15 { 323 catch { sqlite3_bind_double $VM 4 6.0 } 324} {1} 325 326do_test bind-8.99 { 327 sqlite3_finalize $VM 328} SQLITE_OK 329 330do_test bind-9.1 { 331 execsql { 332 CREATE TABLE t2(a,b,c,d,e,f); 333 } 334 set rc [catch { 335 sqlite3_prepare $DB { 336 INSERT INTO t2(a) VALUES(?0) 337 } -1 TAIL 338 } msg] 339 lappend rc $msg 340} {1 {(1) variable number must be between ?1 and ?999}} 341do_test bind-9.2 { 342 set rc [catch { 343 sqlite3_prepare $DB { 344 INSERT INTO t2(a) VALUES(?1000) 345 } -1 TAIL 346 } msg] 347 lappend rc $msg 348} {1 {(1) variable number must be between ?1 and ?999}} 349do_test bind-9.3 { 350 set VM [ 351 sqlite3_prepare $DB { 352 INSERT INTO t2(a,b) VALUES(?1,?999) 353 } -1 TAIL 354 ] 355 sqlite3_bind_parameter_count $VM 356} {999} 357catch {sqlite3_finalize $VM} 358do_test bind-9.4 { 359 set VM [ 360 sqlite3_prepare $DB { 361 INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?) 362 } -1 TAIL 363 ] 364 sqlite3_bind_parameter_count $VM 365} {1001} 366do_test bind-9.5 { 367 sqlite3_bind_int $VM 1 1 368 sqlite3_bind_int $VM 999 999 369 sqlite3_bind_int $VM 1000 1000 370 sqlite3_bind_int $VM 1001 1001 371 sqlite3_step $VM 372} SQLITE_DONE 373do_test bind-9.6 { 374 sqlite3_finalize $VM 375} SQLITE_OK 376do_test bind-9.7 { 377 execsql {SELECT * FROM t2} 378} {1 999 1000 1001 {} {}} 379 380ifcapable {tclvar} { 381 do_test bind-10.1 { 382 set VM [ 383 sqlite3_prepare $DB { 384 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc) 385 } -1 TAIL 386 ] 387 sqlite3_bind_parameter_count $VM 388 } 3 389 set v1 {$abc} 390 set v2 {$ab} 391} 392ifcapable {!tclvar} { 393 do_test bind-10.1 { 394 set VM [ 395 sqlite3_prepare $DB { 396 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc) 397 } -1 TAIL 398 ] 399 sqlite3_bind_parameter_count $VM 400 } 3 401 set v1 {:xyz} 402 set v2 {:xy} 403} 404do_test bind-10.2 { 405 sqlite3_bind_parameter_index $VM :abc 406} 1 407do_test bind-10.3 { 408 sqlite3_bind_parameter_index $VM $v1 409} 2 410do_test bind-10.4 { 411 sqlite3_bind_parameter_index $VM $v2 412} 3 413do_test bind-10.5 { 414 sqlite3_bind_parameter_name $VM 1 415} :abc 416do_test bind-10.6 { 417 sqlite3_bind_parameter_name $VM 2 418} $v1 419do_test bind-10.7 { 420 sqlite3_bind_parameter_name $VM 3 421} $v2 422do_test bind-10.7.1 { 423 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL 424} {} 425do_test bind-10.7.2 { 426 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small 427} {} 428do_test bind-10.7.3 { 429 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big 430} {} 431do_test bind-10.8 { 432 sqlite3_bind_int $VM 1 1 433 sqlite3_bind_int $VM 2 2 434 sqlite3_bind_int $VM 3 3 435 sqlite3_step $VM 436} SQLITE_DONE 437do_test bind-10.8.1 { 438 # Binding attempts after program start should fail 439 set rc [catch { 440 sqlite3_bind_int $VM 1 1 441 } msg] 442 lappend rc $msg 443} {1 {}} 444do_test bind-10.9 { 445 sqlite3_finalize $VM 446} SQLITE_OK 447do_test bind-10.10 { 448 execsql {SELECT * FROM t2} 449} {1 999 1000 1001 {} {} 1 2 1 3 2 1} 450 451# Ticket #918 452# 453do_test bind-10.11 { 454 # catch {sqlite3_finalize $VM} 455 set VM [ 456 sqlite3_prepare $DB { 457 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4) 458 } -1 TAIL 459 ] 460 sqlite3_bind_parameter_count $VM 461} 5 462do_test bind-10.11.1 { 463 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments 464} 0 465do_test bind-10.12 { 466 sqlite3_bind_parameter_index $VM :xyz 467} 0 468do_test bind-10.13 { 469 sqlite3_bind_parameter_index $VM {} 470} 0 471do_test bind-10.14 { 472 sqlite3_bind_parameter_index $VM :pqr 473} 5 474do_test bind-10.15 { 475 sqlite3_bind_parameter_index $VM ?4 476} 4 477do_test bind-10.16 { 478 sqlite3_bind_parameter_name $VM 1 479} :abc 480do_test bind-10.17 { 481 sqlite3_bind_parameter_name $VM 2 482} {} 483do_test bind-10.18 { 484 sqlite3_bind_parameter_name $VM 3 485} {} 486do_test bind-10.19 { 487 sqlite3_bind_parameter_name $VM 4 488} {?4} 489do_test bind-10.20 { 490 sqlite3_bind_parameter_name $VM 5 491} :pqr 492catch {sqlite3_finalize $VM} 493 494# Make sure we catch an unterminated "(" in a Tcl-style variable name 495# 496ifcapable tclvar { 497 do_test bind-11.1 { 498 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;} 499 } {1 {unrecognized token: "$abc(123"}} 500} 501 502if {[execsql {pragma encoding}]=="UTF-8"} { 503 # Test the ability to bind text that contains embedded '\000' characters. 504 # Make sure we can recover the entire input string. 505 # 506 do_test bind-12.1 { 507 execsql { 508 CREATE TABLE t3(x BLOB); 509 } 510 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL] 511 sqlite_bind $VM 1 not-used blob10 512 sqlite3_step $VM 513 sqlite3_finalize $VM 514 execsql { 515 SELECT typeof(x), length(x), quote(x), 516 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3 517 } 518 } {text 3 'abc' 10 X'6162630078797A007071'} 519 do_test bind-12.2 { 520 sqlite3_create_function $DB 521 execsql { 522 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3 523 } 524 } {X'6162630078797A007071'} 525} 526 527# Test the operation of sqlite3_clear_bindings 528# 529do_test bind-13.1 { 530 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL] 531 sqlite3_step $VM 532 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 533 [sqlite3_column_type $VM 2] 534} {NULL NULL NULL} 535do_test bind-13.2 { 536 sqlite3_reset $VM 537 sqlite3_bind_int $VM 1 1 538 sqlite3_bind_int $VM 2 2 539 sqlite3_bind_int $VM 3 3 540 sqlite3_step $VM 541 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 542 [sqlite3_column_type $VM 2] 543} {INTEGER INTEGER INTEGER} 544do_test bind-13.3 { 545 sqlite3_reset $VM 546 sqlite3_step $VM 547 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 548 [sqlite3_column_type $VM 2] 549} {INTEGER INTEGER INTEGER} 550do_test bind-13.4 { 551 sqlite3_reset $VM 552 sqlite3_clear_bindings $VM 553 sqlite3_step $VM 554 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 555 [sqlite3_column_type $VM 2] 556} {NULL NULL NULL} 557sqlite3_finalize $VM 558 559finish_test 560