# 2003 September 6

 #

 # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

 #

 # 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.44 2008/07/09 01:39:44 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 {

   set DB [sqlite3_connection_pointer 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.10 {

    set rc [catch {

      sqlite3_prepare db {INSERT INTO t1 VALUES($abc:123,?,:abc)} -1 TAIL

    } msg]

    lappend rc $msg

 } {1 {(1) near ":123": syntax error}}

 do_test bind-1.11 {

    set rc [catch {

      sqlite3_prepare db {INSERT INTO t1 VALUES(@abc:xyz,?,:abc)} -1 TAIL

    } msg]

    lappend rc $msg

 } {1 {(1) near ":xyz": syntax error}}

 do_test bind-1.99 {

   sqlite3_finalize $VM

 } SQLITE_OK

 # Prepare the statement in different ways depending on whether or not

 # the $var processing is compiled into the library.

 #

 ifcapable {tclvar} {

   do_test bind-2.1 {

     execsql {

       DELETE FROM t1;

     }

     set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES($one,$::two,$x(-z-))}\

             -1 TX]

     set TX

   } {}

   set v1 {$one}

   set v2 {$::two}

   set v3 {$x(-z-)}

 }

 ifcapable {!tclvar} {

   do_test bind-2.1 {

     execsql {

       DELETE FROM t1;

     }

     set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(:one,:two,:_)} -1 TX]

     set TX

   } {}

   set v1 {:one}

   set v2 {:two}

   set v3 {:_}

 }

 do_test bind-2.1.1 {

   sqlite3_bind_parameter_count $VM

 } 3

 do_test bind-2.1.2 {

   sqlite3_bind_parameter_name $VM 1

 } $v1

 do_test bind-2.1.3 {

   sqlite3_bind_parameter_name $VM 2

 } $v2

 do_test bind-2.1.4 {

   sqlite3_bind_parameter_name $VM 3

 } $v3

 do_test bind-2.1.5 {

   sqlite3_bind_parameter_index $VM $v1

 } 1

 do_test bind-2.1.6 {

   sqlite3_bind_parameter_index $VM $v2

 } 2

 do_test bind-2.1.7 {

   sqlite3_bind_parameter_index $VM $v3

 } 3

 do_test bind-2.1.8 {

   sqlite3_bind_parameter_index $VM {:hi}

 } 0

 # 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

   set x [execsql {SELECT rowid, * FROM t1}]

   regsub {1e-005} $x {1e-05} y

   set y

 } {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;

   }

 } {}

 #

 #Symbian OS: this test is failing due to problems in printf format spec implementation

 #

 if {$::tcl_platform(platform)!="symbian"} {

   do_test bind-4.4 {

     sqlite3_bind_double $VM 1 NaN

     sqlite3_bind_double $VM 2 1e300

     sqlite3_bind_double $VM 3 -1e-300

     sqlite_step $VM N VALUES COLNAMES

     sqlite3_reset $VM

     set x [execsql {SELECT rowid, * FROM t1}]

     regsub {1e-005} $x {1e-05} y

     set y

   } {1 {} 1e+300 -1e-300}

 #

 #Symbian OS: this test is commented because depends on 4.4

 #

   do_test bind-4.5 {

     execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}

   } {null real real}

 }

 do_test bind-4.6 {

   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\000 -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;

   }

 } {}

 # Make sure zeros in a string work.

 #

 do_test bind-6.4 {

   db eval {DELETE FROM t1}

   sqlite3_bind_text $VM 1 hello\000there\000 12

   sqlite3_bind_text $VM 2 hello\000there\000 11

   sqlite3_bind_text $VM 3 hello\000there\000 -1

   sqlite_step $VM N VALUES COLNAMES

   sqlite3_reset $VM

   execsql {SELECT * FROM t1}

 } {hello hello hello}

 set enc [db eval {PRAGMA encoding}]

 if {$enc=="UTF-8"} {

   do_test bind-6.5 {

     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}

   } {68656C6C6F00746865726500 68656C6C6F007468657265 68656C6C6F}

 } elseif {$enc=="UTF-16le"} {

   do_test bind-6.5 {

     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}

   } {680065006C006C006F000000740068006500720065000000 680065006C006C006F00000074006800650072006500 680065006C006C006F00}

 } elseif {$enc=="UTF-16be"} {

   do_test bind-6.5 {

     execsql {SELECT  hex(a), hex(b), hex(c) FROM t1}

   } {00680065006C006C006F0000007400680065007200650000 00680065006C006C006F000000740068006500720065 00680065006C006C006F}

 } else {

   do_test bind-6.5 {

     set "Unknown database encoding: $::enc"

   } {}

 }

 do_test bind-6.6 {

   execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}

 } {text text text}

 do_test bind-6.7 {

   execsql {

     DELETE FROM t1;

   }

 } {}

 # UTF-16 text

 ifcapable {utf16} {

   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 {

     db eval {DELETE FROM t1}

     sqlite3_bind_text16 $VM 1 [encoding convertto unicode hi\000yall\000] 16

     sqlite3_bind_text16 $VM 2 [encoding convertto unicode hi\000yall\000] 14

     sqlite3_bind_text16 $VM 3 [encoding convertto unicode hi\000yall\000] -1

     sqlite_step $VM N VALUES COLNAMES

     sqlite3_reset $VM

     execsql {SELECT * FROM t1}

   } {hi hi hi}

   if {$enc=="UTF-8"} {

     do_test bind-7.4 {

       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}

     } {68690079616C6C00 68690079616C6C 6869}

   } elseif {$enc=="UTF-16le"} {

     do_test bind-7.4 {

       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}

     } {680069000000790061006C006C000000 680069000000790061006C006C00 68006900}

   } elseif {$enc=="UTF-16be"} {

     do_test bind-7.4 {

       execsql {SELECT hex(a), hex(b), hex(c) FROM t1}

     } {00680069000000790061006C006C0000 00680069000000790061006C006C 00680069}

   }

   do_test bind-7.5 {

     execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}

   } {text text text}

 }

 do_test bind-7.99 {

   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 or column index out of range}

 ifcapable {utf16} {

   do_test bind-8.3 {

     encoding convertfrom unicode [sqlite3_errmsg16 $DB]

   } {bind or column 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 or column index out of range}

 ifcapable {utf16} {

   do_test bind-8.7 {

     encoding convertfrom unicode [sqlite3_errmsg16 $DB]

   } {bind or column 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}

 ifcapable {utf16} {

   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-8.99 {

   sqlite3_finalize $VM

 } SQLITE_OK

 do_test bind-9.1 {

   execsql {

     CREATE TABLE t2(a,b,c,d,e,f);

   }

   set rc [catch {

     sqlite3_prepare $DB {

       INSERT INTO t2(a) VALUES(?0)

     } -1 TAIL

   } msg]

   lappend rc $msg

 } {1 {(1) variable number must be between ?1 and ?999}}

 do_test bind-9.2 {

   set rc [catch {

     sqlite3_prepare $DB {

       INSERT INTO t2(a) VALUES(?1000)

     } -1 TAIL

   } msg]

   lappend rc $msg

 } {1 {(1) variable number must be between ?1 and ?999}}

 do_test bind-9.3.1 {

   set VM [

     sqlite3_prepare $DB {

       INSERT INTO t2(a,b) VALUES(?1,?999)

     } -1 TAIL

   ]

   sqlite3_bind_parameter_count $VM

 } {999}

 catch {sqlite3_finalize $VM}

 do_test bind-9.3.2 {

   set VM [

     sqlite3_prepare $DB {

       INSERT INTO t2(a,b) VALUES(?2,?998)

     } -1 TAIL

   ]

   sqlite3_bind_parameter_count $VM

 } {998}

 catch {sqlite3_finalize $VM}

 do_test bind-9.4 {

   set VM [

     sqlite3_prepare $DB {

       INSERT INTO t2(a,b,c,d) VALUES(?1,?997,?,?)

     } -1 TAIL

   ]

   sqlite3_bind_parameter_count $VM

 } {999}

 do_test bind-9.5 {

   sqlite3_bind_int $VM 1 1

   sqlite3_bind_int $VM 997 999

   sqlite3_bind_int $VM 998 1000

   sqlite3_bind_int $VM 999 1001

   sqlite3_step $VM

 } SQLITE_DONE

 do_test bind-9.6 {

   sqlite3_finalize $VM

 } SQLITE_OK

 do_test bind-9.7 {

   execsql {SELECT * FROM t2}

 } {1 999 1000 1001 {} {}}

 ifcapable {tclvar} {

   do_test bind-10.1 {

     set VM [

       sqlite3_prepare $DB {

         INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,$abc,:abc,$ab,$abc,:abc)

       } -1 TAIL

     ]

     sqlite3_bind_parameter_count $VM

   } 3

   set v1 {$abc}

   set v2 {$ab}

 }

 ifcapable {!tclvar} {

   do_test bind-10.1 {

     set VM [

       sqlite3_prepare $DB {

         INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,:xyz,:abc,:xy,:xyz,:abc)

       } -1 TAIL

     ]

     sqlite3_bind_parameter_count $VM

   } 3

   set v1 {:xyz}

   set v2 {:xy}

 }

 do_test bind-10.2 {

   sqlite3_bind_parameter_index $VM :abc

 } 1

 do_test bind-10.3 {

   sqlite3_bind_parameter_index $VM $v1

 } 2

 do_test bind-10.4 {

   sqlite3_bind_parameter_index $VM $v2

 } 3

 do_test bind-10.5 {

   sqlite3_bind_parameter_name $VM 1

 } :abc

 do_test bind-10.6 {

   sqlite3_bind_parameter_name $VM 2

 } $v1

 do_test bind-10.7 {

   sqlite3_bind_parameter_name $VM 3

 } $v2

 do_test bind-10.7.1 {

   sqlite3_bind_parameter_name 0 1   ;# Ignore if VM is NULL

 } {}

 do_test bind-10.7.2 {

   sqlite3_bind_parameter_name $VM 0 ;# Ignore if index too small

 } {}

 do_test bind-10.7.3 {

   sqlite3_bind_parameter_name $VM 4 ;# Ignore if index is too big

 } {}

 do_test bind-10.8 {

   sqlite3_bind_int $VM 1 1

   sqlite3_bind_int $VM 2 2

   sqlite3_bind_int $VM 3 3

   sqlite3_step $VM

 } SQLITE_DONE

 do_test bind-10.8.1 {

   # Binding attempts after program start should fail

   set rc [catch {

     sqlite3_bind_int $VM 1 1

   } msg]

   lappend rc $msg

 } {1 {}}

 do_test bind-10.9 {

   sqlite3_finalize $VM

 } SQLITE_OK

 do_test bind-10.10 {

   execsql {SELECT * FROM t2}

 } {1 999 1000 1001 {} {} 1 2 1 3 2 1}

 # Ticket #918

 #

 do_test bind-10.11 {

   # catch {sqlite3_finalize $VM}

   set VM [

     sqlite3_prepare $DB {

       INSERT INTO t2(a,b,c,d,e,f) VALUES(:abc,?,?4,:pqr,:abc,?4)

     } -1 TAIL

   ]

   sqlite3_bind_parameter_count $VM

 } 5

 do_test bind-10.11.1 {

   sqlite3_bind_parameter_index 0 :xyz  ;# ignore NULL VM arguments

 } 0

 do_test bind-10.12 {

   sqlite3_bind_parameter_index $VM :xyz

 } 0

 do_test bind-10.13 {

   sqlite3_bind_parameter_index $VM {}

 } 0

 do_test bind-10.14 {

   sqlite3_bind_parameter_index $VM :pqr

 } 5

 do_test bind-10.15 {

   sqlite3_bind_parameter_index $VM ?4

 } 4

 do_test bind-10.16 {

   sqlite3_bind_parameter_name $VM 1

 } :abc

 do_test bind-10.17 {

   sqlite3_bind_parameter_name $VM 2

 } {}

 do_test bind-10.18 {

   sqlite3_bind_parameter_name $VM 3

 } {}

 do_test bind-10.19 {

   sqlite3_bind_parameter_name $VM 4

 } {?4}

 do_test bind-10.20 {

   sqlite3_bind_parameter_name $VM 5

 } :pqr

 catch {sqlite3_finalize $VM}

 # Make sure we catch an unterminated "(" in a Tcl-style variable name

 #

 ifcapable tclvar {

   do_test bind-11.1 {

     catchsql {SELECT * FROM sqlite_master WHERE name=$abc(123 and sql NOT NULL;}

   } {1 {unrecognized token: "$abc(123"}}

 }

 if {[execsql {pragma encoding}]=="UTF-8"} {

   # Test the ability to bind text that contains embedded '\000' characters.

   # Make sure we can recover the entire input string.

   #

   do_test bind-12.1 {

     execsql {

       CREATE TABLE t3(x BLOB);

     }

     set VM [sqlite3_prepare $DB {INSERT INTO t3 VALUES(?)} -1 TAIL]

     sqlite_bind  $VM 1 not-used blob10

     sqlite3_step $VM

     sqlite3_finalize $VM

     execsql {

       SELECT typeof(x), length(x), quote(x),

              length(cast(x AS BLOB)), quote(cast(x AS BLOB)) FROM t3

     }

   } {text 3 'abc' 10 X'6162630078797A007071'}

   do_test bind-12.2 {

     sqlite3_create_function $DB

     execsql {

       SELECT quote(cast(x_coalesce(x) AS blob)) FROM t3

     }

   } {X'6162630078797A007071'}

 }

 # Test the operation of sqlite3_clear_bindings

 #

 do_test bind-13.1 {

   set VM [sqlite3_prepare $DB {SELECT ?,?,?} -1 TAIL]

   sqlite3_step $VM

   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \

                [sqlite3_column_type $VM 2]

 } {NULL NULL NULL}

 do_test bind-13.2 {

   sqlite3_reset $VM

   sqlite3_bind_int $VM 1 1

   sqlite3_bind_int $VM 2 2

   sqlite3_bind_int $VM 3 3

   sqlite3_step $VM

   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \

                [sqlite3_column_type $VM 2]

 } {INTEGER INTEGER INTEGER}

 do_test bind-13.3 {

   sqlite3_reset $VM

   sqlite3_step $VM

   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \

                [sqlite3_column_type $VM 2]

 } {INTEGER INTEGER INTEGER}

 do_test bind-13.4 {

   sqlite3_reset $VM

   sqlite3_clear_bindings $VM

   sqlite3_step $VM

   list [sqlite3_column_type $VM 0] [sqlite3_column_type $VM 1] \

                [sqlite3_column_type $VM 2]

 } {NULL NULL NULL}

 sqlite3_finalize $VM

 finish_test