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:
- -host hostname
- The host on which the server is located. The local host is used by
default.
- -user user
- The user whose name is used for the connection. The current Unix user-name
is used by default.
- -password password
- The password that must be used for the connection. If it is not present,
the connection is possible only for users with no password on the
server.
- -db db
- If this option is present, db is used as current database, with no
need for a call to mysql::use.
- -port port
- The port number for the TCP/IP connection, if it's different from the
default.
- -socket socket
- The socket or named pipe for the connection.
- -encoding encodingname
- The option works similar to -encoding option in fconfigure. It support
also special encoding name binary. By option -binary no converting will be
done be reading or writing to/from MySQL. If option is not set the system
encoding (see utf-8) is used. Please test all input and outputs with
another program to check that all is the way you expect it. If option
binary is not used the system procedures Tcl_ExternalToUtfDString
(writing) and Tcl_ExternalToUtf (reading) will be used by option binary
the function Tcl_GetByteArrayFromObj and Tcl_NewByteArrayObj are used. If
you want to manipulate binary date use -encoding binary. By handling
textes set your special encoding that you want to use in your database.
Consider what another system access the database and what encoding they
expect. It can useful to use -encoding utf-8. That is standard encoding in
some linux distributions and newer systems.
- -compress boolean
- Use compression protocol. Default is false
- -odbc boolean
- The client is an ODBC client. This changes mysqld to be more
ODBC-friendly. Default is false
- -noschema boolean
- Don't allow the db_name.tbl_name.col_name syntax. This is for ODBC. It
causes the parser to generate an error if you use that syntax, which is
useful for trapping bugs in some ODBC programs. This changes mysqld to be
more ODBC-friendly. Default is false
- -multistatement boolean
- Tell the server that the client may send multiple-row-queries (separated
by `;'). If this flag is not set, multiple-row-queries are disabled.
Default is false.
- -multiresult boolean
- Tell the server that the client can handle multiple-result sets from
multi-queries or stored procedures. This is automatically set if
CLIENT_MULTI_STATEMENTS is set.
- -localfiles boolean
- Enable LOAD DATA LOCAL handling. Default is false.
- -foundrows boolean
- Return the number of found (matched) rows, not the number of affected
rows. Default is false.
- -interactive boolean
- Allow interactive_timeout seconds (instead of wait_timeout seconds) of
inactivity before closing the connection. The client's session
wait_timeout variable will be set to the value of the session
interactive_timeout variable. Default is false.
- -ssl boolean
- Switch to SSL after handshake. Default is false
- -sslkey string
- is the pathname to the key file. Used if -ssl is true
- -sslcert string
- is the pathname to the certificate file. Used if -ssl is true
- -sslca string
- is the pathname to the certificate authority file. Used if -ssl is
true
- -sslcapath string
- is the pathname to a directory that contains trusted SSL CA certificates
in pem format. Used if -ssl is true
- -sslcipher string
- is a list of allowable ciphers to use for SSL encryption. Used if -ssl is
true
- -reconnect boolean
- default is false.
- ::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:
- -list
- generates a list of lists, in which each element is a row of the
result.
- -flatlist
- generates the concatenation of all rows in a single list, which is useful
for scanning with a single foreach.
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.
- name Return the name of a column.
- type
- Return the type of a column; one of the strings decimal,
tiny, short, long, float, double,
null, timestamp, long long, int24,
date, time, date time, year, new date,
enum, set, tiny blob, medium blob, long
blob, blob, var string, or string. Note that a
column of type char will return tiny, while they are represented
equally.
- length Return the length of a column in bytes.
- table Return the name of the table in which this column
occurs.
- non_null Return the string "1" if the column is non-null;
otherwise "0".
- prim_key Return the string "1" if the column is part of
the primary key;
- otherwise "0".
- numeric Return the string "1" if the column is numeric;
otherwise "0".
- decimals Return the string "1" if the column is non-null;
otherwise "0".
The three forms of this command generate their result in a particular way.
- [1]
- If a single option is present the result is a simple list of values; one
for each column.
- [2]
- If the options are given in the form of an option list the result is a
list of lists. Each sublist corresponds to a column and contains the
information specified by the options.
- [3]
- If several options are given, but not in a list, the result is also a list
of lists. In this case each sublist corresponds to an option and contains
one value for each column.
The following is a sample interactive session containing all forms of the
::mysql::col command and their results. The last command uses the
-current option. It could alternatively specify the table name
explicitly.
%::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.
- info
- Return a String with information about last operation. "Records: 3
Duplicates: 0 Warnings: 0" by INSERT or "Rows matched: 40
Changed: 40 Warnings: 0" by UPDATE statements (read the manual for
mysql_info in MySQL C API documentation)
- databases
- Return a list of all database names known to the server. The handle must
be connected.
- dbname
- Return the name of the database with which the handle is associated. The
handle must be in use.
- dbname?
- Return the name of the database with which the handle is associated; an
empty string if the handle is connected, but not in use.
- host
- Return the name of the host to which the handle is connected. The handle
must be connected.
- host
- Return the name of the host to which the handle is connected; an empty
string if the handle is not valid.
- tables
- Return a list of all table names in the database with which the handle is
associated. The handle must be in use.
- serverversion
- Returns the version number of the server as a string.
- serverversionid
- Returns the version number of the server as an integer.
- sqlstate
- Returns a string containing the SQLSTATE error code for the last error.
The error code consists of five characters. '00000' means ``no error.''
The values are specified by ANSI SQL and ODBC. Note that not all MySQL
errors are yet mapped to SQLSTATE's. The value 'HY000' (general error) is
used for unmapped errors.
- state
- Returns a character string containing information similar to that provided
by the mysqladmin status command. This includes uptime in seconds and the
number of running threads, questions, reloads, and open tables.
- ::mysql::baseinfo option
- return information information that do not need handle.
- connectparameters
- return all supported connecting options
- clientversion
- return the version of underlying MYSQL C-API library
- ::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.
- cols
- Return the number of columns in the pending result. There must be a
pending result.
- cols
- Return the number of columns in the pending result; an empty string if no
result is pending.
- current
- Return the current position in the pending result; a non-negative integer.
This value can be used as row-index in the ::mysql::seek command.
An error is raised if there is no pending result.
- current?
- As above, but returns an empty string if there is no pending result.
- rows
- Return the number of rows that can be read sequentially from the current
position in the pending result. There must be a pending result.
- rows
- Return the number of rows that can be read sequentially from the current
position in the pending result; an empty string if no result is pending.
[::mysql::result $db current] + [::mysql::result $db rows] always equals the
total number of rows in the pending result.
- ::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.
- NOT_A_HANDLE (0)
- The string supplied for handle is not a mysqltcl handle at
all.
- UNCONNECTED (1)
- The string supplied for handle is one of the possible mysqltcl
handles, but it is not valid to any server.
- CONNECTED (2)
- The handle is connected to a server, but not associated with a
database.
- IN_USE (3)
- The handle is connected and associated with a database, but there is no
pending result.
- RESULT_PENDING (4)
- The handle is connected, associated with a database, and there is a
pending result.
- ::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.
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.