3 <title>mysqltcl - </title>
5 <! -- Generated from file 'mysqltcl.man' by tcllib/doctools with format 'html'
7 <! -- CVS: $Id$ mysqltcl.n
11 <h1> mysqltcl(n) 3.0 ""</h1>
12 <h2><a name="name">NAME</a></h2>
14 <p> mysqltcl - MySQL server access commands for Tcl
21 <h2><a name="table_of_contents">TABLE OF CONTENTS</a></h2>
22 <p> <a href="#table_of_contents">TABLE OF CONTENTS</a><br>
23 <a href="#synopsis">SYNOPSIS</a><br>
24 <a href="#description">DESCRIPTION</a><br>
25 <a href="#mysqltcl_commands">MYSQLTCL COMMANDS</a><br>
26 <a href="#status_information">STATUS INFORMATION</a><br>
27 <a href="#backward_compatibility">Backward compatibility</a><br>
28 <a href="#bugs_&_possible_misfeatures">BUGS & POSSIBLE MISFEATURES</a><br>
29 <a href="#authors">AUTHORS</a><br>
30 <h2><a name="synopsis">SYNOPSIS</a></h2>
32 package require <b>Tcl 8.4</b><br>
33 package require <b>mysqltcl 3.0</b><br>
34 <br><table border=1 width=100% cellspacing=0 cellpadding=0><tr bgcolor=lightyellow><td bgcolor=lightyellow><table 0 width=100% cellspacing=0 cellpadding=0><tr valign=top ><td ><a href="#1"><b class='cmd'>::mysql::connect</b> ?<i class='arg'>option value</i>...?</a></td></tr>
35 <tr valign=top ><td ><a href="#2"><b class='cmd'>::mysql::use</b> <i class='arg'>handle</i> <i class='arg'>database</i></a></td></tr>
36 <tr valign=top ><td ><a href="#3"><b class='cmd'>::mysql::sel</b> <i class='arg'>handle</i> <i class='arg'>sql-statement</i> ?<i class='arg'>-list|-flatlist</i>?</a></td></tr>
37 <tr valign=top ><td ><a href="#4"><b class='cmd'>::mysql::fetch</b> <i class='arg'>handle</i></a></td></tr>
38 <tr valign=top ><td ><a href="#5"><b class='cmd'>::mysql::exec</b> <i class='arg'>handle</i> <i class='arg'>sql-statement</i></a></td></tr>
39 <tr valign=top ><td ><a href="#6"><b class='cmd'>::mysql::query</b> <i class='arg'>handle</i> <i class='arg'>sql-select-statement</i></a></td></tr>
40 <tr valign=top ><td ><a href="#7"><b class='cmd'>::mysql::endquery</b> <i class='arg'>query-handle</i></a></td></tr>
41 <tr valign=top ><td ><a href="#8"><b class='cmd'>::mysql::map</b> <i class='arg'>handle</i> <i class='arg'>binding-list</i> <i class='arg'>script</i></a></td></tr>
42 <tr valign=top ><td ><a href="#9"><b class='cmd'>::mysql::receive</b> <i class='arg'>handle</i> <i class='arg'>sql-statment</i> <i class='arg'>binding-list</i> <i class='arg'>script</i></a></td></tr>
43 <tr valign=top ><td ><a href="#10"><b class='cmd'>::mysql::seek</b> <i class='arg'>handle</i> <i class='arg'>row-index</i></a></td></tr>
44 <tr valign=top ><td ><a href="#11"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> <i class='arg'>table-name</i> <i class='arg'>option</i></a></td></tr>
45 <tr valign=top ><td ><a href="#12"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> <i class='arg'>table-name</i> <i class='arg'>optionkist</i></a></td></tr>
46 <tr valign=top ><td ><a href="#13"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> ?<i class='arg'>option</i>...?</a></td></tr>
47 <tr valign=top ><td ><a href="#14"><b class='cmd'>::mysql::info</b> <i class='arg'>handle</i> <i class='arg'>option</i></a></td></tr>
48 <tr valign=top ><td ><a href="#15"><b class='cmd'>::mysql::baseinfo</b> <i class='arg'>option</i></a></td></tr>
49 <tr valign=top ><td ><a href="#16"><b class='cmd'>::mysql::ping</b> <i class='arg'>handle</i></a></td></tr>
50 <tr valign=top ><td ><a href="#17"><b class='cmd'>::mysql::changeuser</b> <i class='arg'>user</i> <i class='arg'>password</i> ?<i class='arg'>database</i>?</a></td></tr>
51 <tr valign=top ><td ><a href="#18"><b class='cmd'>::mysql::result</b> <i class='arg'>handle</i> <i class='arg'>option</i></a></td></tr>
52 <tr valign=top ><td ><a href="#19"><b class='cmd'>::mysql::state</b> <i class='arg'>handle</i> ?<i class='arg'>-numeric</i>?</a></td></tr>
53 <tr valign=top ><td ><a href="#20"><b class='cmd'>::mysql::close</b> ?<i class='arg'>handle</i>?</a></td></tr>
54 <tr valign=top ><td ><a href="#21"><b class='cmd'>::mysql::insertid</b> <i class='arg'>handle</i></a></td></tr>
55 <tr valign=top ><td ><a href="#22"><b class='cmd'>::mysql::escape</b> ?<i class='arg'>handle</i>? <i class='arg'>string</i></a></td></tr>
56 <tr valign=top ><td ><a href="#23"><b class='cmd'>::mysql::autocommit</b> <i class='arg'>handle</i> <i class='arg'>boolean</i></a></td></tr>
57 <tr valign=top ><td ><a href="#24"><b class='cmd'>::mysql::commit</b> <i class='arg'>handle</i></a></td></tr>
58 <tr valign=top ><td ><a href="#25"><b class='cmd'>::mysql::rollback</b> <i class='arg'>handle</i></a></td></tr>
59 <tr valign=top ><td ><a href="#26"><b class='cmd'>::mysql::nextresult</b> <i class='arg'>handle</i></a></td></tr>
60 <tr valign=top ><td ><a href="#27"><b class='cmd'>::mysql::moreresult</b> <i class='arg'>handle</i></a></td></tr>
61 <tr valign=top ><td ><a href="#28"><b class='cmd'>::mysql::warningcount</b> <i class='arg'>handle</i></a></td></tr>
62 <tr valign=top ><td ><a href="#29"><b class='cmd'>::mysql::isnull</b> <i class='arg'>value</i></a></td></tr>
63 <tr valign=top ><td ><a href="#30"><b class='cmd'>::mysql::newnull</b> </a></td></tr>
64 <tr valign=top ><td ><a href="#31"><b class='cmd'>::mysql::setserveroption</b> <i class='arg'>handle</i> <i class='arg'>option</i></a></td></tr>
65 <tr valign=top ><td ><a href="#32"><b class='cmd'>::mysql::shutdown</b> <i class='arg'>handle</i></a></td></tr>
66 <tr valign=top ><td ><a href="#33"><b class='cmd'>::mysql::encoding</b> <i class='arg'>handle</i> ?encoding?</a></td></tr>
67 </table></td></tr></table>
68 <h2><a name="description">DESCRIPTION</a></h2>
70 MySQLTcl is a collection of Tcl commands and a Tcl global array that
71 provide access to MySQL database servers.
73 MySQLTcl is nothing more than a patched version of a patched version
74 of Hakan Soderstrom's patch of Tom Poindexter's Sybtcl.
76 Mysqltcl is binary Tcl library (extension) written in C language that use direkt
77 official MySQL C-API. Almost all Tcl commands correspond to MySQL C-API functions.
78 For detail documentation see official MySQL C-API manual.
80 <h2><a name="mysqltcl_commands">MYSQLTCL COMMANDS</a></h2>
85 <dt><a name="1"><b class='cmd'>::mysql::connect</b> ?<i class='arg'>option value</i>...?</a><dd>
88 Connect to a MySQL server.
89 A handle is returned which should be used in other mysqltcl
90 commands using this connection.
91 ::mysql::connect raises a Tcl error if the connection fails.
92 ::mysql::connect read first the options from my.cnf file group mysqltcl.
93 See MySQL documentation chapter "options files".
94 Possible connection options are:
99 <dt><strong>-host</strong> <i class='arg'>hostname</i><dd>
100 The host on which the server is located. The local host is used by default.
103 <dt><strong>-user</strong> <i class='arg'>user</i><dd>
104 The user whose name is used for the connection.
105 The current Unix user-name is used by default.
108 <dt><strong>-password</strong> <i class='arg'>password</i><dd>
109 The password that must be used for the connection.
110 If it is not present, the connection is possible only for users with
111 no password on the server.
114 <dt><strong>-db</strong> <i class='arg'>db</i><dd>
115 If this option is present, <i class='arg'>db</i> is used as current database, with no need
116 for a call to <em>mysql::use</em>.
119 <dt><strong>-port</strong> <i class='arg'>port</i><dd>
120 The port number for the TCP/IP connection, if it's different from the default.
123 <dt><strong>-socket</strong> <i class='arg'>socket</i><dd>
124 The socket or named pipe for the connection.
127 <dt><strong>-encoding</strong> <i class='arg'>encodingname</i><dd>
128 The option works similar to -encoding option in fconfigure. It support also
129 special encoding name binary. By option -binary no converting will be done be reading or writing to/from MySQL.
130 If option is not set the system encoding (see utf-8) is used.
131 Please test all input and outputs with another program to check that all
132 is the way you expect it. If option binary is not used the system procedures
133 Tcl_ExternalToUtfDString (writing) and Tcl_ExternalToUtf (reading) will be used
134 by option binary the function Tcl_GetByteArrayFromObj and Tcl_NewByteArrayObj are used.
135 If you want to manipulate binary date use -encoding binary. By handling textes set your
136 special encoding that you want to use in your database. Consider what another system access the
137 database and what encoding they expect. It can useful
138 to use -encoding utf-8. That is standard encoding in some linux distributions and newer systems.
141 <dt><strong>-compress</strong> <i class='arg'>boolean</i><dd>
142 Use compression protocol. Default is false
145 <dt><strong>-odbc</strong> <i class='arg'>boolean</i><dd>
146 The client is an ODBC client. This changes mysqld to be more ODBC-friendly. Default is false
149 <dt><strong>-noschema</strong> <i class='arg'>boolean</i><dd>
150 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
153 <dt><strong>-multistatement</strong> <i class='arg'>boolean</i><dd>
154 Tell the server that the client may send multiple-row-queries (separated by `;').
155 If this flag is not set, multiple-row-queries are disabled. Default is false.
158 <dt><strong>-multiresult</strong> <i class='arg'>boolean</i><dd>
159 Tell the server that the client can handle multiple-result sets from multi-queries or stored procedures.
160 This is automatically set if CLIENT_MULTI_STATEMENTS is set.
163 <dt><strong>-localfiles</strong> <i class='arg'>boolean</i><dd>
164 Enable LOAD DATA LOCAL handling. Default is false.
167 <dt><strong>-foundrows</strong> <i class='arg'>boolean</i><dd>
168 Return the number of found (matched) rows, not the number of affected rows.
172 <dt><strong>-interactive</strong> <i class='arg'>boolean</i><dd>
173 Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection.
174 The client's session wait_timeout variable will be set to the value of the session interactive_timeout variable.
178 <dt><strong>-ssl</strong> <i class='arg'>boolean</i><dd>
179 Switch to SSL after handshake. Default is false
182 <dt><strong>-sslkey</strong> <i class='arg'>string</i><dd>
183 is the pathname to the key file.
187 <dt><strong>-sslcert</strong> <i class='arg'>string</i><dd>
188 is the pathname to the certificate file.
192 <dt><strong>-sslca</strong> <i class='arg'>string</i><dd>
193 is the pathname to the certificate authority file.
197 <dt><strong>-sslcapath</strong> <i class='arg'>string</i><dd>
198 is the pathname to a directory that contains trusted SSL CA certificates in pem format.
202 <dt><strong>-sslcipher</strong> <i class='arg'>string</i><dd>
203 is a list of allowable ciphers to use for SSL encryption.
207 <dt><strong>-reconnect</strong> <i class='arg'>boolean</i><dd>
212 <dt><a name="2"><b class='cmd'>::mysql::use</b> <i class='arg'>handle</i> <i class='arg'>database</i></a><dd>
215 Associate a connected handle with a particular database.
216 <i class='arg'>handle</i> must be a valid handle previously obtained from ::mysql::connect.
217 mysql::use raises a Tcl error if the handle is not valid or
218 if the database name specified could not be used.
220 Consider you can use mysqltcl without to specify the database, in this case
221 you must use explizit schema notation to specify the table in sql.
222 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
223 ::mysql::sel $handle {select * from uni.student}
224 </pre></td></tr></table></p>
225 with option connection <i class='arg'>-noschema</i> you can prohibit such syntax.
228 <dt><a name="3"><b class='cmd'>::mysql::sel</b> <i class='arg'>handle</i> <i class='arg'>sql-statement</i> ?<i class='arg'>-list|-flatlist</i>?</a><dd>
231 Send <i class='arg'>sql-statement</i> to the server.
233 If <i class='arg'>sql-statement</i> is a SELECT statement and no <i class='arg'>-list</i> or
234 <i class='arg'>-flatlist</i> option is specified, the command returns the
235 number of rows returned as the result of the query.
236 The rows can be obtained by the <i class='arg'>::mysql::fetch</i> and/or the
237 <i class='arg'>::mysql::map</i> commands.
238 The resulting rows are called the <em>pending result</em>.
240 If <i class='arg'>sql-statement</i> is a SELECT statement and <i class='arg'>-list</i> or <i class='arg'>-flatlist</i>
241 is specified, the command returns the full list of rows returned as
242 the result of the query in one of two possible formats:
247 <dt><strong>-list</strong><dd>
248 generates a list of lists, in which each element is a row of the result.
251 <dt><strong>-flatlist</strong><dd>
252 generates the concatenation of all rows in a single list, which
253 is useful for scanning with a single <em>foreach</em>.
259 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
260 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -list
261 {1 Joe} {2 Phil} {3 John}
262 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS" -flatlist
263 {1 Joe 2 Phil 3 John}
264 </pre></td></tr></table></p>
266 Note that both list syntaxes are faster than something like
268 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
269 % ::mysql::sel $db "SELECT ID, NAME FROM FRIENDS"
270 % ::mysql::map $db {id name} {lappend result $id $name}
272 {1 Joe 2 Phil 3 John}
273 </pre></td></tr></table></p>
275 If <i class='arg'>sql-statement</i> is a valid MySQL statement, but not a SELECT
276 statement, the command returns -1 after executing the statement, or an empty
277 string if <i class='arg'>-list</i> or <i class='arg'>-flatlist</i> is specified.
278 There is no pending result in this case.
280 In any case ::mysql::sel implicitly cancels any previous result still
281 pending for the handle.
284 <dt><a name="4"><b class='cmd'>::mysql::fetch</b> <i class='arg'>handle</i></a><dd>
287 Returns the next row from result set as Tcl list.
288 mysql::fetch raises a Tcl error if there is no pending result for <i class='arg'>handle</i>.
289 mysql::fetch was former named mysqlnext.
292 <dt><a name="5"><b class='cmd'>::mysql::exec</b> <i class='arg'>handle</i> <i class='arg'>sql-statement</i></a><dd>
295 Send <i class='arg'>sql-statement</i>, a MySQL non-SELECT statement, to the server.
296 The <i class='arg'>handle</i> must be in use (through ::mysql::connect and ::mysql::use).
298 ::mysql::exec implicitly cancels any previous result pending for the handle.
300 If <i class='arg'>sql-statement</i> is a valid MySQL SELECT statement, the statement
301 is executed, but the result is discarded.
302 No Tcl error is generated.
303 This amounts to a (potentially costly) no-op.
304 Use the ::mysql::sel command for SELECT statements.
306 ::mysql::exec returns the number of affected rows (DELETE, UPDATE).
307 In case of multiple statement ::mysql::exec returns a list of number of affected rows.
311 <dt><a name="6"><b class='cmd'>::mysql::query</b> <i class='arg'>handle</i> <i class='arg'>sql-select-statement</i></a><dd>
314 Send <i class='arg'>sql-select-statement</i> to the server.
316 <i class='arg'>mysql::query</i> allow to send multiple nested queries on one handle (without need to build
317 new handle or caching results).
318 mysql::query return a query handle that can be used as handle in commands as (mysql::fetch,
319 ::mysql::map, mysql::seek, mysql::col, mysql::result).
320 After result proceeding all query must be
321 freed with <em>::mysql::endquery query-hanlde</em> command.
324 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
325 set query1 [::mysql::query $db {SELECT ID, NAME FROM FRIENDS}\]
326 while {[set row [::mysql::fetch $query1]]!=""} {
327 set id [lindex $row 0]
328 set query2 [::mysql::query $db "SELECT ADDRESS FROM ADDRESS WHERE FRIENDID=$ID"]
329 ::mysql::map $query2 address { puts "address = $address" }
330 ::mysql::endquery $query2
332 ::mysql::endquery $query1
333 </pre></td></tr></table></p>
334 In most cases one should use sql-joins and avoid nested queries.
335 SQL-sever can optimize such queries.
336 But in some applications (GUI-Forms) where the results are used long time the inner
337 query is not known before.
340 <dt><a name="7"><b class='cmd'>::mysql::endquery</b> <i class='arg'>query-handle</i></a><dd>
343 free result memory after <i class='arg'>::mysql::query</i> command.
344 You must invoke ::mysql::endquery after each mysqlquery to not cause memory leaks. See mysqlquery command.
346 Using <i class='arg'>::mysql::endquery</i> on db-handle will free also memory (pending result) after <i class='arg'>::mysql::sel</i> command.
351 <dt><a name="8"><b class='cmd'>::mysql::map</b> <i class='arg'>handle</i> <i class='arg'>binding-list</i> <i class='arg'>script</i></a><dd>
354 Iterate a script over the rows of the pending result.
355 ::mysql::map may consume all rows or only some of the rows of the pending
357 Any remaining rows may be obtained by further ::mysql::fetch or ::mysql::map
360 <i class='arg'>handle</i> must be a handle with a pending result from a previous
361 ::mysql::sel command.
362 <i class='arg'>binding-list</i> must be a list of one or more variable names.
363 <i class='arg'>script</i> must be a Tcl script.
364 It may be empty, but usually it contains one or more commands.
366 ::mysql::map processes one row at a time from the pending result.
367 For each row the column values are bound to the variables in the
368 binding list, then the script is executed.
369 Binding is strictly positional.
370 The first variable in the binding list is bound to the first column of
372 The variables are created in the current context (if they do not
374 A variable name begining with a hyphen is not bound; it serves as a
375 placeholder in the binding list.
376 If there are more columns than variables the extra columns are
379 The ::mysql::map command is similar to an ordinary <em>foreach</em>.
380 A <em>foreach</em> iterates over the elements of a list, ::mysql::map
381 iterates over the rows of a pending result.
382 In both cases iteration is affected by <em>break</em> and <em>continue</em>
384 The binding list variables retain their last values after the command
387 A simple example follows.
388 Assume $db is a handle in use.
389 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
391 select lname, fname, area, phone from friends order by lname, fname
393 ::mysql::map $db {ln fn - phone} {
394 if {$phone == {}} continue
395 puts [format "%16s %-8s %s" $ln $fn $phone]
397 </pre></td></tr></table></p>
398 The ::mysql::sel command gets and sorts all rows from table friends.
399 The ::mysql::map command is used to format and print the result in a way
400 suitable for a phone list.
401 For demonstration purposes one of the columns (area) is not used.
402 The script begins by skipping over rows which have no phone number.
403 The second command in the script formats and prints values from the row.
405 ::mysql::map raises a Tcl error if there is no pending result for
406 <i class='arg'>handle</i>, or if <i class='arg'>binding-list</i> contains more variables than
407 there are columns in the pending result.
411 <dt><a name="9"><b class='cmd'>::mysql::receive</b> <i class='arg'>handle</i> <i class='arg'>sql-statment</i> <i class='arg'>binding-list</i> <i class='arg'>script</i></a><dd>
414 This command works the same way as the command mysqtclmap but
415 it do not need leading ::mysql::sel command.
416 The main difference is internal using of MySQL client library.
417 This command use mysql_use_result from C-API that do not
418 store result on client but try to receive the rows directly
420 There is also no client cache.
421 This command can be faster as using of ::mysql::sel and by
422 very big resultset will not overload client machine.
423 The scipt should process the result immadiatly because
424 it can block table (or tables) for another clients.
425 If performance matter please test all alternatives separatly.
426 You must consider two aspects: memory consumption and performance.
429 <dt><a name="10"><b class='cmd'>::mysql::seek</b> <i class='arg'>handle</i> <i class='arg'>row-index</i></a><dd>
432 Moves the current position among the rows in the pending result.
433 This may cause <em>::mysql::fetch</em> and <em>::mysql::map</em> to re-read rows, or to
436 Row index 0 is the position just before the first row in the pending result;
437 row index 1 is the position just before the second row, and so
439 You may specify a negative row index.
440 Row index -1 is the position just before the last row;
441 row index -2 is the position just before the second last row,
443 An out-of-bounds row index will cause ::mysql::seek to set the new current
444 position either just before the first row (if the index is too negative),
445 or just after the last row (if the index exceeds the number of rows).
446 This is not an error condition.
448 ::mysql::seek returns the number of rows that can be read sequentially from
449 the new current position.
450 ::mysql::seek raises a Tcl error if there is no pending result for <i class='arg'>handle</i>.
452 Portability note: The functionality of <i class='arg'>::mysql::seek</i> is frequently
453 absent in other Tcl extensions for SQL.
454 That is because MySQL C-API client library ofers own result set caching functionality
455 that lacks another SQL-APIs.
456 That increase the performance because all rows are received at once and the query does
457 not block the server for another clienst , on the other
458 hand you works on the cached data can use a lot of memory and are up to date only in the moment
459 of query but not fetch.
463 <dt><a name="11"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> <i class='arg'>table-name</i> <i class='arg'>option</i></a><dd>
465 <dt><a name="12"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> <i class='arg'>table-name</i> <i class='arg'>optionkist</i></a><dd>
467 <dt><a name="13"><b class='cmd'>::mysql::col</b> <i class='arg'>handle</i> ?<i class='arg'>option</i>...?</a><dd>
470 Return information about the columns of a table.
471 <i class='arg'>handle</i> must be in use.
472 <i class='arg'>table-name</i> must be the name of a table; it may be a table name
473 or <i class='arg'>-current</i> if there is a pending result.
474 One or more options control what information to return.
475 Each option must be one of the following keywords.
479 <dt><strong>name</strong><dd> Return the name of a column.
482 <dt><strong>type</strong><dd>
483 Return the type of a column; one of the strings <em>decimal</em>,
484 <em>tiny</em>, <em>short</em>, <em>long</em>, <em>float</em>, <em>double</em>,
485 <em>null</em>, <em>timestamp</em>, <em>long long</em>, <em>int24</em>, <em>date</em>,
486 <em>time</em>, <em>date time</em>, <em>year</em>, <em>new date</em>, <em>enum</em>,
487 <em>set</em>, <em>tiny blob</em>, <em>medium blob</em>, <em>long blob</em>,
488 <em>blob</em>, <em>var string</em>, or <em>string</em>.
489 Note that a column of type <em>char</em> will return tiny, while they are
493 <dt><strong>length</strong><dd> Return the length of a column in bytes.
496 <dt><strong>table</strong><dd> Return the name of the table in which this column occurs.
499 <dt><strong>non_null</strong><dd> Return the string "1" if the column is non-null; otherwise "0".
502 <dt><strong>prim_key</strong><dd> Return the string "1" if the column is part of the primary key;
503 otherwise "0".
506 <dt><strong>numeric</strong><dd> Return the string "1" if the column is numeric; otherwise "0".
509 <dt><strong>decimals</strong><dd> Return the string "1" if the column is non-null; otherwise "0".
512 The three forms of this command generate their result in a
517 If a single option is present the result is a simple list of
518 values; one for each column.
521 If the options are given in the form of an option list the
522 result is a list of lists.
523 Each sublist corresponds to a column and contains the information
524 specified by the options.
527 If several options are given, but not in a list, the result is also
529 In this case each sublist corresponds to an option and contains one
530 value for each column.
533 The following is a sample interactive session containing all forms of
534 the ::mysql::col command and their results.
535 The last command uses the <em>-current</em> option.
536 It could alternatively specify the table name explicitly.
537 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
538 %::mysql::col $db friends name
539 name lname area phone
540 % ::mysql::col $db friends {name type length}
541 {fname char 12} {lname char 20} {area char 5} {phone char 12}
542 % ::mysql::sel $db {select * from friends}
543 % ::mysql::col $db -current name type length
544 {fname lname area phone} {char char char char} {12 20 5 12}]
545 </pre></td></tr></table></p>
548 <dt><a name="14"><b class='cmd'>::mysql::info</b> <i class='arg'>handle</i> <i class='arg'>option</i></a><dd>
551 Return various database information depending on the <i class='arg'>option</i>.
552 The option must be one of the following keywords.
556 <dt><strong>info</strong><dd>
557 Return a String with information about last operation.
558 "Records: 3 Duplicates: 0 Warnings: 0" by INSERT or
559 "Rows matched: 40 Changed: 40 Warnings: 0" by UPDATE statements
560 (read the manual for mysql_info in MySQL C API documentation)
563 <dt><strong>databases</strong><dd>
564 Return a list of all database names known to the server.
565 The handle must be connected.
568 <dt><strong>dbname</strong><dd>
569 Return the name of the database with which the handle is associated.
570 The handle must be in use.
573 <dt><strong>dbname?</strong><dd>
574 Return the name of the database with which the handle is associated;
575 an empty string if the handle is connected, but not in use.
578 <dt><strong>host</strong><dd>
579 Return the name of the host to which the handle is connected.
580 The handle must be connected.
583 <dt><strong>host</strong><dd>
584 Return the name of the host to which the handle is connected; an empty
585 string if the handle is not valid.
588 <dt><strong>tables</strong><dd>
589 Return a list of all table names in the database with which the handle
591 The handle must be in use.
594 <dt><strong>serverversion</strong><dd>
595 Returns the version number of the server as a string.
598 <dt><strong>serverversionid</strong><dd>
599 Returns the version number of the server as an integer.
602 <dt><strong>sqlstate</strong><dd>
603 Returns a string containing the SQLSTATE error code for the last error.
604 The error code consists of five characters. '00000' means ``no error.''
605 The values are specified by ANSI SQL and ODBC.
607 Note that not all MySQL errors are yet mapped to SQLSTATE's.
608 The value 'HY000' (general error) is used for unmapped errors.
611 <dt><strong>state</strong><dd>
612 Returns a character string containing information similar to that provided by the mysqladmin status command.
613 This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.
617 <dt><a name="15"><b class='cmd'>::mysql::baseinfo</b> <i class='arg'>option</i></a><dd>
620 return information information that do not need handle.
624 <dt><strong>connectparameters</strong><dd>
625 return all supported connecting options
628 <dt><strong>clientversion</strong><dd>
629 return the version of underlying MYSQL C-API library
632 <dt><a name="16"><b class='cmd'>::mysql::ping</b> <i class='arg'>handle</i></a><dd>
635 Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
637 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.
639 Return True if server is alive
642 <dt><a name="17"><b class='cmd'>::mysql::changeuser</b> <i class='arg'>user</i> <i class='arg'>password</i> ?<i class='arg'>database</i>?</a><dd>
645 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.
647 ::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
649 if database parameter may be set were is no default database.
651 Cause Error if operation is not succesed
654 <dt><a name="18"><b class='cmd'>::mysql::result</b> <i class='arg'>handle</i> <i class='arg'>option</i></a><dd>
657 Return information about the pending result.
658 Note that a result is pending until canceled by a ::mysql::exec command,
659 even if no rows remain to be read.
660 <i class='arg'>Option</i> must be one of the following keywords.
665 <dt><strong>cols</strong><dd>
666 Return the number of columns in the pending result.
667 There must be a pending result.
670 <dt><strong>cols</strong><dd>
671 Return the number of columns in the pending result; an empty string if
672 no result is pending.
675 <dt><strong>current</strong><dd>
676 Return the current position in the pending result; a non-negative integer.
677 This value can be used as <i class='arg'>row-index</i> in the ::mysql::seek command.
678 An error is raised if there is no pending result.
681 <dt><strong>current?</strong><dd>
682 As above, but returns an empty string if there is no pending result.
685 <dt><strong>rows</strong><dd>
686 Return the number of rows that can be read sequentially from the
687 current position in the pending result.
688 There must be a pending result.
691 <dt><strong>rows</strong><dd>
692 Return the number of rows that can be read sequentially from the
693 current position in the pending result; an empty string if no result
696 [::mysql::result $db current] + [::mysql::result $db rows]
697 always equals the total number of rows in the pending result.
700 <dt><a name="19"><b class='cmd'>::mysql::state</b> <i class='arg'>handle</i> ?<i class='arg'>-numeric</i>?</a><dd>
703 Return the state of a handle as a string or in numeric form.
704 There is no requirement on <i class='arg'>handle</i>; it may be any string.
705 The return value is one of the following strings, or the corresponding
706 numeric value if <i class='arg'>-numeric</i> is specified.
707 The states form a progression where each state builds on the previous.
711 <dt><strong>NOT_A_HANDLE (0)</strong><dd>
712 The string supplied for <i class='arg'>handle</i> is not a mysqltcl handle at all.
715 <dt><strong>UNCONNECTED (1)</strong><dd>
716 The string supplied for <i class='arg'>handle</i> is one of the possible mysqltcl
717 handles, but it is not valid to any server.
720 <dt><strong>CONNECTED (2)</strong><dd>
721 The handle is connected to a server, but not associated with a database.
724 <dt><strong>IN_USE (3)</strong><dd>
725 The handle is connected and associated with a database, but there is
729 <dt><strong>RESULT_PENDING (4)</strong><dd>
730 The handle is connected, associated with a database, and there is a
734 <dt><a name="20"><b class='cmd'>::mysql::close</b> ?<i class='arg'>handle</i>?</a><dd>
737 Closes the server connection associated with <i class='arg'>handle</i>, causing it
738 to go back to the unconnected state.
739 Closes all connections if <i class='arg'>handle</i> is omitted.
740 Returns an empty string.
741 ::mysql::close raises a Tcl error if a handle is specified which is not
745 <dt><a name="21"><b class='cmd'>::mysql::insertid</b> <i class='arg'>handle</i></a><dd>
748 Returns the auto increment id of the last INSERT statement.
751 <dt><a name="22"><b class='cmd'>::mysql::escape</b> ?<i class='arg'>handle</i>? <i class='arg'>string</i></a><dd>
754 Returns the content of <i class='arg'>string</i>, with all special characters escaped,
755 so that it is suitable for use in an SQL statement. This is simpler (faster)
756 than using a general <em>regexp</em> or string map.
757 If handle is specified C-API function mysql_real_escape_string is used.
758 This is the recommended usage because in this case current character set is respected.
761 <dt><a name="23"><b class='cmd'>::mysql::autocommit</b> <i class='arg'>handle</i> <i class='arg'>boolean</i></a><dd>
763 Sets autocommit mode on if mode is 1, off if mode is 0.
766 <dt><a name="24"><b class='cmd'>::mysql::commit</b> <i class='arg'>handle</i></a><dd>
768 Commits the current transaction.
771 <dt><a name="25"><b class='cmd'>::mysql::rollback</b> <i class='arg'>handle</i></a><dd>
773 Rollback the current transaction.
776 <dt><a name="26"><b class='cmd'>::mysql::nextresult</b> <i class='arg'>handle</i></a><dd>
778 If more query results exist, mysql::nextresult() reads the next query results and returns the status back to application.
779 returns -1 if no result or number of rows in the result set.
782 <dt><a name="27"><b class='cmd'>::mysql::moreresult</b> <i class='arg'>handle</i></a><dd>
784 Returns true if more results exist from the currently executed query, and the application must call mysql::result to fetch the results.
787 <dt><a name="28"><b class='cmd'>::mysql::warningcount</b> <i class='arg'>handle</i></a><dd>
789 Returns the number of warnings generated during execution of the previous SQL statement.
793 <dt><a name="29"><b class='cmd'>::mysql::isnull</b> <i class='arg'>value</i></a><dd>
795 Null handling is a known problem with Tcl, especially with DB interaction.
796 The mysql "varchar" type has two valid blank values, NULL and an empty
797 string. This is where the problem arises; Tcl is not able to differentiate
798 between the two because of the way it handles strings.
799 Mysql has new internal Tcl type for null that string representation is stored
800 in global array mysqltcl(nullvalue) and as default empty string.
801 mysql::isnull can be used for safe check for null value.
802 Warning mysql::isnull works only reliable if there are no type conversation on
804 Consider row is always Tcl list even when there are only one column in the row.
805 <p><table><tr><td bgcolor=black> </td><td><pre class='sample'>
806 set row [::mysql::next $handle]
807 if {[mysql::isnull [lindex $row 1]]} {
808 puts "2. column of $row is null"
810 if {[mysql::isnull $row]} {
811 puts "this does not work, because of type conversation list to string"
813 </pre></td></tr></table></p>
816 <dt><a name="30"><b class='cmd'>::mysql::newnull</b> </a><dd>
818 create new null object. It can be used to simulate returned row contents.
821 <dt><a name="31"><b class='cmd'>::mysql::setserveroption</b> <i class='arg'>handle</i> <i class='arg'>option</i></a><dd>
823 there are only 2 options now: -multi_statment_on and -multi_statment_off
826 <dt><a name="32"><b class='cmd'>::mysql::shutdown</b> <i class='arg'>handle</i></a><dd>
828 Asks the database server to shut down. The connected user must have SHUTDOWN privileges.
831 <dt><a name="33"><b class='cmd'>::mysql::encoding</b> <i class='arg'>handle</i> ?encoding?</a><dd>
833 Ask or change a encoding of connection.
834 There are special encoding "binary" for binary data transfers.
838 <h2><a name="status_information">STATUS INFORMATION</a></h2>
840 Mysqltcl creates and maintains a Tcl global array to provide status
842 Its name is <em>mysqlstatus</em>.
844 Mysqlstatus elements:
848 A numeric conflict code set after every mysqltcl command.
849 Zero means no conflict; non-zero means some kind of conflict.
850 All conflicts also generate a Tcl error.
852 All MySQL server conflicts set mysqlstatus(code) to the numeric
853 code of the MySQL error.
855 Any other conflict sets mysqlstatus(code) to -1.
859 The last failing mysqltcl command.
860 Not updated for successful commands.
864 Message string for the last conflict detected.
865 The same string is returned as the result of the failing mysqltcl
867 Not updated for successful commands.
871 The string to use in query results to represent the SQL null value.
872 The empty string is used initially.
873 You may set it to another value.
876 <h2><a name="backward_compatibility">Backward compatibility</a></h2>
878 Up from version 3.0 all mysql commands are declared in ::mysql namespace.
879 All names for example mysqlconnect are also aviable but deprecated.
880 All old commands have the name pattern mysql{name} and the most of them are now mysql::{name}.
881 The exception is mysqlnext, which was renamed to mysql::fetch.
883 <h2><a name="bugs_&_possible_misfeatures">BUGS & POSSIBLE MISFEATURES</a></h2>
886 Deleting any of the mysqltcl commands closes all connections.
888 <h2><a name="authors">AUTHORS</a></h2>
899 Artur Trzewik (mail@xdobry.de) - active maintainer
903 MySQLTcl is derived from a patch of msql by Hakan Soderstrom, Soderstrom Programvaruverkstad,
904 S-12242 Enskede, Sweden.
905 msql is derived from Sybtcl by Tom Poindexter.
906 There are many contributors and bug reporter that are not mentioned.
907 If you have contributed to mysqltcl and wants to be on the list contact Artur Trzewik.