# 2003 September 6 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script testing the sqlite_bind API. # # $Id: bind.test,v 1.18 2004/08/25 04:07:03 drh Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl proc sqlite_step {stmt N VALS COLS} { upvar VALS vals upvar COLS cols set vals [list] set cols [list] set rc [sqlite3_step $stmt] for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} { lappend cols [sqlite3_column_name $stmt $i] } for {set i 0} {$i < [sqlite3_data_count $stmt]} {incr i} { lappend vals [sqlite3_column_text $stmt $i] } return $rc } do_test bind-1.1 { db close set DB [sqlite3 db test.db] execsql {CREATE TABLE t1(a,b,c);} set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL] set TAIL } {} do_test bind-1.1.1 { sqlite3_bind_parameter_count $VM } 3 do_test bind-1.1.2 { sqlite3_bind_parameter_name $VM 1 } {:1} do_test bind-1.1.3 { sqlite3_bind_parameter_name $VM 2 } {} do_test bind-1.1.4 { sqlite3_bind_parameter_name $VM 3 } {:abc} do_test bind-1.2 { sqlite_step $VM N VALUES COLNAMES } {SQLITE_DONE} do_test bind-1.3 { execsql {SELECT rowid, * FROM t1} } {1 {} {} {}} do_test bind-1.4 { sqlite3_reset $VM sqlite_bind $VM 1 {test value 1} normal sqlite_step $VM N VALUES COLNAMES } SQLITE_DONE do_test bind-1.5 { execsql {SELECT rowid, * FROM t1} } {1 {} {} {} 2 {test value 1} {} {}} do_test bind-1.6 { sqlite3_reset $VM sqlite_bind $VM 3 {'test value 2'} normal sqlite_step $VM N VALUES COLNAMES } SQLITE_DONE do_test bind-1.7 { execsql {SELECT rowid, * FROM t1} } {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}} do_test bind-1.8 { sqlite3_reset $VM set sqlite_static_bind_value 123 sqlite_bind $VM 1 {} static sqlite_bind $VM 2 {abcdefg} normal sqlite_bind $VM 3 {} null execsql {DELETE FROM t1} sqlite_step $VM N VALUES COLNAMES execsql {SELECT rowid, * FROM t1} } {1 123 abcdefg {}} do_test bind-1.9 { sqlite3_reset $VM sqlite_bind $VM 1 {456} normal sqlite_step $VM N VALUES COLNAMES execsql {SELECT rowid, * FROM t1} } {1 123 abcdefg {} 2 456 abcdefg {}} do_test bind-1.99 { sqlite3_finalize $VM } SQLITE_OK do_test bind-2.1 { execsql { DELETE FROM t1; } set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,${x})} -1 TAIL] set TAIL } {} do_test bind-2.1.1 { sqlite3_bind_parameter_count $VM } 3 do_test bind-2.1.2 { sqlite3_bind_parameter_name $VM 1 } {$one} do_test bind-2.1.3 { sqlite3_bind_parameter_name $VM 2 } {$::two} do_test bind-2.1.4 { sqlite3_bind_parameter_name $VM 3 } {${x}} # 32 bit Integers do_test bind-2.2 { sqlite3_bind_int $VM 1 123 sqlite3_bind_int $VM 2 456 sqlite3_bind_int $VM 3 789 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 123 456 789} do_test bind-2.3 { sqlite3_bind_int $VM 2 -2000000000 sqlite3_bind_int $VM 3 2000000000 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 123 456 789 2 123 -2000000000 2000000000} do_test bind-2.4 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {integer integer integer integer integer integer} do_test bind-2.5 { execsql { DELETE FROM t1; } } {} # 64 bit Integers do_test bind-3.1 { sqlite3_bind_int64 $VM 1 32 sqlite3_bind_int64 $VM 2 -2000000000000 sqlite3_bind_int64 $VM 3 2000000000000 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 32 -2000000000000 2000000000000} do_test bind-3.2 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {integer integer integer} do_test bind-3.3 { execsql { DELETE FROM t1; } } {} # Doubles do_test bind-4.1 { sqlite3_bind_double $VM 1 1234.1234 sqlite3_bind_double $VM 2 0.00001 sqlite3_bind_double $VM 3 123456789 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 1234.1234 1e-05 123456789.0} do_test bind-4.2 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {real real real} do_test bind-4.3 { execsql { DELETE FROM t1; } } {} # NULL do_test bind-5.1 { sqlite3_bind_null $VM 1 sqlite3_bind_null $VM 2 sqlite3_bind_null $VM 3 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 {} {} {}} do_test bind-5.2 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {null null null} do_test bind-5.3 { execsql { DELETE FROM t1; } } {} # UTF-8 text do_test bind-6.1 { sqlite3_bind_text $VM 1 hellothere 5 sqlite3_bind_text $VM 2 ".." 1 sqlite3_bind_text $VM 3 world -1 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 hello . world} do_test bind-6.2 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {text text text} do_test bind-6.3 { execsql { DELETE FROM t1; } } {} # UTF-16 text do_test bind-7.1 { sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10 sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0 sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10 sqlite_step $VM N VALUES COLNAMES sqlite3_reset $VM execsql {SELECT rowid, * FROM t1} } {1 hello {} world} do_test bind-7.2 { execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1} } {text text text} do_test bind-7.3 { execsql { DELETE FROM t1; } } {} # Test that the 'out of range' error works. do_test bind-8.1 { catch { sqlite3_bind_null $VM 0 } } {1} do_test bind-8.2 { sqlite3_errmsg $DB } {bind index out of range} do_test bind-8.3 { encoding convertfrom unicode [sqlite3_errmsg16 $DB] } {bind index out of range} do_test bind-8.4 { sqlite3_bind_null $VM 1 sqlite3_errmsg $DB } {not an error} do_test bind-8.5 { catch { sqlite3_bind_null $VM 4 } } {1} do_test bind-8.6 { sqlite3_errmsg $DB } {bind index out of range} do_test bind-8.7 { encoding convertfrom unicode [sqlite3_errmsg16 $DB] } {bind index out of range} do_test bind-8.8 { catch { sqlite3_bind_blob $VM 0 "abc" 3 } } {1} do_test bind-8.9 { catch { sqlite3_bind_blob $VM 4 "abc" 3 } } {1} do_test bind-8.10 { catch { sqlite3_bind_text $VM 0 "abc" 3 } } {1} do_test bind-8.11 { catch { sqlite3_bind_text16 $VM 4 "abc" 2 } } {1} do_test bind-8.12 { catch { sqlite3_bind_int $VM 0 5 } } {1} do_test bind-8.13 { catch { sqlite3_bind_int $VM 4 5 } } {1} do_test bind-8.14 { catch { sqlite3_bind_double $VM 0 5.0 } } {1} do_test bind-8.15 { catch { sqlite3_bind_double $VM 4 6.0 } } {1} do_test bind-9.99 { sqlite3_finalize $VM } SQLITE_OK finish_test