/********************************************************************** * FILENAME : UsingSingleParameterArray.c * * DESCRIPTION : * Example to illustrate using a SELECT with * one parameter marker and a parameter array * * ODBC USAGE : * sQLSetStmtAttr () - SQL_ATTR_PARAMSET_SIZE * SQL_ATTR_PARAM_STATUS_PTR * SQL_ATTR_PARAMS_PROCESSED_PTR * SQLBindParameter () - To bind 1 input parameters * SQLBindCol () - To bind 5 output paramaters * SQLExecDirect () - to execute a SELECT statement * For each parameter in the param arrays: * SQLFetch () - to retrieve the results sets * SQLMoreResults () - to initiate the next parameter */ #include <stdio.h> #include <stdlib.h> #include <sql.h> #include <sqlext.h> #include <string.h> #include "util.c" /* * Uses TestTBL1, * * PersonID int NOT NULL IDENTITY(1,1), * FirstName varchar(255) NOT NULL, * LastName varchar(255), * Address varchar(255), * City varchar(255) */ #define PARAM_ARRAY_SIZE 10 #define FIRSTNAME_LEN 256 #define LASTNAME_LEN 256 #define ADDRESS_LEN 256 #define CITY_LEN 256 int main () { SQLHENV henv = SQL_NULL_HENV; // Environment SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle SQLRETURN retcode; // Select statements to return person details from TestTBL1 - // one with param, one without SQLCHAR *stmt = "SELECT PersonID, FirstName, LastName, Address, City " "FROM TestTBL1 " "WHERE PersonID=?"; // Array of person IDs to get list of all Record IDs for SQLINTEGER PersonIDs[] = {1,2,3,4,5,6,7,8,9,10}; // Column binding variables SQLLEN PersonID; SQLLEN PersonID_l; SQLCHAR FirstName [FIRSTNAME_LEN]; SQLLEN FirstName_l; SQLCHAR LastName [LASTNAME_LEN]; SQLLEN LastName_l; SQLCHAR Address [ADDRESS_LEN]; SQLLEN Address_l; SQLCHAR City [CITY_LEN]; SQLLEN City_l; SQLUSMALLINT ParamStatusArray[PARAM_ARRAY_SIZE]; SQLLEN ParamsProcessed=0; // Allocate the ODBC environment and save handle. retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv); CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV); // Notify ODBC that this is an ODBC 3.0 app. retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0); CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV); // Allocate ODBC connection handle and connect. retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC); // Connect to DATASOURCE retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE); CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC); // Allocate statement handle. retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT); // Set parameter set size, status array and params processed pointers retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) PARAM_ARRAY_SIZE, 0); retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, PARAM_ARRAY_SIZE); retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0); // Bind array values of parameter 1 retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, PersonIDs, 0, NULL); // Bind columns to the Record_num Field of the first row in the array retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &PersonID, 0, &PersonID_l); retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, FirstName, FIRSTNAME_LEN, &FirstName_l); retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, LastName, LASTNAME_LEN, &LastName_l); retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, Address, ADDRESS_LEN, &Address_l); retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, City, CITY_LEN, &City_l); // execute stmt retcode = SQLExecDirect (hstmt, stmt, SQL_NTS); printf ("\nretcode SQLExecDirect : %i", (int) retcode); CHECK_ERROR(retcode, "SQLExecDirect(SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT); // Note: The PersonID field is an identifier and therefore at most // we have one record per record set per parameter. Were multiple // records per parameter possible, the SQLFetch should have its own // loop endong with status SQL_NO_DATA, to ensure all matching records // are retrieved. do { retcode = SQLFetch (hstmt); if (retcode != SQL_NO_DATA) { printf ("\n - %i, %.10s %.10s %.10s %.10s", (int) PersonID, FirstName, LastName, Address, City); } // MoreResults decides whether there are any more array // params to process } while (SQLMoreResults(hstmt) == SQL_SUCCESS); exit: printf ("\nComplete.\n"); // Free handles // Statement if (hstmt != SQL_NULL_HSTMT) SQLFreeHandle(SQL_HANDLE_STMT, hstmt); // 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; }
