E
Ed Steward
Guest
This example demonstrates basic ODBC functionality. It does all string operations in Unicode. #include windows.h #include stdio.h #include sqlext.h #define MAX_COL_NAME_LEN 255 #define MAX_CONNECT_STRING 2048 typedef struct { SQLWCHAR Name[MAX_COL_NAME_LEN]; SQLSMALLINT NameLen; SQLSMALLINT SqlType; SQLULEN Size; SQLSMALLINT Scale; SQLSMALLINT Nullable; } ColInfoStruct; void DisplayResultSet (HSTMT stmt, boolean printColMetaData); /* Prints Error and Warning Info to console Returns whether the caller can continue prcessing */ boolean CheckReturn ( SQLRETURN CallerReturnCode, SQLSMALLINT HandleType, SQLHANDLE Handle) { SQLRETURN DiagnosticRC = SQL_SUCCESS; SQLSMALLINT RecNumber = 1; SQLWCHAR Sqlstate[7]; SQLINTEGER NativeError; SQLWCHAR MessageText[SQL_MAX_MESSAGE_LENGTH+1]; SQLSMALLINT TextLength; boolean CallerCanContinue; switch (CallerReturnCode) { case SQL_SUCCESS: return true; case SQL_SUCCESS_WITH_INFO: CallerCanContinue = true; break; case SQL_ERROR: CallerCanContinue = false; break; case SQL_INVALID_HANDLE: wprintf (L"\nProgrammer Error. Invalid ODBC Handle!\n"); return false; case SQL_NO_DATA: wprintf (L"\nEnd of data rows\n"); return true; case SQL_STILL_EXECUTING: wprintf (L"\nAsynchronous operation still executing\n"); return true; case SQL_NEED_DATA: wprintf (L"\nParameter Needing Data\n"); // Call SQLParamData to determine which paramter return true; default: wprintf (L"\nProgrammer Error.Unknown Return Code\n"); return false; } while ((DiagnosticRC == SQL_SUCCESS) || (DiagnosticRC == SQL_SUCCESS_WITH_INFO)) { DiagnosticRC = SQLGetDiagRec ( HandleType, Handle, RecNumber++, Sqlstate, &NativeError, MessageText, SQL_MAX_MESSAGE_LENGTH+1, &TextLength); switch (DiagnosticRC) { case SQL_SUCCESS_WITH_INFO: wprintf (L"\nString truncation in SQLGetDiagRec\n"); case SQL_SUCCESS: wprintf (L"\n*** SQLError Occured ***\n\n"); wprintf (L" Native Error Id: %i\n", NativeError); wprintf (L" Msg: %s\n", MessageText); wprintf (L" SQLState: %s\n", Sqlstate); break; case SQL_INVALID_HANDLE: wprintf (L"\nProgrammer Error. Invalid Handle passed to SQLGetDiagRec ***\n\n"); break; case SQL_NO_DATA: wprintf (L"\nNo More Errors\n"); // This should never happen break; default: wprintf (L"\nUnknown Return Code from SQLGetDiagRec\n"); break; } } return CallerCanContinue; } int main (long argc, char *argv[]) { SQLRETURN rc; HENV env = 0; HDBC dbc = 0; HSTMT stmt = 0; boolean connected = false; boolean connecUsingDataSource = true; SQLWCHAR userName[] = L"SCOTT"; SQLWCHAR password[] = L"TIGER"; SQLWCHAR dsn[] = L"ODBCDemo"; SQLWCHAR connectString[] = L"Driver=DataDirect 7.1 Oracle Wire Protocol;host=serverhost;port=1521;sid=cp178;uid=SCOTT;pwd=TIGER"; SQLWCHAR connectStringOut[MAX_CONNECT_STRING]; SQLSMALLINT connectStringOutLen; wprintf (L"*** ODBC Sample Application ***\n\n"); wprintf (L"Hit ENTER to start\n\n"); getchar(); /*************************** ** Initialize Environment ** ****************************/ rc = SQLAllocHandle (SQL_HANDLE_ENV, 0, &env); if (rc != SQL_SUCCESS) { // Can't handle this error in the ODBC way wprintf (L"ODBC Environment Allocation Failed!!!\n"); return -1; } rc = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_ENV, env)) goto cleanup; rc = SQLAllocHandle (SQL_HANDLE_DBC,env, &dbc); if (!CheckReturn(rc, SQL_HANDLE_ENV, env)) goto cleanup; /******************* ** CONNECT TO DB ** ******************/ if (connecUsingDataSource) { // When using SQLConnect, connection options are set // in the DataSource configuration using the ODBC Administrator wprintf (L"Attempting Data Source connection using SQLConnectW %s\n", dsn); rc = SQLConnectW ( dbc, dsn, SQL_NTS, userName, SQL_NTS, password, SQL_NTS); if ((rc == SQL_ERROR) && !CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; connected = true; wprintf (L"Connection Successful to %s\n", dsn); } else { // When using SQLDriverConnect, connection options can be set // in the ConnectStringIn argument wprintf (L"Attempting Driver connection...\n"); rc = SQLDriverConnect ( dbc, 0, connectString, SQL_NTS, connectStringOut, MAX_CONNECT_STRING, &connectStringOutLen, SQL_DRIVER_NOPROMPT); /* Last arg is fDriverCompletion with possible values SQL_DRIVER_NOPROMPT SQL_DRIVER_COMPLETE SQL_DRIVER_PROMPT SQL_DRIVER_COMPLETE_REQUIRED We can only use NOPROMPT here since we don't have a window handle for popping a logon dialog */ if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; connected = true; // Examine the connection string out // It can be used for future connections if (connectStringOutLen != SQL_NULL_DATA) { wprintf (L"Connection String Out: %s\n",connectStringOut); } } // The connection is now established. /***************************** ** DATABASEMETADATA Basics ** *****************************/ // Use the connection handle to get information about the driver and dbms #define VALUE_MAX_LEN 256 SQLWCHAR infoValue[VALUE_MAX_LEN]; SQLSMALLINT valueLen; wprintf (L"\n************************ DRIVER AND DATABASE INFO *****************************\n"); rc = SQLGetInfo (dbc, SQL_USER_NAME, infoValue, VALUE_MAX_LEN, &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; wprintf (L"User Name = %s\n", infoValue); rc = SQLGetInfo (dbc, SQL_DRIVER_VER, infoValue, VALUE_MAX_LEN, &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; wprintf (L"Driver Version = %s\n", infoValue); rc = SQLGetInfo (dbc, SQL_DBMS_NAME, infoValue, VALUE_MAX_LEN, &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; wprintf (L"DBMS Name = %s\n", infoValue); rc = SQLGetInfo (dbc, SQL_DBMS_VER, infoValue, VALUE_MAX_LEN, &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; wprintf (L"DBMS Version = %s\n", infoValue); // Not all info value are strings SQLSMALLINT smallValue; SQLINTEGER integerValue; rc = SQLGetInfo (dbc, SQL_MAX_COLUMN_NAME_LEN, &smallValue, sizeof(smallValue), &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; wprintf (L"Max Column Name Length = %i\n", smallValue); // Some values are bit masks rc = SQLGetInfo (dbc, SQL_UNION, &integerValue, sizeof(integerValue), &valueLen); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; if (integerValue | SQL_U_UNION) { wprintf (L"Union supported\n"); } if (integerValue | SQL_U_UNION_ALL) { wprintf (L"Union All supported\n"); } wprintf (L"*******************************************************************************\n\n"); // Get a statement handle for executing SQL requests rc = SQLAllocHandle (SQL_HANDLE_STMT, dbc, &stmt); if (!CheckReturn(rc, SQL_HANDLE_DBC, dbc)) goto cleanup; /******************** ** TABLE CREATION ** *******************/ // Drop the table. SQLExecDirect (stmt, L"drop table blah", SQL_NTS); // Ignore errors incase the table doesn't exist // Create the table. // The SQL used to create tables is database dependent. // Connections are initially (by default) in autocommit mode. rc = SQLExecDirect (stmt, L"create table blah(intcol int, charcol char(20), tscol timestamp)", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"Table blah created\n"); /****************************************************************** ** TABLE POPULATION, USING SQLPrepare AND PARAM MARKERS ** ******************************************************************/ // Insert a row using literals for the column values. // Note the use of the timestamp escape in the // insert statement. Escape clauses are used to decrease the // database-specific SQL contained in an application. rc = SQLExecDirect (stmt, L"insert into blah values (0, 'first row', {ts '2012-01-01 10:11:12.123456'})", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"Row inserted in to blah table\n"); // Insert 10 rows using parameters rc = SQLPrepare (stmt, L"insert into blah values (?,?,?)", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; SQLINTEGER intval; SQLINTEGER intvalind; SQLWCHAR charval[21]; SQLINTEGER charvalind; TIMESTAMP_STRUCT tsval; SQLINTEGER tsvalind; SYSTEMTIME systime; // Bind once, execute multiple times rc = SQLBindParameter ( stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &intval, sizeof(intval), &intvalind); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; rc = SQLBindParameter ( stmt, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_CHAR, 20, 0, charval, 21*sizeof(SQLWCHAR), &charvalind); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; rc = SQLBindParameter ( stmt, 3, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 26, 6, &tsval, sizeof(TIMESTAMP_STRUCT), &tsvalind); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; for (int i = 1; i = 10; i++) { intval = i; intvalind = sizeof(intval); wsprintf (charval,L"Row %i",i); charvalind = wcslen(charval) * sizeof(SQL_WCHAR); GetSystemTime (&systime); tsval.year = systime.wYear; tsval.month = systime.wMonth; tsval.day = systime.wDay; tsval.hour = systime.wHour; tsval.minute = systime.wMinute; tsval.second = systime.wSecond; // fraction is nanoseconds tsval.fraction = systime.wMilliseconds * 1000; tsvalind = sizeof(tsval); rc = SQLExecute(stmt); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; } wprintf (L"10 rows inserted into blah table\n"); // Tell the driver to forget the parameter bindings // We'll be using the same statement for other SQL rc = SQLFreeStmt (stmt, SQL_UNBIND); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; /**************************** ** SELECTING FROM A TABLE ** ****************************/ rc = SQLExecDirect (stmt, L"select * from blah order by intcol", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"ResultSet contents:"); DisplayResultSet (stmt, true); // Be sure the cursor is closed rc = SQLFreeStmt(stmt, SQL_CLOSE); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; /******************************************* ** CREATING/EXECUTING A STORED PROCEDURE ** *******************************************/ // Drop the procedure. SQLExecDirect(stmt,L"drop procedure blahproc",SQL_NTS); // ignore errors in case the procedure does not exist // Create the procedure SQLExecDirect(stmt, L"create procedure blahproc(id IN int, numRows OUT int) AS " \ L"BEGIN " \ L"SELECT COUNT(*) INTO numRows FROM BLAH where intcol = id;" \ L"END;", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"\nStored procedure created successfully\n"); // Use ODBC escape clause to call procedure. rc = SQLPrepare (stmt, L"{call blahproc(?,?)}", SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; SQLINTEGER inval; SQLINTEGER invalind; SQLINTEGER outval; SQLINTEGER outvalind; inval = 5; invalind = sizeof(inval); outval = -1; // initialize this to show that it changes on the execute outvalind = 0; rc = SQLBindParameter ( stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &inval, sizeof(inval), &invalind); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; rc = SQLBindParameter ( stmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &outval, sizeof(outval), &outvalind); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; rc = SQLExecute (stmt); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"Stored procedure executed successfully\n"); // be sure to check for null value if (outvalind == SQL_NULL_DATA) { wprintf (L"\noutputVal = NULL DATA \n"); } else { wprintf (L"\noutputVal = %i\n",outval); } /********************** ** Catalog Functions ** ***********************/ // Use the SQLTables to query for the created table. rc = SQLTables (stmt, 0, SQL_NTS, 0, SQL_NTS, L"BLAH", SQL_NTS, 0, SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"\nSQLTables result set:"); DisplayResultSet(stmt, false); SQLFreeStmt(stmt, SQL_CLOSE); // Use SQLColumns query for column information // about the created table. rc = SQLColumns (stmt, 0, SQL_NTS, 0, SQL_NTS, L"BLAH", SQL_NTS, 0, SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"\nSQLColumns result set:"); DisplayResultSet(stmt, false); SQLFreeStmt(stmt, SQL_CLOSE); // Use the SQLProcedureColumns to get parameter information // for the created procedure. rc = SQLProcedureColumns (stmt, 0, SQL_NTS, 0, SQL_NTS, L"BLAHPROC", SQL_NTS, 0, SQL_NTS); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) goto cleanup; wprintf (L"\nSQLProcedureColumns result set:"); DisplayResultSet(stmt, false); SQLFreeStmt(stmt, SQL_CLOSE); cleanup: if (stmt) SQLFreeHandle (SQL_HANDLE_STMT, stmt); if (connected) SQLDisconnect (dbc); if (dbc) SQLFreeHandle (SQL_HANDLE_DBC, dbc); dbc = 0; if (dbc) SQLFreeHandle (SQL_HANDLE_ENV, env); wprintf (L"*** Done ***\n"); wprintf (L"Hit ENTER to exit\n\n"); getchar(); return 0; } // End "main" void printRowValues(HSTMT stmt, SQLUSMALLINT numCols, ColInfoStruct* colInfo) { SQLRETURN rc; SQLWCHAR *ColumnValue; SQLLEN ValueLength; for (SQLUSMALLINT i=1; i =numCols; i++) { // Getting everything as string for easy display ColumnValue = new SQLWCHAR[colInfo[i-1].Size + 1]; rc = SQLGetData ( stmt, i, SQL_C_WCHAR, ColumnValue, (colInfo[i-1].Size + 1) * sizeof(SQLWCHAR), &ValueLength); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) { delete ColumnValue; return; } wprintf (L"%s :",colInfo[i-1].Name); // Check for NULL Data if (ValueLength == SQL_NULL_DATA) { wprintf (L" NULL DATA \n"); } else { wprintf (L"%s\n",ColumnValue); } delete [] ColumnValue; } return; } // Prints result set data to the console void DisplayResultSet (HSTMT stmt, boolean printColMetaData) { SQLRETURN rc; SQLSMALLINT NumCols; SQLSMALLINT NumRows; ColInfoStruct *ColInfo; if (!stmt) { return; } rc = SQLNumResultCols (stmt, &NumCols); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) return; ColInfo = new ColInfoStruct[NumCols]; // Get the metadata for (SQLUSMALLINT icol = 0 ; icol NumCols ; icol ++) { // Could use SQLColAttributes rc = SQLDescribeCol ( stmt, icol + 1, ColInfo[icol].Name, MAX_COL_NAME_LEN, &ColInfo[icol].NameLen, &ColInfo[icol].SqlType, &ColInfo[icol].Size, &ColInfo[icol].Scale, &ColInfo[icol].Nullable); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) { delete [] ColInfo; return; } } // Print the metadata. if (printColMetaData) { wprintf (L"****** Column Metadata ********\n"); for (SQLUSMALLINT j = 0; j NumCols; j++) { wprintf (L" Number: %i\n", j+1); wprintf (L" Name: %s\n", ColInfo[j].Name); wprintf (L" SQL Type: "); switch (ColInfo[j].SqlType) { case SQL_CHAR: wprintf (L"SQL_CHAR\n"); break; case SQL_NUMERIC: wprintf (L"SQL_NUMERIC\n"); break; case SQL_DECIMAL: wprintf (L"SQL_DECIMAL\n"); break; case SQL_INTEGER: wprintf (L"SQL_INTEGER\n"); break; case SQL_SMALLINT: wprintf (L"SQL_SMALLINT\n"); break; case SQL_FLOAT: wprintf (L"SQL_FLOAT\n"); break; case SQL_REAL: wprintf (L"SQL_REAL\n"); break; case SQL_DOUBLE: wprintf (L"SQL_DOUBLE\n"); break; case SQL_DATETIME: wprintf (L"SQL_DATETIME\n"); break; case SQL_VARCHAR: wprintf (L"SQL_VARCHAR\n"); break; case SQL_TYPE_DATE: wprintf (L"SQL_TYPE_DATE\n"); break; case SQL_TYPE_TIME: wprintf (L"SQL_TYPE_TIME\n"); break; case SQL_TYPE_TIMESTAMP: wprintf (L"SQL_TYPE_TIMESTAMP\n"); break; default: wprintf (L"SQL_UNKNOWN_TYPE\n"); break; } wprintf (L" Size: %i\n", ColInfo[j].Size); wprintf (L" Scale: %i\n", ColInfo[j].Scale); wprintf (L" Nullable: %i\n", ColInfo[j].Nullable); } wprintf (L"*******************************\n\n"); } NumRows = 0; rc = SQL_SUCCESS; while (SQL_SUCCEEDED(rc)) { rc = SQLFetch (stmt); if (!CheckReturn(rc, SQL_HANDLE_STMT, stmt)) { delete [] ColInfo; return; } if (rc != SQL_NO_DATA_FOUND) { NumRows++; wprintf (L"************************************************************************\n"); wprintf (L"Row DATA:\n"); printRowValues(stmt, NumCols, ColInfo); } } wprintf (L"Num rows in result set = %i\n", NumRows); delete [] ColInfo; return; }
Continue reading...
Continue reading...