NAME

mysqltcl - MySQL server access commands for Tcl

SYNOPSIS

package require Tcl 8.4

package require mysqltcl 3.0

::mysql::connect ?option value...?

::mysql::use handle database

::mysql::sel handle sql-statement ?-list|-flatlist?

::mysql::fetch handle

::mysql::exec handle sql-statement

::mysql::query handle sql-select-statement

::mysql::endquery query-handle

::mysql::map handle binding-list script

::mysql::receive handle sql-statment binding-list script

::mysql::seek handle row-index

::mysql::col handle table-name option

::mysql::col handle table-name optionkist

::mysql::col handle ?option...?

::mysql::info handle option

::mysql::baseinfo option

::mysql::ping handle

::mysql::changeuser user password ?database?

::mysql::result handle option

::mysql::state handle ?-numeric?

::mysql::close ?handle?

::mysql::insertid handle

::mysql::escape ?handle? string

::mysql::autocommit handle boolean

::mysql::commit handle

::mysql::rollback handle

::mysql::nextresult handle

::mysql::moreresult handle

::mysql::warningcount handle

::mysql::isnull value

::mysql::newnull

::mysql::setserveroption handle option

::mysql::shutdown handle

::mysql::encoding handle ?encoding?

DESCRIPTION

MySQLTcl is a collection of Tcl commands and a Tcl global array that provide access to MySQL database servers.

MySQLTcl is nothing more than a patched version of a patched version of Hakan Soderstrom's patch of Tom Poindexter's Sybtcl.

Mysqltcl is binary Tcl library (extension) written in C language that use direkt official MySQL C-API. Almost all Tcl commands correspond to MySQL C-API functions. For detail documentation see official MySQL C-API manual.

MYSQLTCL COMMANDS

::mysql::connect ?option value...?

Connect to a MySQL server. A handle is returned which should be used in other mysqltcl commands using this connection. ::mysql::connect raises a Tcl error if the connection fails. ::mysql::connect read first the options from my.cnf file group mysqltcl. See MySQL documentation chapter "options files". Possible connection options are:

::mysql::use handle database

Associate a connected handle with a particular database. handle must be a valid handle previously obtained from ::mysql::connect. mysql::use raises a Tcl error if the handle is not valid or if the database name specified could not be used.

Consider you can use mysqltcl without to specify the database, in this case you must use explizit schema notation to specify the table in sql.

::mysql::sel $handle {select * from uni.student}
    

with option connection -noschema you can prohibit such syntax.

::mysql::sel handle sql-statement ?-list|-flatlist?

Send sql-statement to the server.

If sql-statement is a SELECT statement and no -list or -flatlist option is specified, the command returns the number of rows returned as the result of the query. The rows can be obtained by the ::mysql::fetch and/or the ::mysql::map commands. The resulting rows are called the pending result.

If sql-statement is a SELECT statement and -list or -flatlist is specified, the command returns the full list of rows returned as the result of the query in one of two possible formats:

Example:

% ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -list
{1 Joe} {2 Phil} {3 John}
% ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -flatlist
{1 Joe 2 Phil 3 John}

Note that both list syntaxes are faster than something like

% ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS"
% ::mysql::map $db {id name} {lappend result $id $name}
% set $result
{1 Joe 2 Phil 3 John}

If sql-statement is a valid MySQL statement, but not a SELECT statement, the command returns -1 after executing the statement, or an empty string if -list or -flatlist is specified. There is no pending result in this case.

In any case ::mysql::sel implicitly cancels any previous result still pending for the handle.

::mysql::fetch handle

Returns the next row from result set as Tcl list. mysql::fetch raises a Tcl error if there is no pending result for handle. mysql::fetch was former named mysqlnext.

::mysql::exec handle sql-statement

Send sql-statement, a MySQL non-SELECT statement, to the server. The handle must be in use (through ::mysql::connect and ::mysql::use).

::mysql::exec implicitly cancels any previous result pending for the handle.

If sql-statement is a valid MySQL SELECT statement, the statement is executed, but the result is discarded. No Tcl error is generated. This amounts to a (potentially costly) no-op. Use the ::mysql::sel command for SELECT statements.

::mysql::exec returns the number of affected rows (DELETE, UPDATE). In case of multiple statement ::mysql::exec returns a list of number of affected rows.

::mysql::query handle sql-select-statement

Send sql-select-statement to the server.

mysql::query allow to send multiple nested queries on one handle (without need to build new handle or caching results). mysql::query return a query handle that can be used as handle in commands as (mysql::fetch, ::mysql::map, mysql::seek, mysql::col, mysql::result). After result proceeding all query must be freed with ::mysql::endquery query-hanlde command.

Example:

