?? bind.test
字號:
# 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.37 2006/01/23 18:42:21 drh Exp $#set testdir [file dirname $argv0]source $testdir/tester.tclproc 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} 3do_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_DONEdo_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_DONEdo_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# 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} 3do_test bind-2.1.2 { sqlite3_bind_parameter_name $VM 1} $v1do_test bind-2.1.3 { sqlite3_bind_parameter_name $VM 2} $v2do_test bind-2.1.4 { sqlite3_bind_parameter_name $VM 3} $v3do_test bind-2.1.5 { sqlite3_bind_parameter_index $VM $v1} 1do_test bind-2.1.6 { sqlite3_bind_parameter_index $VM $v2} 2do_test bind-2.1.7 { sqlite3_bind_parameter_index $VM $v3} 3do_test bind-2.1.8 { sqlite3_bind_parameter_index $VM {:hi}} 0# 32 bit Integersdo_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 Integersdo_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; }} {}# Doublesdo_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; }} {}# NULLdo_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 textdo_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 textifcapable {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 { 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_OKdo_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 { 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.4 { set VM [ sqlite3_prepare $DB { INSERT INTO t2(a,b,c,d) VALUES(?1,?999,?,?) } -1 TAIL ] sqlite3_bind_parameter_count $VM} {1001}do_test bind-9.5 { sqlite3_bind_int $VM 1 1 sqlite3_bind_int $VM 999 999 sqlite3_bind_int $VM 1000 1000 sqlite3_bind_int $VM 1001 1001 sqlite3_step $VM} SQLITE_DONEdo_test bind-9.6 { sqlite3_finalize $VM} SQLITE_OKdo_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} 1do_test bind-10.3 { sqlite3_bind_parameter_index $VM $v1} 2do_test bind-10.4 { sqlite3_bind_parameter_index $VM $v2} 3do_test bind-10.5 { sqlite3_bind_parameter_name $VM 1} :abcdo_test bind-10.6 { sqlite3_bind_parameter_name $VM 2} $v1do_test bind-10.7 { sqlite3_bind_parameter_name $VM 3} $v2do_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_DONEdo_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_OKdo_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} 5do_test bind-10.11.1 { sqlite3_bind_parameter_index 0 :xyz ;# ignore NULL VM arguments} 0do_test bind-10.12 { sqlite3_bind_parameter_index $VM :xyz} 0do_test bind-10.13 { sqlite3_bind_parameter_index $VM {}} 0do_test bind-10.14 { sqlite3_bind_parameter_index $VM :pqr} 5do_test bind-10.15 { sqlite3_bind_parameter_index $VM ?4} 4do_test bind-10.16 { sqlite3_bind_parameter_name $VM 1} :abcdo_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} :pqrcatch {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 enter 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'}}finish_test?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -