![[Go to CFHT Home Page]](/images/cfht-icon.gif){kind=icon}
Man Pages 
Back to Software Index 
Manpage Top Level
Sybtcl is a collection of Tcl commands and a Tcl global array that provides access to a Sybase Server. Each Sybtcl command generally invokes several Sybase Open Client (a.k.a. DB-Library) library functions. Programmers using Sybtcl should be familar with basic concepts of DB-Library programming.
A handle is returned and should be used for all other Sybtcl commands using this connection. Multiple connections to the same or different servers are allowed, up to a maximum of 25 total connections. Sybconnect raises a Tcl error if the connection is not made for any reason (login or password incorrect, server not found in the Sybase interfaces file, network unavailable, etc.).
Multiple SQL statements may be specified in sql-command. Sybnext allows retrieval of return rows generated; Sybretval allows retrieval of any return values from stored procedures. See notes regarding stored procedure output variables.
Sybsql performs an implicit sybcancel if any results are still pending from the last execution of sybsql. Sybsql raises a Tcl error if the handle specified is not open, or if the SQL commands are syntactically incorrect.
Table inserts made with sybsql should follow conversion rules in the Sybase Commands Reference manual (image or binary data is hexadecimal string preceded with "0x"; datetime should be a recognizable date, etc. The SQL Server CONVERT function may be used to force conversions.
The option -all may be specified, in which all handles that have been executed with -async are checked, and a Tcl list of all handles that have results waiting are returned. When -all is combined with a timeout of -1, then sybpoll waits until any async handle has results waiting, and then returns a list of all handles that have results waiting. If the last SQL statements executed with handle were not sent as -async with sybsql, sybpoll returns a null string.
If the SQL statements where executed with the -async option of sybsql, then sybnext will wait until results are available. Sybpoll may be used to check for results. Any errors in the SQL statements will cause sybnext to fail.
The optional commands argument allows sybnext to repeatedly fetch rows and execute commands for each row. Substitutions are made on commands before passing it to Tcl_Eval() for each row. An optional fourth argument consisting of a single character can be specified for a column number substitution character. If none is specified, the character ’@’ will be used to denote the substitution character. If the substitution character is a null string, no column substitutions will be performed on the commands string. Sybnext interprets the substitution character followed by a number (@n) in commands as a result column specification. For example, @1, @2, @3 refer to the first, second, and third columns in the result. @0 refers to the entire result row, as a Tcl list. Substitution columns may appear in any order, or more than once in the same command. Substituted columns are inserted into the commands string as proper list elements, i.e., one space will be added before and after the substitution and column values with embedded spaces are enclosed by {} if needed.
Tcl variables may also be set for commands on each row that is processed. Tcl variables are specified after the substitution_character, consisting of matching pairs of Tcl variable names and a column numbers. Column number may be "0", in which the Tcl variable is set to the entire result row as a Tcl list. Column numbers must be less than or equal to the number of columns in the SQL result set.
Sybnext will execute commands until NO_MORE_ROWS. If additional results are pending, subsequent sybnext commands will retrieve the next set of results.
A Tcl error is raised if a column substitution number is greater than the number of columns in the results. Note that Transact-SQL "compute" statements are considered to be part of the current select’s result set, and thus, a different number of columns may be returned, causing the sybnext column substitution to fail when the compute row is returned. If the commands execute break, sybnext execution is interrupted and returns with TCL_OK. Remaining rows may be fetched with a subsequent sybnext command. If the commands execute return or continue, the remaining commands are skipped and sybnext execution continues with the next row. Sybnext will raise a Tcl error if the commands return an error. Commands should be enclosed in "" or {}.
The sybmsg array index retstatus is set with the return code of a stored procedure, if one was executed in the last SQL command to sybsql; the index nextrow is set to one of several values, depending on the results of sybnext. Refer to the section "SERVER MESSAGE AND ERROR INFORMATION" for information about how the nextrow value is set.
Sybnext performs conversions for image and binary data. Data is returned as a hexadecimal string, without a leading "0x". Use the SQL Server function CONVERT to force a specific conversion.
The sybmsg array index maxtext limits the amount of text or image data returned for each column returned. The default is 32768 bytes.
The sybmsg array index nullvalue can be set to specify the value returned when a column is null. The default is "0" for numeric data, and "" for other datatypes.
The sybmsg array index collengths is set to a Tcl list corresponding to the lengths of the columns; index coltypes is set to a Tcl list corresponding to the types of the columns. Sybcols raises a Tcl error if the handle specified is not open.
Sybwritetext can only be used in a specific sequence with other sybsql commands. Refer to the Sybase DB-Library documentation for dbwritetext() and the DB-Library Reference Supplement discussion on text/image handling.
For example (assume $hand is an open handle, using the "pubs" database):
sybsql $hand "insert
into au_pix (au_id) values (’111-22-3333’)"
sybsql $hand "update au_pix set pic = null where au_id = ’111-22-3333’"
sybsql $hand "select pic from au_pix where au_id = ’111-22-3333’"
sybwritetext $hand au_pix.pic 1 image.file -nolog
An update to an existing text or image column can be made using the last two commands from the above example. Sybwritetext returns a integer number upon successful completion of the number of bytes written to the text/image column.
Sybwritetext raises a Tcl error for a variety of reasons: filename could not be opened or a failure in internal DB-Library routines. Common failures are specifing -nolog when the database does not support nolog; unable to access a valid text pointer due to invalid object or colnum; sybwritetext used out of sequence. Consult sybmsg(msgtext) or sybmsg(dberrstr) for information after a failure.
sybsql $hand "select copy from blurbs where au_id = ’486-29-1786’"
sybreadtext $hand blurb.txt
Sybreadtext returns a decimal number upon successful completion of the number of bytes read from the text/image column. Sybreadtext returns "0" if the last select returned more than one column or no row was returned.
The sybmsg array index maxtext limits the amount of text or image data that can be written to a file by sybreadtext. The default is 32768 bytes.
Sybreadtext raises a Tcl error for a variety of reasons: filename could not be opened, sybreadtext used out of sequence, etc.
Sybtcl creates and maintains a Tcl global array to provide feedback of Sybase server messages, named sybmsg. Sybmsg is also used to communicate with the sybtcl interface routines to specify null return values and text/image limits. In all cases except for nullvalue, fixedchar, floatprec, dateformat and maxtext, each element is reset to null upon invocation of any sybtcl command, and any element affected by the command is set. The sybmsg array is shared among all open sybtcl handles. Sybmsg should be defined with the global statement in any Tcl procedure needing access to sybmsg.
Sybmsg elements:
Tcl errors can also be raised by any sybtcl command if a command’s internal calls to DB-Library routines fail. Sybtcl will return the name of the DB-Lib routine that caused an error.
When executing a stored procedure with sybsql, be sure to include
in the SQL commands a "DECLARE" statement for local variables, and specify
the local variables as "OUTPUT" on the "EXEC" statement. Otherwise, sybretval
will not be able to access the return values. If the return variable names
are to be accessed by sybcols, use the assignment form of "EXEC", e.g.:
If a stored procedure is executed with sybsql, and the procedure uses Transact-SQL "PRINT" statements, check $sybmsg(msgtext) before executing any other Sybtcl commands. Otherwise, the PRINT output will be lost on the next command. Multiple PRINT statements are separated by newlines in $sybmsg(msgtext).
Sybtcl error and message handlers simply populate values in the sybmsg array. The error handler always returns INT_CANCEL.
To use the -nolog feature of sybwritetext, the following option must have been set on the database:
sp_dboption ’yourdbname’, ’select into/bulkcopy’, ’true’
The limit of the number of simultaneous connections is artificial, based on a fixed table in sybtcl. Change the source #define SYBTCLPROCS if more are needed.
The maximum amount of TEXT or IMAGE data returned by sybnext is ultimately dependent on sybtcl’s ability to malloc() maxtext bytes of memory for each TEXT or IMAGE column retrieved. Setting sybmsg(maxtext) to too high a value may cause core dumps or memory shortages. Sybreadtext does not malloc() an area to hold the entire value; instead it retrieves TEXT and IMAGE in chunks and writes to a file. While maxtext limits the amount of data retrieved by sybreadtext, it shouldn’t cause memory shortages as sybnext might.
More items seem to be creeping into the sybmsg array. Perhaps it’s time for a syboption command for passing in nullvalue, maxtext, and whatever else comes along, on a per dbproc basis.