Sybtcl(3Tcl)
Sybtcl - Sybase SQL Server access commands for Tcl
As shipped in IRIX 6.5. First release of IRIX 6.5.
NAME Sybtcl - Sybase SQL Server access commands for Tcl INTRODUCTION 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. SYBTCL COMMANDS sybconnect login-name password ?server? ?appname? Connect to a Sybase server using login-name and password. If server is specified, then an attempt will be made to login to the named Sybase server. If server is not specified, then the environment variable DSQUERY is used to determine a server; if DSQUERY is not set, sybconnect will try to connect to the Sybase server named SYBASE. If an appname is specified, then that value is passed to the server to display during a process list. 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.). sybuse handle ?dbname? Return the database name currently in use. If dbname is specified, then attempt to use the named database. Handle must be a valid handle previously opened with sybconnect. If dbname is used successfully, the dbname is returned. Sybuse raises a Tcl error if the handle specified is not open or the database name specified could not be used. sybsql handle sql-command Send the Sybase Transact SQL statements sql-command to the server. Handle must be a valid handle previously opened with sybconnect. Sybsql will return "REG_ROW" if the SQL statements generated return rows, "NO_MORE_ROWS" if the SQL commands executed, but no rows were returned in the first or only set of results. The sybmsg array index retstatus is set with the return code of a stored procedure, if any; the nextow index is also set to the value returned by sybsql. 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. sybnext handle ?commands? Return the next row from the last SQL statements executed with sybsql as a Tcl list. Handle must be a valid handle previously opened with sybconnect. Sybnext raises a Tcl error if the handle specified is not open. All returned columns are converted to character strings. A null string is returned if there are no more rows in the current set of results. The Tcl list that is returned by sybnext contains the values of the selected columns in the order specified by select. 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. Sybnext interprets @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. 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. 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. sybcols handle Return the names of the columns from the last sybnext or sybretval command as a Tcl list. Sybcols returns the column name used in the SQL select command; a null string for any column that is an aggregate function (count, sum, avg, min, max) in a regular row. A compute row column name is returned as function(column-name). Sybcols may be used after sybretval, in which the output variable names are returned (see notes). 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. sybcancel handle Cancel any pending results from the last sybsql command. Handle must be a valid handle previously opened with sybconnect. Sybcancel may be used before sybnext exhausts all results. Sybcancel raises a Tcl error if the handle specified is not open. sybretval handle Return a Tcl list of the return values from a stored procedure. Handle must be a valid handle previously opened with sybconnect. If a stored procedure also returns rows, sybnext must have previously been called until NO_MORE_ROWS was encountered before sybretval can access the return values. The sybmsg array index retstatus contains the return code from the stored procedure. Sybretval raises a Tcl error if the handle specified is not open. See notes regarding stored procedure output variables. sybwritetext handle object colnum filename ?nolog? Write the contents of a file to a TEXT or IMAGE column. Handle must be a valid handle previously opened with sybconnect. Object is the table and column name in the format table.column. Colnum is the relative position of the column from the last select. Filename is the name of the file that contains the text or image data to write into the column. Text and image writes are logged by default, nolog may be specified to disable logging (the database must have previously been set with a no log option.) 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 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. sybreadtext handle filename Read the contents of a TEXT or IMAGE column and write results into a file. Handle must be a valid handle previously opened with sybconnect. Filename is the name of a file in which to write the text or image data. Sybreadtext can only be used after the successful select of a single text or image column. For example (assume $hand is an open handle, using the "pubs" database): 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. sybclose handle Closes the server connection associated with handle. Handle must be a valid handle previously opened with sybconnect. Sybclose returns a null string. Sybclose raises a Tcl error if the handle specified is not open. SERVER MESSAGE AND ERROR INFORMATION 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, 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: nullvalue can be set by the programmer to indicate the string value returned for any null result. Setting sybmsg(nullvalue) to "default" will return "0" for numeric null data types (integer, float, and money) and a null string for all other data types. Nullvalue is initially set to "default". dateformat can be set by the programmer to indicate formatting for date data types. The dateformat string can contain substitution values or literals. Substitions are made from the list below; other literals are copied verbatim. The default value is null, which will format dates a default format. YYYY four digit year, 1900- YY two digit year, 00-99 MM two digit month, 1-12 MONTH name of month, January-December MON month abbreviation, Jan-Dec DD two digit day, 1-31 hh two digit hour, 0-23 mm two digit minute, 0-59 ss two digit second, 0-59 ms three digit millisecond, 0-999 dy three digit day of year, 0-365 dw one digit day of week, 1-7 (Mon-Sun) maxtext can be set by the programmer to limit the amount of text or image data returned by sybnext and sybreadtext. The default is 32768 bytes. The maximum is 2147483647 bytes. Any value less than or equal to zero is ignored. Any change to maxtext becomes effective on the next call to sybsql. See notes on maxtext usage with sybnext. handle indicates the handle of the last sybtcl command. Handle is set on every sybtcl command (execpt where an invalid handle is used.) nextrow indicates the results of the last SQL command and subsequent next row processing. Nextrow is set by sybsql and sybnext. Possible values are: REG_ROW at least one row is available after execution of sybsql, or the results of sybnext returned a row as a Tcl list. n an integer number, which indicates that last row retrieved by sybnext returned a compute row. The value is the computeid, which is the relative compute statement in the last SQL command executed with sybsql. NO_MORE_ROWS indicates that sybsql executed successfully but no rows are available, or the results of sybnext did not return a row. Sybnext will return a null string. Return values from a stored procedure, if any, are available at this time. If more results are expected, a subsequent execution of sybnext will return the first row, if any, from the next set of results. NO_MORE_RESULTS indicates that the final set of results from the last execution of sybsql have been processed by sybnext. retstatus indicates the return code after a stored procedure has executed. Retstatus is set by sybsql or sybnext, whenever a the results of a stored procedure are available. collengths is a Tcl list of the lengths of the columns returned by sybcols. Numeric columns (Sybase datatypes int, float, etc.) are given by the internal data lengths (e.g., int has a length of 4), character columns lengths are the maximum of any row returned. Collengths is only set by sybcols. coltypes is a Tcl list of the types of the columns returned by sybcols. Coltypes is only set by sybcols. Possible types returned are: char, text, binary, image, tinyint, smallint, int, float, real, bit, money, smallmoney, datetime, smalldatetime. msgno indicates the message number from a Sybase Server message. Can be set by any sybtcl command. Refer to Sybase documentation for interpretation. Since each sybtcl command may invoke several DB-Lib routines, there is a possiblity that several messages may be received from the server. Sybtcl will concatenate all server message numbers received during one sybtcl command, separating individual numbers by newlines. msgtext the message text associated with msgno. Since each sybtcl command may invoke several DB-Lib routines, there is a possiblity that several messages may be received from the server. Sybtcl will concatenate all server messages received during one sybtcl command, separating individual messages by newlines. Output from Transact- SQL PRINT statments are collected in msgtext. severity indicates the severity level from a Sybase Server message or DB- Library routine. Can be set by any sybtcl command. Refer to Sybase documentation for interpretation. svrname indicates the name of the Sybase Server that generated a message. Can be set by any sybtcl command. procname indicates the name of the stored procedure that generated a message. Only set when a stored procedure was executed. Set by sybsql. line indicates the line number of the SQL command or stored procedure that generated a message. Set by sybsql. dberr indicates the error number generated by a DB-Library routine. Can be set by any sybtcl command. Refer to Sybase documentation for interpretation. dberrstr the error text associated with dberr. oserr indicates an operating system specific error number associated with a DB-Library error. Can be set by any sybtcl command. oserrstr the error text associated with oserr. NOTES 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.: declare @local-var datatype exec stored-proc @proc-var-name = @local-var output 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. ENVIRONMENT VARIABLES DSQUERY The default Sybase server. SYBASE The home directory for Sybase files. FILES $SYBASE/interfaces - definitions for Sybase Servers. BUGS 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. AUTHOR Tom Poindexter, Denver Colorado. Version 2.2 released October 1994. The ?commands? option to sybnext was borrowed from my work with Oratcl 2.1, a Tcl interface to the Oracle database product. tpoindex@nyx.cs.du.edu