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 ::mysql::use handle database
208 Associate a connected handle with a particular database. handle
209 must be a valid handle previously obtained from ::mysql::con‐
210 nect. mysql::use raises a Tcl error if the handle is not valid
211 or if the database name specified could not be used.
212 Consider you can use mysqltcl without to specify the database,
214 in this case you must use explizit schema notation to specify
215 the table in sql.
216 ::mysql::sel $handle {select * from uni.student}
217 with option connection -noschema you can prohibit such syntax.
218 ::mysql::sel handle sql-statement ?-list|-flatlist?
220 Send sql-statement to the server.
221 If sql-statement is a SELECT statement and no -list or -flatlist
223 option is specified, the command returns the number of rows
224 returned as the result of the query. The rows can be obtained
225 by the ::mysql::fetch and/or the ::mysql::map commands. The
226 resulting rows are called the pending result.
227 If sql-statement is a SELECT statement and -list or -flatlist is
229 specified, the command returns the full list of rows returned as
230 the result of the query in one of two possible formats:
231 -list generates a list of lists, in which each element is a row
233 of the result.
234 -flatlist
236 generates the concatenation of all rows in a single list,
237 which is useful for scanning with a single foreach.
238 Example:
239 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -list
240 {1 Joe} {2 Phil} {3 John}
241 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -flatlist
242 {1 Joe 2 Phil 3 John}
243 Note that both list syntaxes are faster than something like
244 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS"
245 % ::mysql::map $db {id name} {lappend result $id $name}
246 % set $result
247 {1 Joe 2 Phil 3 John}
248 If sql-statement is a valid MySQL statement, but not a SELECT state‐
249 ment, the command returns -1 after executing the statement, or an empty
250 string if -list or -flatlist is specified. There is no pending result
251 in this case.
252 In any case ::mysql::sel implicitly cancels any previous result still
254 pending for the handle.
255 ::mysql::fetch handle
257 Returns the next row from result set as Tcl list. mysql::fetch
258 raises a Tcl error if there is no pending result for handle.
259 mysql::fetch was former named mysqlnext.
260 ::mysql::exec handle sql-statement
262 Send sql-statement, a MySQL non-SELECT statement, to the server.
263 The handle must be in use (through ::mysql::connect and
264 ::mysql::use).
265 ::mysql::exec implicitly cancels any previous result pending for
267 the handle.
268 If sql-statement is a valid MySQL SELECT statement, the state‐
270 ment is executed, but the result is discarded. No Tcl error is
271 generated. This amounts to a (potentially costly) no-op. Use
272 the ::mysql::sel command for SELECT statements.
273 ::mysql::exec returns the number of affected rows (DELETE,
275 UPDATE). In case of multiple statement ::mysql::exec returns a
276 list of number of affected rows.
277 ::mysql::query handle sql-select-statement
280 Send sql-select-statement to the server.
281 mysql::query allow to send multiple nested queries on one handle
283 (without need to build new handle or caching results).
284 mysql::query return a query handle that can be used as handle in
285 commands as (mysql::fetch, ::mysql::map, mysql::seek,
286 mysql::col, mysql::result). After result proceeding all query
287 must be freed with ::mysql::endquery query-hanlde command.
288 Example:
290 set query1 [::mysql::query $db {SELECT ID, NAME FROM FRIENDS}\]
291 while {[set row [::mysql::fetch $query1]]!=""} {
292 set id [lindex $row 0]
293 set query2 [::mysql::query $db "SELECT ADDRESS FROM ADDRESS WHERE FRIENDID=$ID"]
294 ::mysql::map $query2 address { puts "address = $address" }
295 ::mysql::endquery $query2
296 }
297 ::mysql::endquery $query1
298 In most cases one should use sql-joins and avoid nested queries.
299 SQL-sever can optimize such queries. But in some applications
300 (GUI-Forms) where the results are used long time the inner query
301 is not known before.
302 ::mysql::endquery query-handle
304 free result memory after ::mysql::query command. You must
305 invoke ::mysql::endquery after each mysqlquery to not cause mem‐
306 ory leaks. See mysqlquery command.
307 Using ::mysql::endquery on db-handle will free also memory
309 (pending result) after ::mysql::sel command.
310 ::mysql::map handle binding-list script
313 Iterate a script over the rows of the pending result.
314 ::mysql::map may consume all rows or only some of the rows of
315 the pending result. Any remaining rows may be obtained by fur‐
316 ther ::mysql::fetch or ::mysql::map commands.
317 handle must be a handle with a pending result from a previous
319 ::mysql::sel command. binding-list must be a list of one or
320 more variable names. script must be a Tcl script. It may be
321 empty, but usually it contains one or more commands.
322 ::mysql::map processes one row at a time from the pending
324 result. For each row the column values are bound to the vari‐
325 ables in the binding list, then the script is executed. Binding
326 is strictly positional. The first variable in the binding list
327 is bound to the first column of the row, and so on. The vari‐
328 ables are created in the current context (if they do not already
329 exist). A variable name begining with a hyphen is not bound; it
330 serves as a placeholder in the binding list. If there are more
331 columns than variables the extra columns are ignored.
332 The ::mysql::map command is similar to an ordinary foreach. A
334 foreach iterates over the elements of a list, ::mysql::map iter‐
335 ates over the rows of a pending result. In both cases iteration
336 is affected by break and continue Tcl commands. The binding
337 list variables retain their last values after the command has
338 completed.
339 A simple example follows. Assume $db is a handle in use.
341 ::mysql::sel $db {
342 select lname, fname, area, phone from friends order by lname, fname
343 }
344 ::mysql::map $db {ln fn - phone} {
345 if {$phone == {}} continue
346 puts [format "%16s %-8s %s" $ln $fn $phone]
347 }
348 The ::mysql::sel command gets and sorts all rows from table
349 friends. The ::mysql::map command is used to format and print
350 the result in a way suitable for a phone list. For demonstra‐
351 tion purposes one of the columns (area) is not used. The script
352 begins by skipping over rows which have no phone number. The
353 second command in the script formats and prints values from the
354 row.
355 ::mysql::map raises a Tcl error if there is no pending result
357 for handle, or if binding-list contains more variables than
358 there are columns in the pending result.
359 ::mysql::receive handle sql-statment binding-list script
362 This command works the same way as the command mysqtclmap but it
363 do not need leading ::mysql::sel command. The main difference
364 is internal using of MySQL client library. This command use
365 mysql_use_result from C-API that do not store result on client
366 but try to receive the rows directly from server. There is also
367 no client cache. This command can be faster as using of
368 ::mysql::sel and by very big resultset will not overload client
369 machine. The scipt should process the result immadiatly because
370 it can block table (or tables) for another clients. If perfor‐
371 mance matter please test all alternatives separatly. You must
372 consider two aspects: memory consumption and performance.
373 ::mysql::seek handle row-index
375 Moves the current position among the rows in the pending result.
376 This may cause ::mysql::fetch and ::mysql::map to re-read rows,
377 or to skip over rows.
378 Row index 0 is the position just before the first row in the
380 pending result; row index 1 is the position just before the sec‐
381 ond row, and so on. You may specify a negative row index. Row
382 index -1 is the position just before the last row; row index -2
383 is the position just before the second last row, and so on. An
384 out-of-bounds row index will cause ::mysql::seek to set the new
385 current position either just before the first row (if the index
386 is too negative), or just after the last row (if the index
387 exceeds the number of rows). This is not an error condition.
388 ::mysql::seek returns the number of rows that can be read
390 sequentially from the new current position. ::mysql::seek
391 raises a Tcl error if there is no pending result for handle.
392 Portability note: The functionality of ::mysql::seek is fre‐
394 quently absent in other Tcl extensions for SQL. That is because
395 MySQL C-API client library ofers own result set caching func‐
396 tionality that lacks another SQL-APIs. That increase the per‐
397 formance because all rows are received at once and the query
398 does not block the server for another clienst , on the other
399 hand you works on the cached data can use a lot of memory and
400 are up to date only in the moment of query but not fetch.
401 ::mysql::col handle table-name option
404 ::mysql::col handle table-name optionkist
406 ::mysql::col handle ?option...?
408 Return information about the columns of a table. handle must be
409 in use. table-name must be the name of a table; it may be a ta‐
410 ble name or -current if there is a pending result. One or more
411 options control what information to return. Each option must be
412 one of the following keywords.
413 name Return the name of a column.
415 type Return the type of a column; one of the strings decimal,
417 tiny, short, long, float, double, null, timestamp, long
418 long, int24, date, time, date time, year, new date, enum,
419 set, tiny blob, medium blob, long blob, blob, var string,
420 or string. Note that a column of type char will return
421 tiny, while they are represented equally.
422 length Return the length of a column in bytes.
424 table Return the name of the table in which this column occurs.
426 non_null Return the string "1" if the column is non-null; other‐
428 wise "0".
429 prim_key Return the string "1" if the column is part of the pri‐
431 mary key;
432 otherwise "0".
433 numeric Return the string "1" if the column is numeric; other‐
435 wise "0".
436 decimals Return the string "1" if the column is non-null; other‐
438 wise "0".
439 The three forms of this command generate their result in a particular
440 way.
441 [1] If a single option is present the result is a simple list
443 of values; one for each column.
444 [2] If the options are given in the form of an option list
446 the result is a list of lists. Each sublist corresponds
447 to a column and contains the information specified by the
448 options.
449 [3] If several options are given, but not in a list, the
451 result is also a list of lists. In this case each sub‐
452 list corresponds to an option and contains one value for
453 each column.
454 The following is a sample interactive session containing all forms of
455 the ::mysql::col command and their results. The last command uses the
456 -current option. It could alternatively specify the table name explic‐
457 itly.
458 %::mysql::col $db friends name
459 name lname area phone
460 % ::mysql::col $db friends {name type length}
461 {fname char 12} {lname char 20} {area char 5} {phone char 12}
462 % ::mysql::sel $db {select * from friends}
463 % ::mysql::col $db -current name type length
464 {fname lname area phone} {char char char char} {12 20 5 12}]
465 ::mysql::info handle option
467 Return various database information depending on the option.
468 The option must be one of the following keywords.
469 info Return a String with information about last operation.
471 "Records: 3 Duplicates: 0 Warnings: 0" by INSERT or "Rows
472 matched: 40 Changed: 40 Warnings: 0" by UPDATE statements
473 (read the manual for mysql_info in MySQL C API documenta‐
474 tion)
475 databases
477 Return a list of all database names known to the server.
478 The handle must be connected.
479 dbname Return the name of the database with which the handle is
481 associated. The handle must be in use.
482 dbname?
484 Return the name of the database with which the handle is
485 associated; an empty string if the handle is connected,
486 but not in use.
487 host Return the name of the host to which the handle is con‐
489 nected. The handle must be connected.
490 host Return the name of the host to which the handle is con‐
492 nected; an empty string if the handle is not valid.
493 tables Return a list of all table names in the database with
495 which the handle is associated. The handle must be in
496 use.
497 serverversion
499 Returns the version number of the server as a string.
500 serverversionid
502 Returns the version number of the server as an integer.
503 sqlstate
505 Returns a string containing the SQLSTATE error code for
506 the last error. The error code consists of five charac‐
507 ters. '00000' means ``no error.'' The values are speci‐
508 fied by ANSI SQL and ODBC. Note that not all MySQL
509 errors are yet mapped to SQLSTATE's. The value 'HY000'
510 (general error) is used for unmapped errors.
511 state Returns a character string containing information similar
513 to that provided by the mysqladmin status command. This
514 includes uptime in seconds and the number of running
515 threads, questions, reloads, and open tables.
516 ::mysql::baseinfo option
518 return information information that do not need handle.
519 connectparameters
521 return all supported connecting options
522 clientversion
524 return the version of underlying MYSQL C-API library
525 ::mysql::ping handle
527 Checks whether the connection to the server is working. If it
528 has gone down, an automatic reconnection is attempted.
529 This function can be used by clients that remain idle for a long
531 while, to check whether the server has closed the connection and
532 reconnect if necessary.
533 Return True if server is alive
535 ::mysql::changeuser user password ?database?
537 Changes the user and causes the database specified by database
538 to become the default (current) database on the connection spec‐
539 ified by MySQL. In subsequent queries, this database is the
540 default for table references that do not include an explicit
541 database specifier.
542 ::mysql::changeuser fails unless the connected user can be
544 authenticated or if he doesn't have permission to use the data‐
545 base. In this case the user and database are not changed
546 if database parameter may be set were is no default database.
548 Cause Error if operation is not succesed
550 ::mysql::result handle option
552 Return information about the pending result. Note that a result
553 is pending until canceled by a ::mysql::exec command, even if no
554 rows remain to be read. Option must be one of the following
555 keywords.
556 cols Return the number of columns in the pending result.
558 There must be a pending result.
559 cols Return the number of columns in the pending result; an
561 empty string if no result is pending.
562 current
564 Return the current position in the pending result; a non-
565 negative integer. This value can be used as row-index in
566 the ::mysql::seek command. An error is raised if there
567 is no pending result.
568 current?
570 As above, but returns an empty string if there is no
571 pending result.
572 rows Return the number of rows that can be read sequentially
574 from the current position in the pending result. There
575 must be a pending result.
576 rows Return the number of rows that can be read sequentially
578 from the current position in the pending result; an empty
579 string if no result is pending.
580 [::mysql::result $db current] + [::mysql::result $db
582 rows] always equals the total number of rows in the pend‐
583 ing result.
584 ::mysql::state handle ?-numeric?
586 Return the state of a handle as a string or in numeric form.
587 There is no requirement on handle; it may be any string. The
588 return value is one of the following strings, or the correspond‐
589 ing numeric value if -numeric is specified. The states form a
590 progression where each state builds on the previous.
591 NOT_A_HANDLE (0)
593 The string supplied for handle is not a mysqltcl handle
594 at all.
595 UNCONNECTED (1)
597 The string supplied for handle is one of the possible
598 mysqltcl handles, but it is not valid to any server.
599 CONNECTED (2)
601 The handle is connected to a server, but not associated
602 with a database.
603 IN_USE (3)
605 The handle is connected and associated with a database,
606 but there is no pending result.
607 RESULT_PENDING (4)
609 The handle is connected, associated with a database, and
610 there is a pending result.
611 ::mysql::close ?handle?
613 Closes the server connection associated with handle, causing it
614 to go back to the unconnected state. Closes all connections if
615 handle is omitted. Returns an empty string. ::mysql::close
616 raises a Tcl error if a handle is specified which is not valid.
617 ::mysql::insertid handle
619 Returns the auto increment id of the last INSERT statement.
620 ::mysql::escape ?handle? string
622 Returns the content of string, with all special characters
623 escaped, so that it is suitable for use in an SQL statement.
624 This is simpler (faster) than using a general regexp or string
625 map. If handle is specified C-API function
626 mysql_real_escape_string is used. This is the recommended usage
627 because in this case current character set is respected.
628 ::mysql::autocommit handle boolean
630 Sets autocommit mode on if mode is 1, off if mode is 0.
631 ::mysql::commit handle
633 Commits the current transaction.
634 ::mysql::rollback handle
636 Rollback the current transaction.
637 ::mysql::nextresult handle
639 If more query results exist, mysql::nextresult() reads the next
640 query results and returns the status back to application.
641 returns -1 if no result or number of rows in the result set.
642 ::mysql::moreresult handle
644 Returns true if more results exist from the currently executed
645 query, and the application must call mysql::result to fetch the
646 results.
647 ::mysql::warningcount handle
649 Returns the number of warnings generated during execution of the
650 previous SQL statement.
651 ::mysql::isnull value
653 Null handling is a known problem with Tcl, especially with DB
654 interaction. The mysql "varchar" type has two valid blank val‐
655 ues, NULL and an empty string. This is where the problem arises;
656 Tcl is not able to differentiate between the two because of the
657 way it handles strings. Mysql has new internal Tcl type for
658 null that string representation is stored in global array
659 mysqltcl(nullvalue) and as default empty string. mysql::isnull
660 can be used for safe check for null value. Warning
661 mysql::isnull works only reliable if there are no type conversa‐
662 tion on returned rows. Consider row is always Tcl list even
663 when there are only one column in the row.
664 set row [::mysql::next $handle]
665 if {[mysql::isnull [lindex $row 1]]} {
666 puts "2. column of $row is null"
667 }
668 if {[mysql::isnull $row]} {
669 puts "this does not work, because of type conversation list to string"
670 }
671 ::mysql::newnull
673 create new null object. It can be used to simulate returned row
674 contents.
675 ::mysql::setserveroption handle option
677 there are only 2 options now: -multi_statment_on and
678 -multi_statment_off
679 ::mysql::shutdown handle
681 Asks the database server to shut down. The connected user must
682 have SHUTDOWN privileges.
683 ::mysql::encoding handle ?encoding?
685 Ask or change a encoding of connection. There are special
686 encoding "binary" for binary data transfers.
687
689 Mysqltcl creates and maintains a Tcl global array to provide status
690 information. Its name is mysqlstatus.
691 Mysqlstatus elements:
693 code A numeric conflict code set after every mysqltcl command. Zero
695 means no conflict; non-zero means some kind of conflict. All
696 conflicts also generate a Tcl error.
697 All MySQL server conflicts set mysqlstatus(code) to the numeric
699 code of the MySQL error.
700 Any other conflict sets mysqlstatus(code) to -1.
702 command
704 The last failing mysqltcl command. Not updated for successful
705 commands.
706 message
708 Message string for the last conflict detected. The same string
709 is returned as the result of the failing mysqltcl command. Not
710 updated for successful commands.
711 nullvalue
713 The string to use in query results to represent the SQL null
714 value. The empty string is used initially. You may set it to
715 another value.
716
718 Up from version 3.0 all mysql commands are declared in ::mysql names‐
719 pace. All names for example mysqlconnect are also aviable but depre‐
720 cated. All old commands have the name pattern mysql{name} and the most
721 of them are now mysql::{name}. The exception is mysqlnext, which was
722 renamed to mysql::fetch.
723
725 Deleting any of the mysqltcl commands closes all connections.
726
728 · Tobias Ritzau
729 · Paolo Brutti
731 · Artur Trzewik (mail@xdobry.de) - active maintainer MySQLTcl is
733 derived from a patch of msql by Hakan Soderstrom, Soderstrom
734 Programvaruverkstad, S-12242 Enskede, Sweden. msql is derived
735 from Sybtcl by Tom Poindexter. There are many contributors and
736 bug reporter that are not mentioned. If you have contributed to
737 mysqltcl and wants to be on the list contact Artur Trzewik.
738 3.0 mysqltcl(n)