************************************* * * * DB/C Newsletter * * October 1994 * * * ************************************* Editor's Notes This month's article describes some of the new features in the latest version of the DB/C to SQL interface. These changes are currently implemented for the MS-Windows ODBC interface. They will be implemented in other SQL interfaces so that completely portable SQL access will be possible - regardless of which SQL product or SQL interface is used. There is nothing new to report about the status of the PL/B Standard. We are still waiting for final approval by ANSI. don.wills@swc.com New Information about the DB/C SQL Interface The ODBC interface allows DBCWIN to access an SQL Data Base Management System (DBMS). The ODBC interface is a standard defined by Microsoft. It is currently available only for MS-Windows, but will be available for other environments in the future. The file DBCSQL.DLL contains the ODBC interface code. This file should be in the current working directory, the WINDOWS directory, or one of the directories listed in the PATH environment variable. Before executing an SQL statement from a DB/C program, ODBC drivers should be properly installed, and necessary network requesters should be started. There are three DB/C verbs for using SQL. SQLEXEC executes SQL statements. SQLCODE provides the SQLCODE returned from an SQL statement. And finally, SQLMSG provides the SQLSTATE and any message returned from an SQL statement. The DB/C program "example.prg" provides examples of the following discussions. This file can be found on the DB/C BBS in the SQL directory. The verb SQLEXEC executes SQL statements. It's syntax is as follows: SQLEXEC charexp or SQLEXEC charexp FROM from-list or SQLEXEC charexp INTO into-list or SQLEXEC charexp FROM from-list INTO into-list The charexp contains the SQL statement. It can be either a character literal or character variable. The allowable length of this expression is somewhat variable due to expansion, but it is roughly 1,000 characters. Within this expression, expressions of the form :n, where n is an integer, are replaced with the corresponding variable in the from-list or the into-list. For example, in the expression: SQLEXEC "SELECT :1 USING C1 FROM TABLE" FROM VAR :1 is replaced with the value in VAR before the statement is passed to the DBMS. Care should be taken that variables in the into-list can hold the results, and that variables in the from list are not to large for the table. Of special note, an SQL variable DECIMAL(3,2) is the same size as a DB/C variable NUMBER 1.2. When retrieving results from a DBMS it is necessary to use cursors. This is because a select statement can result in multiple rows of data that must be fetched one at a time into DB/C variables. To use a cursor, the phrase "USING cursor-name" must be in a the statement. The cursor- name may be up to 20 characters long. Up to ten different cursor-names can be declared with select statements. A cursor-name may be redeclared with a new SELECT statement to have a new meaning. To retrieve data after making a SELECT statement, the FETCH statement is used. The FETCH statement should be used as follows: SQLEXEC "FETCH USING cursor-name" INTO into-list This fetches the next row in the table created by the select statement. The FETCH statement may also specify a row to retrieve. In this case the syntax is as follows: SQLEXEC "FETCH row-spec USING cursor-name" INTO into-list The row-specs are as follows: NEXT gets next row PRIOR gets the previous row FIRST gets the first row LAST gets the last row RELATIVE n gets the nth row from the current position ABSOLUTE n gets the nth row from the first row Two other important SQL functions are UPDATE and DELETE. Each has two distinct flavors. They can each be applied to a whole table. For instance: UPDATE table-name SET column-name = value WHERE column-name = value could be implemented as follows: PACK COMMAND WITH "UPDATE ": "PRODUCTS ": "SET PRICE = 12.50 ": "WHERE PRICE = 0" SQLEXEC COMMAND This would set all rows with price $12.50 to have price $0.00. UPDATE and DELETES may also be performed through cursors. To do this, the cursor needs to be made updateable by ending the SELECT statement for the cursor with FOR UPDATE. When updating or deleting through cursors, the current row of the cursor is is updated or deleted. For instance: SQLEXEC "DELETE FROM PRODUCTS WHERE CURRENT OF CURSOR2" will delete the most recently retrieved row from the cursor CURSOR2, and from the table PRODUCTS. Connections to databases are done with the CONNECT statement. For instance: SQLEXEC "CONNECT SERVER1, USER1, PASS1" where SERVER1, USER1 and PASS1 are just examples of the server name, the user name and the password. CONNECT can also be used with no arguments. In this case the user is prompted for the necessary information. SQLEXEC affects flags as follows. If an SQL statement returns an error condition, then the LESS flag is set and the OVER and EQUAL flags are cleared. If the SQL statement is successful, then the EQUAL flag is set and the LESS and OVER flags are cleared. If the SQL statement returns no data, the OVER flag is set and the LESS and EQUAL flags are cleared. This will occur when there are no more rows to fetch from a cursor. NULL values are not completely supported by DB/C. They are returned as zeroes for numeric variables, and character variables with form pointer and length pointer zero. DB/C programs can not write NULL values. This will be remedied in DB/C Release 9.0. The verb SQLMSG places SQLSTATE and any message returned by the DBMS in a character variable. Its syntax is: SQLMSG charvar The verb SQLCODE places the code returned by the DBMS in a numeric variable. The Developers Guide explains how SQLEXEC affects flags. The flags are usually the better way of ascertaing success or failure. DB/C Class Schedule The next DB/C class is scheduled for January 1995. Actual dates are not yet finalized. The class is held in the Oak Brook, Illinois office of Subject, Wills and Company. For more information, contact Judi Tamkevic via email at dbc@swc.com or via telephone at (708) 572-0240.