Subject: A proposal for a standard way to provide for SQL based relational database interface to TCL (with sample interface for INFORMIX). Objective: This article discusses a standard for providing database access directly in TCL (without using pipes to interactive sql programs). The syntax of the C calls which should be provided are also described. Support is provided for bind variables too so that sql queries need not be concatenated with values (causing problems with double quoted strings etc.). A modest amount of robustness is also provided against invalid input. Thus when support is to be added for a new database (like ORACLE, SYBASE etc.), only the C code need be written. The rest of the article is organized as follows TCL CALLS EXAMPLES C CALLS STANDARD COMPILING TK EXAMPLES CONCLUSIONS TCL Calls available 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 STANDARD 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 blanks 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 STANDARD The following C calls should be 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. If type is 0, then the output sqlda is used. This number reflects the number of columns fetched from the dynamic query or the number of bind variables specified. char **sql_sqlda(int fd, int type, int elnum, int *numvalues) Returns array of character pointers (the number of such pointers is set in numvalues) for the sqlda element elnum. This contains information like the column name, type, value etc. type is the same as in sql_sqld elnum varies from 0 to sql_sqld(int fd, int type) int sql_finish() Closes the database opened earlier with sql_database Returns 0 on success, < 0 on failure char *sql_getdatabase() Gets the database name opened with sql_database All the C calls above should ensure that the fd is valid before it proceeds. COMPILING TK To add support to TCL, two files should be provided 1) sql[mod].ec - code containing C calls to database 2) tclsql.c - code for wrappers around sql[mod].ec calls which adds SQL support to TCL. Users who want SQL support in TK should add the call tcl_add_commands(interp, w) in main.c and link the wish interpreter again along with sql[mod].o and tclsql.o EXAMPLES #ifdef TEST main() { int fd, ret; sql_database("test"); { char *argv[] = { "A%"}; fd = sql_open("select emp_name from employee where emp_name like ?", 1, argv); ret = 0; while (ret == 0) { ret = sql_fetch(fd); if (ret == 0) sql_print(fd); } } { char **argv; sql_reopen(fd); sql_fetch(fd); argv = sql_values(fd, &ret, 0); if (!argv) printf("No values for fd%d\n", fd); else { int i; for (i = 0; i < ret; i++) printf("Value %d is (%s)\n", i, argv[i]); } } { char *argv[] = { "19"}; if (sql_exists("employee", "emp_name", 0, 0)==0) printf("At least 1 employee exists\n"); if (sql_exists("employee", "emp_name", "JOLLY'GOOD", 0)==0) printf("Employee JOLLY'GOOD exists\n"); if (sql_exists("employee", "emp_name", 0, "emp_name matches 'A*'")==0) printf("Employee matching A* exists\n"); if (sql_run("set lock mode to wait", 0, 0) == 0) printf("Successful lock mode\n"); if (sql_run("update employee set salary = ?", 1, argv) == 0) printf("Successful update \n"); } } #endif CONCLUSIONS If support for all database SQL access calls are made with the above format, then it will be easy to add support for other vendors and easier to program. I have posted the code files sqlinf.ec tclsql.c to harbor.ecn.purdue.edu If you have any suggestions, please write skumar@netcom.com