This chapter provides two examples of C-language source code for applications. For developers writing ODBC-enables applications a summary of development, debugging, installation, and administration tools provided by the ODBC SDK 2.0 is included.
The following sections contain two examples that are written in the C programming language:
- An example that uses static SQL functions to create a table, add data to it, and select the inserted data.
- An example of interactive, ad-hoc query processing.
These examples can use either ODBC header files or SOLID SQL API header files.
The following example constructs SQL statements within the application. The example comments include equivalent embedded SQL calls for illustrative purposes.
#ifdef SOLIDSQLAPI
#include "CLI0DEFS.H"
#include "CLI0CORE.H"
#include "CLI0EXT1.H"
#else
#include "SQL.H"
#include "SQLEXT.H"
#endif #include <string.h> #ifndef NULL
#define NULL 0
#endif #define MAX_NAME_LEN 50
#define MAX_STMT_LEN 100 int print_err(HDBC hdbc, HSTMT hstmt); int example1(server, uid, pwd)
UCHAR * server;
UCHAR * uid;
UCHAR * pwd;
{
HENV henv;
HDBC hdbc;
HSTMT hstmt; SDWORD id;
UCHAR name[MAX_NAME_LEN + 1];
UCHAR create[MAX_STMT_LEN]
UCHAR insert[MAX_STMT_LEN]
UCHAR select[MAX_STMT_LEN]
SDWORD namelen;
RETCODE rc; /* EXEC SQL CONNECT TO :server USER :uid USING :pwd; */
/* Allocate an environment handle. */
/* Allocate a connection handle. */
/* Connect to a data source. */
/* Allocate a statement handle. */ SQLAllocEnv(&henv);
SQLAllocConnect(henv, &hdbc);
rc = SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(print_err(hdbc, SQL_NULL_HSTMT));
SQLAllocStmt(hdbc, &hstmt); /* EXEC SQL CREATE TABLE NAMEID */
/* (ID integer, NAME varchar(50)); */
/* Execute the SQL statement. */ lstrcpy(create, "CREATE TABLE NAMEID (ID INTEGER, NAME
VARCHAR(50))");
rc = SQLExecDirect(hstmt, create, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(print_err(hdbc, hstmt)); /* EXEC SQL COMMIT WORK; */
/* Commit the table creation. */ /* Note that the default transaction mode for drivers */
/* that support SQLSetConnectOption is auto-commit */
/* and SQLTransact has no effect. */ SQLTransact(hdbc, SQL_COMMIT); /* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */
/* Show the use of the SQLPrepare/SQLExecute method: */
/* Prepare the insertion and bind parameters. */
/* Assign parameter values. */
/* Execute the insertion. */ lstrcpy(insert, "INSERT INTO NAMEID VALUES (?, ?)");
if (SQLPrepare(hstmt, insert, SQL_NTS) != SQL_SUCCESS)
return(print_err(hdbc, hstmt));
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, &id, 0, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, MAX_NAME_LEN, 0, name, 0, NULL);
id=500;
lstrcpy(name, "Babbage");
if (SQLExecute(hstmt) != SQL_SUCCESS)
return(print_err(hdbc, hstmt)); /* EXEC SQL COMMIT WORK; */
/* Commit the insertion. */ SQLTransact(hdbc, SQL_COMMIT); /* EXEC SQL DECLARE c1 CURSOR FOR */
/* SELECT ID, NAME FROM NAMEID; */
/* EXEC SQL OPEN c1; */
/* Show the use of the SQLExecDirect method. */
/* Execute the selection. */
/* Note that the application does not declare a cursor. */ lstrcpy(select, "SELECT ID, NAME FROM NAMEID");
if (SQLExecDirect(hstmt, select, SQL_NTS) !=
SQL_SUCCESS)
return(print_err(hdbc, hstmt)); /* EXEC SQL FETCH c1 INTO :id, :name; */
/* Bind the columns of the result set */
/* with SQLBindCol. */
/* Fetch the first row. */ SQLBindCol(hstmt, 1, SQL_C_SLONG, &id, 0, NULL);
SQLBindCol(hstmt, 2, SQL_C_CHAR, name,
(SDWORD)sizeof(name), &namelen);
SQLFetch(hstmt); /* EXEC SQL COMMIT WORK; */
/* Commit the transaction. */ SQLTransact(hdbc, SQL_COMMIT); /* EXEC SQL CLOSE c1; */
/* Free the statement handle. */ SQLFreeStmt(hstmt, SQL_DROP); /* EXEC SQL DISCONNECT; */
/* Disconnect from the data source. */
/* Free the connection handle. */
/* Free the environment handle. */ SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv); return(0);
}
The following example illustrates how an application can determine the nature of the result set prior to retrieving results.
#ifdef SOLIDSQLAPI
#include "CLI0DEFS.H"
#include "CLI0CORE.H"
#include "CLI0EXT1.H"
#else
#include "SQL.H"
#include "SQLEXT.H"
#endif
#include <string.h>
#include <stdlib.h> #define MAXCOLS 100
#define max(a,b) (a>b?a:b) int print_err(HDBC hdbc, HSTMT hstmt);
UDWORD display_size(SWORD coltype, UDWORD collen, UCHAR *colname); example2(server, uid, pwd, sqlstr)
UCHAR * server;
UCHAR * uid;
UCHAR * pwd;
UCHAR * sqlstr;
{
int i;
HENV henv;
HDBC hdbc;
HSTMT hstmt;
UCHAR errmsg[256];
UCHAR colname[32];
SWORD coltype;
SWORD colnamelen;
SWORD nullable;
UDWORD collen[MAXCOLS];
SWORD scale;
SDWORD outlen[MAXCOLS];
UCHAR * data[MAXCOLS];
SWORD nresultcols;
SDWORD rowcount;
RETCODE rc; /* Allocate environment and connection handles. */
/* Connect to the data source. */
/* Allocate a statement handle. */
SQLAllocEnv(&henv);
SQLAllocConnect(henv, &hdbc);
rc = SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS,
pwd, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(print_err(hdbc, SQL_NULL_HSTMT));
SQLAllocStmt(hdbc, &hstmt); /* Execute the SQL statement. */
if (SQLExecDirect(hstmt, sqlstr, SQL_NTS) !=
SQL_SUCCESS)
return(print_err(hdbc, hstmt)); /* See what kind of statement it was. If there are */
/* no result columns, the statement is not a SELECT */
/* statement. If the number of affected rows is */
/* greater than 0, the statement was probably an */
/* UPDATE, INSERT, or DELETE statement, so print */
/* the number of affected rows. If the number of */
/* affected rows is 0, the statement is probably a */
/* DDL statement, so print that the operation was */
/* successful and commit it. */ SQLNumResultCols(hstmt, &nresultcols);
if (nresultcols == 0) {
SQLRowCount(hstmt, &rowcount);
if (rowcount > 0 ) {
printf("%ld rows affected.\n", rowcount);
} else {
printf("Operation successful.\n");
}
SQLTransact(hdbc, SQL_COMMIT); /* Otherwise, display the column names of the result */
/* set and use the display_size() function to */
/* compute the length needed by each data type. */
/* Next, bind the columns and specify all data will */
/* be converted to char. Finally, fetch and print */
/* each row, printing truncation messages as */
/* necessary. */ } else {
for (i = 0; i < nresultcols; i++) {
SQLDescribeCol(hstmt, i + 1, colname,
(SWORD)sizeof(colname), &colnamelen,
&coltype, &collen[i], &scale,
&nullable);
collen[i] = display_size(coltype, collen[i],
colname);
printf("%*.*s", collen[i], collen[i],
colname);
data[i] = (UCHAR *) malloc(collen[i] + 1);
SQLBindCol(hstmt, i + 1, SQL_C_CHAR,
data[i], collen[i], &outlen[i]);
}
while (TRUE) {
rc = SQLFetch(hstmt);
if (rc == SQL_SUCCESS || rc ==
SQL_SUCCESS_WITH_INFO) {
errmsg[0] = '\0';
for (i = 0; i < nresultcols; i++)
if (outlen[i] == SQL_NULL_DATA)
{
lstrcpy(data[i], "NULL");
}
else if (outlen[i] >= collen[i])
{
sprintf(&errmsg[strlen(errmsg)],
"%d chars truncated, col %d\n",
outlen[i] - collen[i] + 1,
colnum);
}
printf("%*.*s ", collen[i], collen[i],
data[i]);
}
printf("\n%s", errmsg);
} else {
break;
}
}
} /* Free the data buffers. */
for (i = 0; i < nresultcols; i++) {
free(data[i]);
} /* Free the statement handle. */
SQLFreeStmt(hstmt, SQL_DROP );
/* Disconnect from the data source. */
SQLDisconnect(hdbc);
/* Free the connection handle. */
SQLFreeConnect(hdbc);
/* Free the environment handle. */
SQLFreeEnv(henv); return(0);
} /******************************************************//* The following function is included for */
/* completeness,but is not relevant for understanding */
/* the function of ODBC. */
/******************************************************/ #define MAX_NUM_PRECISION 15
/* Define max length of char string representation of */
/* number as: = max(precision) + leading sign + E + */
/* exp sign + max exp length */
/* = 15 + 1 + 1 + 1 + 2 */
/* = 15 + 5 */ #define MAX_NUM_STRING_SIZE (MAX_NUM_PRECISION + 5) UDWORD display_size(coltype, collen, colname)
SWORD coltype;
UDWORD collen;
UCHAR * colname;
{
switch (coltype) {
case SQL_CHAR:
case SQL_VARCHAR:
return(max(collen, strlen(colname))); case SQL_SMALLINT:
return(max(6, strlen(colname))); case SQL_INTEGER:
return(max(11, strlen(colname))); case SQL_DECIMAL:
case SQL_NUMERIC:
case SQL_REAL:
case SQL_FLOAT:
case SQL_DOUBLE:
return(max(MAX_NUM_STRING_SIZE, strlen(colname))); /* Note that this function only supports the */ /* core data types. */
default:
printf("Unknown datatype, %d\n", coltype);
return(0);
}
}
The ODBC SDK provides the following tools for application development:
- ODBC Test, an interactive utility that enables you to perform ad hoc and automated testing on drivers. A sample test DLL (the Quick Test) is included which covers basic areas of ODBC driver conformance.
- ODBC Spy, a debugging tool with which you can capture data source information, emulate drivers, and emulate applications.
- Sample applications, including source code and makefiles.
- A #define, ODBCVER, to specify which version of ODBC you want to compile your application with. By default, the SQL.H and SQLEXT.H files include all ODBC 2.0 constants and prototypes. To use only the ODBC 1.0 constants and prototypes, add the following line to your application code before including SQL.H and SQLEXT.H:
#define ODBCVER 0x0100
For additional infomation about the ODBC SDK tools, see the Microsoft ODBC SDK Guide.
Users install ODBC software with a driver-specific setup program (built with the Driver Setup Toolkit that is shipped with the ODBC SDK) or an application-specific setup program. They configure the ODBC environment with the ODBC Administrator (also shipped with the ODBC SDK) or an application-specific administration program. Application developers must decide whether to redistribute these programs or write their own setup and administration programs. For more information about the Driver Setup Toolkit and the ODBC Administrator, see the Microsoft ODBC SDK Guide.
A setup program written by an application developer uses the installer DLL to retrieve information from the ODBC.INF file, which is created by a driver developer and describes the disks on which the ODBC software is shipped. The setup program also uses the installer DLL to retrieve the target directories for the Driver Manager and the drivers, record information about the installed drivers, and install ODBC software.
Administration programs written by application developers use the installer DLL to retrieve information about the available drivers, to specify default drivers, and to configure data sources.
Application developers who write their own setup and administration programs must ship the installer DLL and the ODBC.INF file.
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.