set query1 [::mysql::query $db {SELECT ID, NAME FROM FRIENDS}\]
while {[set row [::mysql::fetch $query1]]!=""} {


    set id [lindex $row 0]


    set query2 [::mysql::query $db "SELECT ADDRESS FROM ADDRESS WHERE FRIENDID=$ID"]


    ::mysql::map $query2 address { puts "address = $address" }


    ::mysql::endquery $query2
}
::mysql::endquery $query1
    

In most cases one should use sql-joins and avoid nested queries. SQL-sever can optimize such queries. But in some applications (GUI-Forms) where the results are used long time the inner query is not known before.

::mysql::endquery query-handle

free result memory after ::mysql::query command. You must invoke ::mysql::endquery after each mysqlquery to not cause memory leaks. See mysqlquery command.

Using ::mysql::endquery on db-handle will free also memory (pending result) after ::mysql::sel command.

::mysql::map handle binding-list script

Iterate a script over the rows of the pending result. ::mysql::map may consume all rows or only some of the rows of the pending result. Any remaining rows may be obtained by further ::mysql::fetch or ::mysql::map commands.

handle must be a handle with a pending result from a previous ::mysql::sel command. binding-list must be a list of one or more variable names. script must be a Tcl script. It may be empty, but usually it contains one or more commands.

::mysql::map processes one row at a time from the pending result. For each row the column values are bound to the variables in the binding list, then the script is executed. Binding is strictly positional. The first variable in the binding list is bound to the first column of the row, and so on. The variables are created in the current context (if they do not already exist). A variable name beginning with a hyphen is not bound; it serves as a placeholder in the binding list. If there are more columns than variables the extra columns are ignored.

The ::mysql::map command is similar to an ordinary foreach. A foreach iterates over the elements of a list, ::mysql::map iterates over the rows of a pending result. In both cases iteration is affected by break and continue Tcl commands. The binding list variables retain their last values after the command has completed.

A simple example follows. Assume $db is a handle in use.

::mysql::sel $db {


    select lname, fname, area, phone from friends order by lname, fname
}
::mysql::map $db {ln fn - phone} {


   if {$phone == {}} continue


   puts [format "%16s %-8s %s" $ln $fn $phone]
}
    

The ::mysql::sel command gets and sorts all rows from table friends. The ::mysql::map command is used to format and print the result in a way suitable for a phone list. For demonstration purposes one of the columns (area) is not used. The script begins by skipping over rows which have no phone number. The second command in the script formats and prints values from the row.

::mysql::map raises a Tcl error if there is no pending result for handle, or if binding-list contains more variables than there are columns in the pending result.

::mysql::receive handle sql-statment binding-list script

This command works the same way as the command mysqtclmap but it do not need leading ::mysql::sel command. The main difference is internal using of MySQL client library. This command use mysql_use_result from C-API that do not store result on client but try to receive the rows directly from server. There is also no client cache. This command can be faster as using of ::mysql::sel and by very big resultset will not overload client machine. The scipt should process the result immadiatly because it can block table (or tables) for another clients. If performance matter please test all alternatives separately. You must consider two aspects: memory consumption and performance.

::mysql::seek handle row-index

Moves the current position among the rows in the pending result. This may cause ::mysql::fetch and ::mysql::map to re-read rows, or to skip over rows.

Row index 0 is the position just before the first row in the pending result; row index 1 is the position just before the second row, and so on. You may specify a negative row index. Row index -1 is the position just before the last row; row index -2 is the position just before the second last row, and so on. An out-of-bounds row index will cause ::mysql::seek to set the new current position either just before the first row (if the index is too negative), or just after the last row (if the index exceeds the number of rows). This is not an error condition.

::mysql::seek returns the number of rows that can be read sequentially from the new current position. ::mysql::seek raises a Tcl error if there is no pending result for handle.

Portability note: The functionality of ::mysql::seek is frequently absent in other Tcl extensions for SQL. That is because MySQL C-API client library ofers own result set caching functionality that lacks another SQL-APIs. That increase the performance because all rows are received at once and the query does not block the server for another clienst , on the other hand you works on the cached data can use a lot of memory and are up to date only in the moment of query but not fetch.

::mysql::col handle table-name option

::mysql::col handle table-name optionkist

::mysql::col handle ?option...?

Return information about the columns of a table. handle must be in use. table-name must be the name of a table; it may be a table name or -current if there is a pending result. One or more options control what information to return. Each option must be one of the following keywords.

%::mysql::col $db friends name
name lname area phone
% ::mysql::col $db friends {name type length}
{fname char 12} {lname char 20} {area char 5} {phone char 12}
% ::mysql::sel $db {select * from friends}
% ::mysql::col $db -current name type length
{fname lname area phone} {char char char char} {12 20 5 12}]

::mysql::info handle option

Return various database information depending on the option. The option must be one of the following keywords.

::mysql::baseinfo option

