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