/**********************************************************************
* FILENAME : ShowDescriptorContents.c
* DESCRIPTION :
* Example dumps out the descriptors for each of 3 different
* prepared statements
*
* ODBC USAGE :
* Three classes of statement:-
* Insert - with parameters
* Select - with columns
* Select - with parameters and columns
*
* Prepares each statement and dumps out IRD, ARD, IPD and APD
* for each. Uses function DumpDescriptors () in util.c.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define PERSONID_LEN 2
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
void show_error() {
printf("error\n");
}
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt0 = SQL_NULL_HSTMT; // Statement handle
SQLHSTMT hstmt1 = SQL_NULL_HSTMT; // Statement handle
SQLHSTMT hstmt2 = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLLEN cPersonId;
SQLCHAR strFirstName[FIRSTNAME_LEN];
SQLCHAR strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN];
SQLCHAR strCity[CITY_LEN];
SQLLEN lenPersonId=0, lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
SQLSMALLINT NumParams, NumCols;
int i;
//
// Statement test cases
//
// parameters
char stmt0[] = "INSERT INTO TestTBL1 (FirstName, LastName, Address, City)"
"VALUES (?, ?, ?, ?)";
// columns
char stmt1[] = "SELECT PersonID, FirstName, LastName, Address, City"
"FROM TestTBL1";
// columns and parameters
char stmt2[] = "SELECT PersonID FROM TestTBL1 where FirstName=?";
int main () {
int inlenookmarkRec;
// Allocate environment
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Allocate Connection
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC);
// --------------------------------------------------
// Process statement 0 (Needs parameter binding only)
// --------------------------------------------------
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt0);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt0, SQL_HANDLE_STMT);
// Just to force it to create a bookmark record in the descriptors
retcode = SQLSetStmtAttr(hstmt0, SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER)SQL_UB_VARIABLE, 0);
CHECK_ERROR(retcode, "SQLSetStmtAttr(SQL_ATTR_USE_BOOKMARKS)",
hstmt0, SQL_HANDLE_STMT);
// Bind Parameters to all fields
retcode = SQLBindParameter(hstmt0, 1,
SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
FIRSTNAME_LEN, 0,
strFirstName, FIRSTNAME_LEN, &lenFirstName);
CHECK_ERROR(retcode, "SQLBindParameter()", hstmt0, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt0, 2,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LASTNAME_LEN, 0,
strLastName, LASTNAME_LEN, &lenLastName);
CHECK_ERROR(retcode, "SQLBindParameter()", hstmt0, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt0, 3,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, ADDRESS_LEN, 0,
strAddress, ADDRESS_LEN, &lenAddress);
CHECK_ERROR(retcode, "SQLBindParameter()", hstmt0, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt0, 4,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, CITY_LEN, 0,
strCity, CITY_LEN, &lenCity);
CHECK_ERROR(retcode, "SQLBindParameter()", hstmt0, SQL_HANDLE_STMT);
retcode = SQLPrepare(hstmt0, (SQLCHAR*) stmt0, SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(hstmt0)", hstmt0, SQL_HANDLE_STMT);
//
// NumParams is number of parameter records
//
retcode= SQLNumParams(hstmt0, &NumParams);
CHECK_ERROR(retcode, "SQLNumParams(hstmt0)", hstmt0, SQL_HANDLE_STMT);
printf ("\nNo of params in INSERT Statement : %i\n", NumParams);
//
// Now use SQLGetDescRec() to pull out descriptor details
//
inlenookmarkRec=hasBookmarkRecord (hstmt0);
if (inlenookmarkRec) {
printf ("\n** STMT0 HAS BOOKMARK RECORD **\n");
} else {
printf ("\n** STMT0 HAS NO BOOKMARK RECORD **\n");
}
printf ("\nDescriptors Statement 0 (INSERT) Prepare \n");
printf ("\n%s\n", stmt0);
//
// Dump out the header and records of the descriptors
//
dumpDescriptors ("HSTMT 0 ", hstmt0, 'Y', 'Y', inlenookmarkRec);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt0);
hstmt0 = SQL_NULL_HSTMT;
// ------------------------------------------
// Process Statement 1 (Needs column binding only)
// ------------------------------------------
// Setup statement two as if we were to select
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
// Just to force it to create a bookmark record in the descriptors
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER)SQL_UB_VARIABLE, 0);
CHECK_ERROR(retcode, "SQLSetStmtAttr(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
// Bind columns 1, 2, 3, 4 and 5
retcode = SQLBindCol(hstmt1, 1, SQL_C_USHORT, &cPersonId, 2, &lenPersonId);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt1, 2, SQL_C_CHAR, &strFirstName,
FIRSTNAME_LEN, &lenFirstName);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt1, 3, SQL_C_CHAR, &strLastName,
LASTNAME_LEN, &lenLastName);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt1, 4, SQL_C_CHAR, &strAddress,
ADDRESS_LEN, &lenAddress);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt1, 5, SQL_C_CHAR, &strCity,
CITY_LEN, &lenCity);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
retcode = SQLPrepare(hstmt1, (SQLCHAR*) stmt1, SQL_NTS);
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt1, SQL_HANDLE_STMT);
//
// NumCols is number of column records
//
retcode = SQLNumResultCols (hstmt1, &NumCols);
CHECK_ERROR(retcode, "SQLNumResultCols(hstmt1)",
hstmt1, SQL_HANDLE_STMT);
printf ("\nNo of columns in SELECT statement : %i\n", NumCols);
//
// Now use SQLGetDescRec() to pull out descriptor details
//
inlenookmarkRec=hasBookmarkRecord (hstmt1);
if (inlenookmarkRec) {
printf ("\n** STMT1 HAS BOOKMARK RECORD **\n");
} else {
printf ("\n** STMT1 HAS NO BOOKMARK RECORD **\n");
}
printf ("\nDescriptors Statement 1 (SELECT) Prepare \n");
printf ("\n%s\n", stmt1);
//
// Dump out the header and records of the descriptors
//
dumpDescriptors ("HSTMT 1 ", hstmt1, 'Y', 'Y', inlenookmarkRec);
// Read the data and dump out descriptors again
retcode=SQLExecute (hstmt1);
CHECK_ERROR(retcode, "SQLExecute(hstmt1)", hstmt1, SQL_HANDLE_STMT);
printf ("\nDATA :\n");
for (i=0; ; i++) {
retcode = SQLFetch(hstmt1);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf("Record %d : %i %s, %s, %s, %s\n",
i+1, (int) cPersonId, rtrim(strFirstName, ' '),
rtrim(strLastName, ' '), rtrim(strAddress, ' '),
rtrim(strCity, ' '));
} else {
if (retcode != SQL_NO_DATA) {
CHECK_ERROR(retcode, "SQLFetch(hstmt1)",
hstmt1, SQL_HANDLE_STMT);
}
break;
}
}
printf ("EOF :\n\n");
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
hstmt1 = SQL_NULL_HSTMT;
// ---------------------------------------------------------
// Process Statement 2 (Needs both param AND column binding)
// ---------------------------------------------------------
// Setup statement two as if we were to select
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt2);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
//just to force it to create a bookmark record in the descriptors
retcode = SQLSetStmtAttr(hstmt2, SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER)SQL_UB_VARIABLE, 0);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Bind parameter and column
retcode = SQLBindParameter(hstmt2, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, FIRSTNAME_LEN, 0,
strFirstName, FIRSTNAME_LEN, &lenFirstName);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLBindCol(hstmt2, 1, SQL_C_USHORT, &cPersonId, 2, &lenPersonId);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLPrepare(hstmt2, (SQLCHAR*) stmt2, SQL_NTS);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
//
// NumCols is number of column records
//
retcode = SQLNumResultCols (hstmt2, &NumCols);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
printf ("\nNo of columns in statement 2 is: %i\n", NumCols);
//
// NumParams is number of parameter records
//
retcode= SQLNumParams(hstmt2, &NumParams);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
printf ("\nNo of params in statement 2 is : %i\n", NumParams);
//
// Now use SQLGetDescRec() to pull out descriptor details
//
inlenookmarkRec=hasBookmarkRecord (hstmt2);
if (inlenookmarkRec) {
printf ("\n** STMT2 HAS BOOKMARK RECORD **\n");
} else {
printf ("\n** STMT2 HAS NO BOOKMARK RECORD **\n");
}
printf ("\nDescriptors Statement 2 (SELECT) Prepare \n");
printf ("\n%s\n", stmt2);
//
// Dump out the header and records of the descriptors
//
dumpDescriptors ("HSTMT 2 ", hstmt2, 'Y', 'Y', inlenookmarkRec);
printf ("\nThe End.\n");
exit:
printf ("\nComplete.\n");
// Free handles
// Statement 0
if (hstmt0 != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt0);
// Statement 1
if (hstmt1 != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
// Statement 2
if (hstmt2 != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
Further information
