/* Standard C/C++ Headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* DB2 ODBC Header */
#include <sqlcli1.h>
/*Global Constants*/
#define MAX_SQL_QUERY_LENGTH 32760
#define MAX_UID_LENGTH 18
#define MAX_PWD_LENGTH 30
int CommandLineArgs(int argc,
char* argv[],
char dbAlias[],
char userName[],
char password[]);
int CLIAppInit(char dbAlias[],
char userName[],
char password[],
SQLHANDLE *EnvHandle,
SQLHANDLE *ConnHandle);
int getEmployeeDetails(SQLHANDLE *EnvHandle,
SQLHANDLE *ConnHandle);
void GetCLIErrorInfo(SQLSMALLINT handleType,
SQLHANDLE handleName);
/* Main Program
*/int main(int argc, char* argv[]) {
/* Handles */
SQLHENV henv = SQL_NULL_HENV; /*Enviroment Handle*/
SQLHDBC hdbc = SQL_NULL_HDBC; /*Connection Handle*/
SQLHSTMT hstmt= SQL_NULL_HSTMT; /*Statement Handle*/
/* Local variables */
char dbAlias [SQL_MAX_DSN_LENGTH + 1];
char userName [MAX_UID_LENGTH + 1];
char password [MAX_PWD_LENGTH + 1];
int returnCode;
SQLRETURN sqlReturnCode = SQL_SUCCESS;
/*Verify The Login Credentials*/
returnCode = CommandLineArgs ( argc,
argv,
dbAlias,
userName,
password);
if (returnCode != 0) {
return returnCode;
}
/*Initialize the CLI Environment and Connect to Database*/
returnCode = CLIAppInit(dbAlias,
userName,
password,
&henv,
&hdbc);
if (returnCode != 0) {
//Free Allocated Handles
return returnCode;
}
returnCode = getEmployeeDetails( &hdbc, /*Connection Handle*/
&hstmt ); /*Statement Handle */
if (returnCode != 0) {
return returnCode;
}
return 0;
}
int CommandLineArgs(int argc,
char* argv[],
char dbAlias[],
char userName[],
char password[]) {
int returnCode = 0;
switch (argc) {
case 4 :
strcpy(dbAlias, argv[1]);
strcpy(userName,argv[2]);
strcpy(password,argv[3]);
break;
default:
printf("\nUSAGE: %s [dbAlias [userid passwd]]\n", argv[0]);
returnCode = 1;
break;
}
return returnCode;
}
int CLIAppInit(char dbAlias[],
char userName[],
char password[],
SQLHANDLE *EnvHandle,
SQLHANDLE *ConnHandle) {
SQLRETURN sqlReturnCode = SQL_SUCCESS;
int returnCode = 0;
SQLSMALLINT StringLength = 0;
SQLCHAR connectionOutString[255];
SQLCHAR connectionInString [255] = "DRIVER={IBM DB2 ODBC DRIVER};DBALIAS=sample;HOSTNAME=db2c_DB2;PORT=50000;PROTOCOL=TCPIP;DSN=sample;UID=db2admin;PWD=whatever;";
/* Allocate an Enviornment Handle */
sqlReturnCode = SQLAllocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
EnvHandle);
if (sqlReturnCode != SQL_SUCCESS){
return -10;
}
/* Set Environment Attributes */
sqlReturnCode = SQLSetEnvAttr(*EnvHandle,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_UINTEGER);
if (sqlReturnCode != SQL_SUCCESS){
// Free Enviornment Handle
GetCLIErrorInfo(SQL_HANDLE_ENV, *EnvHandle);
return -20;
}
/*Allocate an Connection Handle */
sqlReturnCode = SQLAllocHandle(SQL_HANDLE_DBC,
*EnvHandle,
ConnHandle);
if (sqlReturnCode != SQL_SUCCESS){
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_ENV, *EnvHandle);
return -30;
}
/*Connect to the Databse*/
sqlReturnCode = SQLDriverConnect( *EnvHandle,
NULL,
(SQLWCHAR *) connectionInString,
SQL_NTS,
(SQLWCHAR *) connectionOutString,
sizeof(connectionOutString),
&StringLength,
SQL_DRIVER_COMPLETE );
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_DBC, *ConnHandle);
return -40;
}
return 0;
}
int getEmployeeDetails(SQLHANDLE *ConnHandle,
SQLHANDLE *StmtHandle){
SQLCHAR sqlQuery [MAX_SQL_QUERY_LENGTH + 1];
SQLCHAR jobType [10];
SQLCHAR empNo [8];
SQLCHAR lastName [17];
SQLRETURN sqlReturnCode = SQL_SUCCESS;
int returnCode = 0;
memset(sqlQuery , 0 , sizeof(sqlQuery));
memset(jobType , 0 , sizeof(jobType));
memset(empNo , 0 , sizeof(empNo));
memset(lastName , 0 , sizeof(lastName));
/*Allocate an SQL Statement Handle */
sqlReturnCode = SQLAllocHandle(SQL_HANDLE_STMT,
*ConnHandle,
StmtHandle);
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_DBC, *ConnHandle);
return -50;
}
/* Define a SELECT SQL Statement That uses a Paramter
Marker. !! Protect from Buffer Overflow !! */
strcpy((char *) sqlQuery, "SELECT EMPNO, LASTNAME FROM ");
strcat((char *) sqlQuery, "EMPLOYEE WHERE JOB = ?");
/*Prepare the SQL Statement */
sqlReturnCode = SQLPrepare(*StmtHandle,
(SQLCHAR*) sqlQuery,
SQL_NTS);
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -60;
}
sqlReturnCode = SQLBindParameter(*StmtHandle,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_VARCHAR,
sizeof(jobType),
0,
jobType,
sizeof(jobType),
NULL);
if (sqlReturnCode != SQL_SUCCESS){
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -70;
}
/*Populate the Bound Application Variable*/
strcpy((char*) jobType, "DESIGNER");
/*Execute the SQL Statement*/
sqlReturnCode = SQLExecute(*StmtHandle);
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -80;
}
/* If the SQL Statement has executes successfully,
retrieve the results... */
/*Bind the Columns in the result DataSet returned to
Application Variables */
sqlReturnCode = SQLBindCol( *StmtHandle,
1,
SQL_C_CHAR,
(SQLPOINTER) empNo,
sizeof(empNo),
NULL);
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -90;
}
sqlReturnCode = SQLBindCol( *StmtHandle,
2,
SQL_C_CHAR,
(SQLPOINTER) lastName,
sizeof(lastName),
NULL);
if (sqlReturnCode != SQL_SUCCESS){
//Free Connection Handle
//Free Environment Handle
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -100;
}
while (sqlReturnCode != SQL_NO_DATA) {
sqlReturnCode = SQLFetch(*StmtHandle);
if (sqlReturnCode != SQL_SUCCESS){
if (sqlReturnCode == SQL_NO_DATA) {
printf("%-8s %s\n", empNo, lastName);
}
else {
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -110;
}
}
}
/* Commit the Transaction */
sqlReturnCode = SQLEndTran (SQL_HANDLE_DBC,
*ConnHandle,
SQL_COMMIT);
if (sqlReturnCode != SQL_SUCCESS){
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -120;
}
sqlReturnCode = SQLFreeHandle(SQL_HANDLE_STMT,
*StmtHandle);
if (sqlReturnCode != SQL_SUCCESS){
GetCLIErrorInfo(SQL_HANDLE_STMT, *StmtHandle);
return -130;
}
return 0;
}
void GetCLIErrorInfo(SQLSMALLINT handleType,
SQLHANDLE handleName) {
SQLCHAR message [SQL_MAX_MESSAGE_LENGTH + 1 ];
char usrMessage [SQL_MAX_MESSAGE_LENGTH + 1 ];
SQLCHAR sqlState [SQL_SQLSTATE_SIZE + 7 ];
long sqlReturnCode = 0;
SQLSMALLINT length = 0;
SQLSMALLINT index = 1;
int returnCode = 0;
int i,j = 0;
memset(sqlState , 0 , sizeof(sqlState));
memset(message , 0 , sizeof(message));
memset(usrMessage , 0 , sizeof(usrMessage));
while (SQLGetDiagRec(handleType,
handleName,
index,
(SQLWCHAR *) sqlState,
&sqlReturnCode,
(SQLWCHAR *) message,
SQL_MAX_MESSAGE_LENGTH + 1,
&length) != SQL_NO_DATA) {
for ( i = 0, j = 0 ; i < SQL_MAX_MESSAGE_LENGTH; i++) {
if ( message[i] != '\0') {
usrMessage [j] = message[i];
j++;
}
}
printf("\n SQLSTATE = %s\n", sqlState);
printf(" Native Error Code = %ld\n" , sqlReturnCode);
printf(" Message = %s\n" , usrMessage);
index ++;
}
}