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.1.1.1 2012/02/21 17:04:16 misho 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>