return information information that do not need handle.

::mysql::ping handle

Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.

This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.

Return True if server is alive

::mysql::changeuser user password ?database?

Changes the user and causes the database specified by database to become the default (current) database on the connection specified by MySQL. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.

::mysql::changeuser fails unless the connected user can be authenticated or if he doesn't have permission to use the database. In this case the user and database are not changed

if database parameter may be set were is no default database.

Cause Error if operation is not succesed

::mysql::result handle option

Return information about the pending result. Note that a result is pending until canceled by a ::mysql::exec command, even if no rows remain to be read. Option must be one of the following keywords.

::mysql::state handle ?-numeric?

Return the state of a handle as a string or in numeric form. There is no requirement on handle; it may be any string. The return value is one of the following strings, or the corresponding numeric value if -numeric is specified. The states form a progression where each state builds on the previous.

::mysql::close ?handle?

Closes the server connection associated with handle, causing it to go back to the unconnected state. Closes all connections if handle is omitted. Returns an empty string. ::mysql::close raises a Tcl error if a handle is specified which is not valid.

::mysql::insertid handle

Returns the auto increment id of the last INSERT statement.

::mysql::escape ?handle? string

Returns the content of string, with all special characters escaped, so that it is suitable for use in an SQL statement. This is simpler (faster) than using a general regexp or string map. If handle is specified C-API function mysql_real_escape_string is used. This is the recommended usage because in this case current character set is respected.

::mysql::autocommit handle boolean

Sets autocommit mode on if mode is 1, off if mode is 0.

::mysql::commit handle

Commits the current transaction.

::mysql::rollback handle

Rollback the current transaction.

::mysql::nextresult handle

If more query results exist, mysql::nextresult() reads the next query results and returns the status back to application. returns -1 if no result or number of rows in the result set.

::mysql::moreresult handle

Returns true if more results exist from the currently executed query, and the application must call mysql::result to fetch the results.

::mysql::warningcount handle

Returns the number of warnings generated during execution of the previous SQL statement.

::mysql::isnull value

Null handling is a known problem with Tcl, especially with DB interaction. The mysql "varchar" type has two valid blank values, NULL and an empty string. This is where the problem arises; Tcl is not able to differentiate between the two because of the way it handles strings. Mysql has new internal Tcl type for null that string representation is stored in global array mysqltcl(nullvalue) and as default empty string. mysql::isnull can be used for safe check for null value. Warning mysql::isnull works only reliable if there are no type conversation on returned rows. Consider row is always Tcl list even when there are only one column in the row.

set row [::mysql::next $handle]
if {[mysql::isnull [lindex $row 1]]} {


   puts "2. column of $row is null"
}
if {[mysql::isnull $row]} {


   puts "this does not work, because of type conversation list to string"
}
    

::mysql::newnull

create new null object. It can be used to simulate returned row contents.

::mysql::setserveroption handle option

there are only 2 options now: -multi_statment_on and -multi_statment_off

::mysql::shutdown handle

Asks the database server to shut down. The connected user must have SHUTDOWN privileges.

::mysql::encoding handle ?encoding?

Ask or change a encoding of connection. There are special encoding "binary" for binary data transfers.

STATUS INFORMATION

Mysqltcl creates and maintains a Tcl global array to provide status information. Its name is mysqlstatus.

Mysqlstatus elements:

code

A numeric conflict code set after every mysqltcl command. Zero means no conflict; non-zero means some kind of conflict. All conflicts also generate a Tcl error.

All MySQL server conflicts set mysqlstatus(code) to the numeric code of the MySQL error.

Any other conflict sets mysqlstatus(code) to -1.

command

The last failing mysqltcl command. Not updated for successful commands.

message

Message string for the last conflict detected. The same string is returned as the result of the failing mysqltcl command. Not updated for successful commands.

nullvalue

The string to use in query results to represent the SQL null value. The empty string is used initially. You may set it to another value.

Backward compatibility

Up from version 3.0 all mysql commands are declared in ::mysql namespace. All names for example mysqlconnect are also aviable but deprecated. All old commands have the name pattern mysql{name} and the most of them are now mysql::{name}. The exception is mysqlnext, which was renamed to mysql::fetch.

BUGS & POSSIBLE MISFEATURES

Deleting any of the mysqltcl commands closes all connections.

AUTHORS

• Tobias Ritzau
• Paolo Brutti
• Artur Trzewik (mail@xdobry.de) - active maintainer

MySQLTcl is derived from a patch of msql by Hakan Soderstrom, Soderstrom Programvaruverkstad, S-12242 Enskede, Sweden. msql is derived from Sybtcl by Tom Poindexter. There are many contributors and bug reporter that are not mentioned. If you have contributed to mysqltcl and wants to be on the list contact Artur Trzewik.