Annotation of embedaddon/sqlite3/test/bind.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to bind.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test