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.40 2007/05/10 17:23:12 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 -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# UTF-16 text 270ifcapable {utf16} { 271 do_test bind-7.1 { 272 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10 273 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0 274 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10 275 sqlite_step $VM N VALUES COLNAMES 276 sqlite3_reset $VM 277 execsql {SELECT rowid, * FROM t1} 278 } {1 hello {} world} 279 do_test bind-7.2 { 280 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 281 } {text text text} 282} 283do_test bind-7.3 { 284 execsql { 285 DELETE FROM t1; 286 } 287} {} 288 289# Test that the 'out of range' error works. 290do_test bind-8.1 { 291 catch { sqlite3_bind_null $VM 0 } 292} {1} 293do_test bind-8.2 { 294 sqlite3_errmsg $DB 295} {bind or column index out of range} 296ifcapable {utf16} { 297 do_test bind-8.3 { 298 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 299 } {bind or column index out of range} 300} 301do_test bind-8.4 { 302 sqlite3_bind_null $VM 1 303 sqlite3_errmsg $DB 304} {not an error} 305do_test bind-8.5 { 306 catch { sqlite3_bind_null $VM 4 } 307} {1} 308do_test bind-8.6 { 309 sqlite3_errmsg $DB 310} {bind or column index out of range} 311ifcapable {utf16} { 312 do_test bind-8.7 { 313 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 314 } {bind or column index out of range} 315} 316 317do_test bind-8.8 { 318 catch { sqlite3_bind_blob $VM 0 "abc" 3 } 319} {1} 320do_test bind-8.9 { 321 catch { sqlite3_bind_blob $VM 4 "abc" 3 } 322} {1} 323do_test bind-8.10 { 324 catch { sqlite3_bind_text $VM 0 "abc" 3 } 325} {1} 326ifcapable {utf16} { 327 do_test bind-8.11 { 328 catch { sqlite3_bind_text16 $VM 4 "abc" 2 } 329 } {1} 330} 331do_test bind-8.12 { 332 catch { sqlite3_bind_int $VM 0 5 } 333} {1} 334do_test bind-8.13 { 335 catch { sqlite3_bind_int $VM 4 5 } 336} {1} 337do_test bind-8.14 { 338 catch { sqlite3_bind_double $VM 0 5.0 } 339} {1} 340do_test bind-8.15 { 341 catch { sqlite3_bind_double $VM 4 6.0 } 342} {1} 343 344do_test bind-8.99 { 345 sqlite3_finalize $VM 346} SQLITE_OK 347 348do_test bind-9.1 { 349 execsql { 350 CREATE TABLE t2(a,b,c,d,e,f); 351 } 352 set rc [catch { 353 sqlite3_prepare $DB { 354 INSERT INTO t2(a) VALUES(?0) 355 } -1 TAIL 356 } msg] 357 lappend rc $msg 358} {1 {(1) variable number must be between ?1 and ?999}} 359do_test bind-9.2 { 360 set rc [catch { 361 sqlite3_prepare $DB { 362 INSERT INTO t2(a) VALUES(?1000) 363 } -1 TAIL 364 } msg] 365 lappend rc $msg 366} {1 {(1) variable number must be between ?1 and ?999}} 367do_test bind-9.3 { 368 set VM [ 369 sqlite3_prepare $DB { 370 INSERT INTO t2(a,b) VALUES(?1,?999) 371 } -1 TAIL 372 ] 373 sqlite3_bind_parameter_count $VM 374} {999} 375catch {sqlite3_finalize $VM} 376do_test bind-9.4 { 377 set VM [ 378 sqlite3_prepare $DB { 379 INSERT INTO t2(a,b,c,d) VALUES(?1,?997,?,?) 380 } -1 TAIL 381 ] 382 sqlite3_bind_parameter_count $VM 383} {999} 384do_test bind-9.5 { 385 sqlite3_bind_int $VM 1 1 386 sqlite3_bind_int $VM 997 999 387 sqlite3_bind_int $VM 998 1000 388 sqlite3_bind_int $VM 999 1001 389 sqlite3_step $VM 390} SQLITE_DONE 391do_test bind-9.6 { 392 sqlite3_finalize $VM 393} SQLITE_OK 394do_test bind-9.7 { 395 execsql {SELECT * FROM t2} 396} {1 999 1000 1001 {} {}} 397 398ifcapable {tclvar} { 399 do_test bind-10.1 { 400 set VM [ 401 sqlite3_prepare $DB { 402 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc) 403 } -1 TAIL 404 ] 405 sqlite3_bind_parameter_count $VM 406 } 3 407 set v1 {$abc} 408 set v2 {$ab} 409} 410ifcapable {!tclvar} { 411 do_test bind-10.1 { 412 set VM [ 413 sqlite3_prepare $DB { 414 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc) 415 } -1 TAIL 416 ] 417 sqlite3_bind_parameter_count $VM 418 } 3 419 set v1 {:xyz} 420 set v2 {:xy} 421} 422do_test bind-10.2 { 423 sqlite3_bind_parameter_index $VM :abc 424} 1 425do_test bind-10.3 { 426 sqlite3_bind_parameter_index $VM $v1 427} 2 428do_test bind-10.4 { 429 sqlite3_bind_parameter_index $VM $v2 430} 3 431do_test bind-10.5 { 432 sqlite3_bind_parameter_name $VM 1 433} :abc 434do_test bind-10.6 { 435 sqlite3_bind_parameter_name $VM 2 436} $v1 437do_test bind-10.7 { 438 sqlite3_bind_parameter_name $VM 3 439} $v2 440do_test bind-10.7.1 { 441 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL 442} {} 443do_test bind-10.7.2 { 444 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small 445} {} 446do_test bind-10.7.3 { 447 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big 448} {} 449do_test bind-10.8 { 450 sqlite3_bind_int $VM 1 1 451 sqlite3_bind_int $VM 2 2 452 sqlite3_bind_int $VM 3 3 453 sqlite3_step $VM 454} SQLITE_DONE 455do_test bind-10.8.1 { 456 # Binding attempts after program start should fail 457 set rc [catch { 458 sqlite3_bind_int $VM 1 1 459 } msg] 460 lappend rc $msg 461} {1 {}} 462do_test bind-10.9 { 463 sqlite3_finalize $VM 464} SQLITE_OK 465do_test bind-10.10 { 466 execsql {SELECT * FROM t2} 467} {1 999 1000 1001 {} {} 1 2 1 3 2 1} 468 469# Ticket #918 470# 471do_test bind-10.11 { 472 # catch {sqlite3_finalize $VM} 473 set VM [ 474 sqlite3_prepare $DB { 475 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4) 476 } -1 TAIL 477 ] 478 sqlite3_bind_parameter_count $VM 479} 5 480do_test bind-10.11.1 { 481 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments 482} 0 483do_test bind-10.12 { 484 sqlite3_bind_parameter_index $VM :xyz 485} 0 486do_test bind-10.13 { 487 sqlite3_bind_parameter_index $VM {} 488} 0 489do_test bind-10.14 { 490 sqlite3_bind_parameter_index $VM :pqr 491} 5 492do_test bind-10.15 { 493 sqlite3_bind_parameter_index $VM ?4 494} 4 495do_test bind-10.16 { 496 sqlite3_bind_parameter_name $VM 1 497} :abc 498do_test bind-10.17 { 499 sqlite3_bind_parameter_name $VM 2 500} {} 501do_test bind-10.18 { 502 sqlite3_bind_parameter_name $VM 3 503} {} 504do_test bind-10.19 { 505 sqlite3_bind_parameter_name $VM 4 506} {?4} 507do_test bind-10.20 { 508 sqlite3_bind_parameter_name $VM 5 509} :pqr 510catch {sqlite3_finalize $VM} 511 512# Make sure we catch an unterminated "(" in a Tcl-style variable name 513# 514ifcapable tclvar { 515 do_test bind-11.1 { 516 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;} 517 } {1 {unrecognized token: "$abc(123"}} 518} 519 520if {[execsql {pragma encoding}]=="UTF-8"} { 521 # Test the ability to bind text that contains embedded '\000' characters. 522 # Make sure we can recover the entire input string. 523 # 524 do_test bind-12.1 { 525 execsql { 526 CREATE TABLE t3(x BLOB); 527 } 528 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL] 529 sqlite_bind $VM 1 not-used blob10 530 sqlite3_step $VM 531 sqlite3_finalize $VM 532 execsql { 533 SELECT typeof(x), length(x), quote(x), 534 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3 535 } 536 } {text 3 'abc' 10 X'6162630078797A007071'} 537 do_test bind-12.2 { 538 sqlite3_create_function $DB 539 execsql { 540 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3 541 } 542 } {X'6162630078797A007071'} 543} 544 545# Test the operation of sqlite3_clear_bindings 546# 547do_test bind-13.1 { 548 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL] 549 sqlite3_step $VM 550 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 551 [sqlite3_column_type $VM 2] 552} {NULL NULL NULL} 553do_test bind-13.2 { 554 sqlite3_reset $VM 555 sqlite3_bind_int $VM 1 1 556 sqlite3_bind_int $VM 2 2 557 sqlite3_bind_int $VM 3 3 558 sqlite3_step $VM 559 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 560 [sqlite3_column_type $VM 2] 561} {INTEGER INTEGER INTEGER} 562do_test bind-13.3 { 563 sqlite3_reset $VM 564 sqlite3_step $VM 565 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 566 [sqlite3_column_type $VM 2] 567} {INTEGER INTEGER INTEGER} 568do_test bind-13.4 { 569 sqlite3_reset $VM 570 sqlite3_clear_bindings $VM 571 sqlite3_step $VM 572 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 573 [sqlite3_column_type $VM 2] 574} {NULL NULL NULL} 575sqlite3_finalize $VM 576 577finish_test 578