Last Updated: Fri Oct 20 14:27:30 EDT 1995SEE NOTES ON Release 3.1 at bottomINFORMIX 4.x and 5.x SUPPORT FOR TCL 7.0 and TCL 6.7 (2nd Release)------------------------------------------------------------------    This article describes the Informix support added to TCL (It should work    with the new and the old releases of TCL).    The most significant change has been to make all sql commands as minor    commands.    A single major commands called sql has been added.    Thus,        sql_open "select query ..." [?arg] ...    would now be written as        sql open "select query ..." [?arg]    Similarly        sql_fetch       is now      sql fetch        sql_run         is now      sql run    etc.    A few other minor changes (will not affect the external TCL or C calls)    have also been made. It should now compile cleanly with TCL or TK.    The rest of the article is organized as follows            TCL CALLS            TCL EXAMPLES            C CALLS            COMPILING TCL            COMPILING TK            C EXAMPLES            CONCLUSIONS    TCL CALLS available            A single major command called sql has been added. This            command takes additional arguments (minors like open, close etc.).            The minors may be abbreviated (e.g. o for open etc.).            The following TCL calls are available -            sql database "database"                opens a connection to the database specified by the argument                "database". If the argument is "", the the environment                variable DATABASE will be used                RETURNS integer 0 on success, non-zero on failure            sql open    "sql statement" [?arg] [?arg] ..                opens the sql query specified and sets the bind variables                if specified. To see what bind variables are, look at the                description in C CALLS below.                This command compiles the query, allocates space for the                return values and makes it available for execution using                sql fetch.                RETURNS an integer (>= 0) on success, < 0 on failure                This return values is to be treated like an open file                descriptor and should be closed finally to release space.                This is used typically to open a select query.            sql fetch ?fd [1]                fetches a single row of the opened fd. If an optional second                argument with value 1 is specified, then the trailing white                spaces in the list elements are removed.                RETURN value is a TCL list on successful fetch, the NIL list                or "" when the end is reached.            sql close ?fd                closes the compiled query and release all memory associated                with it                RETURNS 0 on success, non-zero on failure            sql reopen ?fd                reopens the query specified by fd so that fetches may be done                from the start again. Uses the old parameters specified for                the open            sql run "sql statement" [?arg] [?arg] ..                executes the sql query specified immediately after setting                the bind variables. Useful for INSERT, UPDATE, DELETE and                other sql calls.                RETURNS 0 on success, non-zero on failure            sql exists table_name column_name column_value ?optional_where                check for existence of column_name in table_name with                value column_value and optionally a where_clause.                RETURNS 0 on success, non-zero on failure                This can be used to validate values quickly without using up                an fd or setting up a sql open, sql fetch, sql close structure.            sql explain ?fd                sets debug on for the query fd. This feature may be used                for debugging and the implementation may vary from database                to database. It may be used to print out queries as they are                executed along with the bind variables etc. The database may                add other options like cost of the query etc.                RETURNS 0 on success, non-zero on failure            sql geterror                RETURNS a string containing a complete description of the                last sql error. This will include the complete text of the                SQL error (and ISAM error if the database uses ISAM) and the                complete sql statement being processed.            Advanced functions:            sql sqlca                RETURNS a TCL list of the sqlca elements            sql sqld ?fd ?type                RETURNS the number of sqld elements present for the                sqlda associated with fd. If ?type is 1, then the sqlda used                is the input sqlda and if ?type is 0, then the sqlda used is                the output sqlda. This is useful to find out the number of                columns fetched from the dynamic query.            sql sqlda ?fd ?type ?num                RETURNS a TCL list containing all information about the                num'th element in the sqlda structure.                If ?type is 1, then the input sqlda is used.                If ?type is 0, then the output sqlda is used.                Information is returned for the ?num'th element.                ?num varies from 0 to [sql sqld ?fd ?type]            sql finish                closes the database opened earlier                RETURNS 0 on success, non-zero on failure            sql getdatabase                returns the database name opened with sql database    EXAMPLES            1)                set fd [sql open                    "select e.*, d.*                    from employee e, department d                    where e.dept_num = d.dept_num"]                set line [sql fetch $fd]                while {$line != ""} {                    puts stdout "values are ($line)"                    set line [sql fetch $fd]                }                sql close $fd            2)                set emp_name "FOO'BAR"                sql run "delete from employee where emp_name = ?" $emp_name            3)                catch {sql database ""} ret                if {$ret != 0} {                    puts stdout "Connect Error: [sql geterror]"                    exit_action                }    C CALLS            The following C calls have been provided (to ensure that the            above TCL sql calls are available).            int sql_database(char *dbname)                Connects to the database specified by dbname or uses                DATABASE environ variable if dbname is "" or cannot be                opened.                Return value: 0 on success, < 0 on failure            int sql_open (char *stmt, int argc, char **argv)                opens the query specified by stmt and set the bind variables                from the argv. This compiles the query and allocates space                for the return values.                BIND VARIABLES:                    bind variables may be thought of as parameters which get                    substituted (like \1, \2 in regsub etc.) when the                    sql query is compiled. The substituted values may contain                    any character like ", ', : * , embedded spaces etc..                    If we do not use bind variables, then each column                    value has to be inspected to ensure that the special                    characters are escaped (using \, etc).                Return value: fd (>= 0) on success, < 0 on failure            int sql_fetch (int fd)                fetch a single row into the allocated space                Use sql_values() to retrieve it.                There is no need to free the sql_values return value.                Return value: 0 on success, > 0 on end, < 0 on error            char **sql_values(int fd, int *numvalues, int dostrip)                Return the values fetched by the previous fetch.                Set the number of argv values in numvalues.                If dostrip is 1, then trailing blanks are stripped from                each value                There is no need to free the return value from this function.                The function manages it by re-allocating space if needed.                Return value: NULL on error, char **argv on success            int sql_close(int fd)                Closes the compiled query and releases all memory associated                with it.                Return value: 0 on success, < 0 on failure            int sql_run(char *stmt, int argc, char **argv)                Calls sql_open, sql_fetch and sql_close returning the                status of sql_fetch.                Return value: 0 on success, < 0 on failure, > 0 on no such                record            int sql_exists(char *table, char *field, char *value, char *where)                Check for existence of field in table with value value and                an optional where clause.                Return value: 0 on success, < 0 on error, > 0 on no such record            char *sql_geterror()                Return a static pointer into the text of the last error            int sql_explain(int fd)                Sets debug on for the query associated with fd            int sql_print(int fd)                Prints internal values from fd structure on stdout                (the command being executed, bind variables, last return value                etc.)            char **sql_sqlca(int *num)                Returns the sqlca structure as an array of character pointers                and sets the number of such pointers in num            int sql_sqld(int fd, int type)                Returns the number of sqld elements associated with fd                If type is 1, then the input sqlda is used.