User's Guide Part V. The SQL Anywhere Programming Interfaces Chapter 36. ODBC ProgrammingThe ODBC interface is defined by a set of function calls, called the ODBC API (Application Programming Interface).
To write ODBC applications for SQL Anywhere, you need:
Although the interface to ODBC is through function calls, database access is specified using SQL statements passed as strings to ODBC functions.
The following fundamental objects are used for every ODBC program. Each object is referenced by a handle.
All access to these objects is through function calls; the application cannot directly access any information about the object from its handle. In the Windows and Windows NT environments, all the function calls are described in full detail in the ODBC API help file (ODBCAPI.HLP) provided with the Windows and Windows NT installations of SQL Anywhere.
Every C source file using ODBC functions must include one of the following lines:
These files all include the main ODBC include file ODBC.H, which defines all of the functions, data types and constant definitions required to write an ODBC program. The file ODBC.H and the environment specific include files are installed in the H subdirectory of the SQL Anywhere installation directory (usually C:\SQLANY50).
Once your program has been compiled, you must link with the appropriate library file to have access to the ODBC functions:
WODBCMCL.LIB, which is a large model Microsoft C library. You will also need the SQL Anywhere interface library DBLIBMCL.LIB.
WODBCBCL.LIB, which is a large model Borland C library. You will also need the SQL Anywhere interface library DBLIBBCL.LIB.
The following is a simple ODBC program:
{HENV env;HDBC dbc;HSTMT stmt;SQLAllocEnv( &env );SQLAllocConnect( env, &dbc );SQLConnect( dbc, "sademo", SQL_NTS,"dba", SQL_NTS, "sql", SQL_NTS );SQLSetConnectOption( dbc, SQL_AUTOCOMMIT, FALSE );SQLAllocStmt( dbc, &stmt );/* Delete all the order items for order 2015 */SQLExecDirect( stmt,"delete from sales_order_items where id=2015",SQL_NTS );/* Use rollback to undo the delete */SQLTransact( env, dbc, SQL_ROLLBACK );SQLFreeStmt( stmt, SQL_DROP );SQLDisconnect( dbc );SQLFreeConnect( dbc );SQLFreeEnv( env );}
SQL_NTS Every string passed to ODBC has a corresponding length. If the length is unknown, you can pass SQL_NTS indicating that it is a 'Null Terminated String' whose end is marked by the null character ('\0').
SQLExecDirect Executes the SQL statement specified in the parameter.
SQLTransact Used to perform COMMIT and ROLLBACK statements marking the end of a transaction. You should not use SQLExecDirect to perform COMMIT or ROLLBACK.
The previous example did not check for any errors. Errors in ODBC are reported using the return value from each of the ODBC API function calls and the SQLError function.
Every ODBC API function returns a RETCODE which is one of the following status codes:
Every environment, connection and statement handle can have one or more errors or warnings associated with it. Each call to SQLError returns the information for one error and removes the information for that error. If you do not call SQLError to remove all errors, the errors are removed on the next function call which passes the same handle as a parameter.
The following program fragment uses SQLError and return codes:
HDBC dbc;HSTMT stmt;RETCODE retcode;UCHAR errmsg[100];. . .retcode = SQLAllocStmt( dbc, &stmt );if( retcode == SQL_ERROR ) {SQLError( env, dbc, SQL_NULL_HSTMT, NULL, NULL,errmsg, sizeof(errmsg), NULL );/* Assume that print_error is defined */print_error( "Failed SQLAllocStmt", errmsg );return;}/* Delete items for order 2015 */retcode = SQLExecDirect( stmt,"delete from sales_order_itemswhere id=2015", SQL_NTS );if( retcode == SQL_ERROR ) {SQLError( env, dbc, stmt, NULL, NULL,errmsg, sizeof(errmsg), NULL );/* Assume that print_error is defined */print_error( "Failed to delete items", errmsg );return;}. . .
Note that each call to SQLError passes in three handles for an environment, connection and statement. The first call uses SQL_NULL_HSTMT to get the error associated with a connection. Similarly, a call with both SQL_NULL_DBC and SQL_NULL_HSTMT will get any error associated with the environment handle.
The return value from SQLError may seem confusing. It returns SQL_SUCCESS if there is an error to report (not SQL_ERROR), and SQL_NO_DATA_FOUND if there are no more errors to report.
The examples pass the null pointer for some of the parameters to SQLError. The help file contains a full description of SQLError and all its parameters.
ODBC cursors are similar to cursors in Embedded SQL (see "Cursors in Embedded SQL"). A cursor is opened using SQLExecute or SQLExecDirect, rows are fetched using SQLFetch or SQLExtendedFetch and the cursor is closed using SQLDropStmt.
To get values from a cursor, the application can use either SQLBindCol before a fetch or SQLGetData after a fetch.
The following code fragment opens and reads a cursor. Error checking has been omitted to make the example easier to read.
. . .HDBC dbc;HSTMT stmt;RETCODE retcode;long emp_id;char emp_lname[20];SQLAllocStmt( dbc, &stmt );SQLExecDirect( stmt,"select emp_id,emp_lnamefrom employee", SQL_NTS );SQLBindCol( stmt, 1, SQL_C_LONG, &emp_id,sizeof(emp_id), NULL );SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,sizeof(emp_lame), NULL );for(;;) {retcode = SQLFetch( stmt );if( retcode == SQL_NO_DATA_FOUND ) break;print_employee( emp_id, emp_lname);}/* Using SQL_CLOSE closes the cursorbut does not free the statement */SQLFreeStmt( stmt, SQL_CLOSE );. . .
Procedures can be both created and called using ODBC. Triggers can also be created. Refer to "Using Procedures, Triggers, and Batches" for a full description of stored procedures and triggers.
There are two types of procedures in SQL Anywhere; those that return result sets and those that do not. Use SQLNumResultCols to tell the difference. The number of result columns will be zero if the procedure does not return a result set. If there is a result set, you can fetch the values using SQLFetch or SQLExtendedFetch just like any other cursor.
Parameters to procedures should be passed using parameter markers (question marks). Use SQLSetParam to assign a storage area for each parameter marker whether it is an INPUT, OUTPUT or INOUT parameter.
The following example creates and calls a procedure. The procedure takes one INOUT parameter, and increments its value. In the example, the variable "num_col" will have the value zero, since the procedure does not return a result set. Error checking has been omitted to make the example easier to read.
HDBC dbc;HSTMT stmt;long i;SWORD num_col;/* Create a procedure */SQLAllocStmt( dbc, &stmt );SQLExecDirect( stmt,"CREATE PROCEDURE Increment( INOUT a INT )" \" BEGIN" \" SET a = a + 1" \" END", SQL_NTS );/* Call the procedure to increment 'i' */i = 1;SQLSetParam( stmt, 1, SQL_C_LONG, SQL_INTEGER, 0,0, &i, NULL );SQLExecDirect( stmt, "CALL Increment( ? )",SQL_NTS );SQLNumResultCols( stmt, &num_col );do_something( i );
The following example calls a procedure that returns a result set. In the example, the variable "num_col" will have the value two, since the procedure returns a result set with two columns. Again, error checking has been omitted to make the example easier to read.
HDBC dbc;HSTMT stmt;SWORD num_col;RETCODE retcode;char emp_id[ 10 ];char emp_lame[ 20 ];/* Create the procedure */SQLExecDirect( stmt,"CREATE PROCEDURE employees()" \" RESULT( emp_id CHAR(10), emp_lname CHAR(20))"\" BEGIN" \" SELECT emp_id, emp_lame FROM employee" \" END", SQL_NTS );/* Call the procedure - print the results */SQLExecDirect( stmt, "CALL employees()", SQL_NTS );SQLNumResultCols( stmt, &num_col );SQLBindCol( stmt, 1, SQL_C_CHAR, &emp_id,sizeof(emp_id), NULL );SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,sizeof(emp_lname), NULL );for( ;; ) {retcode = SQLFetch( stmt );if( retcode == SQL_NO_DATA_FOUND ) {retcode = SQLMoreResults( stmt );if( retcode == SQL_NO_DATA_FOUND ) break;} else {do_something( emp_id, emp_lname );}}
The SQL Anywhere ODBC driver supports all of the ODBC Version 2.5 API functions (Core, Level 1 and Level 2) under Windows 95 and Windows NT. ODBC Version 2.1 support is provided for Windows 3.x. However, the driver cannot handle the following ODBC features:
A sample ODBC program, ODBC.C, is supplied in the CXMP subdirectory of the SQL Anywhere installation directory (usually C:\SQLANY50). The program performs the same actions as the embedded SQL dynamic cursor example program. See "Database examples" for a description of the associated embedded SQL program.
Along with the sample program is a batch file, MAKEALL.BAT, that can be used to compile the sample program for the various environments and compilers supported by SQL Anywhere. For OS/2 the command is MAKEALL.CMD. For QNX, use the shell script makeall.. The format of the command is as follows:
makeall odbc {Platform} {Compiler} [DOS Extender]
The first parameter is "odbc", meaning compile the ODBC example. The same batch file also compiles the embedded SQL programs.
The second parameter is the platform in which the program will be run. The platform can be one of:
The example program ODBC., when compiled for Windows NT, runs optionally as a service.
The two files containing the example code for NT services are the source file NTSVC.C and the header file NTSVC.H. The code allows the linked executable to be run as either a regular executable or as an NT service.
To run the compiled example as an NT service:
When run as a service, the program displays the normal user interface if possible. It also writes the output to the Application Event Log. If it is not possible to start the user interface, the program prints one page of data to the Application Event Log and stops.
This example has been tested with the Watcom C/C++ 10.5 compiler and the Microsoft Visual C++ 2.0 compiler.