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.48 2009/07/22 07:27:57 danielk1977 Exp $ 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 proc 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 37 do_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 } {} 43 do_test bind-1.1.1 { 44 sqlite3_bind_parameter_count $VM 45 } 3 46 do_test bind-1.1.2 { 47 sqlite3_bind_parameter_name $VM 1 48 } {:1} 49 do_test bind-1.1.3 { 50 sqlite3_bind_parameter_name $VM 2 51 } {} 52 do_test bind-1.1.4 { 53 sqlite3_bind_parameter_name $VM 3 54 } {:abc} 55 do_test bind-1.2 { 56 sqlite_step $VM N VALUES COLNAMES 57 } {SQLITE_DONE} 58 do_test bind-1.3 { 59 execsql {SELECT rowid, * FROM t1} 60 } {1 {} {} {}} 61 do_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 66 do_test bind-1.5 { 67 execsql {SELECT rowid, * FROM t1} 68 } {1 {} {} {} 2 {test value 1} {} {}} 69 do_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 74 do_test bind-1.7 { 75 execsql {SELECT rowid, * FROM t1} 76 } {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}} 77 do_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 {}} 87 do_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 94 do_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}} 100 do_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 107 do_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 # 114 ifcapable {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 } 127 ifcapable {!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 140 do_test bind-2.1.1 { 141 sqlite3_bind_parameter_count $VM 142 } 3 143 do_test bind-2.1.2 { 144 sqlite3_bind_parameter_name $VM 1 145 } $v1 146 do_test bind-2.1.3 { 147 sqlite3_bind_parameter_name $VM 2 148 } $v2 149 do_test bind-2.1.4 { 150 sqlite3_bind_parameter_name $VM 3 151 } $v3 152 do_test bind-2.1.5 { 153 sqlite3_bind_parameter_index $VM $v1 154 } 1 155 do_test bind-2.1.6 { 156 sqlite3_bind_parameter_index $VM $v2 157 } 2 158 do_test bind-2.1.7 { 159 sqlite3_bind_parameter_index $VM $v3 160 } 3 161 do_test bind-2.1.8 { 162 sqlite3_bind_parameter_index $VM {:hi} 163 } 0 164 165 # 32 bit Integers 166 do_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} 174 do_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} 181 do_test bind-2.4 { 182 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 183 } {integer integer integer integer integer integer} 184 do_test bind-2.5 { 185 execsql { 186 DELETE FROM t1; 187 } 188 } {} 189 190 # 64 bit Integers 191 do_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} 199 do_test bind-3.2 { 200 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 201 } {integer integer integer} 202 do_test bind-3.3 { 203 execsql { 204 DELETE FROM t1; 205 } 206 } {} 207 208 # Doubles 209 do_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} 219 do_test bind-4.2 { 220 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 221 } {real real real} 222 do_test bind-4.3 { 223 execsql { 224 DELETE FROM t1; 225 } 226 } {} 227 do_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} 237 do_test bind-4.5 { 238 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 239 } {null real real} 240 do_test bind-4.6 { 241 execsql { 242 DELETE FROM t1; 243 } 244 } {} 245 246 # NULL 247 do_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 {} {} {}} 255 do_test bind-5.2 { 256 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 257 } {null null null} 258 do_test bind-5.3 { 259 execsql { 260 DELETE FROM t1; 261 } 262 } {} 263 264 # UTF-8 text 265 do_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} 273 do_test bind-6.2 { 274 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 275 } {text text text} 276 do_test bind-6.3 { 277 execsql { 278 DELETE FROM t1; 279 } 280 } {} 281 282 # Make sure zeros in a string work. 283 # 284 do_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} 293 set enc [db eval {PRAGMA encoding}] 294 if {$enc=="UTF-8" || $enc==""} { 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 } 311 do_test bind-6.6 { 312 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} 313 } {text text text} 314 do_test bind-6.7 { 315 execsql { 316 DELETE FROM t1; 317 } 318 } {} 319 320 # UTF-16 text 321 ifcapable {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 } 359 do_test bind-7.99 { 360 execsql {DELETE FROM t1;} 361 } {} 362 363 # Test that the 'out of range' error works. 364 do_test bind-8.1 { 365 catch { sqlite3_bind_null $VM 0 } 366 } {1} 367 do_test bind-8.2 { 368 sqlite3_errmsg $DB 369 } {bind or column index out of range} 370 ifcapable {utf16} { 371 do_test bind-8.3 { 372 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 373 } {bind or column index out of range} 374 } 375 do_test bind-8.4 { 376 sqlite3_bind_null $VM 1 377 sqlite3_errmsg $DB 378 } {not an error} 379 do_test bind-8.5 { 380 catch { sqlite3_bind_null $VM 4 } 381 } {1} 382 do_test bind-8.6 { 383 sqlite3_errmsg $DB 384 } {bind or column index out of range} 385 ifcapable {utf16} { 386 do_test bind-8.7 { 387 encoding convertfrom unicode [sqlite3_errmsg16 $DB] 388 } {bind or column index out of range} 389 } 390 391 do_test bind-8.8 { 392 catch { sqlite3_bind_blob $VM 0 "abc" 3 } 393 } {1} 394 do_test bind-8.9 { 395 catch { sqlite3_bind_blob $VM 4 "abc" 3 } 396 } {1} 397 do_test bind-8.10 { 398 catch { sqlite3_bind_text $VM 0 "abc" 3 } 399 } {1} 400 ifcapable {utf16} { 401 do_test bind-8.11 { 402 catch { sqlite3_bind_text16 $VM 4 "abc" 2 } 403 } {1} 404 } 405 do_test bind-8.12 { 406 catch { sqlite3_bind_int $VM 0 5 } 407 } {1} 408 do_test bind-8.13 { 409 catch { sqlite3_bind_int $VM 4 5 } 410 } {1} 411 do_test bind-8.14 { 412 catch { sqlite3_bind_double $VM 0 5.0 } 413 } {1} 414 do_test bind-8.15 { 415 catch { sqlite3_bind_double $VM 4 6.0 } 416 } {1} 417 418 do_test bind-8.99 { 419 sqlite3_finalize $VM 420 } SQLITE_OK 421 422 set iMaxVar $SQLITE_MAX_VARIABLE_NUMBER 423 set zError "(1) variable number must be between ?1 and ?$iMaxVar" 424 do_test bind-9.1 { 425 execsql { 426 CREATE TABLE t2(a,b,c,d,e,f); 427 } 428 set rc [catch { 429 sqlite3_prepare $DB { 430 INSERT INTO t2(a) VALUES(?0) 431 } -1 TAIL 432 } msg] 433 lappend rc $msg 434 } [list 1 $zError] 435 do_test bind-9.2 { 436 set rc [catch { 437 sqlite3_prepare $DB "INSERT INTO t2(a) VALUES(?[expr $iMaxVar+1])" -1 TAIL 438 } msg] 439 lappend rc $msg 440 } [list 1 $zError] 441 do_test bind-9.3.1 { 442 set VM [ 443 sqlite3_prepare $DB " 444 INSERT INTO t2(a,b) VALUES(?1,?$iMaxVar) 445 " -1 TAIL 446 ] 447 sqlite3_bind_parameter_count $VM 448 } $iMaxVar 449 catch {sqlite3_finalize $VM} 450 do_test bind-9.3.2 { 451 set VM [ 452 sqlite3_prepare $DB " 453 INSERT INTO t2(a,b) VALUES(?2,?[expr $iMaxVar - 1]) 454 " -1 TAIL 455 ] 456 sqlite3_bind_parameter_count $VM 457 } [expr {$iMaxVar - 1}] 458 catch {sqlite3_finalize $VM} 459 do_test bind-9.4 { 460 set VM [ 461 sqlite3_prepare $DB " 462 INSERT INTO t2(a,b,c,d) VALUES(?1,?[expr $iMaxVar - 2],?,?) 463 " -1 TAIL 464 ] 465 sqlite3_bind_parameter_count $VM 466 } $iMaxVar 467 do_test bind-9.5 { 468 sqlite3_bind_int $VM 1 1 469 sqlite3_bind_int $VM [expr $iMaxVar - 2] 999 470 sqlite3_bind_int $VM [expr $iMaxVar - 1] 1000 471 sqlite3_bind_int $VM $iMaxVar 1001 472 sqlite3_step $VM 473 } SQLITE_DONE 474 do_test bind-9.6 { 475 sqlite3_finalize $VM 476 } SQLITE_OK 477 do_test bind-9.7 { 478 execsql {SELECT * FROM t2} 479 } {1 999 1000 1001 {} {}} 480 481 ifcapable {tclvar} { 482 do_test bind-10.1 { 483 set VM [ 484 sqlite3_prepare $DB { 485 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc) 486 } -1 TAIL 487 ] 488 sqlite3_bind_parameter_count $VM 489 } 3 490 set v1 {$abc} 491 set v2 {$ab} 492 } 493 ifcapable {!tclvar} { 494 do_test bind-10.1 { 495 set VM [ 496 sqlite3_prepare $DB { 497 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc) 498 } -1 TAIL 499 ] 500 sqlite3_bind_parameter_count $VM 501 } 3 502 set v1 {:xyz} 503 set v2 {:xy} 504 } 505 do_test bind-10.2 { 506 sqlite3_bind_parameter_index $VM :abc 507 } 1 508 do_test bind-10.3 { 509 sqlite3_bind_parameter_index $VM $v1 510 } 2 511 do_test bind-10.4 { 512 sqlite3_bind_parameter_index $VM $v2 513 } 3 514 do_test bind-10.5 { 515 sqlite3_bind_parameter_name $VM 1 516 } :abc 517 do_test bind-10.6 { 518 sqlite3_bind_parameter_name $VM 2 519 } $v1 520 do_test bind-10.7 { 521 sqlite3_bind_parameter_name $VM 3 522 } $v2 523 do_test bind-10.7.1 { 524 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL 525 } {} 526 do_test bind-10.7.2 { 527 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small 528 } {} 529 do_test bind-10.7.3 { 530 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big 531 } {} 532 do_test bind-10.8 { 533 sqlite3_bind_int $VM 1 1 534 sqlite3_bind_int $VM 2 2 535 sqlite3_bind_int $VM 3 3 536 sqlite3_step $VM 537 } SQLITE_DONE 538 do_test bind-10.8.1 { 539 # Binding attempts after program start should fail 540 set rc [catch { 541 sqlite3_bind_int $VM 1 1 542 } msg] 543 lappend rc $msg 544 } {1 {}} 545 do_test bind-10.9 { 546 sqlite3_finalize $VM 547 } SQLITE_OK 548 do_test bind-10.10 { 549 execsql {SELECT * FROM t2} 550 } {1 999 1000 1001 {} {} 1 2 1 3 2 1} 551 552 # Ticket #918 553 # 554 do_test bind-10.11 { 555 # catch {sqlite3_finalize $VM} 556 set VM [ 557 sqlite3_prepare $DB { 558 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4) 559 } -1 TAIL 560 ] 561 sqlite3_bind_parameter_count $VM 562 } 5 563 do_test bind-10.11.1 { 564 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments 565 } 0 566 do_test bind-10.12 { 567 sqlite3_bind_parameter_index $VM :xyz 568 } 0 569 do_test bind-10.13 { 570 sqlite3_bind_parameter_index $VM {} 571 } 0 572 do_test bind-10.14 { 573 sqlite3_bind_parameter_index $VM :pqr 574 } 5 575 do_test bind-10.15 { 576 sqlite3_bind_parameter_index $VM ?4 577 } 4 578 do_test bind-10.16 { 579 sqlite3_bind_parameter_name $VM 1 580 } :abc 581 do_test bind-10.17 { 582 sqlite3_bind_parameter_name $VM 2 583 } {} 584 do_test bind-10.18 { 585 sqlite3_bind_parameter_name $VM 3 586 } {} 587 do_test bind-10.19 { 588 sqlite3_bind_parameter_name $VM 4 589 } {?4} 590 do_test bind-10.20 { 591 sqlite3_bind_parameter_name $VM 5 592 } :pqr 593 catch {sqlite3_finalize $VM} 594 595 # Make sure we catch an unterminated "(" in a Tcl-style variable name 596 # 597 ifcapable tclvar { 598 do_test bind-11.1 { 599 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;} 600 } {1 {unrecognized token: "$abc(123"}} 601 } 602 603 if {[execsql {pragma encoding}]=="UTF-8"} { 604 # Test the ability to bind text that contains embedded '\000' characters. 605 # Make sure we can recover the entire input string. 606 # 607 do_test bind-12.1 { 608 execsql { 609 CREATE TABLE t3(x BLOB); 610 } 611 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL] 612 sqlite_bind $VM 1 not-used blob10 613 sqlite3_step $VM 614 sqlite3_finalize $VM 615 execsql { 616 SELECT typeof(x), length(x), quote(x), 617 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3 618 } 619 } {text 3 'abc' 10 X'6162630078797A007071'} 620 do_test bind-12.2 { 621 sqlite3_create_function $DB 622 execsql { 623 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3 624 } 625 } {X'6162630078797A007071'} 626 } 627 628 # Test the operation of sqlite3_clear_bindings 629 # 630 do_test bind-13.1 { 631 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL] 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} 636 do_test bind-13.2 { 637 sqlite3_reset $VM 638 sqlite3_bind_int $VM 1 1 639 sqlite3_bind_int $VM 2 2 640 sqlite3_bind_int $VM 3 3 641 sqlite3_step $VM 642 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 643 [sqlite3_column_type $VM 2] 644 } {INTEGER INTEGER INTEGER} 645 do_test bind-13.3 { 646 sqlite3_reset $VM 647 sqlite3_step $VM 648 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 649 [sqlite3_column_type $VM 2] 650 } {INTEGER INTEGER INTEGER} 651 do_test bind-13.4 { 652 sqlite3_reset $VM 653 sqlite3_clear_bindings $VM 654 sqlite3_step $VM 655 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \ 656 [sqlite3_column_type $VM 2] 657 } {NULL NULL NULL} 658 sqlite3_finalize $VM 659 660 #-------------------------------------------------------------------- 661 # These tests attempt to reproduce bug #3463. 662 # 663 proc param_names {db zSql} { 664 set ret [list] 665 set VM [sqlite3_prepare db $zSql -1 TAIL] 666 for {set ii 1} {$ii <= [sqlite3_bind_parameter_count $VM]} {incr ii} { 667 lappend ret [sqlite3_bind_parameter_name $VM $ii] 668 } 669 sqlite3_finalize $VM 670 set ret 671 } 672 673 do_test bind-14.1 { 674 param_names db { SELECT @a, @b } 675 } {@a @b} 676 do_test bind-14.2 { 677 param_names db { SELECT NULL FROM (SELECT NULL) WHERE @a = @b } 678 } {@a @b} 679 do_test bind-14.3 { 680 param_names db { SELECT @a FROM (SELECT NULL) WHERE 1 = @b } 681 } {@a @b} 682 do_test bind-14.4 { 683 param_names db { SELECT @a, @b FROM (SELECT NULL) } 684 } {@a @b} 685 686 #-------------------------------------------------------------------------- 687 # Tests of the OP_Variable opcode where P3>1 688 # 689 do_test bind-15.1 { 690 db eval {CREATE TABLE t4(a,b,c,d,e,f,g,h);} 691 set VM [sqlite3_prepare db { 692 INSERT INTO t4(a,b,c,d,f,g,h,e) VALUES(?,?,?,?,?,?,?,?) 693 } -1 TAIL] 694 sqlite3_bind_int $VM 1 1 695 sqlite3_bind_int $VM 2 2 696 sqlite3_bind_int $VM 3 3 697 sqlite3_bind_int $VM 4 4 698 sqlite3_bind_int $VM 5 5 699 sqlite3_bind_int $VM 6 6 700 sqlite3_bind_int $VM 7 7 701 sqlite3_bind_int $VM 8 8 702 sqlite3_step $VM 703 sqlite3_finalize $VM 704 db eval {SELECT * FROM t4} 705 } {1 2 3 4 8 5 6 7} 706 do_test bind-15.2 { 707 db eval {DELETE FROM t4} 708 set VM [sqlite3_prepare db { 709 INSERT INTO t4(a,b,c,d,e,f,g,h) VALUES(?,?,?,?,?,?,?,?) 710 } -1 TAIL] 711 sqlite3_bind_int $VM 1 1 712 sqlite3_bind_int $VM 2 2 713 sqlite3_bind_int $VM 3 3 714 sqlite3_bind_int $VM 4 4 715 sqlite3_bind_int $VM 5 5 716 sqlite3_bind_int $VM 6 6 717 sqlite3_bind_int $VM 7 7 718 sqlite3_bind_int $VM 8 8 719 sqlite3_step $VM 720 sqlite3_finalize $VM 721 db eval {SELECT * FROM t4} 722 } {1 2 3 4 5 6 7 8} 723 do_test bind-15.3 { 724 db eval {DELETE FROM t4} 725 set VM [sqlite3_prepare db { 726 INSERT INTO t4(h,g,f,e,d,c,b,a) VALUES(?,?,?,?,?,?,?,?) 727 } -1 TAIL] 728 sqlite3_bind_int $VM 1 1 729 sqlite3_bind_int $VM 2 2 730 sqlite3_bind_int $VM 3 3 731 sqlite3_bind_int $VM 4 4 732 sqlite3_bind_int $VM 5 5 733 sqlite3_bind_int $VM 6 6 734 sqlite3_bind_int $VM 7 7 735 sqlite3_bind_int $VM 8 8 736 sqlite3_step $VM 737 sqlite3_finalize $VM 738 db eval {SELECT * FROM t4} 739 } {8 7 6 5 4 3 2 1} 740 do_test bind-15.4 { 741 db eval {DELETE FROM t4} 742 set VM [sqlite3_prepare db { 743 INSERT INTO t4(a,b,c,d,e,f,g,h) VALUES(?,?,?,?4,?,?6,?,?) 744 } -1 TAIL] 745 sqlite3_bind_int $VM 1 1 746 sqlite3_bind_int $VM 2 2 747 sqlite3_bind_int $VM 3 3 748 sqlite3_bind_int $VM 4 4 749 sqlite3_bind_int $VM 5 5 750 sqlite3_bind_int $VM 6 6 751 sqlite3_bind_int $VM 7 7 752 sqlite3_bind_int $VM 8 8 753 sqlite3_step $VM 754 sqlite3_finalize $VM 755 db eval {SELECT * FROM t4} 756 } {1 2 3 4 5 6 7 8} 757 758 finish_test 759
