blob: 2a3115e49074d653b4281d1582000ec492294586 [file] [log] [blame]
drh82a48512003-09-06 22:45:20 +00001# 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#
drhf9cb7f52006-06-27 20:06:44 +000014# $Id: bind.test,v 1.38 2006/06/27 20:06:45 drh Exp $
drh82a48512003-09-06 22:45:20 +000015#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
danielk197717240fd2004-05-26 00:07:25 +000020proc 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} {
drheb2e1762004-05-27 01:53:56 +000031 lappend vals [sqlite3_column_text $stmt $i]
danielk197717240fd2004-05-26 00:07:25 +000032 }
33
34 return $rc
35}
36
drh82a48512003-09-06 22:45:20 +000037do_test bind-1.1 {
drhdddca282006-01-03 00:33:50 +000038 set DB [sqlite3_connection_pointer db]
drh2c6674c2004-08-25 04:07:01 +000039 execsql {CREATE TABLE t1(a,b,c);}
40 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL]
drh82a48512003-09-06 22:45:20 +000041 set TAIL
42} {}
drh75f6a032004-07-15 14:15:00 +000043do_test bind-1.1.1 {
44 sqlite3_bind_parameter_count $VM
45} 3
drh895d7472004-08-20 16:02:39 +000046do_test bind-1.1.2 {
47 sqlite3_bind_parameter_name $VM 1
drh2c6674c2004-08-25 04:07:01 +000048} {:1}
drh895d7472004-08-20 16:02:39 +000049do_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
drh2c6674c2004-08-25 04:07:01 +000054} {:abc}
drh82a48512003-09-06 22:45:20 +000055do_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 {
danielk1977106bb232004-05-21 10:08:53 +000062 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000063 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 {
danielk1977106bb232004-05-21 10:08:53 +000070 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000071 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 {
danielk1977106bb232004-05-21 10:08:53 +000078 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000079 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 {
danielk1977106bb232004-05-21 10:08:53 +000088 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000089 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
drh82a48512003-09-06 22:45:20 +000094do_test bind-1.99 {
danielk1977106bb232004-05-21 10:08:53 +000095 sqlite3_finalize $VM
danielk19773cf86062004-05-26 10:11:05 +000096} SQLITE_OK
drh82a48512003-09-06 22:45:20 +000097
drh6bf89572004-11-03 16:27:01 +000098# 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 }
drh288d37f2005-06-22 08:48:06 +0000106 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,$x(-z-))}\
drh48e5aa22005-01-11 17:46:41 +0000107 -1 TX]
drh6bf89572004-11-03 16:27:01 +0000108 set TX
109 } {}
110 set v1 {$one}
111 set v2 {$::two}
drh288d37f2005-06-22 08:48:06 +0000112 set v3 {$x(-z-)}
drh6bf89572004-11-03 16:27:01 +0000113}
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
drh895d7472004-08-20 16:02:39 +0000127do_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
drh6bf89572004-11-03 16:27:01 +0000132} $v1
drh895d7472004-08-20 16:02:39 +0000133do_test bind-2.1.3 {
134 sqlite3_bind_parameter_name $VM 2
drh6bf89572004-11-03 16:27:01 +0000135} $v2
drh895d7472004-08-20 16:02:39 +0000136do_test bind-2.1.4 {
137 sqlite3_bind_parameter_name $VM 3
drh6bf89572004-11-03 16:27:01 +0000138} $v3
drhfa6bc002004-09-07 16:19:52 +0000139do_test bind-2.1.5 {
drh6bf89572004-11-03 16:27:01 +0000140 sqlite3_bind_parameter_index $VM $v1
drhfa6bc002004-09-07 16:19:52 +0000141} 1
142do_test bind-2.1.6 {
drh6bf89572004-11-03 16:27:01 +0000143 sqlite3_bind_parameter_index $VM $v2
drhfa6bc002004-09-07 16:19:52 +0000144} 2
145do_test bind-2.1.7 {
drh6bf89572004-11-03 16:27:01 +0000146 sqlite3_bind_parameter_index $VM $v3
drhfa6bc002004-09-07 16:19:52 +0000147} 3
148do_test bind-2.1.8 {
149 sqlite3_bind_parameter_index $VM {:hi}
150} 0
danielk197751e3d8e2004-05-20 01:12:34 +0000151
152# 32 bit Integers
153do_test bind-2.2 {
drh241db312004-06-22 12:46:53 +0000154 sqlite3_bind_int $VM 1 123
155 sqlite3_bind_int $VM 2 456
156 sqlite3_bind_int $VM 3 789
danielk197751e3d8e2004-05-20 01:12:34 +0000157 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000158 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000159 execsql {SELECT rowid, * FROM t1}
160} {1 123 456 789}
161do_test bind-2.3 {
drh241db312004-06-22 12:46:53 +0000162 sqlite3_bind_int $VM 2 -2000000000
163 sqlite3_bind_int $VM 3 2000000000
danielk197751e3d8e2004-05-20 01:12:34 +0000164 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000165 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000166 execsql {SELECT rowid, * FROM t1}
167} {1 123 456 789 2 123 -2000000000 2000000000}
168do_test bind-2.4 {
danielk197735bb9d02004-05-24 12:55:54 +0000169 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
170} {integer integer integer integer integer integer}
danielk197751e3d8e2004-05-20 01:12:34 +0000171do_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
danielk1977106bb232004-05-21 10:08:53 +0000183 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000184 execsql {SELECT rowid, * FROM t1}
185} {1 32 -2000000000000 2000000000000}
186do_test bind-3.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000187 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
188} {integer integer integer}
danielk197751e3d8e2004-05-20 01:12:34 +0000189do_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
danielk1977106bb232004-05-21 10:08:53 +0000201 sqlite3_reset $VM
drhab34b8f2005-03-20 23:18:57 +0000202 set x [execsql {SELECT rowid, * FROM t1}]
203 regsub {1e-005} $x {1e-05} y
204 set y
drh92febd92004-08-20 18:34:20 +0000205} {1 1234.1234 1e-05 123456789.0}
danielk197751e3d8e2004-05-20 01:12:34 +0000206do_test bind-4.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000207 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
208} {real real real}
danielk197751e3d8e2004-05-20 01:12:34 +0000209do_test bind-4.3 {
210 execsql {
211 DELETE FROM t1;
212 }
213} {}
214
215# NULL
216do_test bind-5.1 {
217 sqlite3_bind_null $VM 1
218 sqlite3_bind_null $VM 2
219 sqlite3_bind_null $VM 3
220 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000221 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000222 execsql {SELECT rowid, * FROM t1}
223} {1 {} {} {}}
224do_test bind-5.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000225 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
226} {null null null}
danielk197751e3d8e2004-05-20 01:12:34 +0000227do_test bind-5.3 {
228 execsql {
229 DELETE FROM t1;
230 }
231} {}
232
233# UTF-8 text
234do_test bind-6.1 {
235 sqlite3_bind_text $VM 1 hellothere 5
danielk1977c572ef72004-05-27 09:28:41 +0000236 sqlite3_bind_text $VM 2 ".." 1
danielk197751e3d8e2004-05-20 01:12:34 +0000237 sqlite3_bind_text $VM 3 world -1
238 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000239 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000240 execsql {SELECT rowid, * FROM t1}
241} {1 hello . world}
242do_test bind-6.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000243 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
244} {text text text}
danielk197751e3d8e2004-05-20 01:12:34 +0000245do_test bind-6.3 {
246 execsql {
247 DELETE FROM t1;
248 }
249} {}
250
251# UTF-16 text
drh6c626082004-11-14 21:56:29 +0000252ifcapable {utf16} {
253 do_test bind-7.1 {
254 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
255 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
256 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
257 sqlite_step $VM N VALUES COLNAMES
258 sqlite3_reset $VM
259 execsql {SELECT rowid, * FROM t1}
260 } {1 hello {} world}
261 do_test bind-7.2 {
262 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
263 } {text text text}
264}
danielk197751e3d8e2004-05-20 01:12:34 +0000265do_test bind-7.3 {
266 execsql {
267 DELETE FROM t1;
268 }
269} {}
270
danielk19776622cce2004-05-20 11:00:52 +0000271# Test that the 'out of range' error works.
272do_test bind-8.1 {
273 catch { sqlite3_bind_null $VM 0 }
274} {1}
275do_test bind-8.2 {
276 sqlite3_errmsg $DB
drhb08153d2004-11-20 20:18:55 +0000277} {bind or column index out of range}
drh6c626082004-11-14 21:56:29 +0000278ifcapable {utf16} {
279 do_test bind-8.3 {
280 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
drhb08153d2004-11-20 20:18:55 +0000281 } {bind or column index out of range}
drh6c626082004-11-14 21:56:29 +0000282}
danielk19776622cce2004-05-20 11:00:52 +0000283do_test bind-8.4 {
284 sqlite3_bind_null $VM 1
285 sqlite3_errmsg $DB
286} {not an error}
287do_test bind-8.5 {
288 catch { sqlite3_bind_null $VM 4 }
289} {1}
290do_test bind-8.6 {
291 sqlite3_errmsg $DB
drhb08153d2004-11-20 20:18:55 +0000292} {bind or column index out of range}
drh6c626082004-11-14 21:56:29 +0000293ifcapable {utf16} {
294 do_test bind-8.7 {
295 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
drhb08153d2004-11-20 20:18:55 +0000296 } {bind or column index out of range}
drh6c626082004-11-14 21:56:29 +0000297}
danielk19776622cce2004-05-20 11:00:52 +0000298
danielk1977f4618892004-06-28 13:09:11 +0000299do_test bind-8.8 {
300 catch { sqlite3_bind_blob $VM 0 "abc" 3 }
301} {1}
302do_test bind-8.9 {
303 catch { sqlite3_bind_blob $VM 4 "abc" 3 }
304} {1}
305do_test bind-8.10 {
306 catch { sqlite3_bind_text $VM 0 "abc" 3 }
307} {1}
drh6c626082004-11-14 21:56:29 +0000308ifcapable {utf16} {
309 do_test bind-8.11 {
310 catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
311 } {1}
312}
danielk1977f4618892004-06-28 13:09:11 +0000313do_test bind-8.12 {
314 catch { sqlite3_bind_int $VM 0 5 }
315} {1}
316do_test bind-8.13 {
317 catch { sqlite3_bind_int $VM 4 5 }
318} {1}
319do_test bind-8.14 {
320 catch { sqlite3_bind_double $VM 0 5.0 }
321} {1}
322do_test bind-8.15 {
323 catch { sqlite3_bind_double $VM 4 6.0 }
324} {1}
danielk19776622cce2004-05-20 11:00:52 +0000325
drhfa6bc002004-09-07 16:19:52 +0000326do_test bind-8.99 {
danielk1977106bb232004-05-21 10:08:53 +0000327 sqlite3_finalize $VM
danielk19773cf86062004-05-26 10:11:05 +0000328} SQLITE_OK
danielk197751e3d8e2004-05-20 01:12:34 +0000329
drhfa6bc002004-09-07 16:19:52 +0000330do_test bind-9.1 {
331 execsql {
332 CREATE TABLE t2(a,b,c,d,e,f);
333 }
334 set rc [catch {
335 sqlite3_prepare $DB {
336 INSERT INTO t2(a) VALUES(?0)
337 } -1 TAIL
338 } msg]
339 lappend rc $msg
340} {1 {(1) variable number must be between ?1 and ?999}}
341do_test bind-9.2 {
342 set rc [catch {
343 sqlite3_prepare $DB {
344 INSERT INTO t2(a) VALUES(?1000)
345 } -1 TAIL
346 } msg]
347 lappend rc $msg
348} {1 {(1) variable number must be between ?1 and ?999}}
349do_test bind-9.3 {
350 set VM [
351 sqlite3_prepare $DB {
352 INSERT INTO t2(a,b) VALUES(?1,?999)
353 } -1 TAIL
354 ]
355 sqlite3_bind_parameter_count $VM
356} {999}
357catch {sqlite3_finalize $VM}
358do_test bind-9.4 {
359 set VM [
360 sqlite3_prepare $DB {
361 INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?)
362 } -1 TAIL
363 ]
364 sqlite3_bind_parameter_count $VM
365} {1001}
366do_test bind-9.5 {
367 sqlite3_bind_int $VM 1 1
368 sqlite3_bind_int $VM 999 999
369 sqlite3_bind_int $VM 1000 1000
370 sqlite3_bind_int $VM 1001 1001
371 sqlite3_step $VM
372} SQLITE_DONE
373do_test bind-9.6 {
374 sqlite3_finalize $VM
375} SQLITE_OK
376do_test bind-9.7 {
377 execsql {SELECT * FROM t2}
378} {1 999 1000 1001 {} {}}
danielk197751e3d8e2004-05-20 01:12:34 +0000379
drh6bf89572004-11-03 16:27:01 +0000380ifcapable {tclvar} {
381 do_test bind-10.1 {
drh6bf89572004-11-03 16:27:01 +0000382 set VM [
383 sqlite3_prepare $DB {
384 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
385 } -1 TAIL
386 ]
387 sqlite3_bind_parameter_count $VM
388 } 3
389 set v1 {$abc}
390 set v2 {$ab}
391}
392ifcapable {!tclvar} {
393 do_test bind-10.1 {
drh6bf89572004-11-03 16:27:01 +0000394 set VM [
395 sqlite3_prepare $DB {
396 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)
397 } -1 TAIL
398 ]
399 sqlite3_bind_parameter_count $VM
400 } 3
401 set v1 {:xyz}
402 set v2 {:xy}
403}
drhfa6bc002004-09-07 16:19:52 +0000404do_test bind-10.2 {
405 sqlite3_bind_parameter_index $VM :abc
406} 1
407do_test bind-10.3 {
drh6bf89572004-11-03 16:27:01 +0000408 sqlite3_bind_parameter_index $VM $v1
drhfa6bc002004-09-07 16:19:52 +0000409} 2
410do_test bind-10.4 {
drh6bf89572004-11-03 16:27:01 +0000411 sqlite3_bind_parameter_index $VM $v2
drhfa6bc002004-09-07 16:19:52 +0000412} 3
413do_test bind-10.5 {
414 sqlite3_bind_parameter_name $VM 1
415} :abc
416do_test bind-10.6 {
417 sqlite3_bind_parameter_name $VM 2
drh6bf89572004-11-03 16:27:01 +0000418} $v1
drhfa6bc002004-09-07 16:19:52 +0000419do_test bind-10.7 {
420 sqlite3_bind_parameter_name $VM 3
drh6bf89572004-11-03 16:27:01 +0000421} $v2
drhc5cdca62005-01-11 16:54:14 +0000422do_test bind-10.7.1 {
423 sqlite3_bind_parameter_name 0 1 ;# Ignore if VM is NULL
424} {}
425do_test bind-10.7.2 {
426 sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small
427} {}
428do_test bind-10.7.3 {
429 sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big
430} {}
drhfa6bc002004-09-07 16:19:52 +0000431do_test bind-10.8 {
432 sqlite3_bind_int $VM 1 1
433 sqlite3_bind_int $VM 2 2
434 sqlite3_bind_int $VM 3 3
435 sqlite3_step $VM
436} SQLITE_DONE
drhc5cdca62005-01-11 16:54:14 +0000437do_test bind-10.8.1 {
438 # Binding attempts after program start should fail
439 set rc [catch {
440 sqlite3_bind_int $VM 1 1
441 } msg]
442 lappend rc $msg
443} {1 {}}
drhfa6bc002004-09-07 16:19:52 +0000444do_test bind-10.9 {
445 sqlite3_finalize $VM
446} SQLITE_OK
447do_test bind-10.10 {
448 execsql {SELECT * FROM t2}
449} {1 999 1000 1001 {} {} 1 2 1 3 2 1}
drh82a48512003-09-06 22:45:20 +0000450
drh971a7c82004-09-24 12:48:12 +0000451# Ticket #918
452#
453do_test bind-10.11 {
drha86a5b62006-01-23 18:42:21 +0000454 # catch {sqlite3_finalize $VM}
drh971a7c82004-09-24 12:48:12 +0000455 set VM [
456 sqlite3_prepare $DB {
457 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4)
458 } -1 TAIL
459 ]
460 sqlite3_bind_parameter_count $VM
461} 5
drhc5cdca62005-01-11 16:54:14 +0000462do_test bind-10.11.1 {
463 sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments
464} 0
drh971a7c82004-09-24 12:48:12 +0000465do_test bind-10.12 {
466 sqlite3_bind_parameter_index $VM :xyz
467} 0
468do_test bind-10.13 {
469 sqlite3_bind_parameter_index $VM {}
470} 0
471do_test bind-10.14 {
472 sqlite3_bind_parameter_index $VM :pqr
473} 5
474do_test bind-10.15 {
475 sqlite3_bind_parameter_index $VM ?4
476} 4
477do_test bind-10.16 {
478 sqlite3_bind_parameter_name $VM 1
479} :abc
danielk1977b3bce662005-01-29 08:32:43 +0000480do_test bind-10.17 {
drh971a7c82004-09-24 12:48:12 +0000481 sqlite3_bind_parameter_name $VM 2
482} {}
danielk1977b3bce662005-01-29 08:32:43 +0000483do_test bind-10.18 {
drh971a7c82004-09-24 12:48:12 +0000484 sqlite3_bind_parameter_name $VM 3
485} {}
danielk1977b3bce662005-01-29 08:32:43 +0000486do_test bind-10.19 {
drh971a7c82004-09-24 12:48:12 +0000487 sqlite3_bind_parameter_name $VM 4
488} {?4}
danielk1977b3bce662005-01-29 08:32:43 +0000489do_test bind-10.20 {
drh971a7c82004-09-24 12:48:12 +0000490 sqlite3_bind_parameter_name $VM 5
491} :pqr
492catch {sqlite3_finalize $VM}
493
drh48e5aa22005-01-11 17:46:41 +0000494# Make sure we catch an unterminated "(" in a Tcl-style variable name
495#
danielk19774489f9b2005-01-20 02:17:01 +0000496ifcapable tclvar {
497 do_test bind-11.1 {
498 catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;}
499 } {1 {unrecognized token: "$abc(123"}}
500}
drh48e5aa22005-01-11 17:46:41 +0000501
drhe57c06f2005-12-09 20:54:34 +0000502if {[execsql {pragma encoding}]=="UTF-8"} {
503 # Test the ability to bind text that contains embedded '\000' characters.
drhf9cb7f52006-06-27 20:06:44 +0000504 # Make sure we can recover the entire input string.
drhe57c06f2005-12-09 20:54:34 +0000505 #
506 do_test bind-12.1 {
507 execsql {
508 CREATE TABLE t3(x BLOB);
509 }
510 set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL]
511 sqlite_bind $VM 1 not-used blob10
512 sqlite3_step $VM
513 sqlite3_finalize $VM
514 execsql {
515 SELECT typeof(x), length(x), quote(x),
516 length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3
517 }
518 } {text 3 'abc' 10 X'6162630078797A007071'}
519 do_test bind-12.2 {
520 sqlite3_create_function $DB
521 execsql {
522 SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3
523 }
524 } {X'6162630078797A007071'}
525}
drhbf8aa2a2005-12-02 02:44:05 +0000526
drhf9cb7f52006-06-27 20:06:44 +0000527# Test the operation of sqlite3_clear_bindings
528#
529do_test bind-13.1 {
530 set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL]
531 sqlite3_step $VM
532 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
533 [sqlite3_column_type $VM 2]
534} {NULL NULL NULL}
535do_test bind-13.2 {
536 sqlite3_reset $VM
537 sqlite3_bind_int $VM 1 1
538 sqlite3_bind_int $VM 2 2
539 sqlite3_bind_int $VM 3 3
540 sqlite3_step $VM
541 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
542 [sqlite3_column_type $VM 2]
543} {INTEGER INTEGER INTEGER}
544do_test bind-13.3 {
545 sqlite3_reset $VM
546 sqlite3_step $VM
547 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
548 [sqlite3_column_type $VM 2]
549} {INTEGER INTEGER INTEGER}
550do_test bind-13.4 {
551 sqlite3_reset $VM
552 sqlite3_clear_bindings $VM
553 sqlite3_step $VM
554 list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
555 [sqlite3_column_type $VM 2]
556} {NULL NULL NULL}
557sqlite3_finalize $VM
558
drh82a48512003-09-06 22:45:20 +0000559finish_test