blob: f1c4d4c6d0ec4b273ba445f2f03824ff45aba5b0 [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#
drhfa6bc002004-09-07 16:19:52 +000014# $Id: bind.test,v 1.19 2004/09/07 16:19:54 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 {
38 db close
drhef4ac8f2004-06-19 00:16:31 +000039 set DB [sqlite3 db test.db]
drh2c6674c2004-08-25 04:07:01 +000040 execsql {CREATE TABLE t1(a,b,c);}
41 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL]
drh82a48512003-09-06 22:45:20 +000042 set TAIL
43} {}
drh75f6a032004-07-15 14:15:00 +000044do_test bind-1.1.1 {
45 sqlite3_bind_parameter_count $VM
46} 3
drh895d7472004-08-20 16:02:39 +000047do_test bind-1.1.2 {
48 sqlite3_bind_parameter_name $VM 1
drh2c6674c2004-08-25 04:07:01 +000049} {:1}
drh895d7472004-08-20 16:02:39 +000050do_test bind-1.1.3 {
51 sqlite3_bind_parameter_name $VM 2
52} {}
53do_test bind-1.1.4 {
54 sqlite3_bind_parameter_name $VM 3
drh2c6674c2004-08-25 04:07:01 +000055} {:abc}
drh82a48512003-09-06 22:45:20 +000056do_test bind-1.2 {
57 sqlite_step $VM N VALUES COLNAMES
58} {SQLITE_DONE}
59do_test bind-1.3 {
60 execsql {SELECT rowid, * FROM t1}
61} {1 {} {} {}}
62do_test bind-1.4 {
danielk1977106bb232004-05-21 10:08:53 +000063 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000064 sqlite_bind $VM 1 {test value 1} normal
65 sqlite_step $VM N VALUES COLNAMES
66} SQLITE_DONE
67do_test bind-1.5 {
68 execsql {SELECT rowid, * FROM t1}
69} {1 {} {} {} 2 {test value 1} {} {}}
70do_test bind-1.6 {
danielk1977106bb232004-05-21 10:08:53 +000071 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000072 sqlite_bind $VM 3 {'test value 2'} normal
73 sqlite_step $VM N VALUES COLNAMES
74} SQLITE_DONE
75do_test bind-1.7 {
76 execsql {SELECT rowid, * FROM t1}
77} {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}}
78do_test bind-1.8 {
danielk1977106bb232004-05-21 10:08:53 +000079 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000080 set sqlite_static_bind_value 123
81 sqlite_bind $VM 1 {} static
82 sqlite_bind $VM 2 {abcdefg} normal
83 sqlite_bind $VM 3 {} null
84 execsql {DELETE FROM t1}
85 sqlite_step $VM N VALUES COLNAMES
86 execsql {SELECT rowid, * FROM t1}
87} {1 123 abcdefg {}}
88do_test bind-1.9 {
danielk1977106bb232004-05-21 10:08:53 +000089 sqlite3_reset $VM
drh82a48512003-09-06 22:45:20 +000090 sqlite_bind $VM 1 {456} normal
91 sqlite_step $VM N VALUES COLNAMES
92 execsql {SELECT rowid, * FROM t1}
93} {1 123 abcdefg {} 2 456 abcdefg {}}
94
drh82a48512003-09-06 22:45:20 +000095do_test bind-1.99 {
danielk1977106bb232004-05-21 10:08:53 +000096 sqlite3_finalize $VM
danielk19773cf86062004-05-26 10:11:05 +000097} SQLITE_OK
drh82a48512003-09-06 22:45:20 +000098
danielk197751e3d8e2004-05-20 01:12:34 +000099do_test bind-2.1 {
100 execsql {
101 DELETE FROM t1;
102 }
drh895d7472004-08-20 16:02:39 +0000103 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,${x})} -1 TAIL]
danielk197751e3d8e2004-05-20 01:12:34 +0000104 set TAIL
105} {}
drh895d7472004-08-20 16:02:39 +0000106do_test bind-2.1.1 {
107 sqlite3_bind_parameter_count $VM
108} 3
109do_test bind-2.1.2 {
110 sqlite3_bind_parameter_name $VM 1
111} {$one}
112do_test bind-2.1.3 {
113 sqlite3_bind_parameter_name $VM 2
114} {$::two}
115do_test bind-2.1.4 {
116 sqlite3_bind_parameter_name $VM 3
117} {${x}}
drhfa6bc002004-09-07 16:19:52 +0000118do_test bind-2.1.5 {
119 sqlite3_bind_parameter_index $VM {$one}
120} 1
121do_test bind-2.1.6 {
122 sqlite3_bind_parameter_index $VM {$::two}
123} 2
124do_test bind-2.1.7 {
125 sqlite3_bind_parameter_index $VM {${x}}
126} 3
127do_test bind-2.1.8 {
128 sqlite3_bind_parameter_index $VM {:hi}
129} 0
danielk197751e3d8e2004-05-20 01:12:34 +0000130
131# 32 bit Integers
132do_test bind-2.2 {
drh241db312004-06-22 12:46:53 +0000133 sqlite3_bind_int $VM 1 123
134 sqlite3_bind_int $VM 2 456
135 sqlite3_bind_int $VM 3 789
danielk197751e3d8e2004-05-20 01:12:34 +0000136 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000137 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000138 execsql {SELECT rowid, * FROM t1}
139} {1 123 456 789}
140do_test bind-2.3 {
drh241db312004-06-22 12:46:53 +0000141 sqlite3_bind_int $VM 2 -2000000000
142 sqlite3_bind_int $VM 3 2000000000
danielk197751e3d8e2004-05-20 01:12:34 +0000143 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000144 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000145 execsql {SELECT rowid, * FROM t1}
146} {1 123 456 789 2 123 -2000000000 2000000000}
147do_test bind-2.4 {
danielk197735bb9d02004-05-24 12:55:54 +0000148 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
149} {integer integer integer integer integer integer}
danielk197751e3d8e2004-05-20 01:12:34 +0000150do_test bind-2.5 {
151 execsql {
152 DELETE FROM t1;
153 }
154} {}
155
156# 64 bit Integers
157do_test bind-3.1 {
158 sqlite3_bind_int64 $VM 1 32
159 sqlite3_bind_int64 $VM 2 -2000000000000
160 sqlite3_bind_int64 $VM 3 2000000000000
161 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000162 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000163 execsql {SELECT rowid, * FROM t1}
164} {1 32 -2000000000000 2000000000000}
165do_test bind-3.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000166 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
167} {integer integer integer}
danielk197751e3d8e2004-05-20 01:12:34 +0000168do_test bind-3.3 {
169 execsql {
170 DELETE FROM t1;
171 }
172} {}
173
174# Doubles
175do_test bind-4.1 {
176 sqlite3_bind_double $VM 1 1234.1234
177 sqlite3_bind_double $VM 2 0.00001
178 sqlite3_bind_double $VM 3 123456789
179 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000180 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000181 execsql {SELECT rowid, * FROM t1}
drh92febd92004-08-20 18:34:20 +0000182} {1 1234.1234 1e-05 123456789.0}
danielk197751e3d8e2004-05-20 01:12:34 +0000183do_test bind-4.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000184 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
185} {real real real}
danielk197751e3d8e2004-05-20 01:12:34 +0000186do_test bind-4.3 {
187 execsql {
188 DELETE FROM t1;
189 }
190} {}
191
192# NULL
193do_test bind-5.1 {
194 sqlite3_bind_null $VM 1
195 sqlite3_bind_null $VM 2
196 sqlite3_bind_null $VM 3
197 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000198 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000199 execsql {SELECT rowid, * FROM t1}
200} {1 {} {} {}}
201do_test bind-5.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000202 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
203} {null null null}
danielk197751e3d8e2004-05-20 01:12:34 +0000204do_test bind-5.3 {
205 execsql {
206 DELETE FROM t1;
207 }
208} {}
209
210# UTF-8 text
211do_test bind-6.1 {
212 sqlite3_bind_text $VM 1 hellothere 5
danielk1977c572ef72004-05-27 09:28:41 +0000213 sqlite3_bind_text $VM 2 ".." 1
danielk197751e3d8e2004-05-20 01:12:34 +0000214 sqlite3_bind_text $VM 3 world -1
215 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000216 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000217 execsql {SELECT rowid, * FROM t1}
218} {1 hello . world}
219do_test bind-6.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000220 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
221} {text text text}
danielk197751e3d8e2004-05-20 01:12:34 +0000222do_test bind-6.3 {
223 execsql {
224 DELETE FROM t1;
225 }
226} {}
227
228# UTF-16 text
229do_test bind-7.1 {
230 sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
231 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
232 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
233 sqlite_step $VM N VALUES COLNAMES
danielk1977106bb232004-05-21 10:08:53 +0000234 sqlite3_reset $VM
danielk197751e3d8e2004-05-20 01:12:34 +0000235 execsql {SELECT rowid, * FROM t1}
236} {1 hello {} world}
237do_test bind-7.2 {
danielk197735bb9d02004-05-24 12:55:54 +0000238 execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
239} {text text text}
danielk197751e3d8e2004-05-20 01:12:34 +0000240do_test bind-7.3 {
241 execsql {
242 DELETE FROM t1;
243 }
244} {}
245
danielk19776622cce2004-05-20 11:00:52 +0000246# Test that the 'out of range' error works.
247do_test bind-8.1 {
248 catch { sqlite3_bind_null $VM 0 }
249} {1}
250do_test bind-8.2 {
251 sqlite3_errmsg $DB
252} {bind index out of range}
253do_test bind-8.3 {
254 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
255} {bind index out of range}
256do_test bind-8.4 {
257 sqlite3_bind_null $VM 1
258 sqlite3_errmsg $DB
259} {not an error}
260do_test bind-8.5 {
261 catch { sqlite3_bind_null $VM 4 }
262} {1}
263do_test bind-8.6 {
264 sqlite3_errmsg $DB
265} {bind index out of range}
266do_test bind-8.7 {
267 encoding convertfrom unicode [sqlite3_errmsg16 $DB]
268} {bind index out of range}
269
danielk1977f4618892004-06-28 13:09:11 +0000270do_test bind-8.8 {
271 catch { sqlite3_bind_blob $VM 0 "abc" 3 }
272} {1}
273do_test bind-8.9 {
274 catch { sqlite3_bind_blob $VM 4 "abc" 3 }
275} {1}
276do_test bind-8.10 {
277 catch { sqlite3_bind_text $VM 0 "abc" 3 }
278} {1}
279do_test bind-8.11 {
280 catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
281} {1}
282do_test bind-8.12 {
283 catch { sqlite3_bind_int $VM 0 5 }
284} {1}
285do_test bind-8.13 {
286 catch { sqlite3_bind_int $VM 4 5 }
287} {1}
288do_test bind-8.14 {
289 catch { sqlite3_bind_double $VM 0 5.0 }
290} {1}
291do_test bind-8.15 {
292 catch { sqlite3_bind_double $VM 4 6.0 }
293} {1}
danielk19776622cce2004-05-20 11:00:52 +0000294
drhfa6bc002004-09-07 16:19:52 +0000295do_test bind-8.99 {
danielk1977106bb232004-05-21 10:08:53 +0000296 sqlite3_finalize $VM
danielk19773cf86062004-05-26 10:11:05 +0000297} SQLITE_OK
danielk197751e3d8e2004-05-20 01:12:34 +0000298
drhfa6bc002004-09-07 16:19:52 +0000299do_test bind-9.1 {
300 execsql {
301 CREATE TABLE t2(a,b,c,d,e,f);
302 }
303 set rc [catch {
304 sqlite3_prepare $DB {
305 INSERT INTO t2(a) VALUES(?0)
306 } -1 TAIL
307 } msg]
308 lappend rc $msg
309} {1 {(1) variable number must be between ?1 and ?999}}
310do_test bind-9.2 {
311 set rc [catch {
312 sqlite3_prepare $DB {
313 INSERT INTO t2(a) VALUES(?1000)
314 } -1 TAIL
315 } msg]
316 lappend rc $msg
317} {1 {(1) variable number must be between ?1 and ?999}}
318do_test bind-9.3 {
319 set VM [
320 sqlite3_prepare $DB {
321 INSERT INTO t2(a,b) VALUES(?1,?999)
322 } -1 TAIL
323 ]
324 sqlite3_bind_parameter_count $VM
325} {999}
326catch {sqlite3_finalize $VM}
327do_test bind-9.4 {
328 set VM [
329 sqlite3_prepare $DB {
330 INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?)
331 } -1 TAIL
332 ]
333 sqlite3_bind_parameter_count $VM
334} {1001}
335do_test bind-9.5 {
336 sqlite3_bind_int $VM 1 1
337 sqlite3_bind_int $VM 999 999
338 sqlite3_bind_int $VM 1000 1000
339 sqlite3_bind_int $VM 1001 1001
340 sqlite3_step $VM
341} SQLITE_DONE
342do_test bind-9.6 {
343 sqlite3_finalize $VM
344} SQLITE_OK
345do_test bind-9.7 {
346 execsql {SELECT * FROM t2}
347} {1 999 1000 1001 {} {}}
danielk197751e3d8e2004-05-20 01:12:34 +0000348
drhfa6bc002004-09-07 16:19:52 +0000349do_test bind-10.1 {
350 catch {sqlite3_finalize $VM}
351 set VM [
352 sqlite3_prepare $DB {
353 INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
354 } -1 TAIL
355 ]
356 sqlite3_bind_parameter_count $VM
357} 3
358do_test bind-10.2 {
359 sqlite3_bind_parameter_index $VM :abc
360} 1
361do_test bind-10.3 {
362 sqlite3_bind_parameter_index $VM {$abc}
363} 2
364do_test bind-10.4 {
365 sqlite3_bind_parameter_index $VM {$ab}
366} 3
367do_test bind-10.5 {
368 sqlite3_bind_parameter_name $VM 1
369} :abc
370do_test bind-10.6 {
371 sqlite3_bind_parameter_name $VM 2
372} {$abc}
373do_test bind-10.7 {
374 sqlite3_bind_parameter_name $VM 3
375} {$ab}
376do_test bind-10.8 {
377 sqlite3_bind_int $VM 1 1
378 sqlite3_bind_int $VM 2 2
379 sqlite3_bind_int $VM 3 3
380 sqlite3_step $VM
381} SQLITE_DONE
382do_test bind-10.9 {
383 sqlite3_finalize $VM
384} SQLITE_OK
385do_test bind-10.10 {
386 execsql {SELECT * FROM t2}
387} {1 999 1000 1001 {} {} 1 2 1 3 2 1}
drh82a48512003-09-06 22:45:20 +0000388
389finish_test