mysqltcl(n)mysqltcl(n)______________________________________________________________________________NAMEmysqltcl - 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 cur‐
rent 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 data‐
base, with no need for a call to mysql::use.
-port port
The port number for the TCP/IP connection, if it's dif‐
ferent from the default.
-socket socket
The socket or named pipe for the connection.
-encoding encodingname
The option works similar to -encoding option in fconfig‐
ure. 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_NewByteAr‐
rayObj 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_time‐
out 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::con‐
nect. 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 state‐
ment, 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 state‐
ment 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 mem‐
ory 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 fur‐
ther ::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 vari‐
ables 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 vari‐
ables are created in the current context (if they do not already
exist). A variable name begining 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 iter‐
ates 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 demonstra‐
tion 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 perfor‐
mance matter please test all alternatives separatly. 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 sec‐
ond 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 fre‐
quently absent in other Tcl extensions for SQL. That is because
MySQL C-API client library ofers own result set caching func‐
tionality that lacks another SQL-APIs. That increase the per‐
formance 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 ta‐
ble 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; other‐
wise "0".
prim_key Return the string "1" if the column is part of the pri‐
mary key;
otherwise "0".
numeric Return the string "1" if the column is numeric; other‐
wise "0".
decimals Return the string "1" if the column is non-null; other‐
wise "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 sub‐
list 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 explic‐
itly.
%::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 documenta‐
tion)
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 con‐
nected. The handle must be connected.
host Return the name of the host to which the handle is con‐
nected; 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 charac‐
ters. '00000' means ``no error.'' The values are speci‐
fied 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 spec‐
ified 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 data‐
base. 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 pend‐
ing 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 correspond‐
ing 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 val‐
ues, 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 conversa‐
tion 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 names‐
pace. All names for example mysqlconnect are also aviable but depre‐
cated. 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, Soder‐
strom 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.
3.0 mysqltcl(n)
