1mysqltcl(n) mysqltcl(n)
2______________________________________________________________________________
6
8 mysqltcl - MySQL server access commands for Tcl
9
11 package require Tcl 8.4
12 package require mysqltcl 3.0
14 ::mysql::connect ?option value...?
16 ::mysql::use handle database
18 ::mysql::sel handle sql-statement ?-list|-flatlist?
20 ::mysql::fetch handle
22 ::mysql::exec handle sql-statement
24 ::mysql::query handle sql-select-statement
26 ::mysql::endquery query-handle
28 ::mysql::map handle binding-list script
30 ::mysql::receive handle sql-statment binding-list script
32 ::mysql::seek handle row-index
34 ::mysql::col handle table-name option
36 ::mysql::col handle table-name optionkist
38 ::mysql::col handle ?option...?
40 ::mysql::info handle option
42 ::mysql::baseinfo option
44 ::mysql::ping handle
46 ::mysql::changeuser user password ?database?
48 ::mysql::result handle option
50 ::mysql::state handle ?-numeric?
52 ::mysql::close ?handle?
54 ::mysql::insertid handle
56 ::mysql::escape ?handle? string
58 ::mysql::autocommit handle boolean
60 ::mysql::commit handle
62 ::mysql::rollback handle
64 ::mysql::nextresult handle
66 ::mysql::moreresult handle
68 ::mysql::warningcount handle
70 ::mysql::isnull value
72 ::mysql::newnull
74 ::mysql::setserveroption handle option
76 ::mysql::shutdown handle
78 ::mysql::encoding handle ?encoding?
80_________________________________________________________________
82
84 MySQLTcl is a collection of Tcl commands and a Tcl global array that
85 provide access to MySQL database servers.
86 MySQLTcl is nothing more than a patched version of a patched version of
88 Hakan Soderstrom's patch of Tom Poindexter's Sybtcl.
89 Mysqltcl is binary Tcl library (extension) written in C language that
91 use direkt official MySQL C-API. Almost all Tcl commands correspond to
92 MySQL C-API functions. For detail documentation see official MySQL C-
93 API manual.
94
96 ::mysql::connect ?option value...?
97 Connect to a MySQL server. A handle is returned which should be
98 used in other mysqltcl commands using this connection.
99 ::mysql::connect raises a Tcl error if the connection fails.
100 ::mysql::connect read first the options from my.cnf file group
101 mysqltcl. See MySQL documentation chapter "options files".
102 Possible connection options are:
103 -host hostname
105 The host on which the server is located. The local host
106 is used by default.
107 -user user
109 The user whose name is used for the connection. The cur‐
110 rent Unix user-name is used by default.
111 -password password
113 The password that must be used for the connection. If it
114 is not present, the connection is possible only for users
115 with no password on the server.
116 -db db If this option is present, db is used as current data‐
118 base, with no need for a call to mysql::use.
119 -port port
121 The port number for the TCP/IP connection, if it's dif‐
122 ferent from the default.
123 -socket socket
125 The socket or named pipe for the connection.
126 -encoding encodingname
128 The option works similar to -encoding option in fconfig‐
129 ure. It support also special encoding name binary. By
130 option -binary no converting will be done be reading or
131 writing to/from MySQL. If option is not set the system
132 encoding (see utf-8) is used. Please test all input and
133 outputs with another program to check that all is the way
134 you expect it. If option binary is not used the system
135 procedures Tcl_ExternalToUtfDString (writing) and
136 Tcl_ExternalToUtf (reading) will be used by option binary
137 the function Tcl_GetByteArrayFromObj and Tcl_NewByteAr‐
138 rayObj are used. If you want to manipulate binary date
139 use -encoding binary. By handling textes set your special
140 encoding that you want to use in your database. Consider
141 what another system access the database and what encoding
142 they expect. It can useful to use -encoding utf-8. That
143 is standard encoding in some linux distributions and
144 newer systems.
145 -compress boolean
147 Use compression protocol. Default is false
148 -odbc boolean
150 The client is an ODBC client. This changes mysqld to be
151 more ODBC-friendly. Default is false
152 -noschema boolean
154 Don't allow the db_name.tbl_name.col_name syntax. This is
155 for ODBC. It causes the parser to generate an error if
156 you use that syntax, which is useful for trapping bugs in
157 some ODBC programs. This changes mysqld to be more ODBC-
158 friendly. Default is false
159 -multistatement boolean
161 Tell the server that the client may send multiple-row-
162 queries (separated by `;'). If this flag is not set,
163 multiple-row-queries are disabled. Default is false.
164 -multiresult boolean
166 Tell the server that the client can handle multiple-
167 result sets from multi-queries or stored procedures.
168 This is automatically set if CLIENT_MULTI_STATEMENTS is
169 set.
170 -localfiles boolean
172 Enable LOAD DATA LOCAL handling. Default is false.
173 -foundrows boolean
175 Return the number of found (matched) rows, not the number
176 of affected rows. Default is false.
177 -interactive boolean
179 Allow interactive_timeout seconds (instead of wait_time‐
180 out seconds) of inactivity before closing the connection.
181 The client's session wait_timeout variable will be set to
182 the value of the session interactive_timeout variable.
183 Default is false.
184 -ssl boolean
186 Switch to SSL after handshake. Default is false
187 -sslkey string
189 is the pathname to the key file. Used if -ssl is true
190 -sslcert string
192 is the pathname to the certificate file. Used if -ssl is
193 true
194 -sslca string
196 is the pathname to the certificate authority file. Used
197 if -ssl is true
198 -sslcapath string
200 is the pathname to a directory that contains trusted SSL
201 CA certificates in pem format. Used if -ssl is true
202 -sslcipher string
204 is a list of allowable ciphers to use for SSL encryption.
205 Used if -ssl is true
206 -reconnect boolean
208 default is false.
209 ::mysql::use handle database
211 Associate a connected handle with a particular database. handle
212 must be a valid handle previously obtained from ::mysql::con‐
213 nect. mysql::use raises a Tcl error if the handle is not valid
214 or if the database name specified could not be used.
215 Consider you can use mysqltcl without to specify the database,
217 in this case you must use explizit schema notation to specify
218 the table in sql.
219 ::mysql::sel $handle {select * from uni.student}
221 with option connection -noschema you can prohibit such syntax.
223 ::mysql::sel handle sql-statement ?-list|-flatlist?
225 Send sql-statement to the server.
226 If sql-statement is a SELECT statement and no -list or -flatlist
228 option is specified, the command returns the number of rows
229 returned as the result of the query. The rows can be obtained
230 by the ::mysql::fetch and/or the ::mysql::map commands. The
231 resulting rows are called the pending result.
232 If sql-statement is a SELECT statement and -list or -flatlist is
234 specified, the command returns the full list of rows returned as
235 the result of the query in one of two possible formats:
236 -list generates a list of lists, in which each element is a row
238 of the result.
239 -flatlist
241 generates the concatenation of all rows in a single list,
242 which is useful for scanning with a single foreach.
243 Example:
244 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -list
246 {1 Joe} {2 Phil} {3 John}
247 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -flatlist
248 {1 Joe 2 Phil 3 John}
249 Note that both list syntaxes are faster than something like
251 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS"
253 % ::mysql::map $db {id name} {lappend result $id $name}
254 % set $result
255 {1 Joe 2 Phil 3 John}
256 If sql-statement is a valid MySQL statement, but not a SELECT state‐
258 ment, the command returns -1 after executing the statement, or an empty
259 string if -list or -flatlist is specified. There is no pending result
260 in this case.
261 In any case ::mysql::sel implicitly cancels any previous result still
263 pending for the handle.
264 ::mysql::fetch handle
266 Returns the next row from result set as Tcl list. mysql::fetch
267 raises a Tcl error if there is no pending result for handle.
268 mysql::fetch was former named mysqlnext.
269 ::mysql::exec handle sql-statement
271 Send sql-statement, a MySQL non-SELECT statement, to the server.
272 The handle must be in use (through ::mysql::connect and
273 ::mysql::use).
274 ::mysql::exec implicitly cancels any previous result pending for
276 the handle.
277 If sql-statement is a valid MySQL SELECT statement, the state‐
279 ment is executed, but the result is discarded. No Tcl error is
280 generated. This amounts to a (potentially costly) no-op. Use
281 the ::mysql::sel command for SELECT statements.
282 ::mysql::exec returns the number of affected rows (DELETE,
284 UPDATE). In case of multiple statement ::mysql::exec returns a
285 list of number of affected rows.
286 ::mysql::query handle sql-select-statement
289 Send sql-select-statement to the server.
290 mysql::query allow to send multiple nested queries on one handle
292 (without need to build new handle or caching results).
293 mysql::query return a query handle that can be used as handle in
294 commands as (mysql::fetch, ::mysql::map, mysql::seek,
295 mysql::col, mysql::result). After result proceeding all query
296 must be freed with ::mysql::endquery query-hanlde command.
297 Example:
299 set query1 [::mysql::query $db {SELECT ID, NAME FROM FRIENDS}\]
301 while {[set row [::mysql::fetch $query1]]!=""} {
302 set id [lindex $row 0]
303 set query2 [::mysql::query $db "SELECT ADDRESS FROM ADDRESS WHERE FRIENDID=$ID"]
304 ::mysql::map $query2 address { puts "address = $address" }
305 ::mysql::endquery $query2
306 }
307 ::mysql::endquery $query1
308 In most cases one should use sql-joins and avoid nested queries.
310 SQL-sever can optimize such queries. But in some applications
311 (GUI-Forms) where the results are used long time the inner query
312 is not known before.
313 ::mysql::endquery query-handle
315 free result memory after ::mysql::query command. You must
316 invoke ::mysql::endquery after each mysqlquery to not cause mem‐
317 ory leaks. See mysqlquery command.
318 Using ::mysql::endquery on db-handle will free also memory
320 (pending result) after ::mysql::sel command.
321 ::mysql::map handle binding-list script
324 Iterate a script over the rows of the pending result.
325 ::mysql::map may consume all rows or only some of the rows of
326 the pending result. Any remaining rows may be obtained by fur‐
327 ther ::mysql::fetch or ::mysql::map commands.
328 handle must be a handle with a pending result from a previous
330 ::mysql::sel command. binding-list must be a list of one or
331 more variable names. script must be a Tcl script. It may be
332 empty, but usually it contains one or more commands.
333 ::mysql::map processes one row at a time from the pending
335 result. For each row the column values are bound to the vari‐
336 ables in the binding list, then the script is executed. Binding
337 is strictly positional. The first variable in the binding list
338 is bound to the first column of the row, and so on. The vari‐
339 ables are created in the current context (if they do not already
340 exist). A variable name begining with a hyphen is not bound; it
341 serves as a placeholder in the binding list. If there are more
342 columns than variables the extra columns are ignored.
343 The ::mysql::map command is similar to an ordinary foreach. A
345 foreach iterates over the elements of a list, ::mysql::map iter‐
346 ates over the rows of a pending result. In both cases iteration
347 is affected by break and continue Tcl commands. The binding
348 list variables retain their last values after the command has
349 completed.
350 A simple example follows. Assume $db is a handle in use.
352 ::mysql::sel $db {
354 select lname, fname, area, phone from friends order by lname, fname
355 }
356 ::mysql::map $db {ln fn - phone} {
357 if {$phone == {}} continue
358 puts [format "%16s %-8s %s" $ln $fn $phone]
359 }
360 The ::mysql::sel command gets and sorts all rows from table
362 friends. The ::mysql::map command is used to format and print
363 the result in a way suitable for a phone list. For demonstra‐
364 tion purposes one of the columns (area) is not used. The script
365 begins by skipping over rows which have no phone number. The
366 second command in the script formats and prints values from the
367 row.
368 ::mysql::map raises a Tcl error if there is no pending result
370 for handle, or if binding-list contains more variables than
371 there are columns in the pending result.
372 ::mysql::receive handle sql-statment binding-list script
375 This command works the same way as the command mysqtclmap but it
376 do not need leading ::mysql::sel command. The main difference
377 is internal using of MySQL client library. This command use
378 mysql_use_result from C-API that do not store result on client
379 but try to receive the rows directly from server. There is also
380 no client cache. This command can be faster as using of
381 ::mysql::sel and by very big resultset will not overload client
382 machine. The scipt should process the result immadiatly because
383 it can block table (or tables) for another clients. If perfor‐
384 mance matter please test all alternatives separatly. You must
385 consider two aspects: memory consumption and performance.
386 ::mysql::seek handle row-index
388 Moves the current position among the rows in the pending result.
389 This may cause ::mysql::fetch and ::mysql::map to re-read rows,
390 or to skip over rows.
391 Row index 0 is the position just before the first row in the
393 pending result; row index 1 is the position just before the sec‐
394 ond row, and so on. You may specify a negative row index. Row
395 index -1 is the position just before the last row; row index -2
396 is the position just before the second last row, and so on. An
397 out-of-bounds row index will cause ::mysql::seek to set the new
398 current position either just before the first row (if the index
399 is too negative), or just after the last row (if the index
400 exceeds the number of rows). This is not an error condition.
401 ::mysql::seek returns the number of rows that can be read
403 sequentially from the new current position. ::mysql::seek
404 raises a Tcl error if there is no pending result for handle.
405 Portability note: The functionality of ::mysql::seek is fre‐
407 quently absent in other Tcl extensions for SQL. That is because
408 MySQL C-API client library ofers own result set caching func‐
409 tionality that lacks another SQL-APIs. That increase the per‐
410 formance because all rows are received at once and the query
411 does not block the server for another clienst , on the other
412 hand you works on the cached data can use a lot of memory and
413 are up to date only in the moment of query but not fetch.
414 ::mysql::col handle table-name option
417 ::mysql::col handle table-name optionkist
419 ::mysql::col handle ?option...?
421 Return information about the columns of a table. handle must be
422 in use. table-name must be the name of a table; it may be a ta‐
423 ble name or -current if there is a pending result. One or more
424 options control what information to return. Each option must be
425 one of the following keywords.
426 name Return the name of a column.
428 type Return the type of a column; one of the strings decimal,
430 tiny, short, long, float, double, null, timestamp, long
431 long, int24, date, time, date time, year, new date, enum,
432 set, tiny blob, medium blob, long blob, blob, var string,
433 or string. Note that a column of type char will return
434 tiny, while they are represented equally.
435 length Return the length of a column in bytes.
437 table Return the name of the table in which this column occurs.
439 non_null Return the string "1" if the column is non-null; other‐
441 wise "0".
442 prim_key Return the string "1" if the column is part of the pri‐
444 mary key;
445 otherwise "0".
446 numeric Return the string "1" if the column is numeric; other‐
448 wise "0".
449 decimals Return the string "1" if the column is non-null; other‐
451 wise "0".
452 The three forms of this command generate their result in a particular
453 way.
454 [1] If a single option is present the result is a simple list
456 of values; one for each column.
457 [2] If the options are given in the form of an option list
459 the result is a list of lists. Each sublist corresponds
460 to a column and contains the information specified by the
461 options.
462 [3] If several options are given, but not in a list, the
464 result is also a list of lists. In this case each sub‐
465 list corresponds to an option and contains one value for
466 each column.
467 The following is a sample interactive session containing all forms of
468 the ::mysql::col command and their results. The last command uses the
469 -current option. It could alternatively specify the table name explic‐
470 itly.
471 %::mysql::col $db friends name
473 name lname area phone
474 % ::mysql::col $db friends {name type length}
475 {fname char 12} {lname char 20} {area char 5} {phone char 12}
476 % ::mysql::sel $db {select * from friends}
477 % ::mysql::col $db -current name type length
478 {fname lname area phone} {char char char char} {12 20 5 12}]
479 ::mysql::info handle option
482 Return various database information depending on the option.
483 The option must be one of the following keywords.
484 info Return a String with information about last operation.
486 "Records: 3 Duplicates: 0 Warnings: 0" by INSERT or "Rows
487 matched: 40 Changed: 40 Warnings: 0" by UPDATE statements
488 (read the manual for mysql_info in MySQL C API documenta‐
489 tion)
490 databases
492 Return a list of all database names known to the server.
493 The handle must be connected.
494 dbname Return the name of the database with which the handle is
496 associated. The handle must be in use.
497 dbname?
499 Return the name of the database with which the handle is
500 associated; an empty string if the handle is connected,
501 but not in use.
502 host Return the name of the host to which the handle is con‐
504 nected. The handle must be connected.
505 host Return the name of the host to which the handle is con‐
507 nected; an empty string if the handle is not valid.
508 tables Return a list of all table names in the database with
510 which the handle is associated. The handle must be in
511 use.
512 serverversion
514 Returns the version number of the server as a string.
515 serverversionid
517 Returns the version number of the server as an integer.
518 sqlstate
520 Returns a string containing the SQLSTATE error code for
521 the last error. The error code consists of five charac‐
522 ters. '00000' means ``no error.'' The values are speci‐
523 fied by ANSI SQL and ODBC. Note that not all MySQL
524 errors are yet mapped to SQLSTATE's. The value 'HY000'
525 (general error) is used for unmapped errors.
526 state Returns a character string containing information similar
528 to that provided by the mysqladmin status command. This
529 includes uptime in seconds and the number of running
530 threads, questions, reloads, and open tables.
531 ::mysql::baseinfo option
533 return information information that do not need handle.
534 connectparameters
536 return all supported connecting options
537 clientversion
539 return the version of underlying MYSQL C-API library
540 ::mysql::ping handle
542 Checks whether the connection to the server is working. If it
543 has gone down, an automatic reconnection is attempted.
544 This function can be used by clients that remain idle for a long
546 while, to check whether the server has closed the connection and
547 reconnect if necessary.
548 Return True if server is alive
550 ::mysql::changeuser user password ?database?
552 Changes the user and causes the database specified by database
553 to become the default (current) database on the connection spec‐
554 ified by MySQL. In subsequent queries, this database is the
555 default for table references that do not include an explicit
556 database specifier.
557 ::mysql::changeuser fails unless the connected user can be
559 authenticated or if he doesn't have permission to use the data‐
560 base. In this case the user and database are not changed
561 if database parameter may be set were is no default database.
563 Cause Error if operation is not succesed
565 ::mysql::result handle option
567 Return information about the pending result. Note that a result
568 is pending until canceled by a ::mysql::exec command, even if no
569 rows remain to be read. Option must be one of the following
570 keywords.
571 cols Return the number of columns in the pending result.
573 There must be a pending result.
574 cols Return the number of columns in the pending result; an
576 empty string if no result is pending.
577 current
579 Return the current position in the pending result; a non-
580 negative integer. This value can be used as row-index in
581 the ::mysql::seek command. An error is raised if there
582 is no pending result.
583 current?
585 As above, but returns an empty string if there is no
586 pending result.
587 rows Return the number of rows that can be read sequentially
589 from the current position in the pending result. There
590 must be a pending result.
591 rows Return the number of rows that can be read sequentially
593 from the current position in the pending result; an empty
594 string if no result is pending.
595 [::mysql::result $db current] + [::mysql::result $db
597 rows] always equals the total number of rows in the pend‐
598 ing result.
599 ::mysql::state handle ?-numeric?
601 Return the state of a handle as a string or in numeric form.
602 There is no requirement on handle; it may be any string. The
603 return value is one of the following strings, or the correspond‐
604 ing numeric value if -numeric is specified. The states form a
605 progression where each state builds on the previous.
606 NOT_A_HANDLE (0)
608 The string supplied for handle is not a mysqltcl handle
609 at all.
610 UNCONNECTED (1)
612 The string supplied for handle is one of the possible
613 mysqltcl handles, but it is not valid to any server.
614 CONNECTED (2)
616 The handle is connected to a server, but not associated
617 with a database.
618 IN_USE (3)
620 The handle is connected and associated with a database,
621 but there is no pending result.
622 RESULT_PENDING (4)
624 The handle is connected, associated with a database, and
625 there is a pending result.
626 ::mysql::close ?handle?
628 Closes the server connection associated with handle, causing it
629 to go back to the unconnected state. Closes all connections if
630 handle is omitted. Returns an empty string. ::mysql::close
631 raises a Tcl error if a handle is specified which is not valid.
632 ::mysql::insertid handle
634 Returns the auto increment id of the last INSERT statement.
635 ::mysql::escape ?handle? string
637 Returns the content of string, with all special characters
638 escaped, so that it is suitable for use in an SQL statement.
639 This is simpler (faster) than using a general regexp or string
640 map. If handle is specified C-API function
641 mysql_real_escape_string is used. This is the recommended usage
642 because in this case current character set is respected.
643 ::mysql::autocommit handle boolean
645 Sets autocommit mode on if mode is 1, off if mode is 0.
646 ::mysql::commit handle
648 Commits the current transaction.
649 ::mysql::rollback handle
651 Rollback the current transaction.
652 ::mysql::nextresult handle
654 If more query results exist, mysql::nextresult() reads the next
655 query results and returns the status back to application.
656 returns -1 if no result or number of rows in the result set.
657 ::mysql::moreresult handle
659 Returns true if more results exist from the currently executed
660 query, and the application must call mysql::result to fetch the
661 results.
662 ::mysql::warningcount handle
664 Returns the number of warnings generated during execution of the
665 previous SQL statement.
666 ::mysql::isnull value
668 Null handling is a known problem with Tcl, especially with DB
669 interaction. The mysql "varchar" type has two valid blank val‐
670 ues, NULL and an empty string. This is where the problem arises;
671 Tcl is not able to differentiate between the two because of the
672 way it handles strings. Mysql has new internal Tcl type for
673 null that string representation is stored in global array
674 mysqltcl(nullvalue) and as default empty string. mysql::isnull
675 can be used for safe check for null value. Warning
676 mysql::isnull works only reliable if there are no type conversa‐
677 tion on returned rows. Consider row is always Tcl list even
678 when there are only one column in the row.
679 set row [::mysql::next $handle]
681 if {[mysql::isnull [lindex $row 1]]} {
682 puts "2. column of $row is null"
683 }
684 if {[mysql::isnull $row]} {
685 puts "this does not work, because of type conversation list to string"
686 }
687 ::mysql::newnull
690 create new null object. It can be used to simulate returned row
691 contents.
692 ::mysql::setserveroption handle option
694 there are only 2 options now: -multi_statment_on and
695 -multi_statment_off
696 ::mysql::shutdown handle
698 Asks the database server to shut down. The connected user must
699 have SHUTDOWN privileges.
700 ::mysql::encoding handle ?encoding?
702 Ask or change a encoding of connection. There are special
703 encoding "binary" for binary data transfers.
704
706 Mysqltcl creates and maintains a Tcl global array to provide status
707 information. Its name is mysqlstatus.
708 Mysqlstatus elements:
710 code A numeric conflict code set after every mysqltcl command. Zero
712 means no conflict; non-zero means some kind of conflict. All
713 conflicts also generate a Tcl error.
714 All MySQL server conflicts set mysqlstatus(code) to the numeric
716 code of the MySQL error.
717 Any other conflict sets mysqlstatus(code) to -1.
719 command
721 The last failing mysqltcl command. Not updated for successful
722 commands.
723 message
725 Message string for the last conflict detected. The same string
726 is returned as the result of the failing mysqltcl command. Not
727 updated for successful commands.
728 nullvalue
730 The string to use in query results to represent the SQL null
731 value. The empty string is used initially. You may set it to
732 another value.
733
735 Up from version 3.0 all mysql commands are declared in ::mysql names‐
736 pace. All names for example mysqlconnect are also aviable but depre‐
737 cated. All old commands have the name pattern mysql{name} and the most
738 of them are now mysql::{name}. The exception is mysqlnext, which was
739 renamed to mysql::fetch.
740
742 Deleting any of the mysqltcl commands closes all connections.
743
745 · Tobias Ritzau
746 · Paolo Brutti
748 · Artur Trzewik (mail@xdobry.de) - active maintainer
750 MySQLTcl is derived from a patch of msql by Hakan Soderstrom, Soder‐
752 strom Programvaruverkstad, S-12242 Enskede, Sweden. msql is derived
753 from Sybtcl by Tom Poindexter. There are many contributors and bug
754 reporter that are not mentioned. If you have contributed to mysqltcl
755 and wants to be on the list contact Artur Trzewik.
756 3.0 mysqltcl(n)