Provided by: mysqltcl_3.052-1build1_amd64 bug

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.

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.

                                               3.0                                    mysqltcl(3)