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.41 2008/04/16 12:58:54 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} {} 214do_test bind-4.4 { 215 sqlite3_bind_double $VM 1 NaN 216 sqlite3_bind_double $VM 2 1e300 217 sqlite3_bind_double $VM 3 -1e-300 218 sqlite_step $VM N VALUES COLNAMES 219 sqlite3_reset $VM 220 set x [execsql {SELECT rowid, * FROM t1}] 221 regsub {1e-005} $x {1e-05} y 222 set y 223} {1 {} 1e+300 -1e-300} 224do_test bind-4.5 { 225 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 226} {null real real} 227do_test bind-4.6 { 228 execsql { 229 DELETE FROM t1; 230 } 231} {} 232 233# NULL 234do_test bind-5.1 { 235 sqlite3_bind_null $VM 1 236 sqlite3_bind_null $VM 2 237 sqlite3_bind_null $VM 3 238 sqlite_step $VM N VALUES COLNAMES 239 sqlite3_reset $VM 240 execsql {SELECT rowid, * FROM t1} 241} {1 {} {} {}} 242do_test bind-5.2 { 243 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 244} {null null null} 245do_test bind-5.3 { 246 execsql { 247 DELETE FROM t1; 248 } 249} {} 250 251# UTF-8 text 252do_test bind-6.1 { 253 sqlite3_bind_text $VM 1 hellothere 5 254 sqlite3_bind_text $VM 2 ".." 1 255 sqlite3_bind_text $VM 3 world\000 -1 256 sqlite_step $VM N VALUES COLNAMES 257 sqlite3_reset $VM 258 execsql {SELECT rowid, * FROM t1} 259} {1 hello . world} 260do_test bind-6.2 { 261 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 262} {text text text} 263do_test bind-6.3 { 264 execsql { 265 DELETE FROM t1; 266 } 267} {} 268 269# Make sure zeros in a string work. 270# 271do_test bind-6.4 { 272 db eval {DELETE FROM t1} 273 sqlite3_bind_text $VM 1 hello\000there\000 12 274 sqlite3_bind_text $VM 2 hello\000there\000 11 275 sqlite3_bind_text $VM 3 hello\000there\000 -1 276 sqlite_step $VM N VALUES COLNAMES 277 sqlite3_reset $VM 278 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 279} {68656C6C6F00746865726500 68656C6C6F007468657265 68656C6C6F} 280do_test bind-6.5 { 281 execsql {SELECT * FROM t1} 282} {hello hello hello} 283do_test bind-6.6 { 284 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 285} {text text text} 286do_test bind-6.7 { 287 execsql { 288 DELETE FROM t1; 289 } 290} {} 291 292# UTF-16 text 293ifcapable {utf16} { 294 do_test bind-7.1 { 295 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10 296 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0 297 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10 298 sqlite_step $VM N VALUES COLNAMES 299 sqlite3_reset $VM 300 execsql {SELECT rowid, * FROM t1} 301 } {1 hello {} world} 302 do_test bind-7.2 { 303 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 304 } {text text text} 305 do_test bind-7.3 { 306 db eval {DELETE FROM t1} 307 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hi\000yall\000] 16 308 sqlite3_bind_text16 $VM 2 [encoding convertto unicode hi\000yall\000] 14 309 sqlite3_bind_text16 $VM 3 [encoding convertto unicode hi\000yall\000] -1 310 sqlite_step $VM N VALUES COLNAMES 311 sqlite3_reset $VM 312 execsql {SELECT * FROM t1} 313 } {hi hi hi} 314 do_test bind-7.4 { 315 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 316 } {68690079616C6C00 68690079616C6C 6869} 317 do_test bind-7.5 { 318 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 319 } {text text text} 320} 321do_test bind-7.99 { 322 execsql {DELETE FROM t1;} 323} {} 324 325# Test that the 'out of range' error works. 326do_test bind-8.1 { 327 catch { sqlite3_bind_null $VM 0 } 328} {1} 329do_test bind-8.2 { 330 sqlite3_errmsg $DB 331} {bind or column index out of range} 332ifcapable {utf16} { 333 do_test bind-8.3 { 334 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 335 } {bind or column index out of range} 336} 337do_test bind-8.4 { 338 sqlite3_bind_null $VM 1 339 sqlite3_errmsg $DB 340} {not an error} 341do_test bind-8.5 { 342 catch { sqlite3_bind_null $VM 4 } 343} {1} 344do_test bind-8.6 { 345 sqlite3_errmsg $DB 346} {bind or column index out of range} 347ifcapable {utf16} { 348 do_test bind-8.7 { 349 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 350 } {bind or column index out of range} 351} 352 353do_test bind-8.8 { 354 catch { sqlite3_bind_blob $VM 0 "abc" 3 } 355} {1} 356do_test bind-8.9 { 357 catch { sqlite3_bind_blob $VM 4 "abc" 3 } 358} {1} 359do_test bind-8.10 { 360 catch { sqlite3_bind_text $VM 0 "abc" 3 } 361} {1} 362ifcapable {utf16} { 363 do_test bind-8.11 { 364 catch { sqlite3_bind_text16 $VM 4 "abc" 2 } 365 } {1} 366} 367do_test bind-8.12 { 368 catch { sqlite3_bind_int $VM 0 5 } 369} {1} 370do_test bind-8.13 { 371 catch { sqlite3_bind_int $VM 4 5 } 372} {1} 373do_test bind-8.14 { 374 catch { sqlite3_bind_double $VM 0 5.0 } 375} {1} 376do_test bind-8.15 { 377 catch { sqlite3_bind_double $VM 4 6.0 } 378} {1} 379 380do_test bind-8.99 { 381 sqlite3_finalize $VM 382} SQLITE_OK 383 384do_test bind-9.1 { 385 execsql { 386 CREATE TABLE t2(a,b,c,d,e,f); 387 } 388 set rc [catch { 389 sqlite3_prepare $DB { 390 INSERT INTO t2(a) VALUES(?0) 391 } -1 TAIL 392 } msg] 393 lappend rc $msg 394} {1 {(1) variable number must be between ?1 and ?999}} 395do_test bind-9.2 { 396 set rc [catch { 397 sqlite3_prepare $DB { 398 INSERT INTO t2(a) VALUES(?1000) 399 } -1 TAIL 400 } msg] 401 lappend rc $msg 402} {1 {(1) variable number must be between ?1 and ?999}} 403do_test bind-9.3 { 404 set VM [ 405 sqlite3_prepare $DB { 406 INSERT INTO t2(a,b) VALUES(?1,?999) 407 } -1 TAIL 408 ] 409 sqlite3_bind_parameter_count $VM 410} {999} 411catch {sqlite3_finalize $VM} 412do_test bind-9.4 { 413 set VM [ 414 sqlite3_prepare $DB { 415 INSERT INTO t2(a,b,c,d) VALUES(?1,?997,?,?) 416 } -1 TAIL 417 ] 418 sqlite3_bind_parameter_count $VM 419} {999} 420do_test bind-9.5 { 421 sqlite3_bind_int $VM 1 1 422 sqlite3_bind_int $VM 997 999 423 sqlite3_bind_int $VM 998 1000 424 sqlite3_bind_int $VM 999 1001 425 sqlite3_step $VM 426} SQLITE_DONE 427do_test bind-9.6 { 428 sqlite3_finalize $VM 429} SQLITE_OK 430do_test bind-9.7 { 431 execsql {SELECT * FROM t2} 432} {1 999 1000 1001 {} {}} 433 434ifcapable {tclvar} { 435 do_test bind-10.1 { 436 set VM [ 437 sqlite3_prepare $DB { 438 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc) 439 } -1 TAIL 440 ] 441 sqlite3_bind_parameter_count $VM 442 } 3 443 set v1 {$abc} 444 set v2 {$ab} 445} 446ifcapable {!tclvar} { 447 do_test bind-10.1 { 448 set VM [ 449 sqlite3_prepare $DB { 450 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc) 451 } -1 TAIL 452 ] 453 sqlite3_bind_parameter_count $VM 454 } 3 455 set v1 {:xyz} 456 set v2 {:xy} 457} 458do_test bind-10.2 { 459 sqlite3_bind_parameter_index $VM :abc 460} 1 461do_test bind-10.3 { 462 sqlite3_bind_parameter_index $VM $v1 463} 2 464do_test bind-10.4 { 465 sqlite3_bind_parameter_index $VM $v2 466} 3 467do_test bind-10.5 { 468 sqlite3_bind_parameter_name $VM 1 469} :abc 470do_test bind-10.6 { 471 sqlite3_bind_parameter_name $VM 2 472} $v1 473do_test bind-10.7 { 474 sqlite3_bind_parameter_name $VM 3 475} $v2 476do_test bind-10.7.1 { 477 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL 478} {} 479do_test bind-10.7.2 { 480 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small 481} {} 482do_test bind-10.7.3 { 483 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big 484} {} 485do_test bind-10.8 { 486 sqlite3_bind_int $VM 1 1 487 sqlite3_bind_int $VM 2 2 488 sqlite3_bind_int $VM 3 3 489 sqlite3_step $VM 490} SQLITE_DONE 491do_test bind-10.8.1 { 492 # Binding attempts after program start should fail 493 set rc [catch { 494 sqlite3_bind_int $VM 1 1 495 } msg] 496 lappend rc $msg 497} {1 {}} 498do_test bind-10.9 { 499 sqlite3_finalize $VM 500} SQLITE_OK 501do_test bind-10.10 { 502 execsql {SELECT * FROM t2} 503} {1 999 1000 1001 {} {} 1 2 1 3 2 1} 504 505# Ticket #918 506# 507do_test bind-10.11 { 508 # catch {sqlite3_finalize $VM} 509 set VM [ 510 sqlite3_prepare $DB { 511 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4) 512 } -1 TAIL 513 ] 514 sqlite3_bind_parameter_count $VM 515} 5 516do_test bind-10.11.1 { 517 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments 518} 0 519do_test bind-10.12 { 520 sqlite3_bind_parameter_index $VM :xyz 521} 0 522do_test bind-10.13 { 523 sqlite3_bind_parameter_index $VM {} 524} 0 525do_test bind-10.14 { 526 sqlite3_bind_parameter_index $VM :pqr 527} 5 528do_test bind-10.15 { 529 sqlite3_bind_parameter_index $VM ?4 530} 4 531do_test bind-10.16 { 532 sqlite3_bind_parameter_name $VM 1 533} :abc 534do_test bind-10.17 { 535 sqlite3_bind_parameter_name $VM 2 536} {} 537do_test bind-10.18 { 538 sqlite3_bind_parameter_name $VM 3 539} {} 540do_test bind-10.19 { 541 sqlite3_bind_parameter_name $VM 4 542} {?4} 543do_test bind-10.20 { 544 sqlite3_bind_parameter_name $VM 5 545} :pqr 546catch {sqlite3_finalize $VM} 547 548# Make sure we catch an unterminated "(" in a Tcl-style variable name 549# 550ifcapable tclvar { 551 do_test bind-11.1 { 552 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;} 553 } {1 {unrecognized token: "$abc(123"}} 554} 555 556if {[execsql {pragma encoding}]=="UTF-8"} { 557 # Test the ability to bind text that contains embedded '\000' characters. 558 # Make sure we can recover the entire input string. 559 # 560 do_test bind-12.1 { 561 execsql { 562 CREATE TABLE t3(x BLOB); 563 } 564 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL] 565 sqlite_bind $VM 1 not-used blob10 566 sqlite3_step $VM 567 sqlite3_finalize $VM 568 execsql { 569 SELECT typeof(x), length(x), quote(x), 570 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3 571 } 572 } {text 3 'abc' 10 X'6162630078797A007071'} 573 do_test bind-12.2 { 574 sqlite3_create_function $DB 575 execsql { 576 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3 577 } 578 } {X'6162630078797A007071'} 579} 580 581# Test the operation of sqlite3_clear_bindings 582# 583do_test bind-13.1 { 584 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL] 585 sqlite3_step $VM 586 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 587 [sqlite3_column_type $VM 2] 588} {NULL NULL NULL} 589do_test bind-13.2 { 590 sqlite3_reset $VM 591 sqlite3_bind_int $VM 1 1 592 sqlite3_bind_int $VM 2 2 593 sqlite3_bind_int $VM 3 3 594 sqlite3_step $VM 595 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 596 [sqlite3_column_type $VM 2] 597} {INTEGER INTEGER INTEGER} 598do_test bind-13.3 { 599 sqlite3_reset $VM 600 sqlite3_step $VM 601 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 602 [sqlite3_column_type $VM 2] 603} {INTEGER INTEGER INTEGER} 604do_test bind-13.4 { 605 sqlite3_reset $VM 606 sqlite3_clear_bindings $VM 607 sqlite3_step $VM 608 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 609 [sqlite3_column_type $VM 2] 610} {NULL NULL NULL} 611sqlite3_finalize $VM 612 613finish_test 614