xref: /sqlite-3.40.0/test/bind.test (revision 494fa66d) 
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.29 2005/01/29 09:00:49 danielk1977 Exp $
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20proc 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
37do_test bind-1.1 {
38  db close
39  set DB [sqlite3 db test.db]
40  execsql {CREATE TABLE t1(a,b,c);}
41  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL]
42  set TAIL
43} {}
44do_test bind-1.1.1 {
45  sqlite3_bind_parameter_count $VM
46} 3
47do_test bind-1.1.2 {
48  sqlite3_bind_parameter_name $VM 1
49} {:1}
50do_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
55} {:abc}
56do_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 {
63  sqlite3_reset $VM
64  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 {
71  sqlite3_reset $VM
72  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 {
79  sqlite3_reset $VM
80  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 {
89  sqlite3_reset $VM
90  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
95do_test bind-1.99 {
96  sqlite3_finalize $VM
97} SQLITE_OK
98
99# Prepare the statement in different ways depending on whether or not
100# the $var processing is compiled into the library.
101#
102ifcapable {tclvar} {
103  do_test bind-2.1 {
104    execsql {
105      DELETE FROM t1;
106    }
107    set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,${x{y}z})}\
108            -1 TX]
109    set TX
110  } {}
111  set v1 {$one}
112  set v2 {$::two}
113  set v3 {${x{y}z}}
114}
115ifcapable {!tclvar} {
116  do_test bind-2.1 {
117    execsql {
118      DELETE FROM t1;
119    }
120    set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX]
121    set TX
122  } {}
123  set v1 {:one}
124  set v2 {:two}
125  set v3 {:_}
126}
127
128do_test bind-2.1.1 {
129  sqlite3_bind_parameter_count $VM
130} 3
131do_test bind-2.1.2 {
132  sqlite3_bind_parameter_name $VM 1
133} $v1
134do_test bind-2.1.3 {
135  sqlite3_bind_parameter_name $VM 2
136} $v2
137do_test bind-2.1.4 {
138  sqlite3_bind_parameter_name $VM 3
139} $v3
140do_test bind-2.1.5 {
141  sqlite3_bind_parameter_index $VM $v1
142} 1
143do_test bind-2.1.6 {
144  sqlite3_bind_parameter_index $VM $v2
145} 2
146do_test bind-2.1.7 {
147  sqlite3_bind_parameter_index $VM $v3
148} 3
149do_test bind-2.1.8 {
150  sqlite3_bind_parameter_index $VM {:hi}
151} 0
152
153# 32 bit Integers
154do_test bind-2.2 {
155  sqlite3_bind_int $VM 1 123
156  sqlite3_bind_int $VM 2 456
157  sqlite3_bind_int $VM 3 789
158  sqlite_step $VM N VALUES COLNAMES
159  sqlite3_reset $VM
160  execsql {SELECT rowid, * FROM t1}
161} {1 123 456 789}
162do_test bind-2.3 {
163  sqlite3_bind_int $VM 2 -2000000000
164  sqlite3_bind_int $VM 3 2000000000
165  sqlite_step $VM N VALUES COLNAMES
166  sqlite3_reset $VM
167  execsql {SELECT rowid, * FROM t1}
168} {1 123 456 789 2 123 -2000000000 2000000000}
169do_test bind-2.4 {
170  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
171} {integer integer integer integer integer integer}
172do_test bind-2.5 {
173  execsql {
174    DELETE FROM t1;
175  }
176} {}
177
178# 64 bit Integers
179do_test bind-3.1 {
180  sqlite3_bind_int64 $VM 1 32
181  sqlite3_bind_int64 $VM 2 -2000000000000
182  sqlite3_bind_int64 $VM 3 2000000000000
183  sqlite_step $VM N VALUES COLNAMES
184  sqlite3_reset $VM
185  execsql {SELECT rowid, * FROM t1}
186} {1 32 -2000000000000 2000000000000}
187do_test bind-3.2 {
188  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
189} {integer integer integer}
190do_test bind-3.3 {
191  execsql {
192    DELETE FROM t1;
193  }
194} {}
195
196# Doubles
197do_test bind-4.1 {
198  sqlite3_bind_double $VM 1 1234.1234
199  sqlite3_bind_double $VM 2 0.00001
200  sqlite3_bind_double $VM 3 123456789
201  sqlite_step $VM N VALUES COLNAMES
202  sqlite3_reset $VM
203  execsql {SELECT rowid, * FROM t1}
204} {1 1234.1234 1e-05 123456789.0}
205do_test bind-4.2 {
206  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
207} {real real real}
208do_test bind-4.3 {
209  execsql {
210    DELETE FROM t1;
211  }
212} {}
213
214# NULL
215do_test bind-5.1 {
216  sqlite3_bind_null $VM 1
217  sqlite3_bind_null $VM 2
218  sqlite3_bind_null $VM 3
219  sqlite_step $VM N VALUES COLNAMES
220  sqlite3_reset $VM
221  execsql {SELECT rowid, * FROM t1}
222} {1 {} {} {}}
223do_test bind-5.2 {
224  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
225} {null null null}
226do_test bind-5.3 {
227  execsql {
228    DELETE FROM t1;
229  }
230} {}
231
232# UTF-8 text
233do_test bind-6.1 {
234  sqlite3_bind_text $VM 1 hellothere 5
235  sqlite3_bind_text $VM 2 ".." 1
236  sqlite3_bind_text $VM 3 world -1
237  sqlite_step $VM N VALUES COLNAMES
238  sqlite3_reset $VM
239  execsql {SELECT rowid, * FROM t1}
240} {1 hello . world}
241do_test bind-6.2 {
242  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
243} {text text text}
244do_test bind-6.3 {
245  execsql {
246    DELETE FROM t1;
247  }
248} {}
249
250# UTF-16 text
251ifcapable {utf16} {
252  do_test bind-7.1 {
253    sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
254    sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
255    sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
256    sqlite_step $VM N VALUES COLNAMES
257    sqlite3_reset $VM
258    execsql {SELECT rowid, * FROM t1}
259  } {1 hello {} world}
260  do_test bind-7.2 {
261    execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
262  } {text text text}
263}
264do_test bind-7.3 {
265  execsql {
266    DELETE FROM t1;
267  }
268} {}
269
270# Test that the 'out of range' error works.
271do_test bind-8.1 {
272  catch { sqlite3_bind_null $VM 0 }
273} {1}
274do_test bind-8.2 {
275  sqlite3_errmsg $DB
276} {bind or column index out of range}
277ifcapable {utf16} {
278  do_test bind-8.3 {
279    encoding convertfrom unicode [sqlite3_errmsg16 $DB]
280  } {bind or column index out of range}
281}
282do_test bind-8.4 {
283  sqlite3_bind_null $VM 1
284  sqlite3_errmsg $DB
285} {not an error}
286do_test bind-8.5 {
287  catch { sqlite3_bind_null $VM 4 }
288} {1}
289do_test bind-8.6 {
290  sqlite3_errmsg $DB
291} {bind or column index out of range}
292ifcapable {utf16} {
293  do_test bind-8.7 {
294    encoding convertfrom unicode [sqlite3_errmsg16 $DB]
295  } {bind or column index out of range}
296}
297
298do_test bind-8.8 {
299  catch { sqlite3_bind_blob $VM 0 "abc" 3 }
300} {1}
301do_test bind-8.9 {
302  catch { sqlite3_bind_blob $VM 4 "abc" 3 }
303} {1}
304do_test bind-8.10 {
305  catch { sqlite3_bind_text $VM 0 "abc" 3 }
306} {1}
307ifcapable {utf16} {
308  do_test bind-8.11 {
309    catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
310  } {1}
311}
312do_test bind-8.12 {
313  catch { sqlite3_bind_int $VM 0 5 }
314} {1}
315do_test bind-8.13 {
316  catch { sqlite3_bind_int $VM 4 5 }
317} {1}
318do_test bind-8.14 {
319  catch { sqlite3_bind_double $VM 0 5.0 }
320} {1}
321do_test bind-8.15 {
322  catch { sqlite3_bind_double $VM 4 6.0 }
323} {1}
324
325do_test bind-8.99 {
326  sqlite3_finalize $VM
327} SQLITE_OK
328
329do_test bind-9.1 {
330  execsql {
331    CREATE TABLE t2(a,b,c,d,e,f);
332  }
333  set rc [catch {
334    sqlite3_prepare $DB {
335      INSERT INTO t2(a) VALUES(?0)
336    } -1 TAIL
337  } msg]
338  lappend rc $msg
339} {1 {(1) variable number must be between ?1 and ?999}}
340do_test bind-9.2 {
341  set rc [catch {
342    sqlite3_prepare $DB {
343      INSERT INTO t2(a) VALUES(?1000)
344    } -1 TAIL
345  } msg]
346  lappend rc $msg
347} {1 {(1) variable number must be between ?1 and ?999}}
348do_test bind-9.3 {
349  set VM [
350    sqlite3_prepare $DB {
351      INSERT INTO t2(a,b) VALUES(?1,?999)
352    } -1 TAIL
353  ]
354  sqlite3_bind_parameter_count $VM
355} {999}
356catch {sqlite3_finalize $VM}
357do_test bind-9.4 {
358  set VM [
359    sqlite3_prepare $DB {
360      INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?)
361    } -1 TAIL
362  ]
363  sqlite3_bind_parameter_count $VM
364} {1001}
365do_test bind-9.5 {
366  sqlite3_bind_int $VM 1 1
367  sqlite3_bind_int $VM 999 999
368  sqlite3_bind_int $VM 1000 1000
369  sqlite3_bind_int $VM 1001 1001
370  sqlite3_step $VM
371} SQLITE_DONE
372do_test bind-9.6 {
373  sqlite3_finalize $VM
374} SQLITE_OK
375do_test bind-9.7 {
376  execsql {SELECT * FROM t2}
377} {1 999 1000 1001 {} {}}
378
379ifcapable {tclvar} {
380  do_test bind-10.1 {
381    set VM [
382      sqlite3_prepare $DB {
383        INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
384      } -1 TAIL
385    ]
386    sqlite3_bind_parameter_count $VM
387  } 3
388  set v1 {$abc}
389  set v2 {$ab}
390}
391ifcapable {!tclvar} {
392  do_test bind-10.1 {
393    set VM [
394      sqlite3_prepare $DB {
395        INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)
396      } -1 TAIL
397    ]
398    sqlite3_bind_parameter_count $VM
399  } 3
400  set v1 {:xyz}
401  set v2 {:xy}
402}
403do_test bind-10.2 {
404  sqlite3_bind_parameter_index $VM :abc
405} 1
406do_test bind-10.3 {
407  sqlite3_bind_parameter_index $VM $v1
408} 2
409do_test bind-10.4 {
410  sqlite3_bind_parameter_index $VM $v2
411} 3
412do_test bind-10.5 {
413  sqlite3_bind_parameter_name $VM 1
414} :abc
415do_test bind-10.6 {
416  sqlite3_bind_parameter_name $VM 2
417} $v1
418do_test bind-10.7 {
419  sqlite3_bind_parameter_name $VM 3
420} $v2
421do_test bind-10.7.1 {
422  sqlite3_bind_parameter_name 0 1   ;# Ignore if VM is NULL
423} {}
424do_test bind-10.7.2 {
425  sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small
426} {}
427do_test bind-10.7.3 {
428  sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big
429} {}
430do_test bind-10.8 {
431  sqlite3_bind_int $VM 1 1
432  sqlite3_bind_int $VM 2 2
433  sqlite3_bind_int $VM 3 3
434  sqlite3_step $VM
435} SQLITE_DONE
436do_test bind-10.8.1 {
437  # Binding attempts after program start should fail
438  set rc [catch {
439    sqlite3_bind_int $VM 1 1
440  } msg]
441  lappend rc $msg
442} {1 {}}
443do_test bind-10.9 {
444  sqlite3_finalize $VM
445} SQLITE_OK
446breakpoint
447do_test bind-10.10 {
448  execsql {SELECT * FROM t2}
449} {1 999 1000 1001 {} {} 1 2 1 3 2 1}
450
451# Ticket #918
452#
453do_test bind-10.11 {
454  catch {sqlite3_finalize $VM}
455  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
462do_test bind-10.11.1 {
463  sqlite3_bind_parameter_index 0 :xyz  ;# ignore NULL VM arguments
464} 0
465do_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
480do_test bind-10.17 {
481  sqlite3_bind_parameter_name $VM 2
482} {}
483do_test bind-10.18 {
484  sqlite3_bind_parameter_name $VM 3
485} {}
486do_test bind-10.19 {
487  sqlite3_bind_parameter_name $VM 4
488} {?4}
489do_test bind-10.20 {
490  sqlite3_bind_parameter_name $VM 5
491} :pqr
492catch {sqlite3_finalize $VM}
493
494# Make sure we catch an unterminated "(" in a Tcl-style variable name
495#
496ifcapable 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}
501
502finish_test
503