xref: /sqlite-3.40.0/test/bind.test (revision 74217cc0) 
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.32 2005/06/22 08:48:07 drh 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(-z-))}\
108            -1 TX]
109    set TX
110  } {}
111  set v1 {$one}
112  set v2 {$::two}
113  set v3 {$x(-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  set x [execsql {SELECT rowid, * FROM t1}]
204  regsub {1e-005} $x {1e-05} y
205  set y
206} {1 1234.1234 1e-05 123456789.0}
207do_test bind-4.2 {
208  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
209} {real real real}
210do_test bind-4.3 {
211  execsql {
212    DELETE FROM t1;
213  }
214} {}
215
216# NULL
217do_test bind-5.1 {
218  sqlite3_bind_null $VM 1
219  sqlite3_bind_null $VM 2
220  sqlite3_bind_null $VM 3
221  sqlite_step $VM N VALUES COLNAMES
222  sqlite3_reset $VM
223  execsql {SELECT rowid, * FROM t1}
224} {1 {} {} {}}
225do_test bind-5.2 {
226  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
227} {null null null}
228do_test bind-5.3 {
229  execsql {
230    DELETE FROM t1;
231  }
232} {}
233
234# UTF-8 text
235do_test bind-6.1 {
236  sqlite3_bind_text $VM 1 hellothere 5
237  sqlite3_bind_text $VM 2 ".." 1
238  sqlite3_bind_text $VM 3 world -1
239  sqlite_step $VM N VALUES COLNAMES
240  sqlite3_reset $VM
241  execsql {SELECT rowid, * FROM t1}
242} {1 hello . world}
243do_test bind-6.2 {
244  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
245} {text text text}
246do_test bind-6.3 {
247  execsql {
248    DELETE FROM t1;
249  }
250} {}
251
252# UTF-16 text
253ifcapable {utf16} {
254  do_test bind-7.1 {
255    sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
256    sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
257    sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
258    sqlite_step $VM N VALUES COLNAMES
259    sqlite3_reset $VM
260    execsql {SELECT rowid, * FROM t1}
261  } {1 hello {} world}
262  do_test bind-7.2 {
263    execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
264  } {text text text}
265}
266do_test bind-7.3 {
267  execsql {
268    DELETE FROM t1;
269  }
270} {}
271
272# Test that the 'out of range' error works.
273do_test bind-8.1 {
274  catch { sqlite3_bind_null $VM 0 }
275} {1}
276do_test bind-8.2 {
277  sqlite3_errmsg $DB
278} {bind or column index out of range}
279ifcapable {utf16} {
280  do_test bind-8.3 {
281    encoding convertfrom unicode [sqlite3_errmsg16 $DB]
282  } {bind or column index out of range}
283}
284do_test bind-8.4 {
285  sqlite3_bind_null $VM 1
286  sqlite3_errmsg $DB
287} {not an error}
288do_test bind-8.5 {
289  catch { sqlite3_bind_null $VM 4 }
290} {1}
291do_test bind-8.6 {
292  sqlite3_errmsg $DB
293} {bind or column index out of range}
294ifcapable {utf16} {
295  do_test bind-8.7 {
296    encoding convertfrom unicode [sqlite3_errmsg16 $DB]
297  } {bind or column index out of range}
298}
299
300do_test bind-8.8 {
301  catch { sqlite3_bind_blob $VM 0 "abc" 3 }
302} {1}
303do_test bind-8.9 {
304  catch { sqlite3_bind_blob $VM 4 "abc" 3 }
305} {1}
306do_test bind-8.10 {
307  catch { sqlite3_bind_text $VM 0 "abc" 3 }
308} {1}
309ifcapable {utf16} {
310  do_test bind-8.11 {
311    catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
312  } {1}
313}
314do_test bind-8.12 {
315  catch { sqlite3_bind_int $VM 0 5 }
316} {1}
317do_test bind-8.13 {
318  catch { sqlite3_bind_int $VM 4 5 }
319} {1}
320do_test bind-8.14 {
321  catch { sqlite3_bind_double $VM 0 5.0 }
322} {1}
323do_test bind-8.15 {
324  catch { sqlite3_bind_double $VM 4 6.0 }
325} {1}
326
327do_test bind-8.99 {
328  sqlite3_finalize $VM
329} SQLITE_OK
330
331do_test bind-9.1 {
332  execsql {
333    CREATE TABLE t2(a,b,c,d,e,f);
334  }
335  set rc [catch {
336    sqlite3_prepare $DB {
337      INSERT INTO t2(a) VALUES(?0)
338    } -1 TAIL
339  } msg]
340  lappend rc $msg
341} {1 {(1) variable number must be between ?1 and ?999}}
342do_test bind-9.2 {
343  set rc [catch {
344    sqlite3_prepare $DB {
345      INSERT INTO t2(a) VALUES(?1000)
346    } -1 TAIL
347  } msg]
348  lappend rc $msg
349} {1 {(1) variable number must be between ?1 and ?999}}
350do_test bind-9.3 {
351  set VM [
352    sqlite3_prepare $DB {
353      INSERT INTO t2(a,b) VALUES(?1,?999)
354    } -1 TAIL
355  ]
356  sqlite3_bind_parameter_count $VM
357} {999}
358catch {sqlite3_finalize $VM}
359do_test bind-9.4 {
360  set VM [
361    sqlite3_prepare $DB {
362      INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?)
363    } -1 TAIL
364  ]
365  sqlite3_bind_parameter_count $VM
366} {1001}
367do_test bind-9.5 {
368  sqlite3_bind_int $VM 1 1
369  sqlite3_bind_int $VM 999 999
370  sqlite3_bind_int $VM 1000 1000
371  sqlite3_bind_int $VM 1001 1001
372  sqlite3_step $VM
373} SQLITE_DONE
374do_test bind-9.6 {
375  sqlite3_finalize $VM
376} SQLITE_OK
377do_test bind-9.7 {
378  execsql {SELECT * FROM t2}
379} {1 999 1000 1001 {} {}}
380
381ifcapable {tclvar} {
382  do_test bind-10.1 {
383    set VM [
384      sqlite3_prepare $DB {
385        INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
386      } -1 TAIL
387    ]
388    sqlite3_bind_parameter_count $VM
389  } 3
390  set v1 {$abc}
391  set v2 {$ab}
392}
393ifcapable {!tclvar} {
394  do_test bind-10.1 {
395    set VM [
396      sqlite3_prepare $DB {
397        INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)
398      } -1 TAIL
399    ]
400    sqlite3_bind_parameter_count $VM
401  } 3
402  set v1 {:xyz}
403  set v2 {:xy}
404}
405do_test bind-10.2 {
406  sqlite3_bind_parameter_index $VM :abc
407} 1
408do_test bind-10.3 {
409  sqlite3_bind_parameter_index $VM $v1
410} 2
411do_test bind-10.4 {
412  sqlite3_bind_parameter_index $VM $v2
413} 3
414do_test bind-10.5 {
415  sqlite3_bind_parameter_name $VM 1
416} :abc
417do_test bind-10.6 {
418  sqlite3_bind_parameter_name $VM 2
419} $v1
420do_test bind-10.7 {
421  sqlite3_bind_parameter_name $VM 3
422} $v2
423do_test bind-10.7.1 {
424  sqlite3_bind_parameter_name 0 1   ;# Ignore if VM is NULL
425} {}
426do_test bind-10.7.2 {
427  sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small
428} {}
429do_test bind-10.7.3 {
430  sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big
431} {}
432do_test bind-10.8 {
433  sqlite3_bind_int $VM 1 1
434  sqlite3_bind_int $VM 2 2
435  sqlite3_bind_int $VM 3 3
436  sqlite3_step $VM
437} SQLITE_DONE
438do_test bind-10.8.1 {
439  # Binding attempts after program start should fail
440  set rc [catch {
441    sqlite3_bind_int $VM 1 1
442  } msg]
443  lappend rc $msg
444} {1 {}}
445do_test bind-10.9 {
446  sqlite3_finalize $VM
447} SQLITE_OK
448do_test bind-10.10 {
449  execsql {SELECT * FROM t2}
450} {1 999 1000 1001 {} {} 1 2 1 3 2 1}
451
452# Ticket #918
453#
454do_test bind-10.11 {
455  catch {sqlite3_finalize $VM}
456  set VM [
457    sqlite3_prepare $DB {
458      INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4)
459    } -1 TAIL
460  ]
461  sqlite3_bind_parameter_count $VM
462} 5
463do_test bind-10.11.1 {
464  sqlite3_bind_parameter_index 0 :xyz  ;# ignore NULL VM arguments
465} 0
466do_test bind-10.12 {
467  sqlite3_bind_parameter_index $VM :xyz
468} 0
469do_test bind-10.13 {
470  sqlite3_bind_parameter_index $VM {}
471} 0
472do_test bind-10.14 {
473  sqlite3_bind_parameter_index $VM :pqr
474} 5
475do_test bind-10.15 {
476  sqlite3_bind_parameter_index $VM ?4
477} 4
478do_test bind-10.16 {
479  sqlite3_bind_parameter_name $VM 1
480} :abc
481do_test bind-10.17 {
482  sqlite3_bind_parameter_name $VM 2
483} {}
484do_test bind-10.18 {
485  sqlite3_bind_parameter_name $VM 3
486} {}
487do_test bind-10.19 {
488  sqlite3_bind_parameter_name $VM 4
489} {?4}
490do_test bind-10.20 {
491  sqlite3_bind_parameter_name $VM 5
492} :pqr
493catch {sqlite3_finalize $VM}
494
495# Make sure we catch an unterminated "(" in a Tcl-style variable name
496#
497ifcapable tclvar {
498  do_test bind-11.1 {
499    catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;}
500  } {1 {unrecognized token: "$abc(123"}}
501}
502
503finish_test
504