/**********************************************************************
* FILENAME : SelectWhereInSelect.c
*
* DESCRIPTION :
* Simple example to retrieve records using a SELECT statement
* within a WHERE clause.
*
* ODBC USAGE :
* SQLExecDirect - to execute SELECT statement with Select statement
* within a WHERE clause.
* SQLBindCol - to bind data areas to columns
* SQLFetch - to fetch records until end
*
*/
#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 256
#define FIRSTNAME_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;
SQLCHAR strFirstName[LASTNAME_LEN], strLastName[FIRSTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN], strCity[CITY_LEN];
SQLLEN lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
SQLLEN cPersonId=0, lenPersonId=0;
int i=0;
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set the ODBC version environment attribute
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 connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set login timeout to 5 seconds
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to data source
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(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);
retcode = SQLExecDirect(hstmt,
(SQLCHAR*) "SELECT PersonID, FirstName, LastName, "
"Address, City FROM TestTBL1 WHERE FirstName IN "
"(SELECT FirstName from TestTBL1Copy)", SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);
// Bind columns 1, 2, 3, 4 and 5
retcode = SQLBindCol(hstmt, 1, SQL_C_USHORT, &cPersonId, 2,
(SQLPOINTER) &lenPersonId);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, &strFirstName,
FIRSTNAME_LEN, &lenFirstName);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, &strLastName,
LASTNAME_LEN, &lenLastName);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, &strAddress,
ADDRESS_LEN, &lenAddress);
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, &strCity,
CITY_LEN, &lenCity);
// Fetch and print each row of data until SQL_NO_DATA returned.
for (i=0; ; i++) {
retcode = SQLFetch(hstmt);
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()", hstmt, SQL_HANDLE_STMT);
} else {
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
