3 # Tests for the tdbc::mysql bridge
5 # Copyright (c) 2008 by Kevin B. Kenny
6 # See the file "license.terms" for information on usage and redistribution
7 # of this file, and for a DISCLAIMER OF ALL WARRANTIES.
9 # RCS: @(#) $Id: tdbcmysql.tcl,v 1.47 2008/02/27 02:08:27 kennykb Exp $
11 #------------------------------------------------------------------------------
14 package require tcltest 2
15 namespace import -force ::tcltest::*
16 tcltest::loadTestedCommands
17 package require tdbc::mysql
19 # We need to know the parameters of the MySQL database for testing.
22 if {[info exists ::env(TDBCMYSQL_TEST_HOST)]} {
23 lappend connFlags -host $::env(TDBCMYSQL_TEST_HOST)
25 if {[info exists ::env(TDBCMYSQL_TEST_USER)]} {
26 lappend connFlags -user $::env(TDBCMYSQL_TEST_USER)
28 if {[info exists ::env(TDBCMYSQL_TEST_PASSWD)]} {
29 lappend connFlags -passwd $::env(TDBCMYSQL_TEST_PASSWD)
31 if {[info exists ::env(TDBCMYSQL_TEST_DB)]} {
32 lappend connFlags -db $::env(TDBCMYSQL_TEST_DB)
34 lappend connFlags -db tdbc_test
36 if {[info exists ::env(TDBCMYSQL_TEST_SOCKET)]} {
37 lappend connFlags -socket $::env(TDBCMYSQL_TEST_SOCKET)
39 if {[info exists ::env(TDBCMYSQL_TEST_PORT)]} {
40 lappend connFlags -port $::env(TDBCMYSQL_TEST_PORT)
43 #------------------------------------------------------------------------------
45 test tdbc::mysql-1.1 {create a connection, wrong # args} {*}{
47 tdbc::mysql::connection create
51 -result {wrong # args*}
54 test tdbc::mysql-1.2 {create a connection, connection string missing} {*}{
56 tdbc::mysql::connection create db -user
60 -result {wrong # args*}
63 test tdbc::mysql-1.3 {create a connection, bad arg} {*}{
65 tdbc::mysql::connection create db -rubbish rubbish
69 -result {bad option "-rubbish"*}
72 test tdbc::mysql-1.4 {create a connection, bad flag} {*}{
74 tdbc::mysql::connection create db -interactive rubbish
77 -result {expected boolean value but got "rubbish"}
80 test tdbc::mysql-1.5 {create a connection, bad port} {*}{
82 tdbc::mysql::connection create db -port rubbish
85 -result {expected integer but got "rubbish"}
88 test tdbc::mysql-1.6 {create a connection, bad port} {*}{
90 tdbc::mysql::connection create db -port 999999999999
94 -result {integer value too large to represent*}
97 test tdbc::mysql-1.7 {create a connection, bad port} {*}{
99 tdbc::mysql::connection create db -port -1
102 -result {port number must be in range [0..65535]}
105 test tdbc::mysql-1.8 {create a connection, bad port} {*}{
107 tdbc::mysql::connection create db -port 65536
110 -result {port number must be in range [0..65535]}
113 test tdbc::mysql-1.9 {create a connection, failure} {*}{
116 tdbc::mysql::connection create db -host rubbish.example.com
118 list $status $result $::errorCode
121 -result {1 {Unknown MySQL server host*} {TDBC GENERAL_ERROR HY000 MYSQL *}}
124 #------------------------------------------------------------------------------
126 # Bail out if the user hasn't set TDBCMYSQL_TEST_DB
128 if {![info exists ::env(TDBCMYSQL_TEST_DB)]} {
129 puts "Not performing functional testing of tdbc::mysql because\
130 ::env(TDBCMYSQL_TEST_DB) is not set."
135 test tdbc::mysql-1.10 {create a connection, successful} {*}{
137 tdbc::mysql::connection create ::db {*}$connFlags
141 catch {rename ::db {}}
145 #------------------------------------------------------------------------------
147 # The tests that follow all require a connection to a database.
149 tdbc::mysql::connection create ::db {*}$connFlags
150 catch {::db allrows {DROP TABLE people}}
152 #------------------------------------------------------------------------------
154 test tdbc::mysql-2.1 {prepare statement, wrong # args} {*}{
160 -result {wrong # args*}
163 test tdbc::mysql-2.2 {don't make a statement without a connection} {*}{
165 tdbc::mysql::statement create stmt rubbish moreRubbish
168 -result {rubbish does not refer to an object}
171 test tdbc::mysql-2.3 {don't make a statement without a connection} {*}{
173 tdbc::mysql::statement create stmt oo::class moreRubbish
176 -result {oo::class does not refer to a MySQL connection}
179 test tdbc::mysql-2.4 {semicolons in statements} {*}{
181 ::db prepare {select foo from bar; select grill from quux}
184 -result {tdbc::mysql does not support semicolons in statements}
187 test tdbc::mysql-3.1 {prepare an invalid statement} {*}{
194 list $status $result $::errorCode
197 -result {1 {*SQL syntax*} {TDBC SYNTAX_ERROR* 42000 MYSQL *}}
200 test tdbc::mysql-3.2 {prepare a valid statement} {*}{
202 set stmt [::db prepare {
204 idnum INTEGER PRIMARY KEY,
205 name VARCHAR(40) NOT NULL
212 catch [rename $stmt {}]
216 test tdbc::mysql-3.3 {execute a valid statement with no results} {*}{
218 set stmt [::db prepare {
220 idnum INTEGER PRIMARY KEY,
221 name VARCHAR(40) NOT NULL
224 set rs [$stmt execute]
225 list [expr {[$rs rowcount] <= 0}] [$rs columns] [$rs nextrow nothing]
232 set stmt [::db prepare {
235 set rs [$stmt execute]
242 test tdbc::mysql-3.4 {result set: wrong # args} {*}{
244 set stmt [::db prepare {
246 idnum INTEGER PRIMARY KEY,
247 name VARCHAR(40) NOT NULL
250 $stmt execute with extra args
254 -result {wrong # args*}
256 catch [rename $stmt {}]
260 test tdbc::mysql-3.5 {result set: trying to create against a non-object} {*}{
262 tdbc::mysql::resultset create rs nothing
265 -result {nothing does not refer to an object}
268 test tdbc::mysql-3.6 {result set: trying to create against a non-statement} {*}{
270 tdbc::mysql::resultset create rs db
273 -result {db does not refer to a MySQL statement}
276 #-------------------------------------------------------------------------------
278 # Following tests need a 'people' table in the database.
279 # They also need to use the InnoDB engine, because some of the test cases
280 # test transaction support.
282 set stmt [::db prepare {
284 idnum INTEGER PRIMARY KEY,
285 name VARCHAR(40) NOT NULL,
289 set rs [$stmt execute]
293 test tdbc::mysql-4.1 {execute an insert with no params} {*}{
295 set stmt [::db prepare {
296 INSERT INTO people(idnum, name, info) values(1, 'fred', 0)
298 set rs [$stmt execute]
299 list [$rs rowcount] [$rs columns] [$rs nextrow nothing]
306 set stmt [::db prepare {
309 set rs [$stmt execute]
316 test tdbc::mysql-4.2 {execute an insert with variable parameters} {*}{
318 set stmt [::db prepare {
319 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
321 $stmt paramtype idnum integer
322 $stmt paramtype name varchar 40
325 set rs [$stmt execute]
326 list [$rs rowcount] [$rs columns] [$rs nextrow nothing]
333 set stmt [::db prepare {
336 set rs [$stmt execute]
343 test tdbc::mysql-4.3 {execute an insert with dictionary parameters} {*}{
345 set stmt [::db prepare {
346 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
348 $stmt paramtype idnum integer
349 $stmt paramtype name varchar 40
350 set rs [$stmt execute {idnum 1 name fred}]
351 list [$rs rowcount] [$rs columns] [$rs nextrow nothing]
358 set stmt [::db prepare {
361 set rs [$stmt execute]
368 test tdbc::mysql-4.4 {bad dictionary} {*}{
370 set stmt [::db prepare {
371 INSERT INTO people(idnum, name) values(:idnum, :name)
373 $stmt paramtype idnum integer
374 $stmt paramtype name varchar 40
375 $stmt execute {idnum 1 name}
378 -result {missing value to go with key}
382 set stmt [::db prepare {
385 set rs [$stmt execute]
392 test tdbc::mysql-4.5 {missing parameter variable} {*}{
398 set stmt [::db prepare {
399 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
401 $stmt paramtype idnum integer
402 $stmt paramtype name varchar 40
408 -result {*cannot be [nN]ull*}
412 set stmt [::db prepare {
415 set rs [$stmt execute]
422 test tdbc::mysql-4.6 {missing parameter in dictionary} {*}{
425 set stmt [::db prepare {
426 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
428 $stmt paramtype idnum integer
429 $stmt paramtype name varchar 40
430 $stmt execute {name fred}
434 -result {*cannot be [nN]ull*}
438 set stmt [::db prepare {
441 set rs [$stmt execute]
448 test tdbc::mysql-4.7 {missing parameter - nullable} {*}{
451 set stmt [::db prepare {
452 INSERT INTO people(idnum, name, info) values(:idnum, :name, :info)
454 $stmt paramtype idnum integer
455 $stmt paramtype name varchar 40
456 $stmt paramtype info integer
457 set stmt2 [::db prepare {
458 SELECT name, info FROM people WHERE idnum = :idnum
460 $stmt2 paramtype idnum integer
463 set name "mr. gravel"
465 set rs [$stmt execute]
467 set rs [$stmt2 execute]
468 $rs nextrow -as dicts row
471 -result {name {mr. gravel}}
473 catch {rename $rs {}}
477 set stmt [::db prepare {
480 set rs [$stmt execute]
487 test tdbc::mysql-4.8 {missing parameter in dictionary - nullable} {*}{
489 set stmt [::db prepare {
490 INSERT INTO people(idnum, name, info) values(:idnum, :name, :info)
492 $stmt paramtype idnum integer
493 $stmt paramtype name varchar 40
494 $stmt paramtype info integer
495 set stmt2 [::db prepare {
496 SELECT name, info FROM people WHERE idnum = :idnum
498 $stmt2 paramtype idnum integer
501 set rs [$stmt execute {name {gary granite} idnum 200}]
503 set rs [$stmt2 execute {idnum 200}]
504 $rs nextrow -as dicts row
507 -result {name {gary granite}}
509 catch {rename $rs {}}
513 set stmt [::db prepare {
516 set rs [$stmt execute]
523 test tdbc::mysql-4.9 {two result sets open against the same statement} {*}{
525 set stmt [::db prepare {
526 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
528 $stmt paramtype idnum integer
529 $stmt paramtype name varchar 40
530 set rs1 [$stmt execute {idnum 1 name fred}]
531 set rs2 [$stmt execute {idnum 2 name wilma}]
532 list [$rs1 rowcount] [$rs1 columns] [$rs1 nextrow nothing] \
533 [$rs2 rowcount] [$rs2 columns] [$rs2 nextrow nothing]
535 -result {1 {} 0 1 {} 0}
541 set stmt [::db prepare {
544 set rs [$stmt execute]
551 test tdbc::mysql-4.10 {failed execution} {*}{
553 set stmt [::db prepare {
554 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
556 $stmt paramtype idnum integer
557 $stmt paramtype name varchar 40
558 set rs [$stmt execute {idnum 1 name fred}]
562 set status [catch {$stmt execute {idnum 1 name barney}} result]
563 list $status $::errorCode
567 set stmt [::db prepare {
570 set rs [$stmt execute]
575 -result {1 {TDBC CONSTRAINT_VIOLATION 23* MYSQL *}}
578 test tdbc::mysql-5.1 {paramtype - too few args} {*}{
580 set stmt [::db prepare {
581 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
585 $stmt paramtype idnum
592 -result {wrong # args*}
595 test tdbc::mysql-5.2 {paramtype - just a direction} {*}{
597 set stmt [::db prepare {
598 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
602 $stmt paramtype idnum in
609 -result {wrong # args*}
612 test tdbc::mysql-5.3 {paramtype - bad type} {*}{
614 set stmt [::db prepare {
615 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
619 $stmt paramtype idnum rubbish
626 -result {bad SQL data type "rubbish":*}
629 test tdbc::mysql-5.4 {paramtype - bad scale} {*}{
631 set stmt [::db prepare {
632 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
636 $stmt paramtype idnum decimal rubbish
643 -result {expected integer but got "rubbish"}
646 test tdbc::mysql-5.5 {paramtype - bad precision} {*}{
648 set stmt [::db prepare {
649 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
653 $stmt paramtype idnum decimal 12 rubbish
660 -result {expected integer but got "rubbish"}
663 test tdbc::mysql-5.6 {paramtype - unknown parameter} {*}{
665 set stmt [::db prepare {
666 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
670 $stmt paramtype rubbish integer
677 -result {unknown parameter "rubbish":*}
680 test tdbc::mysql-6.1 {rowcount - wrong args} {*}{
682 set stmt [::db prepare {
683 INSERT INTO people(idnum, name, info) values(:idnum, :name, 0)
685 $stmt paramtype idnum integer
686 $stmt paramtype name varchar 40
687 set rs [$stmt execute {idnum 1 name fred}]
695 set stmt [::db prepare {
698 set rs [$stmt execute]
704 -result "wrong \# args*"
707 #-------------------------------------------------------------------------------
709 # next tests require data in the database
712 set stmt [db prepare {
713 INSERT INTO people(idnum, name, info) VALUES(:idnum, :name, NULL)
715 $stmt paramtype idnum integer
716 $stmt paramtype name varchar 40
718 foreach name {fred wilma pebbles barney betty bam-bam} {
719 set rs [$stmt execute]
726 #-------------------------------------------------------------------------------
728 test tdbc::mysql-7.1 {columns - bad args} {*}{
730 set stmt [::db prepare {
733 set rs [$stmt execute]
744 -result {wrong # args*}
747 test tdbc::mysql-7.2 {columns - get column names} {*}{
749 set stmt [::db prepare {
752 set rs [$stmt execute]
761 -result {idnum name info}
764 test tdbc::mysql-8.1 {nextrow - as dicts} {*}{
766 set stmt [::db prepare {
767 SELECT idnum, name FROM people ORDER BY idnum
769 set rs [$stmt execute]
774 while {[$rs nextrow -- row]} {
775 if {$idnum != [dict get $row idnum]} {
776 binary scan [dict get $row idnum] c* v; puts $v
777 binary scan [dict get $row name] c* v; puts $v
778 error [list bad idnum [dict get $row idnum] should be $idnum]
780 lappend names [dict get $row name]
789 -result {fred wilma pebbles barney betty bam-bam}
792 test tdbc::mysql-8.2 {nextrow - as lists} {*}{
794 set stmt [::db prepare {
795 SELECT idnum, name FROM people ORDER BY idnum
797 set rs [$stmt execute]
802 while {[$rs nextrow -as lists -- row]} {
803 if {$idnum != [lindex $row 0]} {
804 error [list bad idnum [lindex $row 0] should be $idnum]
806 lappend names [lindex $row 1]
815 -result {fred wilma pebbles barney betty bam-bam}
818 test tdbc::mysql-8.3 {nextrow - bad cursor state} {*}{
820 set stmt [::db prepare {
821 SELECT idnum, name FROM people ORDER BY idnum
825 set rs [$stmt execute]
827 while {[$rs nextrow row]} {}
837 test tdbc::mysql-8.4 {anonymous columns - dicts} {*}{
839 set stmt [::db prepare {
840 SELECT COUNT(*), MAX(idnum) FROM people
842 set rs [$stmt execute]
854 -result {1 {* 6 * 6} 0}
857 test tdbc::mysql-8.5 {anonymous columns - lists} {*}{
859 set stmt [::db prepare {
860 SELECT COUNT(*), MAX(idnum) FROM people
862 set rs [$stmt execute]
865 list [$rs nextrow -as lists row] \
867 [$rs nextrow -as lists row]
875 test tdbc::mysql-8.6 {null results - dicts} {*}{
877 set stmt [::db prepare {
878 SELECT idnum, name, info FROM people WHERE name = 'fred'
880 set rs [$stmt execute]
883 list [$rs nextrow row] $row [$rs nextrow row]
888 -result {1 {idnum 1 name fred} 0}
891 test tdbc::mysql-8.7 {null results - lists} {*}{
893 set stmt [::db prepare {
894 SELECT idnum, name, info FROM people WHERE name = 'fred'
896 set rs [$stmt execute]
899 list [$rs nextrow -as lists -- row] $row [$rs nextrow -as lists -- row]
904 -result {1 {1 fred {}} 0}
907 test tdbc::mysql-9.1 {rs foreach var script} {*}{
909 set stmt [::db prepare {
910 SELECT idnum, name FROM people WHERE name LIKE 'b%'
912 set rs [$stmt execute]
925 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
928 test tdbc::mysql-9.2 {stmt foreach var script} {*}{
930 set stmt [::db prepare {
931 SELECT idnum, name FROM people WHERE name LIKE 'b%'
945 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
948 test tdbc::mysql-9.3 {db foreach var sqlcode script} {*}{
952 SELECT idnum, name FROM people WHERE name LIKE 'b%'
958 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
961 test tdbc::mysql-9.4 {rs foreach -- var script} {*}{
963 set stmt [::db prepare {
964 SELECT idnum, name FROM people WHERE name LIKE 'b%'
966 set rs [$stmt execute]
979 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
982 test tdbc::mysql-9.5 {stmt foreach -- var script} {*}{
984 set stmt [::db prepare {
985 SELECT idnum, name FROM people WHERE name LIKE 'b%'
990 $stmt foreach -- row {
998 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1001 test tdbc::mysql-9.6 {db foreach -- var query script} {*}{
1005 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1011 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1014 test tdbc::mysql-9.7 {rs foreach -- -as lists} {*}{
1016 set stmt [::db prepare {
1017 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1019 set rs [$stmt execute]
1023 $rs foreach -as lists row {
1032 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1035 test tdbc::mysql-9.8 {stmt foreach -as lists} {*}{
1037 set stmt [::db prepare {
1038 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1043 $stmt foreach -as lists row {
1051 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1054 test tdbc::mysql-9.9 {db foreach -as lists} {*}{
1057 db foreach -as lists row {
1058 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1064 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1067 test tdbc::mysql-9.10 {rs foreach -as lists --} {*}{
1069 set stmt [::db prepare {
1070 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1072 set rs [$stmt execute]
1076 $rs foreach -as lists -- row {
1085 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1088 test tdbc::mysql-9.11 {stmt foreach -as lists --} {*}{
1090 set stmt [::db prepare {
1091 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1096 $stmt foreach -as lists -- row {
1104 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1107 test tdbc::mysql-9.12 {db foreach -as lists --} {*}{
1110 db foreach -as lists row {
1111 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1117 -result {{4 barney {}} {5 betty {}} {6 bam-bam {}}}
1120 test tdbc::mysql-9.13 {rs foreach -as lists -columnsvar c --} {*}{
1122 set stmt [::db prepare {
1123 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1125 set rs [$stmt execute]
1129 $rs foreach -as lists -columnsvar c -- row {
1130 foreach cn $c cv $row {
1131 lappend result $cn $cv
1140 -result {idnum 4 name barney idnum 5 name betty idnum 6 name bam-bam}
1143 test tdbc::mysql-9.14 {stmt foreach -as lists -columnsvar c --} {*}{
1145 set stmt [::db prepare {
1146 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1151 $stmt foreach -as lists -columnsvar c -- row {
1152 foreach cn $c cv $row {
1153 lappend result $cn $cv
1161 -result {idnum 4 name barney idnum 5 name betty idnum 6 name bam-bam}
1164 test tdbc::mysql-9.15 {db foreach -as lists -columnsvar c --} {*}{
1167 db foreach -as lists -columnsvar c -- row {
1168 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1170 foreach cn $c cv $row {
1171 lappend result $cn $cv
1176 -result {idnum 4 name barney idnum 5 name betty idnum 6 name bam-bam}
1179 test tdbc::mysql-9.16 {rs foreach / break out of loop} {*}{
1181 set stmt [::db prepare {
1182 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1184 set rs [$stmt execute]
1188 $rs foreach -as lists -- row {
1189 if {[lindex $row 1] eq {betty}} break
1198 -result {{4 barney {}}}
1201 test tdbc::mysql-9.17 {stmt foreach / break out of loop} {*}{
1203 set stmt [::db prepare {
1204 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1209 $stmt foreach -as lists -- row {
1210 if {[lindex $row 1] eq {betty}} break
1218 -result {{4 barney {}}}
1221 test tdbc::mysql-9.18 {db foreach / break out of loop} {*}{
1224 db foreach -as lists -- row {
1225 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1227 if {[lindex $row 1] eq {betty}} break
1232 -result {{4 barney {}}}
1235 test tdbc::mysql-9.19 {rs foreach / continue in loop} {*}{
1237 set stmt [::db prepare {
1238 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1240 set rs [$stmt execute]
1244 $rs foreach -as lists -- row {
1245 if {[lindex $row 1] eq {betty}} continue
1254 -result {{4 barney {}} {6 bam-bam {}}}
1257 test tdbc::mysql-9.20 {stmt foreach / continue in loop} {*}{
1259 set stmt [::db prepare {
1260 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1265 $stmt foreach -as lists -- row {
1266 if {[lindex $row 1] eq {betty}} continue
1274 -result {{4 barney {}} {6 bam-bam {}}}
1277 test tdbc::mysql-9.21 {db foreach / continue in loop} {*}{
1280 db foreach -as lists -- row {
1281 SELECT idnum, name, info FROM people WHERE name LIKE 'b%'
1283 if {[lindex $row 1] eq {betty}} continue
1288 -result {{4 barney {}} {6 bam-bam {}}}
1291 test tdbc::mysql-9.22 {rs foreach / return out of the loop} {*}{
1293 set stmt [::db prepare {
1294 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1296 set rs [$stmt execute]
1297 proc tdbcmysql-9.22 {rs} {
1298 $rs foreach -as lists -- row {
1299 if {[lindex $row 1] eq {betty}} {
1300 return [lindex $row 0]
1310 rename tdbcmysql-9.22 {}
1317 test tdbc::mysql-9.23 {stmt foreach / return out of the loop} {*}{
1319 set stmt [::db prepare {
1320 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1322 proc tdbcmysql-9.23 {stmt} {
1323 $stmt foreach -as lists -- row {
1324 if {[lindex $row 1] eq {betty}} {
1325 return [lindex $row 0]
1332 tdbcmysql-9.23 $stmt
1335 rename tdbcmysql-9.23 {}
1341 test tdbc::mysql-9.24 {db foreach / return out of the loop} {*}{
1343 proc tdbcmysql-9.24 {stmt} {
1344 db foreach -as lists -- row {
1345 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1347 if {[lindex $row 1] eq {betty}} {
1348 return [lindex $row 0]
1355 tdbcmysql-9.24 $stmt
1358 rename tdbcmysql-9.24 {}
1363 test tdbc::mysql-9.25 {rs foreach / error out of the loop} {*}{
1365 set stmt [::db prepare {
1366 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1368 set rs [$stmt execute]
1369 proc tdbcmysql-9.25 {rs} {
1370 $rs foreach -as lists -- row {
1371 if {[lindex $row 1] eq {betty}} {
1372 error [lindex $row 0]
1382 rename tdbcmysql-9.25 {}
1390 test tdbc::mysql-9.26 {stmt foreach - error out of the loop} {*}{
1392 set stmt [::db prepare {
1393 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1395 proc tdbcmysql-9.26 {stmt} {
1396 $stmt foreach -as lists -- row {
1397 if {[lindex $row 1] eq {betty}} {
1398 error [lindex $row 0]
1405 tdbcmysql-9.26 $stmt
1408 rename tdbcmysql-9.26 {}
1415 test tdbc::mysql-9.27 {db foreach / error out of the loop} {*}{
1417 proc tdbcmysql-9.27 {} {
1418 db foreach -as lists -- row {
1419 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1421 if {[lindex $row 1] eq {betty}} {
1422 error [lindex $row 0]
1432 rename tdbcmysql-9.27 {}
1438 test tdbc::mysql-9.28 {rs foreach / unknown status from the loop} {*}{
1440 set stmt [::db prepare {
1441 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1443 set rs [$stmt execute]
1444 proc tdbcmysql-9.28 {rs} {
1445 $rs foreach -as lists -- row {
1446 if {[lindex $row 1] eq {betty}} {
1447 return -code 666 -level 0 [lindex $row 0]
1457 rename tdbcmysql-9.28 {}
1465 test tdbc::mysql-9.29 {stmt foreach / unknown status from the loop} {*}{
1467 set stmt [::db prepare {
1468 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1470 proc tdbcmysql-9.29 {stmt} {
1471 $stmt foreach -as lists -- row {
1472 if {[lindex $row 1] eq {betty}} {
1473 return -code 666 -level 0 [lindex $row 0]
1480 tdbcmysql-9.29 $stmt
1483 rename tdbcmysql-9.29 {}
1490 test tdbc::mysql-9.30 {db foreach / unknown status from the loop} {*}{
1492 proc tdbcmysql-9.30 {stmt} {
1493 db foreach -as lists -- row {
1494 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1496 if {[lindex $row 1] eq {betty}} {
1497 return -code 666 -level 0 [lindex $row 0]
1504 tdbcmysql-9.30 $stmt
1507 rename tdbcmysql-9.30 {}
1513 test tdbc::mysql-9.31 {stmt foreach / params in variables} {*}{
1515 set stmt [::db prepare {
1516 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1518 $stmt paramtype thePattern varchar 40
1531 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1534 test tdbc::mysql-9.32 {db foreach / params in variables} {*}{
1539 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1545 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1548 test tdbc::mysql-9.33 {stmt foreach / parameters in a dictionary} {*}{
1550 set stmt [::db prepare {
1551 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1553 $stmt paramtype thePattern varchar 40
1557 $stmt foreach row {thePattern b%} {
1565 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1568 test tdbc::mysql-9.34 {db foreach / parameters in a dictionary} {*}{
1572 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1578 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1581 test tdbc::mysql-9.35 {stmt foreach - variable not found} {*}{
1583 set stmt [::db prepare {
1584 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1586 $stmt paramtype thePattern varchar 40
1587 catch {unset thePattern}
1591 set thePattern(bogosity) {}
1604 test tdbc::mysql-9.36 {db foreach - variable not found} {*}{
1606 catch {unset thePattern}
1610 set thePattern(bogosity) {}
1612 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1624 test tdbc::mysql-9.37 {rs foreach - too few args} {*}{
1626 set stmt [::db prepare {
1627 SELECT idnum, name FROM people
1629 set rs [$stmt execute]
1639 -result {wrong # args*}
1643 test tdbc::mysql-9.38 {stmt foreach - too few args} {*}{
1645 set stmt [::db prepare {
1646 SELECT idnum, name FROM people
1656 -result {wrong # args*}
1660 test tdbc::mysql-9.39 {db foreach - too few args} {*}{
1663 SELECT idnum, name FROM people
1667 -result {wrong # args*}
1671 test tdbc::mysql-9.40 {rs foreach - too many args} {*}{
1673 set stmt [::db prepare {
1674 SELECT idnum, name FROM people
1676 set rs [$stmt execute]
1679 $rs foreach row do something
1686 -result {wrong # args*}
1690 test tdbc::mysql-9.41 {stmt foreach - too many args} {*}{
1692 set stmt [::db prepare {
1693 SELECT idnum, name FROM people
1697 $stmt foreach row do something else
1703 -result {wrong # args*}
1707 test tdbc::mysql-9.42 {db foreach - too many args} {*}{
1710 SELECT idnum, name FROM people
1714 -result {wrong # args*}
1718 test tdbc::mysql-10.1 {allrows - no args} {*}{
1720 set stmt [::db prepare {
1721 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1723 set rs [$stmt execute]
1732 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1735 test tdbc::mysql-10.2 {allrows - no args} {*}{
1737 set stmt [::db prepare {
1738 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1747 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1750 test tdbc::mysql-10.3 {allrows - no args} {*}{
1753 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1756 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1759 test tdbc::mysql-10.4 {allrows --} {*}{
1761 set stmt [::db prepare {
1762 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1764 set rs [$stmt execute]
1773 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1776 test tdbc::mysql-10.5 {allrows --} {*}{
1778 set stmt [::db prepare {
1779 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1788 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1791 test tdbc::mysql-10.6 {allrows --} {*}{
1794 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1797 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1800 test tdbc::mysql-10.7 {allrows -as lists} {*}{
1802 set stmt [::db prepare {
1803 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1805 set rs [$stmt execute]
1808 $rs allrows -as lists
1814 -result {{4 barney} {5 betty} {6 bam-bam}}
1817 test tdbc::mysql-10.8 {allrows -as lists} {*}{
1819 set stmt [::db prepare {
1820 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1824 $stmt allrows -as lists
1829 -result {{4 barney} {5 betty} {6 bam-bam}}
1832 test tdbc::mysql-10.9 {allrows -as lists} {*}{
1834 db allrows -as lists {
1835 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1838 -result {{4 barney} {5 betty} {6 bam-bam}}
1841 test tdbc::mysql-10.10 {allrows -as lists --} {*}{
1843 set stmt [::db prepare {
1844 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1846 set rs [$stmt execute]
1849 $rs allrows -as lists --
1855 -result {{4 barney} {5 betty} {6 bam-bam}}
1858 test tdbc::mysql-10.11 {allrows -as lists --} {*}{
1860 set stmt [::db prepare {
1861 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1865 $stmt allrows -as lists --
1870 -result {{4 barney} {5 betty} {6 bam-bam}}
1873 test tdbc::mysql-10.12 {allrows -as lists --} {*}{
1875 db allrows -as lists -- {
1876 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1879 -result {{4 barney} {5 betty} {6 bam-bam}}
1882 test tdbc::mysql-10.13 {allrows -as lists -columnsvar c} {*}{
1884 set stmt [::db prepare {
1885 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1887 set rs [$stmt execute]
1890 set result [$rs allrows -as lists -columnsvar c]
1897 -result {{idnum name} {{4 barney} {5 betty} {6 bam-bam}}}
1900 test tdbc::mysql-10.14 {allrows -as lists -columnsvar c} {*}{
1902 set stmt [::db prepare {
1903 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1907 set result [$stmt allrows -as lists -columnsvar c]
1913 -result {{idnum name} {{4 barney} {5 betty} {6 bam-bam}}}
1916 test tdbc::mysql-10.15 {allrows -as lists -columnsvar c} {*}{
1918 set result [db allrows -as lists -columnsvar c {
1919 SELECT idnum, name FROM people WHERE name LIKE 'b%'
1923 -result {{idnum name} {{4 barney} {5 betty} {6 bam-bam}}}
1926 test tdbc::mysql-10.16 {allrows - correct lexical scoping of variables} {*}{
1928 set stmt [::db prepare {
1929 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1931 $stmt paramtype thePattern varchar 40
1940 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1943 test tdbc::mysql-10.17 {allrows - parameters in a dictionary} {*}{
1945 set stmt [::db prepare {
1946 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1948 $stmt paramtype thePattern varchar 40
1951 $stmt allrows {thePattern b%}
1956 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1959 test tdbc::mysql-10.18 {allrows - parameters in a dictionary} {*}{
1962 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1965 -result {{idnum 4 name barney} {idnum 5 name betty} {idnum 6 name bam-bam}}
1968 test tdbc::mysql-10.19 {allrows - variable not found} {*}{
1970 catch {unset thePattern}
1973 set thePattern(bogosity) {}
1975 SELECT idnum, name FROM people WHERE name LIKE :thePattern
1984 test tdbc::mysql-10.20 {allrows - too many args} {*}{
1986 set stmt [::db prepare {
1987 SELECT idnum, name FROM people
1991 $stmt allrows {} rubbish
1997 -result {wrong # args*}
2001 test tdbc::mysql-10.21 {bad -as} {*}{
2003 db allrows -as trash {
2004 SELECT idnum, name FROM people
2008 -result {bad variable type "trash": must be lists or dicts}
2011 test tdbc::mysql-11.1 {update - no rows} {*}{
2013 set stmt [::db prepare {
2014 UPDATE people SET info = 1 WHERE idnum > 6
2016 set rs [$stmt execute]
2028 test tdbc::mysql-11.2 {update - unique row} {*}{
2030 set stmt [::db prepare {
2031 UPDATE people SET info = 1 WHERE name = 'fred'
2035 set rs [$stmt execute]
2045 test tdbc::mysql-11.3 {update - multiple rows} {*}{
2047 set stmt [::db prepare {
2048 UPDATE people SET info = 1 WHERE name LIKE 'b%'
2052 set rs [$stmt execute]
2062 test tdbc::mysql-12.1 {delete - no rows} {*}{
2064 set stmt [::db prepare {
2065 DELETE FROM people WHERE name = 'nobody'
2069 set rs [$stmt execute]
2079 test tdbc::mysql-12.2 {delete - unique row} {*}{
2081 set stmt [::db prepare {
2082 DELETE FROM people WHERE name = 'fred'
2086 set rs [$stmt execute]
2096 test tdbc::mysql-12.3 {delete - multiple rows} {*}{
2098 set stmt [::db prepare {
2099 DELETE FROM people WHERE name LIKE 'b%'
2103 set rs [$stmt execute]
2113 test tdbc::mysql-13.1 {resultsets - no results} {*}{
2115 set stmt [::db prepare {
2116 SELECT name FROM people WHERE idnum = $idnum
2121 [llength [$stmt resultsets]] \
2122 [llength [::db resultsets]]
2130 test tdbc::mysql-13.2 {resultsets - various statements and results} {*}{
2132 for {set i 0} {$i < 6} {incr i} {
2133 set stmts($i) [::db prepare {
2134 SELECT name FROM people WHERE idnum = :idnum
2136 $stmts($i) paramtype idnum integer
2137 for {set j 0} {$j < $i} {incr j} {
2138 set resultsets($i,$j) [$stmts($i) execute [list idnum $j]]
2140 for {set j 1} {$j < $i} {incr j 2} {
2141 $resultsets($i,$j) close
2142 unset resultsets($i,$j)
2147 set x [list [llength [::db resultsets]]]
2148 for {set i 0} {$i < 6} {incr i} {
2149 lappend x [llength [$stmts($i) resultsets]]
2154 for {set i 0} {$i < 6} {incr i} {
2158 -result {9 0 1 1 2 2 3}
2161 #-------------------------------------------------------------------------------
2163 # next tests require a fresh database connection. Close the existing one down
2166 set stmt [db prepare {
2175 tdbc::mysql::connection create ::db {*}$::connFlags
2177 set stmt [db prepare {
2178 INSERT INTO people(idnum, name, info) VALUES(:idnum, :name, NULL)
2180 $stmt paramtype idnum integer
2181 $stmt paramtype name varchar 40
2183 foreach name {fred wilma pebbles barney betty bam-bam} {
2184 set rs [$stmt execute]
2191 test tdbc::mysql-14.1 {begin transaction - wrong # args} {*}{
2193 ::db begintransaction junk
2197 -result {wrong # args*}
2200 test tdbc::mysql-14.2 {commit - wrong # args} {*}{
2206 -result {wrong # args*}
2209 test tdbc::mysql-14.3 {rollback - wrong # args} {*}{
2215 -result {wrong # args*}
2218 test tdbc::mysql-14.4 {commit - not in transaction} {*}{
2220 list [catch {::db commit} result] $result $::errorCode
2223 -result {1 {no transaction is in progress} {TDBC GENERAL_ERROR HY010 MYSQL *}}
2226 test tdbc::mysql-14.5 {rollback - not in transaction} {*}{
2228 list [catch {::db rollback} result] $result $::errorCode
2231 -result {1 {no transaction is in progress} {TDBC GENERAL_ERROR HY010 MYSQL *}}
2234 test tdbc::mysql-14.6 {empty transaction} {*}{
2236 ::db begintransaction
2242 test tdbc::mysql-14.7 {empty rolled-back transaction} {*}{
2244 ::db begintransaction
2250 test tdbc::mysql-14.8 {rollback does not change database} {*}{
2252 ::db begintransaction
2253 set stmt [::db prepare {DELETE FROM people WHERE name = 'fred'}]
2254 set rs [$stmt execute]
2255 while {[$rs nextrow trash]} {}
2259 set stmt [::db prepare {SELECT idnum FROM people WHERE name = 'fred'}]
2260 set id {changes still visible after rollback}
2261 set rs [$stmt execute]
2262 while {[$rs nextrow -as lists row]} {
2263 set id [lindex $row 0]
2271 test tdbc::mysql-14.9 {commit does change database} {*}{
2273 set stmt1 [db prepare {
2274 INSERT INTO people(idnum, name, info)
2275 VALUES(7, 'mr. gravel', 0)
2277 set stmt2 [db prepare {
2278 SELECT idnum FROM people WHERE name = 'mr. gravel'
2282 ::db begintransaction
2283 set rs [$stmt1 execute]
2286 set rs [$stmt2 execute]
2287 while {[$rs nextrow -as lists row]} {
2288 set id [lindex $row 0]
2300 test tdbc::mysql-14.10 {nested transactions} {*}{
2302 ::db begintransaction
2303 list [catch {::db begintransaction} result] $result $::errorCode
2306 catch {::db rollback}
2309 -result {1 {MySQL does not support nested transactions} {TDBC GENERAL_ERROR HYC00 MYSQL *}}
2312 #------------------------------------------------------------------------------
2314 # Clean up database again for the next round.
2317 set stmt [db prepare {
2326 tdbc::mysql::connection create ::db {*}$::connFlags
2328 # Things should be sane enough that we can find out the database version.
2329 # REFERENTIAL_CONSTRAINTS,REFERENCED_TABLE_COLUMN doesn't exist until 5.1.16,
2330 # and [db foreignkeys] depends on it.
2333 db foreach -as lists -- row {SELECT version()} {
2334 set version [lindex $row 0]
2336 tcltest::testConstraint mysqlAtLeast5116 \
2337 [expr {[regexp {^\d+\.\d+(?:\.\d+)?} $version components]
2338 && [package vcompare $components 5.1.16] >= 0}]
2341 set stmt [db prepare {
2342 INSERT INTO people(idnum, name, info) VALUES(:idnum, :name, NULL)
2344 $stmt paramtype idnum integer
2345 $stmt paramtype name varchar 40
2347 foreach name {fred wilma pebbles barney betty bam-bam} {
2348 set rs [$stmt execute]
2355 test tdbc::mysql-15.1 {successful (empty) transaction} {*}{
2364 test tdbc::mysql-15.2 {failing transaction does not get committed} {*}{
2366 set stmt1 [db prepare {
2367 DELETE FROM people WHERE name = 'fred'
2369 set stmt2 [db prepare {
2370 SELECT idnum FROM people WHERE name = 'fred'
2376 set rs [$stmt1 execute]
2378 error "abort the transaction"
2381 set id {failed transaction got committed}
2382 set rs [$stmt2 execute]
2383 while {[$rs nextrow -as lists row]} {
2384 set id [lindex $row 0]
2393 -result {{abort the transaction} 1}
2396 test tdbc::mysql-15.3 {successful transaction gets committed} {*}{
2398 set stmt1 [db prepare {
2399 INSERT INTO people(idnum, name, info)
2400 VALUES(7, 'mr. gravel', 0)
2402 set stmt2 [db prepare {
2403 SELECT idnum FROM people WHERE name = 'mr. gravel'
2408 set rs [$stmt1 execute]
2411 set rs [$stmt2 execute]
2412 while {[$rs nextrow -as lists row]} {
2413 set id [lindex $row 0]
2425 test tdbc::mysql-15.4 {break out of transaction commits it} {*}{
2427 set stmt1 [db prepare {
2428 INSERT INTO people(idnum, name, info)
2429 VALUES(8, 'gary granite', 0)
2431 set stmt2 [db prepare {
2432 SELECT idnum FROM people WHERE name = 'gary granite'
2438 set rs [$stmt1 execute]
2443 set rs [$stmt2 execute]
2444 while {[$rs nextrow -as lists row]} {
2445 set id [lindex $row 0]
2457 test tdbc::mysql-15.5 {continue in transaction commits it} {*}{
2459 set stmt1 [db prepare {
2460 INSERT INTO people(idnum, name, info)
2461 VALUES(9, 'hud rockstone', 0)
2463 set stmt2 [db prepare {
2464 SELECT idnum FROM people WHERE name = 'hud rockstone'
2468 for {set i 0} {$i < 1} {incr i} {
2470 set rs [$stmt1 execute]
2475 set rs [$stmt2 execute]
2476 while {[$rs nextrow -as lists row]} {
2477 set id [lindex $row 0]
2489 test tdbc::mysql-15.6 {return in transaction commits it} {*}{
2491 set stmt1 [db prepare {
2492 INSERT INTO people(idnum, name, info)
2493 VALUES(10, 'nelson stoneyfeller', 0)
2495 set stmt2 [db prepare {
2496 SELECT idnum FROM people WHERE name = 'nelson stoneyfeller'
2498 proc tdbcmysql-15.6 {stmt1} {
2500 set rs [$stmt1 execute]
2507 tdbcmysql-15.6 $stmt1
2508 set rs [$stmt2 execute]
2509 while {[$rs nextrow -as lists row]} {
2510 set id [lindex $row 0]
2518 rename tdbcmysql-15.6 {}
2523 test tdbc::mysql-16.1 {database tables, wrong # args} {
2525 set dict [::db tables % rubbish]
2529 -result {wrong # args*}
2532 test tdbc::mysql-16.2 {database tables - empty set} {
2539 test tdbc::mysql-16.3 {enumerate database tables} {*}{
2541 set dict [::db tables]
2542 list [dict exists $dict people] [dict exists $dict property]
2547 test tdbc::mysql-16.4 {enumerate database tables} {*}{
2549 set dict [::db tables p%]
2550 list [dict exists $dict people] [dict exists $dict property]
2555 test tdbc::mysql-17.1 {database columns - wrong # args} {*}{
2557 set dict [::db columns people % rubbish]
2561 -result {wrong # args*}
2564 test tdbc::mysql-17.2 {database columns - no such table} {*}{
2566 ::db columns rubbish
2570 -result {Table * doesn't exist}
2573 test tdbc::mysql-17.3 {database columns - no match pattern} {*}{
2576 dict for {colname attrs} [::db columns people] {
2577 lappend result $colname \
2578 [dict get $attrs type] \
2579 [expr {[dict exists $attrs precision] ?
2580 [dict get $attrs precision] : {NULL}}] \
2581 [expr {[dict exists $attrs scale] ?
2582 [dict get $attrs scale] : {NULL}}] \
2583 [dict get $attrs nullable]
2588 -result {idnum integer * 0 0 name varchar 40 * info integer * 0 1}
2591 # sqlite driver appears not to implement pattern matching for SQLGetColumns
2592 test tdbc::mysql-17.4 {database columns - match pattern} {*}{
2593 -constraints !sqlite
2596 dict for {colname attrs} [::db columns people i%] {
2597 lappend result $colname \
2598 [dict get $attrs type] \
2599 [expr {[dict exists $attrs precision] ?
2600 [dict get $attrs precision] : {NULL}}] \
2601 [expr {[dict exists $attrs scale] ?
2602 [dict get $attrs scale] : {NULL}}] \
2603 [dict get $attrs nullable]
2607 -result {idnum integer 11 0 0 info integer 11 0 1}
2610 test tdbc::mysql-18.1 {$statement params - excess arg} {*}{
2612 set s [::db prepare {
2613 SELECT name FROM people
2614 WHERE name LIKE :pattern
2617 $s paramtype minid numeric 10 0
2618 $s paramtype pattern varchar 40
2628 -result {wrong # args*}
2631 test tdbc::mysql-18.2 {$statement params - no params} {*}{
2633 set s [::db prepare {
2634 SELECT name FROM people
2646 test tdbc::mysql-18.3 {$statement params - try a few data types} {*}{
2648 set s [::db prepare {
2649 SELECT name FROM people
2650 WHERE name LIKE :pattern
2653 $s paramtype minid decimal 10 0
2654 $s paramtype pattern varchar 40
2659 [dict get $d minid direction] \
2660 [dict get $d minid type] \
2661 [dict get $d minid precision] \
2662 [dict get $d minid scale] \
2663 [dict get $d pattern direction] \
2664 [dict get $d pattern type] \
2665 [dict get $d pattern precision]
2670 -result {in decimal 10 0 in varchar 40}
2673 test tdbc::mysql-19.1 {$connection configure - no args} \
2679 -compress * -database * -encoding utf-8 \
2680 -host * -interactive * -isolation repeatableread \
2681 -password {} -port * -readonly 0 -socket * \
2682 -ssl_ca * -ssl_capath * -ssl_cert * -ssl_cipher * \
2683 -ssl_key * -timeout * -user *]
2685 test tdbc::mysql-19.2 {$connection configure - unknown arg} {*}{
2687 ::db configure -junk
2691 -result "bad option *"
2694 test tdbc::mysql-19.3 {$connection configure - unknown arg} {*}{
2696 list [catch {::db configure -rubbish} result] $result $::errorCode
2699 -result {1 {bad option "-rubbish": must be *} {TCL LOOKUP INDEX option -rubbish}}
2702 test tdbc::mysql-19.4 {$connection configure - set unknown arg} {*}{
2704 list [catch {::db configure -rubbish rubbish} result] \
2705 $result $::errorCode
2708 -result {1 {bad option "-rubbish": must be *} {TCL LOOKUP INDEX option -rubbish}}
2711 test tdbc::mysql-19.5 {$connection configure - set inappropriate arg} {*}{
2713 list [catch {::db configure -encoding ebcdic} result] \
2714 $result $::errorCode
2716 -result {1 {"-encoding" option cannot be changed dynamically} {TDBC GENERAL_ERROR HY000 MYSQL -1}}
2719 test tdbc::mysql-19.6 {$connection configure - wrong # args} {*}{
2721 ::db configure -parent . -junk
2725 -result "wrong # args*"
2728 test tdbc::mysql-19.9 {$connection configure - -encoding} {*}{
2730 ::db configure -encoding
2736 test tdbc::mysql-19.10 {$connection configure - -isolation} {*}{
2738 ::db configure -isolation junk
2742 -result {bad isolation level "junk"*}
2745 test tdbc::mysql-19.11 {$connection configure - -isolation} {*}{
2747 list [::db configure -isolation readuncommitted] \
2748 [::db configure -isolation] \
2749 [::db configure -isolation readcommitted] \
2750 [::db configure -isolation] \
2751 [::db configure -isolation serializable] \
2752 [::db configure -isolation] \
2753 [::db configure -isolation repeatableread] \
2754 [::db configure -isolation]
2756 -result {{} readuncommitted {} readcommitted {} serializable {} repeatableread}
2759 test tdbc::mysql-19.12 {$connection configure - -readonly} {*}{
2761 ::db configure -readonly junk
2764 -result {"-readonly" option cannot be changed dynamically}
2767 test tdbc::mysql-19.13 {$connection configure - -readonly} {*}{
2769 ::db configure -readonly
2774 test tdbc::mysql-19.14 {$connection configure - -timeout} {*}{
2776 ::db configure -timeout junk
2779 -result {expected integer but got "junk"}
2782 test tdbc::mysql-19.15 {$connection configure - -timeout} {*}{
2784 set x [::db configure -timeout]
2785 list [::db configure -timeout 5000] [::db configure -timeout] \
2786 [::db configure -timeout $x]
2788 -result {{} 5000 {}}
2791 test tdbc::mysql-19.16 {$connection configure - -db} {*}{
2793 set x [::db configure -db]
2794 list [::db configure -db information_schema] \
2795 [::db configure -db] \
2796 [::db configure -db $x]
2798 -result {{} information_schema {}}
2801 test tdbc::mysql-19.17 {$connection configure - -user} \
2803 set flags $::connFlags
2804 dict unset flags -host
2805 catch [dict unset flags -port]
2806 catch [dict unset flags -socket]
2808 dict set flags -db information_schema
2809 list [::db configure {*}$flags] [::db configure -db] \
2810 [::db configure {*}$flags2] [::db configure -db]
2812 -result [list {} information_schema {} [dict get $connFlags -db]]
2814 test tdbc::mysql-20.1 {bit values} {*}{
2816 catch {db allrows {DROP TABLE bittest}}
2818 CREATE TABLE bittest (
2822 db allrows {INSERT INTO bittest(bitstring) VALUES(b'11010001010110')}
2825 db allrows {SELECT bitstring FROM bittest}
2827 -result {{bitstring 13398}}
2829 db allrows {DROP TABLE bittest}
2833 test tdbc::mysql-20.2 {direct value transfers} {*}{
2835 set bigtext [string repeat a 200]
2836 set bigbinary [string repeat \xc2\xa1 100]
2837 catch {db allrows {DROP TABLE typetest}}
2839 CREATE TABLE typetest (
2845 xtimestamp1 TIMESTAMP,
2850 xdatetime1 DATETIME,
2862 xvarb1 VARBINARY(256),
2863 xvarc1 VARCHAR(256),
2868 set stmt [db prepare {
2869 INSERT INTO typetest(
2870 xtiny1, xsmall1, xint1, xfloat1,
2871 xdouble1, xtimestamp1, xbig1, xmed1,
2872 xdate1, xtime1, xdatetime1, xyear1,
2873 xbit1, xdec1, xtinyt1, xtinyb1,
2874 xmedt1, xmedb1, xlongt1, xlongb1,
2875 xtext1, xblob1, xvarb1, xvarc1,
2878 :xtiny1, :xsmall1, :xint1, :xfloat1,
2879 :xdouble1, :xtimestamp1, :xbig1, :xmed1,
2880 :xdate1, :xtime1, :xdatetime1, :xyear1,
2881 :xbit1, :xdec1, :xtinyt1, :xtinyb1,
2882 :xmedt1, :xmedb1, :xlongt1, :xlongb1,
2883 :xtext1, :xblob1, :xvarb1, :xvarc1,
2887 $stmt paramtype xtiny1 tinyint
2888 $stmt paramtype xsmall1 smallint
2889 $stmt paramtype xint1 integer
2890 $stmt paramtype xfloat1 float
2891 $stmt paramtype xdouble1 double
2892 $stmt paramtype xtimestamp1 timestamp
2893 $stmt paramtype xbig1 bigint
2894 $stmt paramtype xmed1 mediumint
2895 $stmt paramtype xdate1 date
2896 $stmt paramtype xtime1 time
2897 $stmt paramtype xdatetime1 datetime
2898 $stmt paramtype xyear1 year
2899 $stmt paramtype xbit1 bit 14
2900 $stmt paramtype xdec1 decimal 10 0
2901 $stmt paramtype xtinyt1 tinytext
2902 $stmt paramtype xtinyb1 tinyblob
2903 $stmt paramtype xmedt1 mediumtext
2904 $stmt paramtype xmedb1 mediumblob
2905 $stmt paramtype xlongt1 longtext
2906 $stmt paramtype xlongb1 longblob
2907 $stmt paramtype xtext1 text
2908 $stmt paramtype xblob1 blob
2909 $stmt paramtype xvarb1 varbinary
2910 $stmt paramtype xvarc1 varchar
2911 $stmt paramtype xbin1 binary 20
2912 $stmt paramtype xchar1 char 20
2921 set xtimestamp1 {2001-02-03 04:05:06}
2924 set xdate1 2001-02-03
2926 set xdatetime1 {2001-02-03 04:05:06}
2928 set xbit1 0b11010001010110
2930 set xtinyt1 $bigtext
2931 set xtinyb1 $bigbinary
2933 set xmedb1 $bigbinary
2934 set xlongt1 $bigtext
2935 set xlongb1 $bigbinary
2937 set xblob1 $bigbinary
2938 set xvarb1 $bigbinary
2940 set xbin1 [string repeat \xc2\xa1 10]
2941 set xchar1 [string repeat a 20]
2943 db foreach row {select * from typetest} {
2945 xtiny1 xsmall1 xint1 xfloat1
2946 xdouble1 xtimestamp1 xbig1 xmed1
2947 xdate1 xtime1 xdatetime1 xyear1
2948 xbit1 xdec1 xtinyt1 xtinyb1
2949 xmedt1 xmedb1 xlongt1 xlongb1
2950 xtext1 xblob1 xvarb1 xvarc1
2953 if {![dict exists $row $v]} {
2954 append trouble $v " did not appear in result set\n"
2955 } elseif {[set $v] != [dict get $row $v]} {
2956 append trouble [list $v is [dict get $row $v] \
2957 should be [set $v]] \n
2972 test tdbc::mysql-21.2 {transfers of binary data} {*}{
2975 db allrows {DROP TABLE bintest}
2978 CREATE TABLE bintest (
2979 xint1 INTEGER PRIMARY KEY,
2983 set stmt1 [db prepare {
2984 INSERT INTO bintest (xint1, xbin)
2987 $stmt1 paramtype i1 integer
2988 $stmt1 paramtype b1 varbinary 256
2989 set stmt2 [db prepare {
2990 SELECT xbin FROM bintest WHERE xint1 = :i1
2992 $stmt2 paramtype i1 integer
2996 for {set i 0} {$i < 256} {incr i} {
2999 set b1 [binary format c* $listdata]
3002 $stmt2 foreach -as lists row { set b2 [lindex $row 0] }
3003 list [string length $b2] [string compare $b1 $b2]
3009 db allrows {DROP TABLE bintest}
3013 test tdbc::mysql-22.1 {duplicate column name} {*}{
3015 set stmt [::db prepare {
3016 SELECT a.idnum, b.idnum
3017 FROM people a, people b
3018 WHERE a.name = 'hud rockstone'
3021 set rs [$stmt execute]
3024 -result {idnum idnum#2}
3031 # Information schema tests require additional tables in the database.
3034 catch {::db allrows {DROP TABLE d}}
3035 catch {::db allrows {DROP TABLE c}}
3036 catch {::db allrows {DROP TABLE b}}
3037 catch {::db allrows {DROP TABLE a}}
3039 # The MyISAM engine doesn't track foreign key constraints, so force the
3040 # tables to be InnoDB.
3045 CONSTRAINT pk_a PRIMARY KEY(k1)
3053 CONSTRAINT pk_b PRIMARY KEY(k1, k2),
3054 CONSTRAINT fk_b1 FOREIGN KEY (k1) REFERENCES a(k1),
3055 CONSTRAINT fk_b2 FOREIGN KEY (k2) REFERENCES a(k1)
3063 CONSTRAINT pk_c PRIMARY KEY(p1, p2),
3064 CONSTRAINT fk_c1 FOREIGN KEY (p1) REFERENCES a(k1),
3065 CONSTRAINT fk_c2 FOREIGN KEY (p2) REFERENCES a(k1),
3066 CONSTRAINT fk_cpair FOREIGN KEY (p1,p2) REFERENCES b(k2,k1)
3076 test tdbc::mysql-23.1 {Primary keys - no arg} {*}{
3082 -result {wrong # args*}
3084 test tdbc::mysql-23.2 {Primary keys - no primary key} {*}{
3090 test tdbc::mysql-23.3 {Primary keys - simple primary key} {*}{
3093 foreach row [::db primarykeys a] {
3094 lappend result [dict get $row columnName] [dict get $row ordinalPosition]
3100 test tdbc::mysql-23.4 {Primary keys - compound primary key} {*}{
3103 foreach row [::db primarykeys b] {
3104 lappend result [dict get $row columnName] [dict get $row ordinalPosition]
3111 test tdbc::mysql-24.1 {Foreign keys - wrong # args} {*}{
3113 ::db foreignkeys -wrong
3117 -result {wrong # args*}
3120 test tdbc::mysql-24.2 {Foreign keys - bad arg} {*}{
3122 ::db foreignkeys -primary a -rubbish b
3126 -result {bad option "-rubbish"*}
3129 test tdbc::mysql-24.3 {Foreign keys - redundant arg} {*}{
3131 ::db foreignkeys -primary a -primary b
3135 -result {duplicate option "primary"*}
3138 test tdbc::mysql-24.4 {Foreign keys - list all} \
3139 -constraints mysqlAtLeast5116 \
3142 set wanted {fk_b1 {} fk_b2 {} fk_c1 {} fk_c2 {} fk_cpair {}}
3143 foreach row [::db foreignkeys] {
3144 if {[dict exists $wanted [dict get $row foreignConstraintName]]} {
3145 dict set result [dict get $row foreignConstraintName] \
3146 [dict get $row ordinalPosition] \
3147 [list [dict get $row foreignTable] \
3148 [dict get $row foreignColumn] \
3149 [dict get $row primaryTable] \
3150 [dict get $row primaryColumn]]
3153 lsort -index 0 -stride 2 $result
3156 fk_b1 {1 {b k1 a k1}} \
3157 fk_b2 {1 {b k2 a k1}} \
3158 fk_c1 {1 {c p1 a k1}} \
3159 fk_c2 {1 {c p2 a k1}} \
3160 fk_cpair {1 {c p1 b k2} 2 {c p2 b k1}}]
3162 test tdbc::mysql-24.5 {Foreign keys - -foreign} \
3163 -constraints mysqlAtLeast5116 \
3166 set wanted {fk_b1 {} fk_b2 {} fk_c1 {} fk_c2 {} fk_cpair {}}
3167 foreach row [::db foreignkeys -foreign c] {
3168 if {[dict exists $wanted [dict get $row foreignConstraintName]]} {
3169 dict set result [dict get $row foreignConstraintName] \
3170 [dict get $row ordinalPosition] \
3171 [list [dict get $row foreignTable] \
3172 [dict get $row foreignColumn] \
3173 [dict get $row primaryTable] \
3174 [dict get $row primaryColumn]]
3177 lsort -index 0 -stride 2 $result
3180 fk_c1 {1 {c p1 a k1}} \
3181 fk_c2 {1 {c p2 a k1}} \
3182 fk_cpair {1 {c p1 b k2} 2 {c p2 b k1}}]
3184 test tdbc::mysql-24.6 {Foreign keys - -primary} \
3185 -constraints mysqlAtLeast5116 \
3188 set wanted {fk_b1 {} fk_b2 {} fk_c1 {} fk_c2 {} fk_cpair {}}
3189 foreach row [::db foreignkeys -primary a] {
3190 if {[dict exists $wanted [dict get $row foreignConstraintName]]} {
3191 dict set result [dict get $row foreignConstraintName] \
3192 [dict get $row ordinalPosition] \
3193 [list [dict get $row foreignTable] \
3194 [dict get $row foreignColumn] \
3195 [dict get $row primaryTable] \
3196 [dict get $row primaryColumn]]
3199 lsort -index 0 -stride 2 $result
3202 fk_b1 {1 {b k1 a k1}} \
3203 fk_b2 {1 {b k2 a k1}} \
3204 fk_c1 {1 {c p1 a k1}} \
3205 fk_c2 {1 {c p2 a k1}}]
3207 test tdbc::mysql-24.7 {Foreign keys - -foreign and -primary} \
3208 -constraints mysqlAtLeast5116 \
3211 set wanted {fk_b1 {} fk_b2 {} fk_c1 {} fk_c2 {} fk_cpair {}}
3212 foreach row [::db foreignkeys -foreign c -primary b] {
3213 if {[dict exists $wanted [dict get $row foreignConstraintName]]} {
3214 dict set result [dict get $row foreignConstraintName] \
3215 [dict get $row ordinalPosition] \
3216 [list [dict get $row foreignTable] \
3217 [dict get $row foreignColumn] \
3218 [dict get $row primaryTable] \
3219 [dict get $row primaryColumn]]
3222 lsort -index 0 -stride 2 $result
3224 -result [list fk_cpair {1 {c p1 b k2} 2 {c p2 b k1}}]
3226 # MySQL doesn't "do nothing gracefully", so the empty statement yields an
3227 # error rather than an empty result set.
3228 test tdbc::mysql-30.0 {Multiple result sets} {*}{
3229 -constraints knownBug
3231 set stmt [::db prepare { }]
3234 set resultset [$stmt execute {}]
3240 -result {empty query}
3243 test tdbc::mysql-30.1 {Multiple result sets - but in reality only one} {*}{
3245 ::db allrows {delete from people}
3246 set stmt [db prepare {
3247 INSERT INTO people(idnum, name, info) VALUES(:idnum, :name, NULL)
3249 $stmt paramtype idnum integer
3250 $stmt paramtype name varchar 40
3252 foreach name {fred wilma pebbles barney betty bam-bam} {
3253 set rs [$stmt execute]
3260 set stmt [::db prepare {
3261 select idnum, name from people where name = :a
3264 set resultset [$stmt execute {a wilma}]
3269 while {[$resultset nextrow row]} {
3272 lappend rowsets $rows
3273 if {[$resultset nextresults] == 0} break
3277 rename $resultset {}
3283 -result {{{idnum 2 name wilma}}}
3287 #-------------------------------------------------------------------------------
3289 # Test cleanup. Drop tables and get rid of the test database.
3292 catch {::db allrows {DROP TABLE d}}
3293 catch {::db allrows {DROP TABLE c}}
3294 catch {::db allrows {DROP TABLE b}}
3295 catch {::db allrows {DROP TABLE a}}
3296 catch {::db allrows {DROP TABLE people}}
3298 catch {rename ::db {}}