sl@0: # 2003 September 6
sl@0: #
sl@0: # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script testing the sqlite_bind API.
sl@0: #
sl@0: # $Id: bind.test,v 1.44 2008/07/09 01:39:44 drh Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: proc sqlite_step {stmt N VALS COLS} {
sl@0:   upvar VALS vals
sl@0:   upvar COLS cols
sl@0:   set vals [list]
sl@0:   set cols [list]
sl@0: 
sl@0:   set rc [sqlite3_step $stmt]
sl@0:   for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
sl@0:     lappend cols [sqlite3_column_name $stmt $i]
sl@0:   }
sl@0:   for {set i 0} {$i < [sqlite3_data_count $stmt]} {incr i} {
sl@0:     lappend vals [sqlite3_column_text $stmt $i]
sl@0:   }
sl@0: 
sl@0:   return $rc
sl@0: }
sl@0: 
sl@0: do_test bind-1.1 {
sl@0:   set DB [sqlite3_connection_pointer db]
sl@0:   execsql {CREATE TABLE t1(a,b,c);}
sl@0:   set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:1,?,:abc)} -1 TAIL]
sl@0:   set TAIL
sl@0: } {}
sl@0: do_test bind-1.1.1 {
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } 3
sl@0: do_test bind-1.1.2 {
sl@0:   sqlite3_bind_parameter_name $VM 1
sl@0: } {:1}
sl@0: do_test bind-1.1.3 {
sl@0:   sqlite3_bind_parameter_name $VM 2
sl@0: } {}
sl@0: do_test bind-1.1.4 {
sl@0:   sqlite3_bind_parameter_name $VM 3
sl@0: } {:abc}
sl@0: do_test bind-1.2 {
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0: } {SQLITE_DONE}
sl@0: do_test bind-1.3 {
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 {} {} {}}
sl@0: do_test bind-1.4 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite_bind $VM 1 {test value 1} normal
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0: } SQLITE_DONE
sl@0: do_test bind-1.5 {
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 {} {} {} 2 {test value 1} {} {}}
sl@0: do_test bind-1.6 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite_bind $VM 3 {'test value 2'} normal
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0: } SQLITE_DONE
sl@0: do_test bind-1.7 {
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 {} {} {} 2 {test value 1} {} {} 3 {test value 1} {} {'test value 2'}}
sl@0: do_test bind-1.8 {
sl@0:   sqlite3_reset $VM
sl@0:   set sqlite_static_bind_value 123
sl@0:   sqlite_bind $VM 1 {} static
sl@0:   sqlite_bind $VM 2 {abcdefg} normal
sl@0:   sqlite_bind $VM 3 {} null
sl@0:   execsql {DELETE FROM t1}
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 123 abcdefg {}}
sl@0: do_test bind-1.9 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite_bind $VM 1 {456} normal
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 123 abcdefg {} 2 456 abcdefg {}}
sl@0: 
sl@0: do_test bind-1.10 {
sl@0:    set rc [catch {
sl@0:      sqlite3_prepare db {INSERT INTO t1 VALUES($abc:123,?,:abc)} -1 TAIL
sl@0:    } msg]
sl@0:    lappend rc $msg
sl@0: } {1 {(1) near ":123": syntax error}}
sl@0: do_test bind-1.11 {
sl@0:    set rc [catch {
sl@0:      sqlite3_prepare db {INSERT INTO t1 VALUES(@abc:xyz,?,:abc)} -1 TAIL
sl@0:    } msg]
sl@0:    lappend rc $msg
sl@0: } {1 {(1) near ":xyz": syntax error}}
sl@0: 
sl@0: do_test bind-1.99 {
sl@0:   sqlite3_finalize $VM
sl@0: } SQLITE_OK
sl@0: 
sl@0: # Prepare the statement in different ways depending on whether or not
sl@0: # the $var processing is compiled into the library.
sl@0: #
sl@0: ifcapable {tclvar} {
sl@0:   do_test bind-2.1 {
sl@0:     execsql {
sl@0:       DELETE FROM t1;
sl@0:     }
sl@0:     set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,$x(-z-))}\
sl@0:             -1 TX]
sl@0:     set TX
sl@0:   } {}
sl@0:   set v1 {$one}
sl@0:   set v2 {$::two}
sl@0:   set v3 {$x(-z-)}
sl@0: }
sl@0: ifcapable {!tclvar} {
sl@0:   do_test bind-2.1 {
sl@0:     execsql {
sl@0:       DELETE FROM t1;
sl@0:     }
sl@0:     set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX]
sl@0:     set TX
sl@0:   } {}
sl@0:   set v1 {:one}
sl@0:   set v2 {:two}
sl@0:   set v3 {:_}
sl@0: }
sl@0: 
sl@0: do_test bind-2.1.1 {
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } 3
sl@0: do_test bind-2.1.2 {
sl@0:   sqlite3_bind_parameter_name $VM 1
sl@0: } $v1
sl@0: do_test bind-2.1.3 {
sl@0:   sqlite3_bind_parameter_name $VM 2
sl@0: } $v2
sl@0: do_test bind-2.1.4 {
sl@0:   sqlite3_bind_parameter_name $VM 3
sl@0: } $v3
sl@0: do_test bind-2.1.5 {
sl@0:   sqlite3_bind_parameter_index $VM $v1
sl@0: } 1
sl@0: do_test bind-2.1.6 {
sl@0:   sqlite3_bind_parameter_index $VM $v2
sl@0: } 2
sl@0: do_test bind-2.1.7 {
sl@0:   sqlite3_bind_parameter_index $VM $v3
sl@0: } 3
sl@0: do_test bind-2.1.8 {
sl@0:   sqlite3_bind_parameter_index $VM {:hi}
sl@0: } 0
sl@0: 
sl@0: # 32 bit Integers
sl@0: do_test bind-2.2 {
sl@0:   sqlite3_bind_int $VM 1 123
sl@0:   sqlite3_bind_int $VM 2 456
sl@0:   sqlite3_bind_int $VM 3 789
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 123 456 789}
sl@0: do_test bind-2.3 {
sl@0:   sqlite3_bind_int $VM 2 -2000000000
sl@0:   sqlite3_bind_int $VM 3 2000000000
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 123 456 789 2 123 -2000000000 2000000000}
sl@0: do_test bind-2.4 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {integer integer integer integer integer integer}
sl@0: do_test bind-2.5 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # 64 bit Integers
sl@0: do_test bind-3.1 {
sl@0:   sqlite3_bind_int64 $VM 1 32
sl@0:   sqlite3_bind_int64 $VM 2 -2000000000000
sl@0:   sqlite3_bind_int64 $VM 3 2000000000000
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 32 -2000000000000 2000000000000}
sl@0: do_test bind-3.2 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {integer integer integer}
sl@0: do_test bind-3.3 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Doubles
sl@0: do_test bind-4.1 {
sl@0:   sqlite3_bind_double $VM 1 1234.1234
sl@0:   sqlite3_bind_double $VM 2 0.00001
sl@0:   sqlite3_bind_double $VM 3 123456789
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   set x [execsql {SELECT rowid, * FROM t1}]
sl@0:   regsub {1e-005} $x {1e-05} y
sl@0:   set y
sl@0: } {1 1234.1234 1e-05 123456789.0}
sl@0: do_test bind-4.2 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {real real real}
sl@0: do_test bind-4.3 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: #
sl@0: #Symbian OS: this test is failing due to problems in printf format spec implementation
sl@0: #
sl@0: if {$::tcl_platform(platform)!="symbian"} {
sl@0:   do_test bind-4.4 {
sl@0:     sqlite3_bind_double $VM 1 NaN
sl@0:     sqlite3_bind_double $VM 2 1e300
sl@0:     sqlite3_bind_double $VM 3 -1e-300
sl@0:     sqlite_step $VM N VALUES COLNAMES
sl@0:     sqlite3_reset $VM
sl@0:     set x [execsql {SELECT rowid, * FROM t1}]
sl@0:     regsub {1e-005} $x {1e-05} y
sl@0:     set y
sl@0:   } {1 {} 1e+300 -1e-300}
sl@0: #
sl@0: #Symbian OS: this test is commented because depends on 4.4
sl@0: #
sl@0:   do_test bind-4.5 {
sl@0:     execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0:   } {null real real}
sl@0: }
sl@0: do_test bind-4.6 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # NULL
sl@0: do_test bind-5.1 {
sl@0:   sqlite3_bind_null $VM 1
sl@0:   sqlite3_bind_null $VM 2
sl@0:   sqlite3_bind_null $VM 3 
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 {} {} {}}
sl@0: do_test bind-5.2 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {null null null}
sl@0: do_test bind-5.3 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # UTF-8 text
sl@0: do_test bind-6.1 {
sl@0:   sqlite3_bind_text $VM 1 hellothere 5
sl@0:   sqlite3_bind_text $VM 2 ".." 1
sl@0:   sqlite3_bind_text $VM 3 world\000 -1
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT rowid, * FROM t1}
sl@0: } {1 hello . world}
sl@0: do_test bind-6.2 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {text text text}
sl@0: do_test bind-6.3 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Make sure zeros in a string work.
sl@0: #
sl@0: do_test bind-6.4 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   sqlite3_bind_text $VM 1 hello\000there\000 12
sl@0:   sqlite3_bind_text $VM 2 hello\000there\000 11
sl@0:   sqlite3_bind_text $VM 3 hello\000there\000 -1
sl@0:   sqlite_step $VM N VALUES COLNAMES
sl@0:   sqlite3_reset $VM
sl@0:   execsql {SELECT * FROM t1}
sl@0: } {hello hello hello}
sl@0: set enc [db eval {PRAGMA encoding}]
sl@0: if {$enc=="UTF-8"} {
sl@0:   do_test bind-6.5 {
sl@0:     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}
sl@0:   } {68656C6C6F00746865726500 68656C6C6F007468657265 68656C6C6F}
sl@0: } elseif {$enc=="UTF-16le"} {
sl@0:   do_test bind-6.5 {
sl@0:     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}
sl@0:   } {680065006C006C006F000000740068006500720065000000 680065006C006C006F00000074006800650072006500 680065006C006C006F00}
sl@0: } elseif {$enc=="UTF-16be"} {
sl@0:   do_test bind-6.5 {
sl@0:     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}
sl@0:   } {00680065006C006C006F0000007400680065007200650000 00680065006C006C006F000000740068006500720065 00680065006C006C006F}
sl@0: } else {
sl@0:   do_test bind-6.5 {
sl@0:     set "Unknown database encoding: $::enc"
sl@0:   } {}
sl@0: }
sl@0: do_test bind-6.6 {
sl@0:   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0: } {text text text}
sl@0: do_test bind-6.7 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # UTF-16 text
sl@0: ifcapable {utf16} {
sl@0:   do_test bind-7.1 {
sl@0:     sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
sl@0:     sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
sl@0:     sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
sl@0:     sqlite_step $VM N VALUES COLNAMES
sl@0:     sqlite3_reset $VM
sl@0:     execsql {SELECT rowid, * FROM t1}
sl@0:   } {1 hello {} world}
sl@0:   do_test bind-7.2 {
sl@0:     execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0:   } {text text text}
sl@0:   do_test bind-7.3 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     sqlite3_bind_text16 $VM 1 [encoding convertto unicode hi\000yall\000] 16
sl@0:     sqlite3_bind_text16 $VM 2 [encoding convertto unicode hi\000yall\000] 14
sl@0:     sqlite3_bind_text16 $VM 3 [encoding convertto unicode hi\000yall\000] -1
sl@0:     sqlite_step $VM N VALUES COLNAMES
sl@0:     sqlite3_reset $VM
sl@0:     execsql {SELECT * FROM t1}
sl@0:   } {hi hi hi}
sl@0:   if {$enc=="UTF-8"} {
sl@0:     do_test bind-7.4 {
sl@0:       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}
sl@0:     } {68690079616C6C00 68690079616C6C 6869}
sl@0:   } elseif {$enc=="UTF-16le"} {
sl@0:     do_test bind-7.4 {
sl@0:       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}
sl@0:     } {680069000000790061006C006C000000 680069000000790061006C006C00 68006900}
sl@0:   } elseif {$enc=="UTF-16be"} {
sl@0:     do_test bind-7.4 {
sl@0:       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}
sl@0:     } {00680069000000790061006C006C0000 00680069000000790061006C006C 00680069}
sl@0:   }
sl@0:   do_test bind-7.5 {
sl@0:     execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
sl@0:   } {text text text}
sl@0: }
sl@0: do_test bind-7.99 {
sl@0:   execsql {DELETE FROM t1;}
sl@0: } {}
sl@0: 
sl@0: # Test that the 'out of range' error works.
sl@0: do_test bind-8.1 {
sl@0:   catch { sqlite3_bind_null $VM 0 }
sl@0: } {1}
sl@0: do_test bind-8.2 {
sl@0:   sqlite3_errmsg $DB
sl@0: } {bind or column index out of range}
sl@0: ifcapable {utf16} {
sl@0:   do_test bind-8.3 {
sl@0:     encoding convertfrom unicode [sqlite3_errmsg16 $DB]
sl@0:   } {bind or column index out of range}
sl@0: }
sl@0: do_test bind-8.4 {
sl@0:   sqlite3_bind_null $VM 1 
sl@0:   sqlite3_errmsg $DB
sl@0: } {not an error}
sl@0: do_test bind-8.5 {
sl@0:   catch { sqlite3_bind_null $VM 4 }
sl@0: } {1}
sl@0: do_test bind-8.6 {
sl@0:   sqlite3_errmsg $DB
sl@0: } {bind or column index out of range}
sl@0: ifcapable {utf16} {
sl@0:   do_test bind-8.7 {
sl@0:     encoding convertfrom unicode [sqlite3_errmsg16 $DB]
sl@0:   } {bind or column index out of range}
sl@0: }
sl@0: 
sl@0: do_test bind-8.8 {
sl@0:   catch { sqlite3_bind_blob $VM 0 "abc" 3 }
sl@0: } {1}
sl@0: do_test bind-8.9 {
sl@0:   catch { sqlite3_bind_blob $VM 4 "abc" 3 }
sl@0: } {1}
sl@0: do_test bind-8.10 {
sl@0:   catch { sqlite3_bind_text $VM 0 "abc" 3 }
sl@0: } {1}
sl@0: ifcapable {utf16} {
sl@0:   do_test bind-8.11 {
sl@0:     catch { sqlite3_bind_text16 $VM 4 "abc" 2 }
sl@0:   } {1}
sl@0: }
sl@0: do_test bind-8.12 {
sl@0:   catch { sqlite3_bind_int $VM 0 5 }
sl@0: } {1}
sl@0: do_test bind-8.13 {
sl@0:   catch { sqlite3_bind_int $VM 4 5 }
sl@0: } {1}
sl@0: do_test bind-8.14 {
sl@0:   catch { sqlite3_bind_double $VM 0 5.0 }
sl@0: } {1}
sl@0: do_test bind-8.15 {
sl@0:   catch { sqlite3_bind_double $VM 4 6.0 }
sl@0: } {1}
sl@0: 
sl@0: do_test bind-8.99 {
sl@0:   sqlite3_finalize $VM
sl@0: } SQLITE_OK
sl@0: 
sl@0: do_test bind-9.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2(a,b,c,d,e,f);
sl@0:   }
sl@0:   set rc [catch {
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a) VALUES(?0)
sl@0:     } -1 TAIL
sl@0:   } msg]
sl@0:   lappend rc $msg
sl@0: } {1 {(1) variable number must be between ?1 and ?999}}
sl@0: do_test bind-9.2 {
sl@0:   set rc [catch {
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a) VALUES(?1000)
sl@0:     } -1 TAIL
sl@0:   } msg]
sl@0:   lappend rc $msg
sl@0: } {1 {(1) variable number must be between ?1 and ?999}}
sl@0: do_test bind-9.3.1 {
sl@0:   set VM [
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a,b) VALUES(?1,?999)
sl@0:     } -1 TAIL
sl@0:   ]
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } {999}
sl@0: catch {sqlite3_finalize $VM}
sl@0: do_test bind-9.3.2 {
sl@0:   set VM [
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a,b) VALUES(?2,?998)
sl@0:     } -1 TAIL
sl@0:   ]
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } {998}
sl@0: catch {sqlite3_finalize $VM}
sl@0: do_test bind-9.4 {
sl@0:   set VM [
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a,b,c,d) VALUES(?1,?997,?,?)
sl@0:     } -1 TAIL
sl@0:   ]
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } {999}
sl@0: do_test bind-9.5 {
sl@0:   sqlite3_bind_int $VM 1 1
sl@0:   sqlite3_bind_int $VM 997 999
sl@0:   sqlite3_bind_int $VM 998 1000
sl@0:   sqlite3_bind_int $VM 999 1001
sl@0:   sqlite3_step $VM
sl@0: } SQLITE_DONE
sl@0: do_test bind-9.6 {
sl@0:   sqlite3_finalize $VM
sl@0: } SQLITE_OK
sl@0: do_test bind-9.7 {
sl@0:   execsql {SELECT * FROM t2}
sl@0: } {1 999 1000 1001 {} {}}
sl@0: 
sl@0: ifcapable {tclvar} {
sl@0:   do_test bind-10.1 {
sl@0:     set VM [
sl@0:       sqlite3_prepare $DB {
sl@0:         INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)
sl@0:       } -1 TAIL
sl@0:     ]
sl@0:     sqlite3_bind_parameter_count $VM
sl@0:   } 3
sl@0:   set v1 {$abc}
sl@0:   set v2 {$ab}
sl@0: }
sl@0: ifcapable {!tclvar} {
sl@0:   do_test bind-10.1 {
sl@0:     set VM [
sl@0:       sqlite3_prepare $DB {
sl@0:         INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)
sl@0:       } -1 TAIL
sl@0:     ]
sl@0:     sqlite3_bind_parameter_count $VM
sl@0:   } 3
sl@0:   set v1 {:xyz}
sl@0:   set v2 {:xy}
sl@0: }
sl@0: do_test bind-10.2 {
sl@0:   sqlite3_bind_parameter_index $VM :abc
sl@0: } 1
sl@0: do_test bind-10.3 {
sl@0:   sqlite3_bind_parameter_index $VM $v1
sl@0: } 2
sl@0: do_test bind-10.4 {
sl@0:   sqlite3_bind_parameter_index $VM $v2
sl@0: } 3
sl@0: do_test bind-10.5 {
sl@0:   sqlite3_bind_parameter_name $VM 1
sl@0: } :abc
sl@0: do_test bind-10.6 {
sl@0:   sqlite3_bind_parameter_name $VM 2
sl@0: } $v1
sl@0: do_test bind-10.7 {
sl@0:   sqlite3_bind_parameter_name $VM 3
sl@0: } $v2
sl@0: do_test bind-10.7.1 {
sl@0:   sqlite3_bind_parameter_name 0 1   ;# Ignore if VM is NULL
sl@0: } {}
sl@0: do_test bind-10.7.2 {
sl@0:   sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small
sl@0: } {}
sl@0: do_test bind-10.7.3 {
sl@0:   sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big
sl@0: } {}
sl@0: do_test bind-10.8 {
sl@0:   sqlite3_bind_int $VM 1 1
sl@0:   sqlite3_bind_int $VM 2 2
sl@0:   sqlite3_bind_int $VM 3 3
sl@0:   sqlite3_step $VM
sl@0: } SQLITE_DONE
sl@0: do_test bind-10.8.1 {
sl@0:   # Binding attempts after program start should fail
sl@0:   set rc [catch {
sl@0:     sqlite3_bind_int $VM 1 1
sl@0:   } msg]
sl@0:   lappend rc $msg
sl@0: } {1 {}}
sl@0: do_test bind-10.9 {
sl@0:   sqlite3_finalize $VM
sl@0: } SQLITE_OK
sl@0: do_test bind-10.10 {
sl@0:   execsql {SELECT * FROM t2}
sl@0: } {1 999 1000 1001 {} {} 1 2 1 3 2 1}
sl@0: 
sl@0: # Ticket #918
sl@0: #
sl@0: do_test bind-10.11 {
sl@0:   # catch {sqlite3_finalize $VM}
sl@0:   set VM [
sl@0:     sqlite3_prepare $DB {
sl@0:       INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4)
sl@0:     } -1 TAIL
sl@0:   ]
sl@0:   sqlite3_bind_parameter_count $VM
sl@0: } 5
sl@0: do_test bind-10.11.1 {
sl@0:   sqlite3_bind_parameter_index 0 :xyz  ;# ignore NULL VM arguments
sl@0: } 0
sl@0: do_test bind-10.12 {
sl@0:   sqlite3_bind_parameter_index $VM :xyz
sl@0: } 0
sl@0: do_test bind-10.13 {
sl@0:   sqlite3_bind_parameter_index $VM {}
sl@0: } 0
sl@0: do_test bind-10.14 {
sl@0:   sqlite3_bind_parameter_index $VM :pqr
sl@0: } 5
sl@0: do_test bind-10.15 {
sl@0:   sqlite3_bind_parameter_index $VM ?4
sl@0: } 4
sl@0: do_test bind-10.16 {
sl@0:   sqlite3_bind_parameter_name $VM 1
sl@0: } :abc
sl@0: do_test bind-10.17 {
sl@0:   sqlite3_bind_parameter_name $VM 2
sl@0: } {}
sl@0: do_test bind-10.18 {
sl@0:   sqlite3_bind_parameter_name $VM 3
sl@0: } {}
sl@0: do_test bind-10.19 {
sl@0:   sqlite3_bind_parameter_name $VM 4
sl@0: } {?4}
sl@0: do_test bind-10.20 {
sl@0:   sqlite3_bind_parameter_name $VM 5
sl@0: } :pqr
sl@0: catch {sqlite3_finalize $VM}
sl@0: 
sl@0: # Make sure we catch an unterminated "(" in a Tcl-style variable name
sl@0: #
sl@0: ifcapable tclvar {
sl@0:   do_test bind-11.1 {
sl@0:     catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;}
sl@0:   } {1 {unrecognized token: "$abc(123"}}
sl@0: }
sl@0: 
sl@0: if {[execsql {pragma encoding}]=="UTF-8"} {
sl@0:   # Test the ability to bind text that contains embedded '\000' characters.
sl@0:   # Make sure we can recover the entire input string.
sl@0:   #
sl@0:   do_test bind-12.1 {
sl@0:     execsql {
sl@0:       CREATE TABLE t3(x BLOB);
sl@0:     }
sl@0:     set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL]
sl@0:     sqlite_bind  $VM 1 not-used blob10
sl@0:     sqlite3_step $VM
sl@0:     sqlite3_finalize $VM
sl@0:     execsql {
sl@0:       SELECT typeof(x), length(x), quote(x),
sl@0:              length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3
sl@0:     }
sl@0:   } {text 3 'abc' 10 X'6162630078797A007071'}
sl@0:   do_test bind-12.2 {
sl@0:     sqlite3_create_function $DB
sl@0:     execsql {
sl@0:       SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3
sl@0:     }
sl@0:   } {X'6162630078797A007071'}
sl@0: }
sl@0: 
sl@0: # Test the operation of sqlite3_clear_bindings
sl@0: #
sl@0: do_test bind-13.1 {
sl@0:   set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL]
sl@0:   sqlite3_step $VM
sl@0:   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
sl@0:                [sqlite3_column_type $VM 2]
sl@0: } {NULL NULL NULL}
sl@0: do_test bind-13.2 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite3_bind_int $VM 1 1
sl@0:   sqlite3_bind_int $VM 2 2
sl@0:   sqlite3_bind_int $VM 3 3
sl@0:   sqlite3_step $VM
sl@0:   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
sl@0:                [sqlite3_column_type $VM 2]
sl@0: } {INTEGER INTEGER INTEGER}
sl@0: do_test bind-13.3 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite3_step $VM
sl@0:   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
sl@0:                [sqlite3_column_type $VM 2]
sl@0: } {INTEGER INTEGER INTEGER}
sl@0: do_test bind-13.4 {
sl@0:   sqlite3_reset $VM
sl@0:   sqlite3_clear_bindings $VM
sl@0:   sqlite3_step $VM
sl@0:   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \
sl@0:                [sqlite3_column_type $VM 2]
sl@0: } {NULL NULL NULL}
sl@0: sqlite3_finalize $VM
sl@0: 
sl@0: finish_test