/**********************************************************************
* FILENAME : UsingSelectWithParameterArrays.c
*
* DESCRIPTION :
* Example to illustrate using a SELECT with FOUR parameter markers
* and FOUR parameter arrays
*
* ODBC USAGE :
* SQLSetStmtAttr() - SQL_ATTR_PARAM_BIND_TYPE
* SQL_ATTR_PARAMSET_SIZE
* SQL_ATTR_PARAM_STATUS_PTR
* SQL_ATTR_PARAMS_PROCESSED_PTR
* SQLBindParameter() - to bind the 4 SELECT parameters
* SQLBindCol() - to bind the 4 SELECT columns
* SQLExecDirect() - to execute a SELECT statement
* For each parameter in the param arrays:
* While more data
* SQLFetch() - to retrieve the next result set
* SQLMoreResults() - to initiate the next parameter
* Print Param Status Array
*/
#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 statement to return person details from TestTBL2
SQLCHAR *stmt = "SELECT FirstName, LastName, Address, City "
"FROM TestTBL2 "
"WHERE FirstName=? AND LastName=? AND Address=? AND City=?";
// Arrays of data
SQLCHAR ArrFirstNames[PARAM_ARRAY_SIZE][2] =
{"a","b","c","d","e","f","g","h","i","j"};
SQLCHAR ArrLastNames[PARAM_ARRAY_SIZE][2] =
{"a","b","c","d","e","f","g","h","i","j"};
SQLCHAR ArrAddress[PARAM_ARRAY_SIZE][2] =
{"a","b","c","d","e","f","g","h","i","j"};
SQLCHAR ArrCity[PARAM_ARRAY_SIZE][2] =
{"a","b","c","d","e","f","g","h","i","j"};
// Column binding variables
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;
int i;
char * tmp;
char strPersonID[11];
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_ATTR_ODBC_VERSION)",
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=SQLSRV2014;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect(SQLSRV2014)",
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_PARAM_BIND_TYPE,
SQL_PARAM_BIND_BY_COLUMN, 0);
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 parameters 1-4
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 2, 0, ArrFirstNames, 2, NULL);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 2, 0, ArrLastNames, 2, NULL);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 2, 0, ArrAddress, 2, NULL);
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 2, 0, ArrCity, 2, NULL);
// Bind columns 1-4
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, FirstName,
FIRSTNAME_LEN, &FirstName_l);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, LastName,
LASTNAME_LEN, &LastName_l);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, Address,
ADDRESS_LEN, &Address_l);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, City,
CITY_LEN, &City_l);
// execute stmt
retcode = SQLExecDirect (hstmt, stmt, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);
// If you want to look at what is contained on the descriptors,
// un-comment the next line.
// dumpDescriptors ("HSTMT", hstmt, 'Y', 'Y', 1);
// Since the SELECT may find more than one row per parameter set,
// (e.g. match more than one record on 'a,a,a,a' or 'b,b,b,b' etc),
// SQLFetch() is put in a loop until SQL_NO_DATA is returned.
// Without the loop we would only get (at most) one record per parameter
// set because the call to SQLMoreResults() initialises processing for
// the next parameter set (if one exists) and the next call to
// SQLFetch() would then based on that.
do {
retcode=SQL_SUCCESS;
while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLFetch (hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf ("\n - %.10s %.10s %.10s %.10s",
FirstName, LastName, Address, City);
}
}
// We would expect to finish with a status of SQL_NO_DATA
if (retcode != SQL_NO_DATA) {
CHECK_ERROR(retcode, "SQLFetch(SQL_NO_DATA)",
hstmt, SQL_HANDLE_STMT);
}
// MoreResults decides whether there are any more array params to process
// and intiates processing for the next parameter if more exist.
} while (SQLMoreResults(hstmt) == SQL_SUCCESS);
// Check to see which sets of parameters were processed successfully.
printf ("\n\nParameter Status Array :\n");
for (i = 0; i < ParamsProcessed; i++) {
printf(" Parameter Set ");
switch (ParamStatusArray[i]) {
case SQL_PARAM_SUCCESS:
case SQL_PARAM_SUCCESS_WITH_INFO:
printf("%d Success With Info\n", i);
break;
case SQL_PARAM_ERROR:
printf("%d Error\n", i);
break;
case SQL_PARAM_UNUSED:
printf("%d Not processed\n", i);
break;
case SQL_PARAM_DIAG_UNAVAILABLE:
printf("%d Status Unknown\n", i);
break;
}
}
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;
}
Further information
