codepad
[
create a new paste
]
login
|
about
Language:
C
C++
D
Haskell
Lua
OCaml
PHP
Perl
Plain Text
Python
Ruby
Scheme
Tcl
/* 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 ++; } }
Private
[
?
]
Run code
Submit