This topic provides two examples of C-language source code for applications: an example that uses static SQL functions to create a table, add data to it, and select the inserted data; and another example of interactive, ad-hoc query processing.
Microsoft provides two types of header files, one for ASCII data and the other for Unicode data. The following example can use either of the Microsoft ODBC header files.
Static SQL example
The following example constructs SQL statements within the application.
/*************************************************************** Sample Name: Example1.c Author : UNICOM Systems, Inc.
Location : CONSTRUCTING AN APPLICATION Programmer Guide Purpose : Sample example that uses static SQL functions to create a table, add data to it, and select the inserted data.
/************************************************************** Function Name: DrawLine Purpose : To Draw a specified charcter (chr) for specified number of times (len) **************************************************************/ void DrawLine(SQLINTEGER len, SQLCHAR chr) { printf("\n"); while(len > 0) { printf("%c",chr); len--; } printf("\n"); }
/*************************************************************** Function Name: example1 Purpose : Connect to the specified data source and execute the set of SQL Statements ***************************************************************/ SQLINTEGER example1(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *pwd) { SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN rc; SQLINTEGER id; SQLWCHAR drop[MAX_STMT_LEN]; SQLCHAR name[MAX_NAME_LEN+1]; SQLWCHAR create[MAX_STMT_LEN]; SQLWCHAR insert[MAX_STMT_LEN]; SQLWCHAR select[MAX_STMT_LEN]; SQLINTEGER namelen;
/* Allocate environment and connection handles. */ /* Connect to the data source. */ /* Allocate a statement handle. */
/* Free the statement handle. */ rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_STMT, hstmt));
/* Disconnect from the data source. */ rc = SQLDisconnect(hdbc); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_DBC, hdbc));
/* Free the connection handle. */ rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_DBC, hdbc));
/* Free the environment handle. */ rc = SQLFreeHandle(SQL_HANDLE_ENV,henv); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_ENV, henv));
return(0); }
/*************************************************************** Function Name: main Purpose : To Control all operations ***************************************************************/ void main(SQLINTEGER argc, SQLCHAR *argv[]) { puts("\n\t SOLID ODBC Driver 3.51:"); puts("\n\t -Usage of static SQL functions"); puts("\n\t ==============================");
The following example illustrates how an application can determine the nature of the result set prior to retrieving the results.
/*************************************************************** Sample Name : Example2.c(ad-hoc query processing) Author : UNICOM Systems, Inc. Location : CONSTRUCTING AN APPLICATION-Programmer Guide Purpose : To illustrate how an application determines the nature of the result set prior to retrieving results. ***************************************************************/ #if (defined(SS_UNIX) || defined(SS_LINUX)) #include <solidodbc3.h> #else #include <windows.h> #endif
#include <stdio.h>
#ifndef TRUE #define TRUE 1 #endif
#define MAXCOLS 100 #define MAX_DATA_LEN 255
SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt;
/*************************************************************** Function Name : PrintError Purpose : To Display the error associated with the handle ***************************************************************/ SQLINTEGER PrintError(SQLSMALLINT handleType, SQLHANDLE handle) { SQLRETURN rc = SQL_ERROR; SQLCHAR sqlState[6]; SQLCHAR eMsg[SQL_MAX_MESSAGE_LENGTH]; SQLINTEGER nError;
/************************************************************** Function Name: DrawLine Purpose : To Draw a specified character (line) for specified number of times (len) **************************************************************/ void DrawLine(SQLINTEGER len, SQLCHAR line) { printf("\n"); while(len > 0) { printf("%c",line); len--; } printf("\n"); }
/*************************************************************** Function Name: example2 Purpose : Connect to the specified data source and execute the given SQL statement. ***************************************************************/ SQLINTEGER example2(SQLCHAR *sqlstr) { SQLINTEGER i;
/* 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. */
/* 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++) { rc = SQLDescribeCol(hstmt, i + 1, colname, (SQLSMALLINT)sizeof(colname), &colnamelen, &coltype, &collen[i], &scale, &nullable); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){ return(PrintError(SQL_HANDLE_STMT, hstmt)); } /* print column names */ printf("%s\t", colname); rc = SQLBindCol(hstmt, i + 1, SQL_C_CHAR, data[i], sizeof(data[i]), &outlen[i]); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){ return(PrintError(SQL_HANDLE_STMT, hstmt)); } lineLength += 6 + strlen(colname); }
DrawLine(lineLength-6,'-');
while (TRUE) { rc = SQLFetch(hstmt); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO){ nRowCount++; for (i = 0; i < nresultcols; i++) { if (outlen[i] == SQL_NULL_DATA) { strcpy((char *)data[i], "NULL"); } printf("%s\t",data[i]); } printf("\n"); } else { if (rc == SQL_ERROR) PrintError(SQL_HANDLE_STMT, hstmt); break; } } printf("\n\tTotal Rows:%d\n", nRowCount); }
/*************************************************************** Function Name: main Purpose : To Control all operations ***************************************************************/ int __cdecl main(SQLINTEGER argc, SQLCHAR *argv[]) { SQLRETURN rc;
/* execute the following SQL statements */ example2("SELECT * FROM SYS_TABLES"); example2("DROP TABLE TEST_TAB"); example2("CREATE TABLE TEST_TAB(F1 INT, F2 VARCHAR)"); example2("INSERT INTO TEST_TAB VALUES(10, 'SOLID')"); example2("INSERT INTO TEST_TAB VALUES(20, 'MVP')"); example2("UPDATE TEST_TAB SET F2='UPDATED' WHERE F1 = 20"); example2("SELECT * FROM TEST_TAB");
/* Free the statement handle. */ rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_STMT, hstmt));
/* Disconnect from the data source. */ rc = SQLDisconnect(hdbc); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_DBC, hdbc));
/* Free the connection handle. */ rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_DBC, hdbc));
/* Free the environment handle. */ rc = SQLFreeHandle(SQL_HANDLE_ENV, henv); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_ENV, henv));