# 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