xref: /sqlite-3.40.0/test/bind.test (revision ef5ecb41) 
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.11 2004/05/27 09:28:44 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 [sqlite db test.db]
40  execsql {CREATE TABLE t1(a,b,c)}
41  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(?,?,?)} -1 TAIL]
42  set TAIL
43} {}
44do_test bind-1.2 {
45  sqlite_step $VM N VALUES COLNAMES
46} {SQLITE_DONE}
47do_test bind-1.3 {
48  execsql {SELECT rowid, * FROM t1}
49} {1 {} {} {}}
50do_test bind-1.4 {
51  sqlite3_reset $VM
52  sqlite_bind $VM 1 {test value 1} normal
53  sqlite_step $VM N VALUES COLNAMES
54} SQLITE_DONE
55do_test bind-1.5 {
56  execsql {SELECT rowid, * FROM t1}
57} {1 {} {} {} 2 {test value 1} {} {}}
58do_test bind-1.6 {
59  sqlite3_reset $VM
60  sqlite_bind $VM 3 {'test value 2'} normal
61  sqlite_step $VM N VALUES COLNAMES
62} SQLITE_DONE
63do_test bind-1.7 {
64  execsql {SELECT rowid, * FROM t1}
65} {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}}
66do_test bind-1.8 {
67  sqlite3_reset $VM
68  set sqlite_static_bind_value 123
69  sqlite_bind $VM 1 {} static
70  sqlite_bind $VM 2 {abcdefg} normal
71  sqlite_bind $VM 3 {} null
72  execsql {DELETE FROM t1}
73  sqlite_step $VM N VALUES COLNAMES
74  execsql {SELECT rowid, * FROM t1}
75} {1 123 abcdefg {}}
76do_test bind-1.9 {
77  sqlite3_reset $VM
78  sqlite_bind $VM 1 {456} normal
79  sqlite_step $VM N VALUES COLNAMES
80  execsql {SELECT rowid, * FROM t1}
81} {1 123 abcdefg {} 2 456 abcdefg {}}
82
83do_test bind-1.99 {
84  sqlite3_finalize $VM
85} SQLITE_OK
86
87do_test bind-2.1 {
88  execsql {
89    DELETE FROM t1;
90  }
91  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(?,?,?)} -1 TAIL]
92  set TAIL
93} {}
94
95# 32 bit Integers
96do_test bind-2.2 {
97  sqlite3_bind_int32 $VM 1 123
98  sqlite3_bind_int32 $VM 2 456
99  sqlite3_bind_int32 $VM 3 789
100  sqlite_step $VM N VALUES COLNAMES
101  sqlite3_reset $VM
102  execsql {SELECT rowid, * FROM t1}
103} {1 123 456 789}
104do_test bind-2.3 {
105  sqlite3_bind_int32 $VM 2 -2000000000
106  sqlite3_bind_int32 $VM 3 2000000000
107  sqlite_step $VM N VALUES COLNAMES
108  sqlite3_reset $VM
109  execsql {SELECT rowid, * FROM t1}
110} {1 123 456 789 2 123 -2000000000 2000000000}
111do_test bind-2.4 {
112  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
113} {integer integer integer integer integer integer}
114do_test bind-2.5 {
115  execsql {
116    DELETE FROM t1;
117  }
118} {}
119
120# 64 bit Integers
121do_test bind-3.1 {
122  sqlite3_bind_int64 $VM 1 32
123  sqlite3_bind_int64 $VM 2 -2000000000000
124  sqlite3_bind_int64 $VM 3 2000000000000
125  sqlite_step $VM N VALUES COLNAMES
126  sqlite3_reset $VM
127  execsql {SELECT rowid, * FROM t1}
128} {1 32 -2000000000000 2000000000000}
129do_test bind-3.2 {
130  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
131} {integer integer integer}
132do_test bind-3.3 {
133  execsql {
134    DELETE FROM t1;
135  }
136} {}
137
138# Doubles
139do_test bind-4.1 {
140  sqlite3_bind_double $VM 1 1234.1234
141  sqlite3_bind_double $VM 2 0.00001
142  sqlite3_bind_double $VM 3 123456789
143  sqlite_step $VM N VALUES COLNAMES
144  sqlite3_reset $VM
145  execsql {SELECT rowid, * FROM t1}
146} {1 1234.1234 1e-05 123456789}
147do_test bind-4.2 {
148  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
149} {real real real}
150do_test bind-4.3 {
151  execsql {
152    DELETE FROM t1;
153  }
154} {}
155
156# NULL
157do_test bind-5.1 {
158  sqlite3_bind_null $VM 1
159  sqlite3_bind_null $VM 2
160  sqlite3_bind_null $VM 3
161  sqlite_step $VM N VALUES COLNAMES
162  sqlite3_reset $VM
163  execsql {SELECT rowid, * FROM t1}
164} {1 {} {} {}}
165do_test bind-5.2 {
166  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
167} {null null null}
168do_test bind-5.3 {
169  execsql {
170    DELETE FROM t1;
171  }
172} {}
173
174# UTF-8 text
175do_test bind-6.1 {
176  sqlite3_bind_text $VM 1 hellothere 5
177  sqlite3_bind_text $VM 2 ".." 1
178  sqlite3_bind_text $VM 3 world -1
179  sqlite_step $VM N VALUES COLNAMES
180  sqlite3_reset $VM
181  execsql {SELECT rowid, * FROM t1}
182} {1 hello . world}
183do_test bind-6.2 {
184  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
185} {text text text}
186do_test bind-6.3 {
187  execsql {
188    DELETE FROM t1;
189  }
190} {}
191
192# UTF-16 text
193do_test bind-7.1 {
194  sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
195  sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
196  sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
197  sqlite_step $VM N VALUES COLNAMES
198  sqlite3_reset $VM
199  execsql {SELECT rowid, * FROM t1}
200} {1 hello {} world}
201do_test bind-7.2 {
202  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
203} {text text text}
204do_test bind-7.3 {
205  execsql {
206    DELETE FROM t1;
207  }
208} {}
209
210# Test that the 'out of range' error works.
211do_test bind-8.1 {
212  catch { sqlite3_bind_null $VM 0 }
213} {1}
214do_test bind-8.2 {
215  sqlite3_errmsg $DB
216} {bind index out of range}
217do_test bind-8.3 {
218  encoding convertfrom unicode [sqlite3_errmsg16 $DB]
219} {bind index out of range}
220do_test bind-8.4 {
221  sqlite3_bind_null $VM 1
222  sqlite3_errmsg $DB
223} {not an error}
224do_test bind-8.5 {
225  catch { sqlite3_bind_null $VM 4 }
226} {1}
227do_test bind-8.6 {
228  sqlite3_errmsg $DB
229} {bind index out of range}
230do_test bind-8.7 {
231  encoding convertfrom unicode [sqlite3_errmsg16 $DB]
232} {bind index out of range}
233
234
235do_test bind-9.99 {
236  sqlite3_finalize $VM
237} SQLITE_OK
238
239
240
241finish_test
242