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.43 2008/07/08 00:06:51 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.10 { 95 set rc [catch { 96 sqlite3_prepare db {INSERT INTO t1 VALUES($abc:123,?,:abc)} -1 TAIL 97 } msg] 98 lappend rc $msg 99} {1 {(1) near ":123": syntax error}} 100do_test bind-1.11 { 101 set rc [catch { 102 sqlite3_prepare db {INSERT INTO t1 VALUES(@abc:xyz,?,:abc)} -1 TAIL 103 } msg] 104 lappend rc $msg 105} {1 {(1) near ":xyz": syntax error}} 106 107do_test bind-1.99 { 108 sqlite3_finalize $VM 109} SQLITE_OK 110 111# Prepare the statement in different ways depending on whether or not 112# the $var processing is compiled into the library. 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,$x(-z-))}\ 120 -1 TX] 121 set TX 122 } {} 123 set v1 {$one} 124 set v2 {$::two} 125 set v3 {$x(-z-)} 126} 127ifcapable {!tclvar} { 128 do_test bind-2.1 { 129 execsql { 130 DELETE FROM t1; 131 } 132 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX] 133 set TX 134 } {} 135 set v1 {:one} 136 set v2 {:two} 137 set v3 {:_} 138} 139 140do_test bind-2.1.1 { 141 sqlite3_bind_parameter_count $VM 142} 3 143do_test bind-2.1.2 { 144 sqlite3_bind_parameter_name $VM 1 145} $v1 146do_test bind-2.1.3 { 147 sqlite3_bind_parameter_name $VM 2 148} $v2 149do_test bind-2.1.4 { 150 sqlite3_bind_parameter_name $VM 3 151} $v3 152do_test bind-2.1.5 { 153 sqlite3_bind_parameter_index $VM $v1 154} 1 155do_test bind-2.1.6 { 156 sqlite3_bind_parameter_index $VM $v2 157} 2 158do_test bind-2.1.7 { 159 sqlite3_bind_parameter_index $VM $v3 160} 3 161do_test bind-2.1.8 { 162 sqlite3_bind_parameter_index $VM {:hi} 163} 0 164 165# 32 bit Integers 166do_test bind-2.2 { 167 sqlite3_bind_int $VM 1 123 168 sqlite3_bind_int $VM 2 456 169 sqlite3_bind_int $VM 3 789 170 sqlite_step $VM N VALUES COLNAMES 171 sqlite3_reset $VM 172 execsql {SELECT rowid, * FROM t1} 173} {1 123 456 789} 174do_test bind-2.3 { 175 sqlite3_bind_int $VM 2 -2000000000 176 sqlite3_bind_int $VM 3 2000000000 177 sqlite_step $VM N VALUES COLNAMES 178 sqlite3_reset $VM 179 execsql {SELECT rowid, * FROM t1} 180} {1 123 456 789 2 123 -2000000000 2000000000} 181do_test bind-2.4 { 182 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 183} {integer integer integer integer integer integer} 184do_test bind-2.5 { 185 execsql { 186 DELETE FROM t1; 187 } 188} {} 189 190# 64 bit Integers 191do_test bind-3.1 { 192 sqlite3_bind_int64 $VM 1 32 193 sqlite3_bind_int64 $VM 2 -2000000000000 194 sqlite3_bind_int64 $VM 3 2000000000000 195 sqlite_step $VM N VALUES COLNAMES 196 sqlite3_reset $VM 197 execsql {SELECT rowid, * FROM t1} 198} {1 32 -2000000000000 2000000000000} 199do_test bind-3.2 { 200 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 201} {integer integer integer} 202do_test bind-3.3 { 203 execsql { 204 DELETE FROM t1; 205 } 206} {} 207 208# Doubles 209do_test bind-4.1 { 210 sqlite3_bind_double $VM 1 1234.1234 211 sqlite3_bind_double $VM 2 0.00001 212 sqlite3_bind_double $VM 3 123456789 213 sqlite_step $VM N VALUES COLNAMES 214 sqlite3_reset $VM 215 set x [execsql {SELECT rowid, * FROM t1}] 216 regsub {1e-005} $x {1e-05} y 217 set y 218} {1 1234.1234 1e-05 123456789.0} 219do_test bind-4.2 { 220 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 221} {real real real} 222do_test bind-4.3 { 223 execsql { 224 DELETE FROM t1; 225 } 226} {} 227do_test bind-4.4 { 228 sqlite3_bind_double $VM 1 NaN 229 sqlite3_bind_double $VM 2 1e300 230 sqlite3_bind_double $VM 3 -1e-300 231 sqlite_step $VM N VALUES COLNAMES 232 sqlite3_reset $VM 233 set x [execsql {SELECT rowid, * FROM t1}] 234 regsub {1e-005} $x {1e-05} y 235 set y 236} {1 {} 1e+300 -1e-300} 237do_test bind-4.5 { 238 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 239} {null real real} 240do_test bind-4.6 { 241 execsql { 242 DELETE FROM t1; 243 } 244} {} 245 246# NULL 247do_test bind-5.1 { 248 sqlite3_bind_null $VM 1 249 sqlite3_bind_null $VM 2 250 sqlite3_bind_null $VM 3 251 sqlite_step $VM N VALUES COLNAMES 252 sqlite3_reset $VM 253 execsql {SELECT rowid, * FROM t1} 254} {1 {} {} {}} 255do_test bind-5.2 { 256 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 257} {null null null} 258do_test bind-5.3 { 259 execsql { 260 DELETE FROM t1; 261 } 262} {} 263 264# UTF-8 text 265do_test bind-6.1 { 266 sqlite3_bind_text $VM 1 hellothere 5 267 sqlite3_bind_text $VM 2 ".." 1 268 sqlite3_bind_text $VM 3 world\000 -1 269 sqlite_step $VM N VALUES COLNAMES 270 sqlite3_reset $VM 271 execsql {SELECT rowid, * FROM t1} 272} {1 hello . world} 273do_test bind-6.2 { 274 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 275} {text text text} 276do_test bind-6.3 { 277 execsql { 278 DELETE FROM t1; 279 } 280} {} 281 282# Make sure zeros in a string work. 283# 284do_test bind-6.4 { 285 db eval {DELETE FROM t1} 286 sqlite3_bind_text $VM 1 hello\000there\000 12 287 sqlite3_bind_text $VM 2 hello\000there\000 11 288 sqlite3_bind_text $VM 3 hello\000there\000 -1 289 sqlite_step $VM N VALUES COLNAMES 290 sqlite3_reset $VM 291 execsql {SELECT * FROM t1} 292} {hello hello hello} 293set enc [db eval {PRAGMA encoding}] 294if {$enc=="UTF-8"} { 295 do_test bind-6.5 { 296 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 297 } {68656C6C6F00746865726500 68656C6C6F007468657265 68656C6C6F} 298} elseif {$enc=="UTF-16le"} { 299 do_test bind-6.5 { 300 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 301 } {680065006C006C006F000000740068006500720065000000 680065006C006C006F00000074006800650072006500 680065006C006C006F00} 302} elseif {$enc=="UTF-16be"} { 303 do_test bind-6.5 { 304 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 305 } {00680065006C006C006F0000007400680065007200650000 00680065006C006C006F000000740068006500720065 00680065006C006C006F} 306} else { 307 do_test bind-6.5 { 308 set "Unknown database encoding: $::enc" 309 } {} 310} 311do_test bind-6.6 { 312 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 313} {text text text} 314do_test bind-6.7 { 315 execsql { 316 DELETE FROM t1; 317 } 318} {} 319 320# UTF-16 text 321ifcapable {utf16} { 322 do_test bind-7.1 { 323 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10 324 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0 325 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10 326 sqlite_step $VM N VALUES COLNAMES 327 sqlite3_reset $VM 328 execsql {SELECT rowid, * FROM t1} 329 } {1 hello {} world} 330 do_test bind-7.2 { 331 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 332 } {text text text} 333 do_test bind-7.3 { 334 db eval {DELETE FROM t1} 335 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hi\000yall\000] 16 336 sqlite3_bind_text16 $VM 2 [encoding convertto unicode hi\000yall\000] 14 337 sqlite3_bind_text16 $VM 3 [encoding convertto unicode hi\000yall\000] -1 338 sqlite_step $VM N VALUES COLNAMES 339 sqlite3_reset $VM 340 execsql {SELECT * FROM t1} 341 } {hi hi hi} 342 if {$enc=="UTF-8"} { 343 do_test bind-7.4 { 344 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 345 } {68690079616C6C00 68690079616C6C 6869} 346 } elseif {$enc=="UTF-16le"} { 347 do_test bind-7.4 { 348 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 349 } {680069000000790061006C006C000000 680069000000790061006C006C00 68006900} 350 } elseif {$enc=="UTF-16be"} { 351 do_test bind-7.4 { 352 execsql {SELECT hex(a), hex(b), hex(c) FROM t1} 353 } {00680069000000790061006C006C0000 00680069000000790061006C006C 00680069} 354 } 355 do_test bind-7.5 { 356 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 357 } {text text text} 358} 359do_test bind-7.99 { 360 execsql {DELETE FROM t1;} 361} {} 362 363# Test that the 'out of range' error works. 364do_test bind-8.1 { 365 catch { sqlite3_bind_null $VM 0 } 366} {1} 367do_test bind-8.2 { 368 sqlite3_errmsg $DB 369} {bind or column index out of range} 370ifcapable {utf16} { 371 do_test bind-8.3 { 372 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 373 } {bind or column index out of range} 374} 375do_test bind-8.4 { 376 sqlite3_bind_null $VM 1 377 sqlite3_errmsg $DB 378} {not an error} 379do_test bind-8.5 { 380 catch { sqlite3_bind_null $VM 4 } 381} {1} 382do_test bind-8.6 { 383 sqlite3_errmsg $DB 384} {bind or column index out of range} 385ifcapable {utf16} { 386 do_test bind-8.7 { 387 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 388 } {bind or column index out of range} 389} 390 391do_test bind-8.8 { 392 catch { sqlite3_bind_blob $VM 0 "abc" 3 } 393} {1} 394do_test bind-8.9 { 395 catch { sqlite3_bind_blob $VM 4 "abc" 3 } 396} {1} 397do_test bind-8.10 { 398 catch { sqlite3_bind_text $VM 0 "abc" 3 } 399} {1} 400ifcapable {utf16} { 401 do_test bind-8.11 { 402 catch { sqlite3_bind_text16 $VM 4 "abc" 2 } 403 } {1} 404} 405do_test bind-8.12 { 406 catch { sqlite3_bind_int $VM 0 5 } 407} {1} 408do_test bind-8.13 { 409 catch { sqlite3_bind_int $VM 4 5 } 410} {1} 411do_test bind-8.14 { 412 catch { sqlite3_bind_double $VM 0 5.0 } 413} {1} 414do_test bind-8.15 { 415 catch { sqlite3_bind_double $VM 4 6.0 } 416} {1} 417 418do_test bind-8.99 { 419 sqlite3_finalize $VM 420} SQLITE_OK 421 422do_test bind-9.1 { 423 execsql { 424 CREATE TABLE t2(a,b,c,d,e,f); 425 } 426 set rc [catch { 427 sqlite3_prepare $DB { 428 INSERT INTO t2(a) VALUES(?0) 429 } -1 TAIL 430 } msg] 431 lappend rc $msg 432} {1 {(1) variable number must be between ?1 and ?999}} 433do_test bind-9.2 { 434 set rc [catch { 435 sqlite3_prepare $DB { 436 INSERT INTO t2(a) VALUES(?1000) 437 } -1 TAIL 438 } msg] 439 lappend rc $msg 440} {1 {(1) variable number must be between ?1 and ?999}} 441do_test bind-9.3 { 442 set VM [ 443 sqlite3_prepare $DB { 444 INSERT INTO t2(a,b) VALUES(?1,?999) 445 } -1 TAIL 446 ] 447 sqlite3_bind_parameter_count $VM 448} {999} 449catch {sqlite3_finalize $VM} 450do_test bind-9.4 { 451 set VM [ 452 sqlite3_prepare $DB { 453 INSERT INTO t2(a,b,c,d) VALUES(?1,?997,?,?) 454 } -1 TAIL 455 ] 456 sqlite3_bind_parameter_count $VM 457} {999} 458do_test bind-9.5 { 459 sqlite3_bind_int $VM 1 1 460 sqlite3_bind_int $VM 997 999 461 sqlite3_bind_int $VM 998 1000 462 sqlite3_bind_int $VM 999 1001 463 sqlite3_step $VM 464} SQLITE_DONE 465do_test bind-9.6 { 466 sqlite3_finalize $VM 467} SQLITE_OK 468do_test bind-9.7 { 469 execsql {SELECT * FROM t2} 470} {1 999 1000 1001 {} {}} 471 472ifcapable {tclvar} { 473 do_test bind-10.1 { 474 set VM [ 475 sqlite3_prepare $DB { 476 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc) 477 } -1 TAIL 478 ] 479 sqlite3_bind_parameter_count $VM 480 } 3 481 set v1 {$abc} 482 set v2 {$ab} 483} 484ifcapable {!tclvar} { 485 do_test bind-10.1 { 486 set VM [ 487 sqlite3_prepare $DB { 488 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc) 489 } -1 TAIL 490 ] 491 sqlite3_bind_parameter_count $VM 492 } 3 493 set v1 {:xyz} 494 set v2 {:xy} 495} 496do_test bind-10.2 { 497 sqlite3_bind_parameter_index $VM :abc 498} 1 499do_test bind-10.3 { 500 sqlite3_bind_parameter_index $VM $v1 501} 2 502do_test bind-10.4 { 503 sqlite3_bind_parameter_index $VM $v2 504} 3 505do_test bind-10.5 { 506 sqlite3_bind_parameter_name $VM 1 507} :abc 508do_test bind-10.6 { 509 sqlite3_bind_parameter_name $VM 2 510} $v1 511do_test bind-10.7 { 512 sqlite3_bind_parameter_name $VM 3 513} $v2 514do_test bind-10.7.1 { 515 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL 516} {} 517do_test bind-10.7.2 { 518 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small 519} {} 520do_test bind-10.7.3 { 521 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big 522} {} 523do_test bind-10.8 { 524 sqlite3_bind_int $VM 1 1 525 sqlite3_bind_int $VM 2 2 526 sqlite3_bind_int $VM 3 3 527 sqlite3_step $VM 528} SQLITE_DONE 529do_test bind-10.8.1 { 530 # Binding attempts after program start should fail 531 set rc [catch { 532 sqlite3_bind_int $VM 1 1 533 } msg] 534 lappend rc $msg 535} {1 {}} 536do_test bind-10.9 { 537 sqlite3_finalize $VM 538} SQLITE_OK 539do_test bind-10.10 { 540 execsql {SELECT * FROM t2} 541} {1 999 1000 1001 {} {} 1 2 1 3 2 1} 542 543# Ticket #918 544# 545do_test bind-10.11 { 546 # catch {sqlite3_finalize $VM} 547 set VM [ 548 sqlite3_prepare $DB { 549 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4) 550 } -1 TAIL 551 ] 552 sqlite3_bind_parameter_count $VM 553} 5 554do_test bind-10.11.1 { 555 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments 556} 0 557do_test bind-10.12 { 558 sqlite3_bind_parameter_index $VM :xyz 559} 0 560do_test bind-10.13 { 561 sqlite3_bind_parameter_index $VM {} 562} 0 563do_test bind-10.14 { 564 sqlite3_bind_parameter_index $VM :pqr 565} 5 566do_test bind-10.15 { 567 sqlite3_bind_parameter_index $VM ?4 568} 4 569do_test bind-10.16 { 570 sqlite3_bind_parameter_name $VM 1 571} :abc 572do_test bind-10.17 { 573 sqlite3_bind_parameter_name $VM 2 574} {} 575do_test bind-10.18 { 576 sqlite3_bind_parameter_name $VM 3 577} {} 578do_test bind-10.19 { 579 sqlite3_bind_parameter_name $VM 4 580} {?4} 581do_test bind-10.20 { 582 sqlite3_bind_parameter_name $VM 5 583} :pqr 584catch {sqlite3_finalize $VM} 585 586# Make sure we catch an unterminated "(" in a Tcl-style variable name 587# 588ifcapable tclvar { 589 do_test bind-11.1 { 590 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;} 591 } {1 {unrecognized token: "$abc(123"}} 592} 593 594if {[execsql {pragma encoding}]=="UTF-8"} { 595 # Test the ability to bind text that contains embedded '\000' characters. 596 # Make sure we can recover the entire input string. 597 # 598 do_test bind-12.1 { 599 execsql { 600 CREATE TABLE t3(x BLOB); 601 } 602 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL] 603 sqlite_bind $VM 1 not-used blob10 604 sqlite3_step $VM 605 sqlite3_finalize $VM 606 execsql { 607 SELECT typeof(x), length(x), quote(x), 608 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3 609 } 610 } {text 3 'abc' 10 X'6162630078797A007071'} 611 do_test bind-12.2 { 612 sqlite3_create_function $DB 613 execsql { 614 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3 615 } 616 } {X'6162630078797A007071'} 617} 618 619# Test the operation of sqlite3_clear_bindings 620# 621do_test bind-13.1 { 622 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL] 623 sqlite3_step $VM 624 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 625 [sqlite3_column_type $VM 2] 626} {NULL NULL NULL} 627do_test bind-13.2 { 628 sqlite3_reset $VM 629 sqlite3_bind_int $VM 1 1 630 sqlite3_bind_int $VM 2 2 631 sqlite3_bind_int $VM 3 3 632 sqlite3_step $VM 633 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 634 [sqlite3_column_type $VM 2] 635} {INTEGER INTEGER INTEGER} 636do_test bind-13.3 { 637 sqlite3_reset $VM 638 sqlite3_step $VM 639 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 640 [sqlite3_column_type $VM 2] 641} {INTEGER INTEGER INTEGER} 642do_test bind-13.4 { 643 sqlite3_reset $VM 644 sqlite3_clear_bindings $VM 645 sqlite3_step $VM 646 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 647 [sqlite3_column_type $VM 2] 648} {NULL NULL NULL} 649sqlite3_finalize $VM 650 651finish_test 652