static void test_rows(void) { const rows_set_t *p; SQLULEN len; SQLUINTEGER *ids = MALLOC_N(SQLUINTEGER,ARRAY_SIZE); SQLLEN *id_lens = MALLOC_N(SQLLEN,ARRAY_SIZE); unsigned long int h, l; unsigned int n; for (n = 0; n < ARRAY_SIZE; ++n) { ids[n] = n; id_lens[n] = 0; } /* test setting just some test pointers */ set_ird_params1(int2ptr(0x01020304)); check_ird_params(); set_ird_params2(int2ptr(0xabcdef12)); check_ird_params(); /* now see results */ for (p = row_set; ; ++p) { const char *test_name = NULL; odbc_reset_statement(); len = 0xdeadbeef; len <<= 16; len <<= 16; len |= 12345678; if (*p) (*p)(&len); check_ird_params(); #if 0 CHKSetStmtAttr(SQL_ATTR_PARAMSET_SIZE, (void *) int2ptr(ARRAY_SIZE), 0, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0, ids, 0, id_lens, "S"); #endif CHKBindCol(1, SQL_C_ULONG, ids, 0, id_lens, "S"); if (*p) { CHKSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE, (void *) int2ptr(ARRAY_SIZE), 0, "S"); odbc_command("SELECT DISTINCT i FROM #tmp1"); SQLFetch(odbc_stmt); test_name = "SQLSetStmtAttr"; } else { CHKSetStmtAttr(SQL_ROWSET_SIZE, (void *) int2ptr(ARRAY_SIZE), 0, "S"); odbc_command("SELECT DISTINCT i FROM #tmp1"); CHKExtendedFetch(SQL_FETCH_NEXT, 0, &len, NULL, "S"); test_name = "SQLExtendedFetch"; } SQLMoreResults(odbc_stmt); l = len; len >>= 16; h = len >> 16; l &= 0xfffffffflu; if (h != 0 || l != 2) { fprintf(stderr, "Wrong number returned in rows high %lu(0x%lx) low %lu(0x%lx) test %s\n", h, h, l, l, test_name); exit(1); } if (!*p) break; } free(ids); free(id_lens); }
/* --------------------------------------------------------- TestSQLTransact --------------------------------------------------------- */ PassFail TestSQLTransact(TestInfo *pTestInfo) { TEST_DECLARE; RETCODE returncode; TCHAR Heading[MAX_STRING_SIZE]; SQLHANDLE henv; SQLHANDLE hdbc; SQLHANDLE hstmt; TCHAR *ExecDirStr[6]; UWORD fType[] = { SQL_ROLLBACK,SQL_COMMIT }; TCHAR *TypeDesc[] = { _T("SQL_ROLLBACK"),_T("SQL_COMMIT") }; TCHAR *Output; SQLLEN OutputLen; struct { SWORD ExeRes[2]; SWORD FetchRes[2]; TCHAR *DataRes[2]; } CheckRes[] = { {SQL_ERROR,SQL_SUCCESS,SQL_NO_DATA_FOUND,SQL_NO_DATA_FOUND,_T(""),_T("")}, {SQL_SUCCESS,SQL_SUCCESS,SQL_NO_DATA_FOUND,SQL_SUCCESS,_T(""),_T("--")}, {SQL_SUCCESS,SQL_SUCCESS,SQL_SUCCESS,SQL_SUCCESS,_T("--"),_T("--")}, {SQL_SUCCESS,SQL_SUCCESS,SQL_SUCCESS,SQL_NO_DATA_FOUND,_T("--"),_T("")}, {SQL_SUCCESS,SQL_ERROR,SQL_NO_DATA_FOUND,SQL_NO_DATA_FOUND,_T(""),_T("")} }; int i = 0, j = 0, iend = 5, jend = 1, commit_on_off = 0; //=========================================================================================================== var_list_t *var_list; var_list = load_api_vars(_T("SQLTransact"), charset_file); if (var_list == NULL) return FAILED; ExecDirStr[0] = var_mapping(_T("SQLTransact_ExecDirStr_0"), var_list); ExecDirStr[1] = var_mapping(_T("SQLTransact_ExecDirStr_1"), var_list); ExecDirStr[2] = var_mapping(_T("SQLTransact_ExecDirStr_2"), var_list); ExecDirStr[3] = var_mapping(_T("SQLTransact_ExecDirStr_3"), var_list); ExecDirStr[4] = var_mapping(_T("SQLTransact_ExecDirStr_4"), var_list); ExecDirStr[5] = var_mapping(_T("SQLTransact_ExecDirStr_5"), var_list); CheckRes[1].DataRes[1] = var_mapping(_T("SQLTransact_Insert"), var_list); CheckRes[2].DataRes[0] = var_mapping(_T("SQLTransact_Insert"), var_list); CheckRes[2].DataRes[1] = var_mapping(_T("SQLTransact_Update"), var_list); CheckRes[3].DataRes[0] = var_mapping(_T("SQLTransact_Update"), var_list); //======================================================================================================== LogMsg(LINEBEFORE+SHORTTIMESTAMP,_T("Begin testing API => SQLTransact.\n")); TEST_INIT; TESTCASE_BEGIN("Initializing SQLTransact test environment\n"); // if(!FullConnect(pTestInfo)) { LogMsg(NONE,_T("Unable to connect\n")); TEST_FAILED; TEST_RETURN; } henv = pTestInfo->henv; hdbc = pTestInfo->hdbc; hstmt = (SQLHANDLE)pTestInfo->hstmt; returncode = SQLAllocStmt((SQLHANDLE)hdbc, &hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLAllocStmt")) { LogAllErrors(henv,hdbc,hstmt); FullDisconnect(pTestInfo); TEST_FAILED; TEST_RETURN; } for (commit_on_off = 0; commit_on_off < 10; commit_on_off++) { returncode = SQLSetConnectOption((SQLHANDLE)hdbc,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_ON); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLSetConnectOption")) { LogAllErrors(henv,hdbc,hstmt); FullDisconnect(pTestInfo); TEST_FAILED; TEST_RETURN; } TESTCASE_END; SQLExecDirect(hstmt, (SQLTCHAR*)ExecDirStr[4], SQL_NTS); /* CLEANUP */ for (i = 0; i <= (iend-1); i++) { _stprintf(Heading,_T("Test Positive Functionality of SQLTransact while Autocommit is ON and executing\n")); _tcscat(Heading, ExecDirStr[i]); _tcscat(Heading, _T("\n")); TESTCASE_BEGINW(Heading); returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i],SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[4],SQL_NTS); /* CLEANUP */ returncode = SQLSetConnectOption((SQLHANDLE)hdbc,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLSetConnectOption")) { LogAllErrors(henv,hdbc,hstmt); FullDisconnect(pTestInfo); TEST_FAILED; TEST_RETURN; } TESTCASE_END; for (i = 0; i <= (iend-1); i++) { for (j = 0; j <= jend; j++) { _stprintf(Heading,_T("Test Positive Functionality of SQLTransact while Autocommit is OFF and executing\n")); _tcscat(Heading, ExecDirStr[i]); _tcscat(Heading, _T(" & ")); _tcscat(Heading, TypeDesc[j]); _tcscat(Heading, _T("\n")); TESTCASE_BEGINW(Heading); //MAY 9, 2014 - TURNING AUTOCOMMIT ON FOR DDLs FOR TRAF, AS DDLs ARE NOT SUPPORTED WITH AUTOCOMMIT OFF if (i == 4 || i == 0) SQLSetConnectOption((SQLHANDLE)hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON); returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i],SQL_NTS); SQLSetConnectOption((SQLHANDLE)hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); if (!CHECKRC(SQL_SUCCESS, returncode, "SQLExecDirect")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else { returncode=SQLTransact((SQLHANDLE)henv,(SQLHANDLE)hdbc,fType[j]); Sleep(2); // tmf rollback is slower. if(!CHECKRC(SQL_SUCCESS,returncode,"SQLTransact")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[iend],SQL_NTS); if(!CHECKRC(CheckRes[i].ExeRes[j],returncode,"SQLExecDirect")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else { if (returncode == SQL_SUCCESS || returncode == SQL_SUCCESS_WITH_INFO) { Output = (TCHAR *)malloc(NAME_LEN); returncode=SQLBindCol(hstmt,1,SQL_C_TCHAR,Output,NAME_LEN,&OutputLen); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else { returncode = SQLFetch(hstmt); if(!CHECKRC(CheckRes[i].FetchRes[j],returncode,"SQLFetch")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else { if (returncode != SQL_NO_DATA_FOUND && returncode != SQL_ERROR) { if (_tcscspn(CheckRes[i].DataRes[j],Output) == 0) { LogMsg(NONE,_T("expect: %s and actual: %s are matched\n"),Output,CheckRes[i].DataRes[j]); } else { LogMsg(NONE,_T("expect: %s and actual: %s are not matched\n"),Output,CheckRes[i].DataRes[j]); TEST_FAILED; } } } free(Output); SQLFreeStmt(hstmt,SQL_CLOSE); } } } } TESTCASE_END; }/* end j loop */ }/* end i loop */ } //======================================================================================================== SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[4],SQL_NTS); /* CLEANUP */ returncode = SQLDisconnect((SQLHANDLE)hdbc); if(!CHECKRC(SQL_ERROR,returncode,"SQLDisconnect")) { LogAllErrorsVer3(henv,hdbc,hstmt); TEST_FAILED; } // Free the open transactions. returncode=SQLTransact((SQLHANDLE)henv,(SQLHANDLE)hdbc,SQL_ROLLBACK); Sleep(2); returncode=FullDisconnect(pTestInfo); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLFullDisconnect")) { LogAllErrorsVer3(henv,hdbc,hstmt); TEST_FAILED; } LogMsg(SHORTTIMESTAMP+LINEAFTER,_T("End testing API => SQLTransact.\n")); free_list(var_list); TEST_RETURN; }
svalue_t * f_sql_fetch( svalue_t * argv, int argc ) { #if ODBC_DEBUG & DEBUG_FUNC printf( "call f_sql_fetch( )\n" ); #endif int id, method = 0; hDBC * handle; SQLRETURN ret; STORE_DOUBLE_USED; switch( argc ) { case 2 : TYPE_TEST2( argv, T_NUMBER ); method = argv->u.number; free_svalue( argv ); argv--; case 1 : TYPE_TEST1( argv, T_NUMBER ); id = argv->u.number; free_svalue( argv ); break; default: errorf( "Too many arguments to sql_fetch().\n" ); return( NULL ); } if ( !(handle = get_db_connection_by_id( id )) ) { errorf( "Illegal handle for database.\n" ); return( NULL ); } if ( !handle->hStmt ) { put_number( argv, 0 ); return( argv ); } //printf( "\nFetching ....\n" ); ret = SQLFetch( handle->hStmt ); if ( ret == SQL_NO_DATA ) { //printf( "NO_DATA\n" ); put_number( argv, 0 ); return( argv ); } else if ( !SQL_SUCCEEDED( ret ) ) { put_number( argv, 0 ); return( argv ); } if ( !handle->columns ) { put_number( argv, 0 ); return( argv ); } if ( !method ) { //fetch as array put_array( argv, fetch_into_vector( handle ) ); } else { //fetch as mapping put_mapping( argv, fetch_into_mapping( handle ) ); } #if ODBC_DEBUG & DEBUG_FUNC printf( "ret f_sql_fetch( )\n" ); #endif return( argv ); }
/* * Test program to run on the connected database */ int ODBC_Test () { SQLTCHAR request[4096]; SQLTCHAR fetchBuffer[1024]; char buf[4096]; size_t displayWidths[MAXCOLS]; size_t displayWidth; short numCols; short colNum; SQLTCHAR colName[50]; SQLSMALLINT colType; SQLULEN colPrecision; SQLLEN colIndicator; SQLSMALLINT colScale; SQLSMALLINT colNullable; unsigned long totalRows; unsigned long totalSets; int i; SQLRETURN sts; while (1) { /* * Ask the user for a dynamic SQL statement */ printf ("\nSQL>"); if (fgets (buf, sizeof (buf), stdin) == NULL) break; #ifndef UNICODE strcpy ((char *) request, (char *) buf); #else strcpy_A2W (request, buf); #endif request[TXTLEN (request) - 1] = TEXTC ('\0'); if (request[0] == TEXTC ('\0')) continue; /* * If the user just types tables, give him a list */ if (!TXTCMP (request, TEXT ("tables"))) { if (SQLTables (hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0) != SQL_SUCCESS) { ODBC_Errors ("SQLTables(tables)"); continue; } } /* * If the user just types qualifiers, give him a list */ else if (!TXTCMP (request, TEXT ("qualifiers"))) { if (SQLTables (hstmt, TEXT ("%"), SQL_NTS, TEXT (""), 0, TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS) { ODBC_Errors ("SQLTables(qualifiers)"); continue; } } /* * If the user just types owners, give him a list */ else if (!TXTCMP (request, TEXT ("owners"))) { if (SQLTables (hstmt, TEXT (""), 0, TEXT ("%"), SQL_NTS, TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS) { ODBC_Errors ("SQLTables(owners)"); continue; } } /* * If the user just types "types", give him a list */ else if (!TXTCMP (request, TEXT ("types"))) { if (SQLTables (hstmt, TEXT (""), 0, TEXT (""), 0, TEXT (""), 0, TEXT ("%"), SQL_NTS) != SQL_SUCCESS) { ODBC_Errors ("SQLTables(types)"); continue; } } /* * If the user just types "datatypes", give him a list */ else if (!TXTCMP (request, TEXT ("datatypes"))) { if (SQLGetTypeInfo (hstmt, 0) != SQL_SUCCESS) { ODBC_Errors ("SQLGetTypeInfo"); continue; } } else if (!TXTCMP (request, TEXT ("reconnect"))) { if (ODBC_Reconnect()) return -1; continue; } #if defined (unix) else if (!TXTCMP (request, TEXT ("environment"))) { extern char **environ; int i; for (i = 0; environ[i]; i++) fprintf (stderr, "%03d: [%s]\n", i, environ[i]); continue; } #endif else if (!TXTCMP (request, TEXT ("quit")) || !TXTCMP (request, TEXT ("exit"))) break; /* If you want to quit, just say so */ else { /* * Prepare & Execute the statement */ if (SQLPrepare (hstmt, (SQLTCHAR *) request, SQL_NTS) != SQL_SUCCESS) { ODBC_Errors ("SQLPrepare"); continue; } if ((sts = SQLExecute (hstmt)) != SQL_SUCCESS) { ODBC_Errors ("SQLExec"); if (sts != SQL_SUCCESS_WITH_INFO) continue; } } /* * Loop through all the result sets */ totalSets = 1; do { /* * Get the number of result columns for this cursor. * If it is 0, then the statement was probably a select */ if (SQLNumResultCols (hstmt, &numCols) != SQL_SUCCESS) { ODBC_Errors ("SQLNumResultCols"); goto endCursor; } if (numCols == 0) { SQLLEN nrows = 0; SQLRowCount (hstmt, &nrows); printf ("Statement executed. %ld rows affected.\n", nrows > 0 ? (long) nrows : 0L); goto endCursor; } if (numCols > MAXCOLS) { numCols = MAXCOLS; fprintf (stderr, "NOTE: Resultset truncated to %d columns.\n", MAXCOLS); } /* * Get the names for the columns */ putchar ('\n'); for (colNum = 1; colNum <= numCols; colNum++) { /* * Get the name and other type information */ if (SQLDescribeCol (hstmt, colNum, (SQLTCHAR *) colName, NUMTCHAR (colName), NULL, &colType, &colPrecision, &colScale, &colNullable) != SQL_SUCCESS) { ODBC_Errors ("SQLDescribeCol"); goto endCursor; } /* * Calculate the display width for the column */ switch (colType) { case SQL_VARCHAR: case SQL_CHAR: case SQL_WVARCHAR: case SQL_WCHAR: case SQL_GUID: displayWidth = colPrecision; break; case SQL_BINARY: displayWidth = colPrecision * 2; break; case SQL_LONGVARCHAR: case SQL_WLONGVARCHAR: case SQL_LONGVARBINARY: displayWidth = 30; /* show only first 30 */ break; case SQL_BIT: displayWidth = 1; break; case SQL_TINYINT: case SQL_SMALLINT: case SQL_INTEGER: case SQL_BIGINT: displayWidth = colPrecision + 1; /* sign */ break; case SQL_DOUBLE: case SQL_DECIMAL: case SQL_NUMERIC: case SQL_FLOAT: case SQL_REAL: displayWidth = colPrecision + 2; /* sign, comma */ break; #ifdef SQL_TYPE_DATE case SQL_TYPE_DATE: #endif case SQL_DATE: displayWidth = 10; break; #ifdef SQL_TYPE_TIME case SQL_TYPE_TIME: #endif case SQL_TIME: displayWidth = 8; break; #ifdef SQL_TYPE_TIMESTAMP case SQL_TYPE_TIMESTAMP: #endif case SQL_TIMESTAMP: displayWidth = 19; if (colScale > 0) displayWidth = displayWidth + colScale + 1; break; default: displayWidths[colNum - 1] = 0; /* skip other data types */ continue; } if (displayWidth < TXTLEN (colName)) displayWidth = TXTLEN (colName); if (displayWidth > NUMTCHAR (fetchBuffer) - 1) displayWidth = NUMTCHAR (fetchBuffer) - 1; displayWidths[colNum - 1] = displayWidth; /* * Print header field */ #ifdef UNICODE printf ("%-*.*S", displayWidth, displayWidth, colName); #else printf ("%-*.*s", displayWidth, displayWidth, colName); #endif if (colNum < numCols) putchar ('|'); } putchar ('\n'); /* * Print second line */ for (colNum = 1; colNum <= numCols; colNum++) { for (i = 0; i < displayWidths[colNum - 1]; i++) putchar ('-'); if (colNum < numCols) putchar ('+'); } putchar ('\n'); /* * Print all the fields */ totalRows = 0; while (1) { #if (ODBCVER < 0x0300) int sts = SQLFetch (hstmt); #else int sts = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 1); #endif if (sts == SQL_NO_DATA_FOUND) break; if (sts != SQL_SUCCESS) { ODBC_Errors ("Fetch"); break; } for (colNum = 1; colNum <= numCols; colNum++) { /* * Fetch this column as character */ #ifdef UNICODE sts = SQLGetData (hstmt, colNum, SQL_C_WCHAR, fetchBuffer, NUMTCHAR (fetchBuffer), &colIndicator); #else sts = SQLGetData (hstmt, colNum, SQL_C_CHAR, fetchBuffer, NUMTCHAR (fetchBuffer), &colIndicator); #endif if (sts != SQL_SUCCESS_WITH_INFO && sts != SQL_SUCCESS) { ODBC_Errors ("SQLGetData"); goto endCursor; } /* * Show NULL fields as **** */ if (colIndicator == SQL_NULL_DATA) { for (i = 0; i < displayWidths[colNum - 1]; i++) fetchBuffer[i] = TEXTC ('*'); fetchBuffer[i] = TEXTC ('\0'); } #ifdef UNICODE printf ("%-*.*S", displayWidths[colNum - 1], displayWidths[colNum - 1], fetchBuffer); #else printf ("%-*.*s", displayWidths[colNum - 1], displayWidths[colNum - 1], fetchBuffer); #endif if (colNum < numCols) putchar ('|'); } putchar ('\n'); totalRows++; } printf ("\n result set %lu returned %lu rows.\n\n", totalSets, totalRows); totalSets++; } while ((sts = SQLMoreResults (hstmt)) == SQL_SUCCESS); if (sts == SQL_ERROR) ODBC_Errors ("SQLMoreResults"); endCursor: #if (ODBCVER < 0x0300) SQLFreeStmt (hstmt, SQL_CLOSE); #else SQLCloseCursor (hstmt); #endif } return 0; }
void PeterRoosenTracksConverter::run_converter(bool status) { SQLRETURN sql_return; SQLHSTMT hstmt; sql_return = SQLAllocHandle(SQL_HANDLE_STMT, _connection, &hstmt); if (sql_return != SQL_SUCCESS) { std::cout << "Allocating statement handle failed." << std::endl; } double x1, y1, x2, y2; SQLINTEGER x1_ind, y1_ind, x2_ind, y2_ind; sql_return = SQLBindCol(hstmt, 1, SQL_C_DOUBLE, &x1, sizeof(double), &x1_ind); if (sql_return != SQL_SUCCESS) { std::cout << "Binding to column 1 failed." << std::endl; } sql_return = SQLBindCol(hstmt, 2, SQL_C_DOUBLE, &y1, sizeof(double), &y1_ind); if (sql_return != SQL_SUCCESS) { std::cout << "Binding to column 2 failed." << std::endl; } sql_return = SQLBindCol(hstmt, 3, SQL_C_DOUBLE, &x2, sizeof(double), &x2_ind); if (sql_return != SQL_SUCCESS) { std::cout << "Binding to column 3 failed." << std::endl; } sql_return = SQLBindCol(hstmt, 4, SQL_C_DOUBLE, &y2, sizeof(double), &y2_ind); if (sql_return != SQL_SUCCESS) { std::cout << "Binding to column 4 failed." << std::endl; } std::string stmt_string("SELECT x1, y1, x2, y2 FROM tracks WHERE id < 100000 ORDER BY id ASC;"); sql_return = SQLExecDirect(hstmt, (SQLCHAR*)(stmt_string.c_str()), SQL_NTS); if (sql_return != SQL_SUCCESS) { std::cout << "Executing SQL statement failed." << std::endl; } // init positions: double old_x1 = -1.0; double old_x2 = -1.0; double old_y1 = -1.0; double old_y2 = -1.0; enum CoordOrder { COORD_ORDER_1TO2, COORD_ORDER_2TO1, UNKNOWN }; CoordOrder coord_order = UNKNOWN; // done. // init time (to 2000-01-01 00:00:00): tm* current_time = new tm; current_time->tm_sec = 0; current_time->tm_min = 0; current_time->tm_hour = 0; current_time->tm_mday = 1; current_time->tm_mon = 0; current_time->tm_year = 100; time_t count = mktime(current_time); // done. // init and open output file: std::ofstream output_file; output_file.setf(std::ios::fixed); output_file.precision(4); std::string init_filename(_output_file); init_filename.append("0"); output_file.open(init_filename.c_str()); int file_counter = 1; // done. // init step size: const int STEP_SIZE = 10; // done. // loop over result set: while ( (sql_return = SQLFetch(hstmt)) != SQL_NO_DATA) { if ( (status) && (file_counter % 10000 == 0) ) std::cout << file_counter << std::endl; if (sql_return == SQL_ERROR) std::cout << "Fetching column " << count + 1 << " failed." << std::endl; /* if (output_file.tellp() >= _max_output_file_size) { output_file.close(); std::stringstream number; number << file_counter; ++file_counter; std::string filename(_output_file); filename.append(number.str()); output_file.open(filename.c_str()); coord_order = UNKNOWN; }*/ if (coord_order == UNKNOWN) { if ( (old_x2 == x1) && (old_y2 == y1) ) { coord_order = COORD_ORDER_1TO2; new_file(output_file, file_counter); write_to_file(output_file, count, x1, y1); count += STEP_SIZE; } else if ( (old_x1 == x2) && (old_y1 == y2) ) { coord_order = COORD_ORDER_2TO1; new_file(output_file, file_counter); write_to_file(output_file, count, x2, y2); count += STEP_SIZE; } else { // Coordinates do not belong together. That would result // in a 2 point track. Just forget it! // set coord_order to UNKNOWN. Actually not needed. But // you known where to continue immediately. coord_order = UNKNOWN; } } if (coord_order == COORD_ORDER_1TO2) { if ( (old_x2 == x1) && (old_y2 == y1) ) { write_to_file(output_file, count, x2, y2); count += STEP_SIZE; } else if ( (old_x2 == x2) && (old_y2 == y2) ) { // coord_order changed: output_file << "\t/* Coordinate order changed. "; output_file << "Now it is from 2 to 1. */"; output_file << std::endl; coord_order = COORD_ORDER_2TO1; write_to_file(output_file, count, x1, y1); count += STEP_SIZE; } else { coord_order = UNKNOWN; } } else // if (coord_order == COORD_ORDER_2TO1) { if ( (old_x1 == x2) && (old_y1 == y2) ) { write_to_file(output_file, count, x1, y1); count += STEP_SIZE; } else if ( (old_x1 == x1) && (old_y1 == y1) ) { // coord_order changed: output_file << "\t/* Coordinaten order changed. "; output_file << "Now it is from 1 to 2. */"; output_file << std::endl; coord_order = COORD_ORDER_1TO2; write_to_file(output_file, count, x2, y2); count += STEP_SIZE; } else { coord_order = UNKNOWN; } } old_x1 = x1; old_y1 = y1; old_x2 = x2; old_y2 = y2; } output_file.close(); delete current_time; }
/* get the privileges for tables that meet specified criteria */ int TbListTablePrivileges(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ struct { SQLINTEGER ind; SQLCHAR val[129]; } tbSchema, tbName, grantor, grantee, privilege; struct { SQLINTEGER ind; SQLCHAR val[4]; } is_grantable; /* criteria look for */ SQLCHAR tbSchemaPattern[] = "%"; SQLCHAR tbNamePattern[] = "ORG"; printf("\n-----------------------------------------------------------"); printf("\nUSE THE CLI FUNCTIONS\n"); printf(" SQLSetConnectAttr\n"); printf(" SQLAllocHandle\n"); printf(" SQLTablePrivileges\n"); printf(" SQLBindCol\n"); printf(" SQLFetch\n"); printf(" SQLFreeHandle\n"); printf("TO GET PRIVILEGES FOR TABLES THAT MEET SPECIFIED CRITERIA:\n"); /* set AUTOCOMMIT on */ cliRC = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS); DBC_HANDLE_CHECK(hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* call SQLTablePrivileges */ printf("\n Call SQLTablePrivileges for:\n"); printf(" tbSchemaPattern = %s\n", tbSchemaPattern); printf(" tbNamePattern = %s\n", tbNamePattern); /* get privileges associated with a table */ cliRC = SQLTablePrivileges(hstmt, NULL, 0, tbSchemaPattern, SQL_NTS, tbNamePattern, SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 2 to variable */ cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, tbSchema.val, 129, &tbSchema.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 3 to variable */ cliRC = SQLBindCol(hstmt, 3, SQL_C_CHAR, tbName.val, 129, &tbName.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 4 to variable */ cliRC = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER)grantor.val, 129, &grantor.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 5 to variable */ cliRC = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER)grantee.val, 129, &grantee.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 6 to variable */ cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER)privilege.val, 129, &privilege.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 7 to variable */ cliRC = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER)is_grantable.val, 4, &is_grantable.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ printf("\n Current User's Privileges \n"); printf(" Table Grantor Grantee Privilege Grantable\n"); printf(" ----- -------- ------------ ---------- ---------\n"); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } while (cliRC != SQL_NO_DATA_FOUND) { printf(" %-5s", tbName.val); printf(" %-8s", grantor.val); printf(" %-12s", grantee.val); printf(" %-10s", privilege.val); printf(" %-3s\n", is_grantable.val); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); } /* endwhile */ /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); return rc; } /* TbListTablePrivileges */
static void WriteBodyDelimited( SQLHSTMT hStmt, char cDelimiter ) { SQLINTEGER nCol = 0; SQLSMALLINT nColumns = 0; SQLLEN nIndicator = 0; SQLCHAR szColumnValue[MAX_DATA_WIDTH+1]; SQLRETURN nReturn = 0; SQLRETURN ret; SQLINTEGER *types; *szColumnValue = '\0'; if ( SQLNumResultCols( hStmt, &nColumns ) != SQL_SUCCESS ) nColumns = -1; if ( bQuote && nColumns > 0 ) { types = malloc( nColumns * sizeof ( SQLINTEGER )); for ( nCol = 1; nCol <= nColumns && types; nCol++ ) { SQLSMALLINT type = 0; nReturn = SQLDescribeCol( hStmt, nCol, NULL, 0, NULL, &type, NULL, NULL, NULL ); switch ( type ) { case SQL_CHAR: case SQL_VARCHAR: case SQL_WCHAR: case SQL_WVARCHAR: case SQL_LONGVARCHAR: case SQL_WLONGVARCHAR: types[ nCol - 1 ] = 1; break; default: types[ nCol - 1 ] = 0; break; } } } else { types = NULL; } /* ROWS */ while (( ret = SQLFetch( hStmt )) == SQL_SUCCESS ) { /* COLS */ for ( nCol = 1; nCol <= nColumns; nCol++ ) { nReturn = SQLGetData( hStmt, nCol, SQL_C_CHAR, (SQLPOINTER)szColumnValue, sizeof(szColumnValue), &nIndicator ); if ( nReturn == SQL_SUCCESS && nIndicator != SQL_NULL_DATA ) { if ( types && types[ nCol - 1 ] ) { putchar( '"' ); } fputs((char*) szColumnValue, stdout ); if ( types && types[ nCol - 1 ] ) { putchar( '"' ); } if ( nCol < nColumns ) { putchar( cDelimiter ); } } else if ( nReturn == SQL_ERROR ) { ret = SQL_ERROR; break; } else { if ( nCol < nColumns ) { putchar( cDelimiter ); } } } if (ret != SQL_SUCCESS) { break; } printf( "\n" ); } if ( ret == SQL_ERROR ) { if ( bVerbose ) DumpODBCLog( 0, 0, hStmt ); } if ( types ) { free( types ); } }
void RecvCMD_SET_COMMANDER(t_packet *p, t_connection c[], int cn ) // 사령관의 데이터를 세트한다. { NW_Character tempNWCharacter; DWORD tempDWORD; HSTMT hStmt=NULL; RETCODE ret; SWORD nClos; char query_stmt[80]={0,}; SDWORD cbValue; SQLAllocStmt(hDBC,&hStmt); wsprintf(query_stmt,"select fame_pk from chr_info where name='%s'",p->u.NationWar.SetCommander.CommanderName); ret = SQLExecDirect(hStmt,(UCHAR*)query_stmt,SQL_NTS); if (ret!=SQL_SUCCESS_WITH_INFO && ret !=SQL_SUCCESS) { MyLog(0,"Query Failure!! RecvCMD_SET_COMMANDER.."); SQLFreeStmt(hStmt,SQL_DROP); return; } SQLNumResultCols(hStmt,&nClos); ret=SQLFetch(hStmt); if (ret!=SQL_SUCCESS_WITH_INFO && ret !=SQL_SUCCESS) { //MyLog(0,"WarField Fetch Error!!"); SQLFreeStmt(hStmt,SQL_DROP); return ; } ret=SQLGetData(hStmt,1,SQL_C_SLONG,&tempDWORD,sizeof(int),&cbValue); if (ret!=SQL_SUCCESS_WITH_INFO && ret!=SQL_SUCCESS) { MyLog(0,"SQL Return Error(%d)!!",ret); SQLFreeStmt(hStmt,SQL_DROP); return ; } SQLFreeStmt(hStmt,SQL_DROP); memcpy(&tempNWCharacter,&tempDWORD,sizeof(DWORD)); tempNWCharacter.isCommander=1; memcpy(&tempDWORD,&tempNWCharacter,sizeof(DWORD)); memcpy(&tempNWCharacter,&tempDWORD,sizeof(DWORD)); SQLAllocStmt(hDBC,&hStmt); wsprintf(query_stmt,"update chr_info set fame_pk=%d where name='%s'",tempDWORD,p->u.NationWar.SetCommander.CommanderName); ret=SQLExecDirect(hStmt,(UCHAR*)query_stmt,SQL_NTS); if (ret!=SQL_SUCCESS_WITH_INFO && ret!=SQL_SUCCESS) { MyLog(0,"Update Query Failure!! RecvCMD_SET_COMMANDER.."); SQLFreeStmt(hStmt,SQL_DROP); return; } SQLFreeStmt(hStmt,SQL_DROP); }
int main (int argc, char *argv[]) { if (argc < 4) { fprintf (stderr, "ERR : called as setcurs <dsn> <uid> <pwd> [<scroll>=1]\n"); exit (-3); } rc = SQLAllocEnv (&henv); if (rc != SQL_SUCCESS) { fprintf (stderr, "ERR : cannot alloc ODBC environment\n"); exit (-3); } rc = SQLAllocConnect (henv, &hdbc); CHK_ERR (SQL_NULL_HSTMT); rc = SQLConnect (hdbc, argv[1], SQL_NTS, argv[2], SQL_NTS, argv[3], SQL_NTS); CHK_ERR (SQL_NULL_HSTMT); /* Allocate the statements and set the cursor name. */ rc = SQLAllocStmt (hdbc, &hstmtSelect); CHK_ERR (SQL_NULL_HSTMT); rc = SQLAllocStmt (hdbc, &hstmtUpdate); CHK_ERR (SQL_NULL_HSTMT); if (argc <= 4 || atoi (argv[4]) != 0) { rc = SQLSetStmtOption (hstmtSelect, SQL_CURSOR_TYPE, SQL_CURSOR_STATIC); CHK_ERR (hstmtSelect); rc = SQLSetScrollOptions (hstmtSelect, SQL_CONCUR_READ_ONLY, 1, 1); CHK_ERR (hstmtSelect); } rc = SQLSetCursorName (hstmtSelect, "C1", SQL_NTS); CHK_ERR (hstmtSelect); /* SELECT the result set and bind its columns to local buffers. */ rc = SQLExecDirect (hstmtSelect, "SELECT NAME,PHONE from GOGO", SQL_NTS); CHK_ERR (hstmtSelect); rc = SQLBindCol (hstmtSelect, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName); CHK_ERR (hstmtSelect); rc = SQLBindCol (hstmtSelect, 2, SQL_C_CHAR, szPhone, PHONE_LEN, &cbPhone); CHK_ERR (hstmtSelect); /* Read through the result set until the cursor is */ /* positioned on the row for John Smith. */ do retcode = SQLFetch (hstmtSelect); while ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) && (strcmp (szName, "Smith, John") != 0)); rc = retcode; CHK_ERR (hstmtSelect); /* Perform a positioned update of John Smith's name. */ if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { rc = SQLExecDirect (hstmtUpdate, "UPDATE GOGO SET PHONE='2064890154' WHERE CURRENT OF C1", SQL_NTS); CHK_ERR (hstmtUpdate); } #if 0 SQLFreeStmt (hstmtUpdate, SQL_DROP); SQLFreeStmt (hstmtSelect, SQL_DROP); SQLDisconnect (hdbc); SQLFreeConnect (hdbc); #endif printf ("PASSED: SetCursorName Test\n"); exit (0); }
static int backsql_get_attr_vals( void *v_at, void *v_bsi ) { backsql_at_map_rec *at = v_at; backsql_srch_info *bsi = v_bsi; backsql_info *bi = (backsql_info *)bsi->bsi_op->o_bd->be_private; RETCODE rc; SQLHSTMT sth = SQL_NULL_HSTMT; BACKSQL_ROW_NTS row; unsigned long i, k = 0, oldcount = 0, res = 0; #ifdef BACKSQL_COUNTQUERY unsigned count, j, append = 0; SQLLEN countsize = sizeof( count ); Attribute *attr = NULL; slap_mr_normalize_func *normfunc = NULL; #endif /* BACKSQL_COUNTQUERY */ #ifdef BACKSQL_PRETTY_VALIDATE slap_syntax_validate_func *validate = NULL; slap_syntax_transform_func *pretty = NULL; #endif /* BACKSQL_PRETTY_VALIDATE */ assert( at != NULL ); assert( bsi != NULL ); #ifdef BACKSQL_ARBITRARY_KEY Debug( LDAP_DEBUG_TRACE, "==>backsql_get_attr_vals(): " "oc=\"%s\" attr=\"%s\" keyval=%s\n", BACKSQL_OC_NAME( bsi->bsi_oc ), at->bam_ad->ad_cname.bv_val, bsi->bsi_c_eid->eid_keyval.bv_val ); #else /* ! BACKSQL_ARBITRARY_KEY */ Debug( LDAP_DEBUG_TRACE, "==>backsql_get_attr_vals(): " "oc=\"%s\" attr=\"%s\" keyval=%ld\n", BACKSQL_OC_NAME( bsi->bsi_oc ), at->bam_ad->ad_cname.bv_val, bsi->bsi_c_eid->eid_keyval ); #endif /* ! BACKSQL_ARBITRARY_KEY */ #ifdef BACKSQL_PRETTY_VALIDATE validate = at->bam_true_ad->ad_type->sat_syntax->ssyn_validate; pretty = at->bam_true_ad->ad_type->sat_syntax->ssyn_pretty; if ( validate == NULL && pretty == NULL ) { return 1; } #endif /* BACKSQL_PRETTY_VALIDATE */ #ifdef BACKSQL_COUNTQUERY if ( at->bam_true_ad->ad_type->sat_equality ) { normfunc = at->bam_true_ad->ad_type->sat_equality->smr_normalize; } /* Count how many rows will be returned. This avoids memory * fragmentation that can result from loading the values in * one by one and using realloc() */ rc = backsql_Prepare( bsi->bsi_dbh, &sth, at->bam_countquery, 0 ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error preparing count query: %s\n", at->bam_countquery, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, bsi->bsi_dbh, sth, rc ); return 1; } rc = backsql_BindParamID( sth, 1, SQL_PARAM_INPUT, &bsi->bsi_c_eid->eid_keyval ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error binding key value parameter\n", 0, 0, 0 ); SQLFreeStmt( sth, SQL_DROP ); return 1; } rc = SQLExecute( sth ); if ( ! BACKSQL_SUCCESS( rc ) ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error executing attribute count query '%s'\n", at->bam_countquery, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, bsi->bsi_dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); return 1; } SQLBindCol( sth, (SQLUSMALLINT)1, SQL_C_LONG, (SQLPOINTER)&count, (SQLINTEGER)sizeof( count ), &countsize ); rc = SQLFetch( sth ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error fetch results of count query: %s\n", at->bam_countquery, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, bsi->bsi_dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); return 1; } Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "number of values in query: %u\n", count, 0, 0 ); SQLFreeStmt( sth, SQL_DROP ); if ( count == 0 ) { return 1; } attr = attr_find( bsi->bsi_e->e_attrs, at->bam_true_ad ); if ( attr != NULL ) { BerVarray tmp; if ( attr->a_vals != NULL ) { oldcount = attr->a_numvals; } tmp = ch_realloc( attr->a_vals, ( oldcount + count + 1 ) * sizeof( struct berval ) ); if ( tmp == NULL ) { return 1; } attr->a_vals = tmp; memset( &attr->a_vals[ oldcount ], 0, ( count + 1 ) * sizeof( struct berval ) ); if ( normfunc ) { tmp = ch_realloc( attr->a_nvals, ( oldcount + count + 1 ) * sizeof( struct berval ) ); if ( tmp == NULL ) { return 1; } attr->a_nvals = tmp; memset( &attr->a_nvals[ oldcount ], 0, ( count + 1 ) * sizeof( struct berval ) ); } else { attr->a_nvals = attr->a_vals; } attr->a_numvals += count; } else { append = 1; /* Make space for the array of values */ attr = attr_alloc( at->bam_true_ad ); attr->a_numvals = count; attr->a_vals = ch_calloc( count + 1, sizeof( struct berval ) ); if ( attr->a_vals == NULL ) { Debug( LDAP_DEBUG_TRACE, "Out of memory!\n", 0,0,0 ); ch_free( attr ); return 1; } if ( normfunc ) { attr->a_nvals = ch_calloc( count + 1, sizeof( struct berval ) ); if ( attr->a_nvals == NULL ) { ch_free( attr->a_vals ); ch_free( attr ); return 1; } } else { attr->a_nvals = attr->a_vals; } } #endif /* BACKSQL_COUNTQUERY */ rc = backsql_Prepare( bsi->bsi_dbh, &sth, at->bam_query, 0 ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error preparing query: %s\n", at->bam_query, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, bsi->bsi_dbh, sth, rc ); #ifdef BACKSQL_COUNTQUERY if ( append ) { attr_free( attr ); } #endif /* BACKSQL_COUNTQUERY */ return 1; } rc = backsql_BindParamID( sth, 1, SQL_PARAM_INPUT, &bsi->bsi_c_eid->eid_keyval ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error binding key value parameter\n", 0, 0, 0 ); #ifdef BACKSQL_COUNTQUERY if ( append ) { attr_free( attr ); } #endif /* BACKSQL_COUNTQUERY */ return 1; } #ifdef BACKSQL_TRACE #ifdef BACKSQL_ARBITRARY_KEY Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "query=\"%s\" keyval=%s\n", at->bam_query, bsi->bsi_c_eid->eid_keyval.bv_val, 0 ); #else /* !BACKSQL_ARBITRARY_KEY */ Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "query=\"%s\" keyval=%d\n", at->bam_query, bsi->bsi_c_eid->eid_keyval, 0 ); #endif /* ! BACKSQL_ARBITRARY_KEY */ #endif /* BACKSQL_TRACE */ rc = SQLExecute( sth ); if ( ! BACKSQL_SUCCESS( rc ) ) { Debug( LDAP_DEBUG_TRACE, "backsql_get_attr_vals(): " "error executing attribute query \"%s\"\n", at->bam_query, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, bsi->bsi_dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); #ifdef BACKSQL_COUNTQUERY if ( append ) { attr_free( attr ); } #endif /* BACKSQL_COUNTQUERY */ return 1; } backsql_BindRowAsStrings_x( sth, &row, bsi->bsi_op->o_tmpmemctx ); #ifdef BACKSQL_COUNTQUERY j = oldcount; #endif /* BACKSQL_COUNTQUERY */ for ( rc = SQLFetch( sth ), k = 0; BACKSQL_SUCCESS( rc ); rc = SQLFetch( sth ), k++ ) { for ( i = 0; i < (unsigned long)row.ncols; i++ ) { if ( row.value_len[ i ] > 0 ) { struct berval bv; int retval; #ifdef BACKSQL_TRACE AttributeDescription *ad = NULL; const char *text; retval = slap_bv2ad( &row.col_names[ i ], &ad, &text ); if ( retval != LDAP_SUCCESS ) { Debug( LDAP_DEBUG_ANY, "==>backsql_get_attr_vals(\"%s\"): " "unable to find AttributeDescription %s " "in schema (%d)\n", bsi->bsi_e->e_name.bv_val, row.col_names[ i ].bv_val, retval ); res = 1; goto done; } if ( ad != at->bam_ad ) { Debug( LDAP_DEBUG_ANY, "==>backsql_get_attr_vals(\"%s\"): " "column name %s differs from " "AttributeDescription %s\n", bsi->bsi_e->e_name.bv_val, ad->ad_cname.bv_val, at->bam_ad->ad_cname.bv_val ); res = 1; goto done; } #endif /* BACKSQL_TRACE */ /* ITS#3386, ITS#3113 - 20070308 * If a binary is fetched? * must use the actual size read * from the database. */ if ( BACKSQL_IS_BINARY( row.col_type[ i ] ) ) { #ifdef BACKSQL_TRACE Debug( LDAP_DEBUG_ANY, "==>backsql_get_attr_vals(\"%s\"): " "column name %s: data is binary; " "using database size %ld\n", bsi->bsi_e->e_name.bv_val, ad->ad_cname.bv_val, row.value_len[ i ] ); #endif /* BACKSQL_TRACE */ bv.bv_val = row.cols[ i ]; bv.bv_len = row.value_len[ i ]; } else { ber_str2bv( row.cols[ i ], 0, 0, &bv ); } #ifdef BACKSQL_PRETTY_VALIDATE if ( pretty ) { struct berval pbv; retval = pretty( at->bam_true_ad->ad_type->sat_syntax, &bv, &pbv, bsi->bsi_op->o_tmpmemctx ); bv = pbv; } else { retval = validate( at->bam_true_ad->ad_type->sat_syntax, &bv ); } if ( retval != LDAP_SUCCESS ) { char buf[ SLAP_TEXT_BUFLEN ]; /* FIXME: we're ignoring invalid values, * but we're accepting the attributes; * should we fail at all? */ snprintf( buf, sizeof( buf ), "unable to %s value #%lu " "of AttributeDescription %s", pretty ? "prettify" : "validate", k - oldcount, at->bam_ad->ad_cname.bv_val ); Debug( LDAP_DEBUG_TRACE, "==>backsql_get_attr_vals(\"%s\"): " "%s (%d)\n", bsi->bsi_e->e_name.bv_val, buf, retval ); continue; } #endif /* BACKSQL_PRETTY_VALIDATE */ #ifndef BACKSQL_COUNTQUERY (void)backsql_entry_addattr( bsi->bsi_e, at->bam_true_ad, &bv, bsi->bsi_op->o_tmpmemctx ); #else /* BACKSQL_COUNTQUERY */ if ( normfunc ) { struct berval nbv; retval = (*normfunc)( SLAP_MR_VALUE_OF_ATTRIBUTE_SYNTAX, at->bam_true_ad->ad_type->sat_syntax, at->bam_true_ad->ad_type->sat_equality, &bv, &nbv, bsi->bsi_op->o_tmpmemctx ); if ( retval != LDAP_SUCCESS ) { char buf[ SLAP_TEXT_BUFLEN ]; /* FIXME: we're ignoring invalid values, * but we're accepting the attributes; * should we fail at all? */ snprintf( buf, sizeof( buf ), "unable to normalize value #%lu " "of AttributeDescription %s", k - oldcount, at->bam_ad->ad_cname.bv_val ); Debug( LDAP_DEBUG_TRACE, "==>backsql_get_attr_vals(\"%s\"): " "%s (%d)\n", bsi->bsi_e->e_name.bv_val, buf, retval ); #ifdef BACKSQL_PRETTY_VALIDATE if ( pretty ) { bsi->bsi_op->o_tmpfree( bv.bv_val, bsi->bsi_op->o_tmpmemctx ); } #endif /* BACKSQL_PRETTY_VALIDATE */ continue; } ber_dupbv( &attr->a_nvals[ j ], &nbv ); bsi->bsi_op->o_tmpfree( nbv.bv_val, bsi->bsi_op->o_tmpmemctx ); } ber_dupbv( &attr->a_vals[ j ], &bv ); assert( j < oldcount + count ); j++; #endif /* BACKSQL_COUNTQUERY */ #ifdef BACKSQL_PRETTY_VALIDATE if ( pretty ) { bsi->bsi_op->o_tmpfree( bv.bv_val, bsi->bsi_op->o_tmpmemctx ); } #endif /* BACKSQL_PRETTY_VALIDATE */ #ifdef BACKSQL_TRACE Debug( LDAP_DEBUG_TRACE, "prec=%d\n", (int)row.col_prec[ i ], 0, 0 ); } else { Debug( LDAP_DEBUG_TRACE, "NULL value " "in this row for attribute \"%s\"\n", row.col_names[ i ].bv_val, 0, 0 ); #endif /* BACKSQL_TRACE */ } } } #ifdef BACKSQL_COUNTQUERY if ( BER_BVISNULL( &attr->a_vals[ 0 ] ) ) { /* don't leave around attributes with no values */ attr_free( attr ); } else if ( append ) { Attribute **ap; for ( ap = &bsi->bsi_e->e_attrs; (*ap) != NULL; ap = &(*ap)->a_next ) /* goto last */ ; *ap = attr; } #endif /* BACKSQL_COUNTQUERY */ SQLFreeStmt( sth, SQL_DROP ); Debug( LDAP_DEBUG_TRACE, "<==backsql_get_attr_vals()\n", 0, 0, 0 ); if ( at->bam_next ) { res = backsql_get_attr_vals( at->bam_next, v_bsi ); } else { res = 1; } #ifdef BACKSQL_TRACE done:; #endif /* BACKSQL_TRACE */ backsql_FreeRow_x( &row, bsi->bsi_op->o_tmpmemctx ); return res; }
static void retrieve_file(char *dir) { int x = 0; int res; int fd=-1; size_t fdlen = 0; void *fdm = MAP_FAILED; SQLHSTMT stmt; char sql[256]; char fmt[80]="", empty[10] = ""; char *c; SQLLEN colsize; char full_fn[256]; struct odbc_obj *obj; struct generic_prepare_struct gps = { .sql = sql, .param = dir }; obj = ast_odbc_request_obj(odbc_database, 1); if (obj) { do { ast_copy_string(fmt, vmfmts, sizeof(fmt)); c = strchr(fmt, '|'); if (c) *c = '\0'; if (!strcasecmp(fmt, "wav49")) strcpy(fmt, "WAV"); snprintf(full_fn, sizeof(full_fn), "%s.%s", dir, fmt); snprintf(sql, sizeof(sql), "SELECT recording FROM %s WHERE dir=? AND msgnum=-1", odbc_table); stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); break; } res = SQLFetch(stmt); if (res == SQL_NO_DATA) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); break; } else if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql); SQLFreeHandle(SQL_HANDLE_STMT, stmt); break; } fd = open(full_fn, O_RDWR | O_CREAT | O_TRUNC, 0770); if (fd < 0) { ast_log(LOG_WARNING, "Failed to write '%s': %s\n", full_fn, strerror(errno)); SQLFreeHandle(SQL_HANDLE_STMT, stmt); break; } res = SQLGetData(stmt, 1, SQL_BINARY, empty, 0, &colsize); fdlen = colsize; if (fd > -1) { char tmp[1]=""; lseek(fd, fdlen - 1, SEEK_SET); if (write(fd, tmp, 1) != 1) { close(fd); fd = -1; break; } if (fd > -1) fdm = mmap(NULL, fdlen, PROT_READ | PROT_WRITE, MAP_SHARED, fd, 0); } if (fdm != MAP_FAILED) { memset(fdm, 0, fdlen); res = SQLGetData(stmt, x + 1, SQL_BINARY, fdm, fdlen, &colsize); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql); SQLFreeHandle(SQL_HANDLE_STMT, stmt); break; } } SQLFreeHandle(SQL_HANDLE_STMT, stmt); } while (0); ast_odbc_release_obj(obj); } else ast_log(LOG_WARNING, "Failed to obtain database object for '%s'!\n", odbc_database); if (fdm != MAP_FAILED) munmap(fdm, fdlen); if (fd > -1) close(fd); return; }
int backsql_count_children( Operation *op, SQLHDBC dbh, struct berval *dn, unsigned long *nchildren ) { backsql_info *bi = (backsql_info *)op->o_bd->be_private; SQLHSTMT sth = SQL_NULL_HSTMT; BACKSQL_ROW_NTS row; RETCODE rc; int res = LDAP_SUCCESS; Debug( LDAP_DEBUG_TRACE, "==>backsql_count_children(): dn=\"%s\"\n", dn->bv_val, 0, 0 ); if ( dn->bv_len > BACKSQL_MAX_DN_LEN ) { Debug( LDAP_DEBUG_TRACE, "backsql_count_children(): DN \"%s\" (%ld bytes) " "exceeds max DN length (%d):\n", dn->bv_val, dn->bv_len, BACKSQL_MAX_DN_LEN ); return LDAP_OTHER; } /* begin TimesTen */ Debug(LDAP_DEBUG_TRACE, "children id query \"%s\"\n", bi->sql_has_children_query, 0, 0); assert( bi->sql_has_children_query != NULL ); rc = backsql_Prepare( dbh, &sth, bi->sql_has_children_query, 0 ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_count_children(): error preparing SQL:\n%s", bi->sql_has_children_query, 0, 0); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); return LDAP_OTHER; } rc = backsql_BindParamBerVal( sth, 1, SQL_PARAM_INPUT, dn ); if ( rc != SQL_SUCCESS) { /* end TimesTen */ Debug( LDAP_DEBUG_TRACE, "backsql_count_children(): " "error binding dn=\"%s\" parameter:\n", dn->bv_val, 0, 0 ); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); return LDAP_OTHER; } rc = SQLExecute( sth ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, "backsql_count_children(): " "error executing query (\"%s\", \"%s\"):\n", bi->sql_has_children_query, dn->bv_val, 0 ); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); SQLFreeStmt( sth, SQL_DROP ); return LDAP_OTHER; } backsql_BindRowAsStrings_x( sth, &row, op->o_tmpmemctx ); rc = SQLFetch( sth ); if ( BACKSQL_SUCCESS( rc ) ) { char *end; *nchildren = strtol( row.cols[ 0 ], &end, 0 ); if ( end == row.cols[ 0 ] ) { res = LDAP_OTHER; } else { switch ( end[ 0 ] ) { case '\0': break; case '.': { unsigned long ul; /* FIXME: braindead RDBMSes return * a fractional number from COUNT! */ if ( lutil_atoul( &ul, end + 1 ) != 0 || ul != 0 ) { res = LDAP_OTHER; } } break; default: res = LDAP_OTHER; } } } else { res = LDAP_OTHER; } backsql_FreeRow_x( &row, op->o_tmpmemctx ); SQLFreeStmt( sth, SQL_DROP ); Debug( LDAP_DEBUG_TRACE, "<==backsql_count_children(): %lu\n", *nchildren, 0, 0 ); return res; }
/* * NOTE: the dn must be normalized */ int backsql_dn2id( Operation *op, SlapReply *rs, SQLHDBC dbh, struct berval *ndn, backsql_entryID *id, int matched, int muck ) { backsql_info *bi = op->o_bd->be_private; SQLHSTMT sth = SQL_NULL_HSTMT; BACKSQL_ROW_NTS row = { 0 }; RETCODE rc; int res; struct berval realndn = BER_BVNULL; /* TimesTen */ char upperdn[ BACKSQL_MAX_DN_LEN + 1 ]; struct berval tbbDN; int i, j; /* * NOTE: id can be NULL; in this case, the function * simply checks whether the DN can be successfully * turned into an ID, returning LDAP_SUCCESS for * positive cases, or the most appropriate error */ Debug( LDAP_DEBUG_TRACE, "==>backsql_dn2id(\"%s\")%s%s\n", ndn->bv_val, id == NULL ? " (no ID expected)" : "", matched ? " matched expected" : "" ); if ( id ) { /* NOTE: trap inconsistencies */ assert( BER_BVISNULL( &id->eid_ndn ) ); } if ( ndn->bv_len > BACKSQL_MAX_DN_LEN ) { Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): DN length=%ld " "exceeds max DN length %d:\n", ndn->bv_val, ndn->bv_len, BACKSQL_MAX_DN_LEN ); return LDAP_OTHER; } /* return baseObject if available and matches */ /* FIXME: if ndn is already mucked, we cannot check this */ if ( bi->sql_baseObject != NULL && dn_match( ndn, &bi->sql_baseObject->e_nname ) ) { if ( id != NULL ) { #ifdef BACKSQL_ARBITRARY_KEY ber_dupbv_x( &id->eid_id, &backsql_baseObject_bv, op->o_tmpmemctx ); ber_dupbv_x( &id->eid_keyval, &backsql_baseObject_bv, op->o_tmpmemctx ); #else /* ! BACKSQL_ARBITRARY_KEY */ id->eid_id = BACKSQL_BASEOBJECT_ID; id->eid_keyval = BACKSQL_BASEOBJECT_KEYVAL; #endif /* ! BACKSQL_ARBITRARY_KEY */ id->eid_oc_id = BACKSQL_BASEOBJECT_OC; ber_dupbv_x( &id->eid_ndn, &bi->sql_baseObject->e_nname, op->o_tmpmemctx ); ber_dupbv_x( &id->eid_dn, &bi->sql_baseObject->e_name, op->o_tmpmemctx ); id->eid_next = NULL; } return LDAP_SUCCESS; } /* begin TimesTen */ Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): id_query \"%s\"\n", ndn->bv_val, bi->sql_id_query, 0 ); assert( bi->sql_id_query != NULL ); rc = backsql_Prepare( dbh, &sth, bi->sql_id_query, 0 ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "error preparing SQL:\n %s", ndn->bv_val, bi->sql_id_query, 0 ); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); res = LDAP_OTHER; goto done; } realndn = *ndn; if ( muck ) { if ( backsql_api_dn2odbc( op, rs, &realndn ) ) { Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "backsql_api_dn2odbc(\"%s\") failed\n", ndn->bv_val, realndn.bv_val, 0 ); res = LDAP_OTHER; goto done; } } if ( BACKSQL_HAS_LDAPINFO_DN_RU( bi ) ) { /* * Prepare an upper cased, byte reversed version * that can be searched using indexes */ for ( i = 0, j = realndn.bv_len - 1; realndn.bv_val[ i ]; i++, j--) { upperdn[ i ] = realndn.bv_val[ j ]; } upperdn[ i ] = '\0'; ldap_pvt_str2upper( upperdn ); Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "upperdn=\"%s\"\n", ndn->bv_val, upperdn, 0 ); ber_str2bv( upperdn, 0, 0, &tbbDN ); } else { if ( BACKSQL_USE_REVERSE_DN( bi ) ) { AC_MEMCPY( upperdn, realndn.bv_val, realndn.bv_len + 1 ); ldap_pvt_str2upper( upperdn ); Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "upperdn=\"%s\"\n", ndn->bv_val, upperdn, 0 ); ber_str2bv( upperdn, 0, 0, &tbbDN ); } else { tbbDN = realndn; } } rc = backsql_BindParamBerVal( sth, 1, SQL_PARAM_INPUT, &tbbDN ); if ( rc != SQL_SUCCESS) { /* end TimesTen */ Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "error binding dn=\"%s\" parameter:\n", ndn->bv_val, tbbDN.bv_val, 0 ); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); res = LDAP_OTHER; goto done; } rc = SQLExecute( sth ); if ( rc != SQL_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "error executing query (\"%s\", \"%s\"):\n", ndn->bv_val, bi->sql_id_query, tbbDN.bv_val ); backsql_PrintErrors( bi->sql_db_env, dbh, sth, rc ); res = LDAP_OTHER; goto done; } backsql_BindRowAsStrings_x( sth, &row, op->o_tmpmemctx ); rc = SQLFetch( sth ); if ( BACKSQL_SUCCESS( rc ) ) { char buf[ SLAP_TEXT_BUFLEN ]; #ifdef LDAP_DEBUG snprintf( buf, sizeof(buf), "id=%s keyval=%s oc_id=%s dn=%s", row.cols[ 0 ], row.cols[ 1 ], row.cols[ 2 ], row.cols[ 3 ] ); Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): %s\n", ndn->bv_val, buf, 0 ); #endif /* LDAP_DEBUG */ res = LDAP_SUCCESS; if ( id != NULL ) { struct berval dn; id->eid_next = NULL; #ifdef BACKSQL_ARBITRARY_KEY ber_str2bv_x( row.cols[ 0 ], 0, 1, &id->eid_id, op->o_tmpmemctx ); ber_str2bv_x( row.cols[ 1 ], 0, 1, &id->eid_keyval, op->o_tmpmemctx ); #else /* ! BACKSQL_ARBITRARY_KEY */ if ( lutil_atoulx( &id->eid_id, row.cols[ 0 ], 0 ) != 0 ) { res = LDAP_OTHER; goto done; } if ( lutil_atoulx( &id->eid_keyval, row.cols[ 1 ], 0 ) != 0 ) { res = LDAP_OTHER; goto done; } #endif /* ! BACKSQL_ARBITRARY_KEY */ if ( lutil_atoulx( &id->eid_oc_id, row.cols[ 2 ], 0 ) != 0 ) { res = LDAP_OTHER; goto done; } ber_str2bv( row.cols[ 3 ], 0, 0, &dn ); if ( backsql_api_odbc2dn( op, rs, &dn ) ) { res = LDAP_OTHER; goto done; } res = dnPrettyNormal( NULL, &dn, &id->eid_dn, &id->eid_ndn, op->o_tmpmemctx ); if ( res != LDAP_SUCCESS ) { Debug( LDAP_DEBUG_TRACE, " backsql_dn2id(\"%s\"): " "dnPrettyNormal failed (%d: %s)\n", realndn.bv_val, res, ldap_err2string( res ) ); /* cleanup... */ (void)backsql_free_entryID( id, 0, op->o_tmpmemctx ); } if ( dn.bv_val != row.cols[ 3 ] ) { free( dn.bv_val ); } } } else { res = LDAP_NO_SUCH_OBJECT; if ( matched ) { struct berval pdn = *ndn; /* * Look for matched */ rs->sr_matched = NULL; while ( !be_issuffix( op->o_bd, &pdn ) ) { char *matchedDN = NULL; dnParent( &pdn, &pdn ); /* * Empty DN ("") defaults to LDAP_SUCCESS */ rs->sr_err = backsql_dn2id( op, rs, dbh, &pdn, id, 0, 1 ); switch ( rs->sr_err ) { case LDAP_NO_SUCH_OBJECT: /* try another one */ break; case LDAP_SUCCESS: matchedDN = pdn.bv_val; /* fail over to next case */ default: rs->sr_err = LDAP_NO_SUCH_OBJECT; rs->sr_matched = matchedDN; goto done; } } } } done:; backsql_FreeRow_x( &row, op->o_tmpmemctx ); Debug( LDAP_DEBUG_TRACE, "<==backsql_dn2id(\"%s\"): err=%d\n", ndn->bv_val, res, 0 ); if ( sth != SQL_NULL_HSTMT ) { SQLFreeStmt( sth, SQL_DROP ); } if ( !BER_BVISNULL( &realndn ) && realndn.bv_val != ndn->bv_val ) { ch_free( realndn.bv_val ); } return res; }
/*! * \brief Gets a partial result set, fetch rows from a result * * Gets a partial result set, fetch a number of rows from a databae result. * This function initialize the given result structure on the first run, and * fetches the nrows number of rows. On subsequenting runs, it uses the * existing result and fetches more rows, until it reaches the end of the * result set. Because of this the result needs to be null in the first * invocation of the function. If the number of wanted rows is zero, the * function returns anything with a result of zero. * \param _h structure representing the database connection * \param _r pointer to a structure representing the result * \param nrows number of fetched rows * \return return zero on success, negative value on failure */ int db_unixodbc_fetch_result(const db1_con_t* _h, db1_res_t** _r, const int nrows) { int row_n = 0, i = 0, ret = 0, len; SQLSMALLINT columns; list* rows = NULL; list* rowstart = NULL; strn* temp_row = NULL; if ((!_h) || (!_r) || nrows < 0) { LM_ERR("invalid parameter value\n"); return -1; } /* exit if the fetch count is zero */ if (nrows == 0) { if (*_r) db_free_result(*_r); *_r = 0; return 0; } /* On the first fetch for a query, allocate structures and get columns */ if(*_r == NULL) { /* Allocate a new result structure */ *_r = db_new_result(); LM_DBG("just allocated a new db result structure"); if (*_r == NULL) { LM_ERR("no memory left\n"); return -2; } /* Get columns names and count */ if (db_unixodbc_get_columns(_h, *_r) < 0) { LM_ERR("getting column names failed\n"); db_free_columns(*_r); return -2; } /* On subsequent fetch attempts, reuse already allocated structures */ } else { LM_DBG("db result structure already exist, reusing\n"); /* free old rows */ if(RES_ROWS(*_r) != NULL) db_free_rows(*_r); RES_ROWS(*_r) = 0; RES_ROW_N(*_r) = 0; } SQLNumResultCols(CON_RESULT(_h), (SQLSMALLINT *)&columns); /* Now fetch nrows at most */ len = sizeof(db_row_t) * nrows; RES_ROWS(*_r) = (struct db_row*)pkg_malloc(len); if (!RES_ROWS(*_r)) { LM_ERR("no memory left\n"); return -5; } LM_DBG("allocated %d bytes for RES_ROWS at %p\n", len, RES_ROWS(*_r)); LM_DBG("Now fetching %i rows at most\n", nrows); while(SQL_SUCCEEDED(ret = SQLFetch(CON_RESULT(_h)))) { /* Allocate a temporary row */ temp_row = db_unixodbc_new_cellrow(columns); if (!temp_row) { LM_ERR("no private memory left\n"); pkg_free(RES_ROWS(*_r)); pkg_free(*_r); *_r = 0; return -1; } LM_DBG("fetching %d columns for row %d...\n",columns, row_n); for(i=0; i < columns; i++) { LM_DBG("fetching column %d\n",i); if (!db_unixodbc_load_cell(_h, i+1, temp_row + i, RES_TYPES(*_r)[i])) { pkg_free(RES_ROWS(*_r)); db_unixodbc_free_cellrow(columns, temp_row); pkg_free(*_r); *_r = 0; return -5; } } LM_DBG("got temp_row at %p\n", temp_row); if (db_unixodbc_list_insert(&rowstart, &rows, columns, temp_row) < 0) { LM_ERR("SQL result row insert failed\n"); pkg_free(RES_ROWS(*_r)); db_unixodbc_free_cellrow(columns, temp_row); pkg_free(*_r); *_r = 0; return -5; } /* Free temporary row data */ LM_DBG("freeing temp_row at %p\n", temp_row); db_unixodbc_free_cellrow(columns, temp_row); temp_row = NULL; row_n++; if (row_n == nrows) { break; } } CON_ROW(_h) = NULL; RES_ROW_N(*_r) = row_n; if (!row_n) { LM_DBG("no more rows to process for db fetch"); pkg_free(RES_ROWS(*_r)); RES_ROWS(*_r) = 0; return 0; } /* Convert rows to internal format */ memset(RES_ROWS(*_r), 0, len); i = 0; rows = rowstart; while(rows) { LM_DBG("converting row #%d\n", i); CON_ROW(_h) = rows->data; if (!CON_ROW(_h)) { LM_ERR("string null\n"); RES_ROW_N(*_r) = row_n; db_free_rows(*_r); return -3; } if (db_unixodbc_convert_row(_h, *_r, &(RES_ROWS(*_r)[i]), rows->lengths) < 0) { LM_ERR("converting fetched row #%d failed\n", i); RES_ROW_N(*_r) = i; db_free_rows(*_r); return -4; } i++; rows = rows->next; } db_unixodbc_list_destroy(rowstart); /* update the total number of rows processed */ RES_LAST_ROW(*_r) += row_n; LM_DBG("fetch from db processed %d rows so far\n", RES_LAST_ROW(*_r)); return 0; }
/* list columns that meet specified criteria */ int TbListColumns(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ struct { SQLINTEGER ind; SQLCHAR val[129]; } colName, colType; struct { SQLINTEGER ind; SQLCHAR val[255]; } colRemarks; struct { SQLINTEGER ind; SQLINTEGER val; } colLength; struct { SQLINTEGER ind; SQLSMALLINT val; } colScale, colNullable; /* criteria to look for */ SQLCHAR tbSchemaPattern[] = "%"; SQLCHAR tbNamePattern[] = "STAFF"; SQLCHAR colNamePattern[] = "%"; printf("\n-----------------------------------------------------------"); printf("\nUSE THE CLI FUNCTIONS\n"); printf(" SQLSetConnectAttr\n"); printf(" SQLAllocHandle\n"); printf(" SQLColumns\n"); printf(" SQLBindCol\n"); printf(" SQLFetch\n"); printf(" SQLFreeHandle\n"); printf("TO SHOW HOW TO LIST COLUMNS THAT MEET SPECIFIED CRITERIA:\n"); /* set AUTOCOMMIT on */ cliRC = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS); DBC_HANDLE_CHECK(hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* call SQLColumns */ printf("\n Call SQLColumns for:\n"); printf(" tbSchemaPattern = %s\n", tbSchemaPattern); printf(" tbNamePattern = %s\n", tbNamePattern); printf(" colNamePattern = %s\n", colNamePattern); /* get column information for a table */ cliRC = SQLColumns(hstmt, NULL, 0, tbSchemaPattern, SQL_NTS, tbNamePattern, SQL_NTS, colNamePattern, SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 4 to variable */ cliRC = SQLBindCol(hstmt, 4, SQL_C_CHAR, colName.val, 129, &colName.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 6 to variable */ cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, colType.val, 129, &colType.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 7 to variable */ cliRC = SQLBindCol(hstmt, 7, SQL_C_LONG, (SQLPOINTER)&colLength.val, sizeof(colLength.val), &colLength.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 9 to variable */ cliRC = SQLBindCol(hstmt, 9, SQL_C_SHORT, (SQLPOINTER)&colScale.val, sizeof(colScale.val), &colScale.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 11 to variable */ cliRC = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER)&colNullable.val, sizeof(colNullable.val), &colNullable.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 12 to variable */ cliRC = SQLBindCol(hstmt, 12, SQL_C_CHAR, colRemarks.val, 255, &colRemarks.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ printf("\n Fetch each row and display.\n"); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } while (cliRC != SQL_NO_DATA_FOUND) { printf(" %-10.10s", colName.val); if (colNullable.val == SQL_NULLABLE) { printf(", NULLABLE"); } else { printf(", NOT NULLABLE"); } printf(", %s", colType.val); if (colLength.ind != SQL_NULL_DATA) { printf(" (%ld", colLength.val); } else { printf("("); } if (colScale.ind != SQL_NULL_DATA) { printf(", %d)\n", colScale.val); } else { printf(")\n"); } /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); } /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); return rc; } /* TbListColumns */
/* --------------------------------------------------------- TestSQLProcedureColumns --------------------------------------------------------- */ PassFail TestMXSQLProcedureColumns(TestInfo *pTestInfo) { TEST_DECLARE; TCHAR Heading[MAX_HEADING_SIZE]; RETCODE returncode; SQLHANDLE henv; SQLHANDLE hdbc; SQLHANDLE hstmt; TCHAR *ProcStr; TCHAR ProcCatalog[NAME_LEN],ProcSchema[NAME_LEN],ProcName[NAME_LEN]; TCHAR ColName[NAME_LEN]; /* SEAQUEST */ char tmpbuf[1024]; TCHAR *myTestSch; TCHAR *createSchStr; TCHAR *setSchStr; TCHAR *dropSchStr; /* end of SEAQUEST */ TCHAR oProcCatalog[NAME_LEN]; TCHAR oProcSchema[NAME_LEN]; TCHAR oProcName[NAME_LEN]; TCHAR oColName[NAME_LEN]; SWORD oColType; SWORD oColDataType; TCHAR oColTypeName[NAME_LEN]; SDWORD oColSize; SDWORD oBufferLen; SWORD oDecDigits; SWORD oColRadix; SWORD oColNullable; TCHAR oRemark[REM_LEN]; TCHAR oColDef[NAME_LEN]; SWORD oSQLDataType; SWORD oSQLDateTimeSub; SDWORD oCharOctetLen; SDWORD oOrdinalPos; TCHAR oIsNullable[NAME_LEN]; SQLLEN oProcCataloglen; // sushil SQLLEN oProcSchemalen; SQLLEN oProcNamelen; SQLLEN oColNamelen; SQLLEN oColTypelen; SQLLEN oColDataTypelen; SQLLEN oColTypeNamelen; SQLLEN oColSizelen; SQLLEN oBufferLenlen; SQLLEN oDecDigitslen; SQLLEN oColRadixlen; SQLLEN oColNullablelen; SQLLEN oRemarklen; SQLLEN oColDeflen; SQLLEN oSQLDataTypelen; SQLLEN oSQLDateTimeSublen; SQLLEN oCharOctetLenlen; SQLLEN oOrdinalPoslen; SQLLEN oIsNullablelen; struct { TCHAR *DropProc; TCHAR *CrtProc; } CreateProc[] = { {_T("DROP PROCEDURE N4210"), _T("CREATE PROCEDURE N4210 (IN IN1 TIME) EXTERNAL NAME 'Procs.N4210' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4260"), _T("CREATE PROCEDURE N4260 (IN IN1 REAL, INOUT INOUT1 INTEGER) EXTERNAL NAME 'Procs.N4260' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4261"), _T("CREATE PROCEDURE N4261 (IN IN1 NUMERIC, INOUT INOUT1 REAL) EXTERNAL NAME 'Procs.N4261' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264"), _T("CREATE PROCEDURE N4264 (IN IN1 VARCHAR(30) CHARACTER SET ISO88591, OUT OUT1 VARCHAR(45) CHARACTER SET ISO88591) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264A"), _T("CREATE PROCEDURE N4264A (IN IN1 CHAR(30) CHARACTER SET ISO88591, OUT OUT1 CHAR(45) CHARACTER SET ISO88591) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264B"), _T("CREATE PROCEDURE N4264B (IN IN1 VARCHAR(2000) CHARACTER SET ISO88591, OUT OUT1 VARCHAR(2000) CHARACTER SET ISO88591) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264C"), _T("CREATE PROCEDURE N4264C (IN IN1 VARCHAR(30) CHARACTER SET UCS2, OUT OUT1 VARCHAR(45) CHARACTER SET UCS2) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264D"), _T("CREATE PROCEDURE N4264D (IN IN1 CHAR(30) CHARACTER SET UCS2, OUT OUT1 CHAR(45) CHARACTER SET UCS2) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4264E"), _T("CREATE PROCEDURE N4264E (IN IN1 VARCHAR(2000) CHARACTER SET UCS2, OUT OUT1 VARCHAR(2000) CHARACTER SET UCS2) EXTERNAL NAME 'Procs.N4264' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("DROP PROCEDURE N4267"), _T("CREATE PROCEDURE N4267 (IN IN1 NUMERIC, INOUT INOUT1 REAL) EXTERNAL NAME 'Procs.N4267' EXTERNAL PATH '/home/SQFQA/SPJRoot/spjrs/nci/spjrs.jar' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO ISOLATE")}, {_T("endloop"),_T("endloop")} }; struct { TCHAR *ProcName; TCHAR *ColName; SWORD ColType; SWORD ColDataType; TCHAR *ColTypeName; SDWORD ColSize; SDWORD BufferLen; SWORD DecDigits; SWORD ColRadix; SWORD ColNullable; TCHAR *Remark; TCHAR *ColDef; SWORD SQLDataType; SWORD SQLDateTimeSub; SDWORD CharOctetLen; SDWORD OrdinalPos; TCHAR *IsNullable; } ProcCol[] = { /* {"N4210", "IN1", SQL_PARAM_INPUT, SQL_TYPE_TIME, "TIME", 8, 6, 0, <Null>, SQL_NULLABLE, <Null>, <Null>, 9, 2, <Null>, 1, "YES"}, {"N4260", "IN1", SQL_PARAM_INPUT, SQL_REAL, "REAL", 22, 4, 0, 2, SQL_NULLABLE, <Null>, <Null>, 7, <Null>, <Null>, 1, "YES"}, {"N4260", "INOUT1", SQL_PARAM_INPUT_OUTPUT, SQL_INTEGER, "INTEGER SIGNED", 10, 4, 0, 10, SQL_NULLABLE, <Null>, <Null>, 4, <Null>, <Null>, 2, "YES"}, {"N4261", "IN1", SQL_PARAM_INPUT, SQL_NUMERIC, "NUMERIC", 9, 11, 0, 10, SQL_NULLABLE, <Null>, <Null>, 2, <Null>, <Null>, 1, "YES"}, {"N4261", "INOUT1", SQL_PARAM_INPUT_OUTPUT, SQL_REAL, "REAL", 22, 4, 0, 2, SQL_NULLABLE, <Null>, <Null>, 7, <Null>, <Null>, 2, "YES"}, {"N4264", "IN1", SQL_PARAM_INPUT, SQL_WVARCHAR, "VARCHAR", 30, 30, 0, <Null>, SQL_NULLABLE, <Null>, <Null>, 12,<Null>, 30, 1, "YES"}, {"N4264", "OUT1", SQL_PARAM_OUTPUT, SQL_WVARCHAR, "VARCHAR", 45, 45, 0, <Null>, SQL_NULLABLE, <Null>, <Null>, 12,<Null>, 45, 2, "YES"}, {"N4267", "IN1", SQL_PARAM_INPUT, SQL_NUMERIC, "NUMERIC", 9, 11, 0, 10, SQL_NULLABLE, <Null>, <Null>, 2, <Null>, <Null>, 1, "YES"}, {"N4267", "INOUT1", SQL_PARAM_INPUT_OUTPUT, SQL_REAL, "REAL", 22, 4, 0, 2, SQL_NULLABLE, <Null>, <Null>, 7, <Null>, <Null>, 2, "YES"}, */ {_T("N4210"), _T("IN1"), SQL_PARAM_INPUT, SQL_TYPE_TIME, _T("TIME"), 8, 6, 0, 0, SQL_NULLABLE, _T(""), _T(""), 9, 2, 0, 1, _T("YES")}, {_T("N4260"), _T("IN1"), SQL_PARAM_INPUT, SQL_REAL, _T("REAL"), 22, 4, 0, 2, SQL_NULLABLE, _T(""), _T(""), 7, 0, 0, 1, _T("YES")}, {_T("N4260"), _T("INOUT1"), SQL_PARAM_INPUT_OUTPUT, SQL_INTEGER, _T("INTEGER SIGNED"),10, 4, 0, 10, SQL_NULLABLE, _T(""), _T(""), 4, 0, 0, 2, _T("YES")}, {_T("N4261"), _T("IN1"), SQL_PARAM_INPUT, SQL_NUMERIC, _T("NUMERIC SIGNED"),9, 11, 0, 10, SQL_NULLABLE, _T(""), _T(""), 2, 0, 0, 1, _T("YES")}, {_T("N4261"), _T("INOUT1"), SQL_PARAM_INPUT_OUTPUT, SQL_REAL, _T("REAL"), 22, 4, 0, 2, SQL_NULLABLE, _T(""), _T(""), 7, 0, 0, 2, _T("YES")}, {_T("N4264"), _T("IN1"), SQL_PARAM_INPUT, SQL_VARCHAR, _T("VARCHAR"), 30, 30, 0, 0, SQL_NULLABLE, _T(""), _T(""), 12,0, 30,1, _T("YES")}, {_T("N4264"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_VARCHAR, _T("VARCHAR"), 45, 45, 0, 0, SQL_NULLABLE, _T(""), _T(""), 12,0, 45,2, _T("YES")}, {_T("N4264A"), _T("IN1"), SQL_PARAM_INPUT, SQL_CHAR, _T("CHAR"), 30, 30, 0, 0, SQL_NULLABLE, _T(""), _T(""), 1,0, 30,1, _T("YES")}, {_T("N4264A"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_CHAR, _T("CHAR"), 45, 45, 0, 0, SQL_NULLABLE, _T(""), _T(""), 1,0, 45,2, _T("YES")}, {_T("N4264B"), _T("IN1"), SQL_PARAM_INPUT, SQL_VARCHAR, _T("VARCHAR"),2000,2000,0,0, SQL_NULLABLE, _T(""), _T(""), 12,0,2000,1, _T("YES")}, {_T("N4264B"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_VARCHAR, _T("VARCHAR"),2000,2000,0,0, SQL_NULLABLE, _T(""), _T(""), 12,0,2000,2, _T("YES")}, {_T("N4264C"), _T("IN1"), SQL_PARAM_INPUT, SQL_WVARCHAR, _T("NCHAR VARYING"),30, 60, 0, 0, SQL_NULLABLE, _T(""), _T(""), -9,0, 60,1, _T("YES")}, {_T("N4264C"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_WVARCHAR, _T("NCHAR VARYING"),45, 90, 0, 0, SQL_NULLABLE, _T(""), _T(""), -9,0, 90,2, _T("YES")}, {_T("N4264D"), _T("IN1"), SQL_PARAM_INPUT, SQL_WCHAR, _T("NCHAR"), 30, 60, 0, 0, SQL_NULLABLE, _T(""), _T(""), -8,0, 60,1, _T("YES")}, {_T("N4264D"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_WCHAR, _T("NCHAR"), 45, 90, 0, 0, SQL_NULLABLE, _T(""), _T(""), -8,0, 90,2, _T("YES")}, {_T("N4264E"), _T("IN1"), SQL_PARAM_INPUT, SQL_WVARCHAR,_T("NCHAR VARYING"),2000,4000,0,0, SQL_NULLABLE, _T(""), _T(""),-9,0,4000,1, _T("YES")}, {_T("N4264E"), _T("OUT1"), SQL_PARAM_OUTPUT, SQL_WVARCHAR,_T("NCHAR VARYING"),2000,4000,0,0, SQL_NULLABLE, _T(""), _T(""),-9,0,4000,2, _T("YES")}, {_T("N4267"), _T("IN1"), SQL_PARAM_INPUT, SQL_NUMERIC, _T("NUMERIC SIGNED"),9, 11, 0, 10, SQL_NULLABLE, _T(""), _T(""), 2, 0, 0, 1, _T("YES")}, {_T("N4267"), _T("INOUT1"), SQL_PARAM_INPUT_OUTPUT, SQL_REAL, _T("REAL"), 22, 4, 0, 2, SQL_NULLABLE, _T(""), _T(""), 7, 0, 0, 2, _T("YES")}, {_T("endloop"),} }; int i = 0, k = 0; TCHAR State[STATE_SIZE]; SDWORD NativeError; TCHAR buf[MAX_STRING_SIZE]; //TCHAR *charNameUCS2 = _T("NCHAR"); //TCHAR *varcharNameUCS2 = _T("NCHAR VARYING"); LogMsg(SHORTTIMESTAMP,_T("Begin testing API => MX Specific SQLProcedureColumns.\n")); //================================================================================================= //if(isUCS2) { // LogMsg(LINEBEFORE,_T("Setup for UCS2 mode testing: ColSize,BufferLen and CharOctetlen has to be doubled\n")); // k = sizeof(ProcCol)/sizeof(ProcCol[0]); // while(i < k) { // if(ProcCol[i].ColDataType == SQL_WCHAR) // { // ProcCol[i].ColDataType = SQL_WCHAR; // ProcCol[i].SQLDataType = SQL_WCHAR; // ProcCol[i].ColTypeName = charNameUCS2; // //ProcCol[i].ColSize *= 2; --> This is in character, no need to double // ProcCol[i].BufferLen *= 2; // ProcCol[i].CharOctetLen *= 2; // } // else if (ProcCol[i].ColDataType == SQL_WVARCHAR) // { // ProcCol[i].ColDataType = SQL_WVARCHAR; // ProcCol[i].SQLDataType = SQL_WVARCHAR; // ProcCol[i].ColTypeName = varcharNameUCS2; // //ProcCol[i].ColSize *= 2; --> This is in character, no need to double // ProcCol[i].BufferLen *= 2; // ProcCol[i].CharOctetLen *= 2; // } // else if (ProcCol[i].ColDataType == SQL_WLONGVARCHAR) // { // ProcCol[i].ColDataType = SQL_WLONGVARCHAR; // ProcCol[i].SQLDataType = SQL_WLONGVARCHAR; // ProcCol[i].ColTypeName = varcharNameUCS2; // //ProcCol[i].ColSize *= 2; --> This is in character, no need to double // ProcCol[i].BufferLen *= 2; // ProcCol[i].CharOctetLen *= 2; // } // else {} // i++; // } // i = 0; // k = 0; //} //================================================================================================= TEST_INIT; TESTCASE_BEGIN("Setup for SQLProcedureColumns tests\n"); if(!FullConnectWithOptions(pTestInfo, CONNECT_ODBC_VERSION_3)) { LogMsg(NONE,_T("Unable to connect\n")); //TEST_FAILED; TEST_RETURN; } henv = pTestInfo->henv; hdbc = pTestInfo->hdbc; hstmt = (SQLHANDLE)pTestInfo->hstmt; returncode = SQLAllocHandle(SQL_HANDLE_STMT, (SQLHANDLE)hdbc, &hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLAllocHandle")) { LogAllErrorsVer3(henv,hdbc,hstmt); FullDisconnect3(pTestInfo); //TEST_FAILED; TEST_RETURN; } //TESTCASE_END; ProcStr = (TCHAR *)malloc(MAX_NOS_SIZE); /* SEAQUEST */ myTestSch = (TCHAR *)malloc(MAX_NOS_SIZE); createSchStr = (TCHAR *)malloc(MAX_NOS_SIZE); setSchStr = (TCHAR *)malloc(MAX_NOS_SIZE); dropSchStr = (TCHAR *)malloc(MAX_NOS_SIZE); _tcscpy (myTestSch, _T("ODBC_PROCCOL_TEST")); _tcscpy (createSchStr, _T("create schema ")); _tcscat (createSchStr, pTestInfo->Catalog); _tcscat (createSchStr, _T(".")); _tcscat (createSchStr, myTestSch); _tcscpy (setSchStr, _T("set schema ")); _tcscat (setSchStr, pTestInfo->Catalog); _tcscat (setSchStr, _T(".")); _tcscat (setSchStr, myTestSch); _tcscpy (dropSchStr, _T("drop schema ")); _tcscat (dropSchStr, pTestInfo->Catalog); _tcscat (dropSchStr, _T(".")); _tcscat (dropSchStr, myTestSch); _tcscat (dropSchStr, _T(" cascade")); returncode = SQLExecDirect(hstmt,(SQLTCHAR*) dropSchStr,SQL_NTS); // if(!CHECKRC(SQL_SUCCESS,returncode,"Drop Schema")) // { // TEST_FAILED; // LogAllErrors(henv,hdbc,hstmt); // } returncode = SQLExecDirect(hstmt,(SQLTCHAR*) createSchStr,SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"Create Schema")) { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } returncode = SQLExecDirect(hstmt,(SQLTCHAR*) setSchStr,SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"Set Schema")) { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } /* end of SEAQUEST */ while (_tcsicmp(CreateProc[i].DropProc,_T("endloop")) != 0) { _tcscpy(ProcStr,_T("")); _tcscat(ProcStr,CreateProc[i].DropProc); SQLExecDirect(hstmt,(SQLTCHAR*) ProcStr,SQL_NTS); // cleanup _tcscpy(ProcStr,_T("")); _tcscat(ProcStr,CreateProc[i].CrtProc); _stprintf(Heading,_T("Adding Procedure => ")); _tcscat(Heading,ProcStr); _tcscat(Heading,_T("\n")); TESTCASE_BEGINW(Heading); returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ProcStr,SQL_NTS); if(returncode != SQL_SUCCESS) { returncode = SQLError((SQLHANDLE)NULL, (SQLHANDLE)NULL, hstmt, (SQLTCHAR*)State, &NativeError, (SQLTCHAR*)buf, MAX_STRING_SIZE, NULL); if (NativeError == -2013) { LogMsg(NONE, _T("Stored Procedures not supported\n")); _gTestCount--; free(ProcStr); FullDisconnect(pTestInfo); LogMsg(SHORTTIMESTAMP,_T("End testing API => MX Specific SQLProcedureColumns.\n")); TEST_RETURN; } else { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } } TESTCASE_END; i++; } _tcscpy(ProcCatalog, pTestInfo->Catalog); _tcscpy(ProcSchema, myTestSch /*SEAQUEST pTestInfo->Schema*/); _stprintf(Heading,_T("Test Positive Functionality of SQLProcedureColumns \n")); TESTCASE_BEGINW(Heading); returncode = SQLProcedureColumns(hstmt,(SQLTCHAR*)pTestInfo->Catalog,(SWORD)_tcslen(pTestInfo->Catalog),(SQLTCHAR*)myTestSch/* SEAQUEST pTestInfo->Schema*/,(SWORD)_tcslen(myTestSch/* SEAQUEST pTestInfo->Schema*/),(SQLTCHAR *)_T("%"),(SWORD)1,(SQLTCHAR *)_T("%"),(SWORD)1); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLProcedureColumns")) { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } if (returncode == SQL_SUCCESS) { _tcscpy(oProcCatalog,_T("")); _tcscpy(oProcSchema,_T("")); _tcscpy(oProcName,_T("")); _tcscpy(oColName,_T("")); oColType = 0; oColDataType = 0; _tcscpy(oColTypeName,_T("")); oColSize = 0; oBufferLen = 0; oDecDigits = 0; oColRadix = 0; oColNullable = 0; _tcscpy(oRemark,_T("")); _tcscpy(oColDef,_T("")); oSQLDataType = 0; oSQLDateTimeSub = 0; oCharOctetLen = 0; oOrdinalPos = 0; _tcscpy(oIsNullable,_T("")); SQLBindCol(hstmt,1,SQL_C_TCHAR,oProcCatalog,NAME_LEN,&oProcCataloglen); SQLBindCol(hstmt,2,SQL_C_TCHAR,oProcSchema,NAME_LEN,&oProcSchemalen); SQLBindCol(hstmt,3,SQL_C_TCHAR,oProcName,NAME_LEN,&oProcNamelen); SQLBindCol(hstmt,4,SQL_C_TCHAR,oColName,NAME_LEN,&oColNamelen); SQLBindCol(hstmt,5,SQL_C_SHORT,&oColType,0,&oColTypelen); SQLBindCol(hstmt,6,SQL_C_SHORT,&oColDataType,0,&oColDataTypelen); SQLBindCol(hstmt,7,SQL_C_TCHAR,oColTypeName,NAME_LEN,&oColTypeNamelen); SQLBindCol(hstmt,8,SQL_C_LONG,&oColSize,0,&oColSizelen); SQLBindCol(hstmt,9,SQL_C_LONG,&oBufferLen,0,&oBufferLenlen); SQLBindCol(hstmt,10,SQL_C_SHORT,&oDecDigits,0,&oDecDigitslen); SQLBindCol(hstmt,11,SQL_C_SHORT,&oColRadix,0,&oColRadixlen); SQLBindCol(hstmt,12,SQL_C_SHORT,&oColNullable,0,&oColNullablelen); SQLBindCol(hstmt,13,SQL_C_TCHAR,oRemark,REM_LEN,&oRemarklen); SQLBindCol(hstmt,14,SQL_C_TCHAR,oColDef,NAME_LEN,&oColDeflen); SQLBindCol(hstmt,15,SQL_C_SHORT,&oSQLDataType,0,&oSQLDataTypelen); SQLBindCol(hstmt,16,SQL_C_SHORT,&oSQLDateTimeSub,0,&oSQLDateTimeSublen); SQLBindCol(hstmt,17,SQL_C_LONG,&oCharOctetLen,0,&oCharOctetLenlen); SQLBindCol(hstmt,18,SQL_C_LONG,&oOrdinalPos,0,&oOrdinalPoslen); SQLBindCol(hstmt,19,SQL_C_TCHAR,oIsNullable,NAME_LEN,&oIsNullablelen); k = 0; i = 0; while (returncode == SQL_SUCCESS) { if(_tcscmp(ProcCol[i].ProcName,_T("endloop")) == 0) break; returncode = SQLFetch(hstmt); if((returncode!=SQL_NO_DATA_FOUND) &&(!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch"))) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else { if (returncode == SQL_SUCCESS) { k++; _stprintf(Heading,_T("SQLProcedureColumns: Comparing Results\n")); TESTCASE_BEGINW(Heading); if ((_tcsicmp(ProcCatalog,oProcCatalog) == 0) && (_tcsicmp(ProcSchema,oProcSchema) == 0) && (_tcsicmp(ProcCol[i].ProcName,oProcName) == 0) && (_tcsicmp(ProcCol[i].ColName,oColName) == 0) && (ProcCol[i].ColType == oColType) && (ProcCol[i].ColDataType == oColDataType) && (_tcsicmp(ProcCol[i].ColTypeName,oColTypeName) == 0) && (ProcCol[i].ColSize == oColSize) && (ProcCol[i].BufferLen == oBufferLen) && (ProcCol[i].DecDigits == oDecDigits) && (ProcCol[i].ColRadix == oColRadix) && (ProcCol[i].ColNullable == oColNullable) && (_tcsicmp(ProcCol[i].Remark,oRemark) == 0) && (_tcsicmp(ProcCol[i].ColDef,oColDef) == 0) && (ProcCol[i].SQLDataType == oSQLDataType) && (ProcCol[i].SQLDateTimeSub == oSQLDateTimeSub) && (ProcCol[i].CharOctetLen == oCharOctetLen) && (ProcCol[i].OrdinalPos == oOrdinalPos) && (_tcsicmp(ProcCol[i].IsNullable,oIsNullable) == 0)){ LogMsg(NONE,_T("ProcCatalog expect: %s and actual: %s are matched\n"),ProcCatalog,oProcCatalog); LogMsg(NONE,_T("ProcSchema expect: %s and actual: %s are matched\n"),ProcSchema,oProcSchema); LogMsg(NONE,_T("ProcName expect: %s and actual: %s are matched\n"),ProcCol[i].ProcName,oProcName); LogMsg(NONE,_T("ColName expect: %s and actual: %s are matched\n"),ProcCol[i].ColName,oColName); LogMsg(NONE,_T("ColType expect: %d and actual: %d are matched\n"),ProcCol[i].ColType,oColType); LogMsg(NONE,_T("ColDataType expect: %d and actual: %d are matched\n"),ProcCol[i].ColDataType,oColDataType); LogMsg(NONE,_T("ColTypeName expect: %s and actual: %s are matched\n"),ProcCol[i].ColTypeName,oColTypeName); LogMsg(NONE,_T("ColSize expect: %d and actual: %d are matched\n"),ProcCol[i].ColSize,oColSize); LogMsg(NONE,_T("BufferLen expect: %d and actual: %d are matched\n"),ProcCol[i].BufferLen,oBufferLen); LogMsg(NONE,_T("DecDigits expect: %d and actual: %d are matched\n"),ProcCol[i].DecDigits,oDecDigits); LogMsg(NONE,_T("ColRadix expect: %d and actual: %d are matched\n"),ProcCol[i].ColRadix,oColRadix); LogMsg(NONE,_T("ColNullable expect: %d and actual: %d are matched\n"),ProcCol[i].ColNullable,oColNullable); LogMsg(NONE,_T("Remark expect: %s and actual: %s are matched\n"),ProcCol[i].Remark,oRemark); LogMsg(NONE,_T("ColDef expect: %s and actual: %s are matched\n"),ProcCol[i].ColDef,oColDef); LogMsg(NONE,_T("SQLDataType expect: %d and actual: %d are matched\n"),ProcCol[i].SQLDataType,oSQLDataType); LogMsg(NONE,_T("SQLDateTimeSub expect: %d and actual: %d are matched\n"),ProcCol[i].SQLDateTimeSub,oSQLDateTimeSub); LogMsg(NONE,_T("CharOctetLen expect: %d and actual: %d are matched\n"),ProcCol[i].CharOctetLen,oCharOctetLen); LogMsg(NONE,_T("OrdinalPos expect: %d and actual: %d are matched\n"),ProcCol[i].OrdinalPos,oOrdinalPos); LogMsg(NONE,_T("IsNullable expect: %s and actual: %s are matched\n"),ProcCol[i].IsNullable,oIsNullable); } else { TEST_FAILED; if (_tcsicmp(ProcCatalog,oProcCatalog) != 0) LogMsg(ERRMSG,_T("ProcCatalog expect: %s and actual: %s are not matched\n"),ProcCatalog,oProcCatalog); else LogMsg(NONE,_T("ProcCatalog expect: %s and actual: %s are matched\n"),ProcCatalog,oProcCatalog); if (_tcsicmp(ProcSchema,oProcSchema) != 0) LogMsg(ERRMSG,_T("ProcSchema expect: %s and actual: %s are not matched\n"),ProcSchema,oProcSchema); else LogMsg(NONE,_T("ProcSchema expect: %s and actual: %s are matched\n"),ProcSchema,oProcSchema); if (_tcsicmp(ProcCol[i].ProcName,oProcName) != 0) LogMsg(ERRMSG,_T("ProcName expect: %s and actual: %s are not matched\n"),ProcCol[i].ProcName,oProcName); else LogMsg(NONE,_T("ProcName expect: %s and actual: %s are matched\n"),ProcCol[i].ProcName,oProcName); if (_tcsicmp(ProcCol[i].ColName,oColName) != 0) LogMsg(ERRMSG,_T("ColName expect: %s and actual: %s are not matched\n"),ProcCol[i].ColName,oColName); else LogMsg(NONE,_T("ColName expect: %s and actual: %s are matched\n"),ProcCol[i].ColName,oColName); if (ProcCol[i].ColType != oColType) LogMsg(ERRMSG,_T("ColType expect: %d and actual: %d are not matched\n"),ProcCol[i].ColType,oColType); else LogMsg(NONE,_T("ColType expect: %d and actual: %d are matched\n"),ProcCol[i].ColType,oColType); if (ProcCol[i].ColDataType != oColDataType) LogMsg(ERRMSG,_T("ColDataType expect: %d and actual: %d are not matched\n"),ProcCol[i].ColDataType,oColDataType); else LogMsg(NONE,_T("ColDataType expect: %d and actual: %d are matched\n"),ProcCol[i].ColDataType,oColDataType); if (_tcsicmp(ProcCol[i].ColTypeName,oColTypeName) != 0) LogMsg(ERRMSG,_T("ColTypeName expect: %s and actual: %s are not matched\n"),ProcCol[i].ColTypeName,oColTypeName); else LogMsg(NONE,_T("ColTypeName expect: %s and actual: %s are matched\n"),ProcCol[i].ColTypeName,oColTypeName); if (ProcCol[i].ColSize != oColSize) LogMsg(ERRMSG,_T("ColSize expect: %d and actual: %d are not matched\n"),ProcCol[i].ColSize,oColSize); else LogMsg(NONE,_T("ColSize expect: %d and actual: %d are matched\n"),ProcCol[i].ColSize,oColSize); if (ProcCol[i].BufferLen != oBufferLen) LogMsg(ERRMSG,_T("BufferLen expect: %d and actual: %d are not matched\n"),ProcCol[i].BufferLen,oBufferLen); else LogMsg(NONE,_T("BufferLen expect: %d and actual: %d are matched\n"),ProcCol[i].BufferLen,oBufferLen); if (ProcCol[i].DecDigits != oDecDigits) LogMsg(ERRMSG,_T("DecDigits expect: %d and actual: %d are not matched\n"),ProcCol[i].DecDigits,oDecDigits); else LogMsg(NONE,_T("DecDigits expect: %d and actual: %d are matched\n"),ProcCol[i].DecDigits,oDecDigits); if (ProcCol[i].ColRadix != oColRadix) LogMsg(ERRMSG,_T("ColRadix expect: %d and actual: %d are not matched\n"),ProcCol[i].ColRadix,oColRadix); else LogMsg(NONE,_T("ColRadix expect: %d and actual: %d are matched\n"),ProcCol[i].ColRadix,oColRadix); if (ProcCol[i].ColNullable != oColNullable) LogMsg(ERRMSG,_T("ColNullable expect: %d and actual: %d are not matched\n"),ProcCol[i].ColNullable,oColNullable); else LogMsg(NONE,_T("ColNullable expect: %d and actual: %d are matched\n"),ProcCol[i].ColNullable,oColNullable); if (_tcsicmp(ProcCol[i].Remark,oRemark) != 0) LogMsg(ERRMSG,_T("Remark expect: %s and actual: %s are not matched\n"),ProcCol[i].Remark,oRemark); else LogMsg(NONE,_T("Remark expect: %s and actual: %s are matched\n"),ProcCol[i].Remark,oRemark); if (_tcsicmp(ProcCol[i].ColDef,oColDef) != 0) LogMsg(ERRMSG,_T("Remark expect: %s and actual: %s are not matched\n"),ProcCol[i].ColDef,oColDef); else LogMsg(NONE,_T("Remark expect: %s and actual: %s are matched\n"),ProcCol[i].ColDef,oColDef); if (ProcCol[i].SQLDataType != oSQLDataType) LogMsg(ERRMSG,_T("SQLDataType expect: %d and actual: %d are not matched\n"),ProcCol[i].SQLDataType,oSQLDataType); else LogMsg(NONE,_T("SQLDataType expect: %d and actual: %d are matched\n"),ProcCol[i].SQLDataType,oSQLDataType); if (ProcCol[i].SQLDateTimeSub != oSQLDateTimeSub) LogMsg(ERRMSG,_T("SQLDateTimeSub expect: %d and actual: %d are not matched\n"),ProcCol[i].SQLDateTimeSub,oSQLDateTimeSub); else LogMsg(NONE,_T("SQLDateTimeSub expect: %d and actual: %d are matched\n"),ProcCol[i].SQLDateTimeSub,oSQLDateTimeSub); if (ProcCol[i].CharOctetLen != oCharOctetLen) LogMsg(ERRMSG,_T("CharOctetLen expect: %d and actual: %d are not matched\n"),ProcCol[i].CharOctetLen,oCharOctetLen); else LogMsg(NONE,_T("CharOctetLen expect: %d and actual: %d are matched\n"),ProcCol[i].CharOctetLen,oCharOctetLen); if (ProcCol[i].OrdinalPos != oOrdinalPos) LogMsg(ERRMSG,_T("OrdinalPos expect: %d and actual: %d are not matched\n"),ProcCol[i].OrdinalPos,oOrdinalPos); else LogMsg(NONE,_T("OrdinalPos expect: %d and actual: %d are matched\n"),ProcCol[i].OrdinalPos,oOrdinalPos); if (_tcsicmp(ProcCol[i].IsNullable,oIsNullable) != 0) LogMsg(ERRMSG,_T("IsNullable expect: %s and actual: %s are not matched\n"),ProcCol[i].IsNullable,oIsNullable); else LogMsg(NONE,_T("IsNullable expect: %s and actual: %s are matched\n"),ProcCol[i].IsNullable,oIsNullable); } } } if(k == 0) { TEST_FAILED; LogMsg(ERRMSG,_T("No Data Found => Atleast one row should be fetched\n")); } TESTCASE_END; _tcscpy(oProcCatalog,_T("")); _tcscpy(oProcSchema,_T("")); _tcscpy(oProcName,_T("")); _tcscpy(oColName,_T("")); oColType = 0; oColDataType = 0; _tcscpy(oColTypeName,_T("")); oColSize = 0; oBufferLen = 0; oDecDigits = 0; oColRadix = 0; oColNullable = 0; _tcscpy(oRemark,_T("")); _tcscpy(oColDef,_T("")); oSQLDataType = 0; oSQLDateTimeSub = 0; oCharOctetLen = 0; oOrdinalPos = 0; _tcscpy(oIsNullable,_T("")); i++; } // while } SQLFreeStmt(hstmt,SQL_UNBIND); SQLFreeStmt(hstmt,SQL_CLOSE); i=0; while (_tcsicmp(CreateProc[i].DropProc,_T("endloop")) != 0) { _tcscpy(ProcStr,_T("")); _tcscat(ProcStr,CreateProc[i].DropProc); returncode = SQLExecDirect(hstmt,(SQLTCHAR*) ProcStr,SQL_NTS); // cleanup if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")) { LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } i++; } SQLFreeStmt(hstmt,SQL_UNBIND); SQLFreeStmt(hstmt,SQL_CLOSE); //======================================================================================================== _stprintf(Heading,_T("SQLProcedureColumns: Negative test with NULL handle\n")); TESTCASE_BEGINW(Heading); hstmt = (SQLHANDLE)NULL; _tcscpy(ProcName,_T("junkproc")); _tcscpy(ColName,_T("C1")); returncode = SQLProcedureColumns(hstmt,(SQLTCHAR*)pTestInfo->Catalog,(SWORD)_tcslen(pTestInfo->Catalog),(SQLTCHAR*)myTestSch /* SEAQUEST pTestInfo->Schema */,(SWORD)_tcslen(myTestSch /* SEAQUEST pTestInfo->Schema*/),(SQLTCHAR*)ProcName,(SWORD)_tcslen(ProcName),(SQLTCHAR*)ColName,(SWORD)_tcslen(ColName)); if(!CHECKRC(SQL_INVALID_HANDLE,returncode,"SQLProcedureColumns")) { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } TESTCASE_END; //======================================================================================================== returncode = SQLExecDirect(hstmt,(SQLTCHAR*) dropSchStr,SQL_NTS); free(ProcStr); FullDisconnect3(pTestInfo); LogMsg(SHORTTIMESTAMP+LINEAFTER,_T("End testing API => MX Specific SQLProcedureColumns.\n")); TEST_RETURN; }
/* get the number of rows and pages that meet specified criteria */ int TbListRowsAndPages(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ struct { SQLINTEGER ind; SQLINTEGER val; } tbCardinality, tbPages; struct { SQLINTEGER ind; SQLSMALLINT val; } infoType; /* criteria to look for */ SQLCHAR tbSchema[] = "SYSCAT"; SQLCHAR tbName[] = "TABLES"; printf("\n-----------------------------------------------------------"); printf("\nUSE THE CLI FUNCTIONS\n"); printf(" SQLSetConnectAttr\n"); printf(" SQLAllocHandle\n"); printf(" SQLStatistics\n"); printf(" SQLBindCol\n"); printf(" SQLFetch\n"); printf(" SQLFreeHandle\n"); printf("TO SHOW HOW TO GET THE NUMBER OF ROWS AND PAGES\n"); printf("THAT MEET SPECIFIED CRITERIA:\n"); /* set AUTOCOMMIT on */ cliRC = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS); DBC_HANDLE_CHECK(hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* call SQLStatistics */ printf("\n Call SQLStatistics for:\n"); printf(" tbSchema = %s\n", tbSchema); printf(" tbName = %s\n", tbName); /* get index and statistics information for a base table */ cliRC = SQLStatistics(hstmt, NULL, 0, tbSchema, SQL_NTS, tbName, SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 7 to variable */ cliRC = SQLBindCol(hstmt, 7, SQL_C_SHORT, (SQLPOINTER)&infoType.val, sizeof(infoType.val), &infoType.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 11 to variable */ cliRC = SQLBindCol(hstmt, 11, SQL_C_LONG, (SQLPOINTER)&tbCardinality.val, sizeof(tbCardinality.val), &tbCardinality.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 12 to variable */ cliRC = SQLBindCol(hstmt, 12, SQL_C_LONG, (SQLPOINTER)&tbPages.val, sizeof(tbPages.val), &tbPages.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ printf("\n Display the number of rows and pages.\n"); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } while (cliRC != SQL_NO_DATA_FOUND) { if (infoType.val == SQL_TABLE_STAT) { if (tbCardinality.ind == SQL_NULL_DATA) { printf(" Number of rows = (Unavailable)\n"); } else { printf(" Number of rows = %u\n", tbCardinality.val); } if (tbPages.ind == SQL_NULL_DATA) { printf(" Number of pages used to store the table ="); printf("(Unavailable)\n"); } else { printf(" Number of pages used to store the table = %u\n", tbCardinality.val); } break; } /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); } /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); return rc; } /* TbListRowsAndPages */
void TypeInfo::fillTypeInfo(SQLHDBC pHDBC) { _pHDBC = &pHDBC; if (_typeInfo.empty() && _pHDBC) { const static int stringSize = 512; TypeInfoVec().swap(_typeInfo); SQLRETURN rc; SQLHSTMT hstmt = SQL_NULL_HSTMT; rc = SQLAllocHandle(SQL_HANDLE_STMT, *_pHDBC, &hstmt); if (!SQL_SUCCEEDED(rc)) throw StatementException(hstmt, "SQLGetData()"); rc = SQLGetTypeInfo(hstmt, SQL_ALL_TYPES); if (SQL_SUCCEEDED(rc)) { while (SQLFetch(hstmt) != SQL_NO_DATA_FOUND) { char typeName[stringSize] = { 0 }; char literalPrefix[stringSize] = { 0 }; char literalSuffix[stringSize] = { 0 }; char createParams[stringSize] = { 0 }; char localTypeName[stringSize] = { 0 }; TypeInfoTup ti("TYPE_NAME", "", "DATA_TYPE", 0, "COLUMN_SIZE", 0, "LITERAL_PREFIX", "", "LITERAL_SUFFIX", "", "CREATE_PARAMS", "", "NULLABLE", 0, "CASE_SENSITIVE", 0, "SEARCHABLE", 0, "UNSIGNED_ATTRIBUTE", 0, "FIXED_PREC_SCALE", 0, "AUTO_UNIQUE_VALUE", 0, "LOCAL_TYPE_NAME", "", "MINIMUM_SCALE", 0, "MAXIMUM_SCALE", 0, "SQL_DATA_TYPE", 0, "SQL_DATETIME_SUB", 0, "NUM_PREC_RADIX", 0, "INTERVAL_PRECISION", 0); SQLLEN ind = 0; rc = SQLGetData(hstmt, 1, SQL_C_CHAR, typeName, sizeof(typeName), &ind); ti.set<0>(typeName); rc = SQLGetData(hstmt, 2, SQL_C_SSHORT, &ti.get<1>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 3, SQL_C_SLONG, &ti.get<2>(), sizeof(SQLINTEGER), &ind); rc = SQLGetData(hstmt, 4, SQL_C_CHAR, literalPrefix, sizeof(literalPrefix), &ind); ti.set<3>(literalPrefix); rc = SQLGetData(hstmt, 5, SQL_C_CHAR, literalSuffix, sizeof(literalSuffix), &ind); ti.set<4>(literalSuffix); rc = SQLGetData(hstmt, 6, SQL_C_CHAR, createParams, sizeof(createParams), &ind); ti.set<5>(createParams); rc = SQLGetData(hstmt, 7, SQL_C_SSHORT, &ti.get<6>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 8, SQL_C_SSHORT, &ti.get<7>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 9, SQL_C_SSHORT, &ti.get<8>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 10, SQL_C_SSHORT, &ti.get<9>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 11, SQL_C_SSHORT, &ti.get<10>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 12, SQL_C_SSHORT, &ti.get<11>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 13, SQL_C_CHAR, localTypeName, sizeof(localTypeName), &ind); ti.set<12>(localTypeName); rc = SQLGetData(hstmt, 14, SQL_C_SSHORT, &ti.get<13>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 15, SQL_C_SSHORT, &ti.get<14>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 16, SQL_C_SSHORT, &ti.get<15>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 17, SQL_C_SSHORT, &ti.get<16>(), sizeof(SQLSMALLINT), &ind); rc = SQLGetData(hstmt, 18, SQL_C_SLONG, &ti.get<17>(), sizeof(SQLINTEGER), &ind); rc = SQLGetData(hstmt, 19, SQL_C_SSHORT, &ti.get<18>(), sizeof(SQLSMALLINT), &ind); _typeInfo.push_back(ti); } } SQLFreeHandle(SQL_HANDLE_STMT, hstmt); } }
/* get the column privileges that meet specified criteria */ int TbListColumnPrivileges(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ SQLHANDLE hstmtTable; struct { SQLINTEGER ind; SQLCHAR val[129]; } colName, grantor, grantee, privilege; struct { SQLINTEGER ind; SQLCHAR val[4]; } is_grantable; /* criteria to look for */ SQLCHAR tbSchema[] = "SCHEMA"; SQLCHAR tbName[] = "TABLE_NAME"; SQLCHAR colNamePattern[] = "%"; SQLCHAR stmt[100]; printf("\n-----------------------------------------------------------"); printf("\nUSE THE CLI FUNCTIONS\n"); printf(" SQLSetConnectAttr\n"); printf(" SQLAllocHandle\n"); printf(" SQLTablePrivileges\n"); printf(" SQLBindCol\n"); printf(" SQLFetch\n"); printf(" SQLFreeHandle\n"); printf("TO GET COLUMN PRIVILEGES THAT MEET SPECIFIED CRITERIA:\n"); /* set AUTOCOMMIT on */ cliRC = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS); DBC_HANDLE_CHECK(hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtTable); DBC_HANDLE_CHECK(hdbc, cliRC); /* create a test table */ sprintf((char *)stmt, "CREATE TABLE %s.%s (COL1 CHAR(10))", tbSchema, tbName); printf("\n Directly execute\n"); printf(" %s\n", stmt); /* directly execute the statement */ cliRC = SQLExecDirect(hstmtTable, stmt, SQL_NTS); STMT_HANDLE_CHECK(hstmtTable, hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* call SQLColumnPrivileges */ printf("\n Call SQLColumnPrivileges for:\n"); printf(" tbSchema = %s\n", tbSchema); printf(" tbName = %s\n", tbName); /* get privileges associated with the columns of a table */ cliRC = SQLColumnPrivileges(hstmt, NULL, 0, tbSchema, SQL_NTS, tbName, SQL_NTS, colNamePattern, SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 4 to variable */ cliRC = SQLBindCol(hstmt, 4, SQL_C_CHAR, colName.val, 129, &colName.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 5 to variable */ cliRC = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER)grantor.val, 129, &grantor.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 6 to variable */ cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER)grantee.val, 129, &grantee.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 7 to variable */ cliRC = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER)privilege.val, 129, &privilege.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 8 to variable */ cliRC = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER)is_grantable.val, 4, &is_grantable.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ printf("\n Current User's Privileges for table %s.%s\n", tbSchema, tbName); printf(" Column Grantor Grantee Privilege Grantable\n"); printf(" ------- -------- ------------ ---------- ---------\n"); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } while (cliRC != SQL_NO_DATA_FOUND) { printf(" %-7s", colName.val); printf(" %-8s", grantor.val); printf(" %-12s", grantee.val); printf(" %-10s", privilege.val); printf(" %-3s\n", is_grantable.val); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); } /* endwhile */ /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* drop the test table */ sprintf((char *)stmt, "DROP TABLE %s.%s", tbSchema, tbName); printf("\n Directly execute\n"); printf(" %s\n", stmt); /* directly execute the statement */ cliRC = SQLExecDirect(hstmtTable, stmt, SQL_NTS); STMT_HANDLE_CHECK(hstmtTable, hdbc, cliRC); /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmtTable); STMT_HANDLE_CHECK(hstmtTable, hdbc, cliRC); return rc; } /* TbListColumnPrivileges */
/* --------------------------------------------------------- TestSQLNumResultsCol --------------------------------------------------------- */ PassFail TestSQLNumResultCols(TestInfo *pTestInfo) { TEST_DECLARE; TCHAR Heading[MAX_STRING_SIZE]; RETCODE returncode; SQLHANDLE henv; SQLHANDLE hdbc; SQLHANDLE hstmt; SWORD col; TCHAR *ExecDirStr[] = {_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--"),_T("--")}; TCHAR *TestCase[] = { _T("after preparing stmt "), _T("after preparing & executing stmt "), _T("after preparing, executing & fetching stmt "), _T("after execdirect stmt "), _T("after execdirect & fetching stmt "), _T("after preparing param stmt "), _T("after preparing & binding stmt "), _T("after preparing, binding & executing stmt "), _T("after preparing, binding, executing & fetching stmt ") }; int lend = 9, iend = 5; SQLUSMALLINT i = 0, l = 0; int expcol[] = {1,2,6,9,2}; SQLLEN cbIn = SQL_NTS; //=========================================================================================================== var_list_t *var_list; var_list = load_api_vars(_T("SQLNumResultCols"), charset_file); if (var_list == NULL) return FAILED; ExecDirStr[0] = var_mapping(_T("SQLNumResultCols_ExecDirStr_0"), var_list); ExecDirStr[1] = var_mapping(_T("SQLNumResultCols_ExecDirStr_1"), var_list); ExecDirStr[2] = var_mapping(_T("SQLNumResultCols_ExecDirStr_2"), var_list); ExecDirStr[3] = var_mapping(_T("SQLNumResultCols_ExecDirStr_3"), var_list); ExecDirStr[4] = var_mapping(_T("SQLNumResultCols_ExecDirStr_4"), var_list); ExecDirStr[5] = var_mapping(_T("SQLNumResultCols_ExecDirStr_5"), var_list); ExecDirStr[6] = var_mapping(_T("SQLNumResultCols_ExecDirStr_6"), var_list); ExecDirStr[7] = var_mapping(_T("SQLNumResultCols_ExecDirStr_7"), var_list); ExecDirStr[8] = var_mapping(_T("SQLNumResultCols_ExecDirStr_8"), var_list); ExecDirStr[9] = var_mapping(_T("SQLNumResultCols_ExecDirStr_9"), var_list); ExecDirStr[10] = var_mapping(_T("SQLNumResultCols_ExecDirStr_10"), var_list); ExecDirStr[11] = var_mapping(_T("SQLNumResultCols_ExecDirStr_11"), var_list); ExecDirStr[12] = var_mapping(_T("SQLNumResultCols_ExecDirStr_12"), var_list); ExecDirStr[13] = var_mapping(_T("SQLNumResultCols_ExecDirStr_13"), var_list); ExecDirStr[14] = var_mapping(_T("SQLNumResultCols_ExecDirStr_14"), var_list); ExecDirStr[15] = var_mapping(_T("SQLNumResultCols_ExecDirStr_15"), var_list); ExecDirStr[16] = var_mapping(_T("SQLNumResultCols_ExecDirStr_16"), var_list); ExecDirStr[17] = var_mapping(_T("SQLNumResultCols_ExecDirStr_17"), var_list); ExecDirStr[18] = var_mapping(_T("SQLNumResultCols_ExecDirStr_18"), var_list); ExecDirStr[19] = var_mapping(_T("SQLNumResultCols_ExecDirStr_19"), var_list); //=========================================================================================================== LogMsg(LINEBEFORE+SHORTTIMESTAMP,_T("Begin testing API =>SQLNumResultcols.\n")); TEST_INIT; TESTCASE_BEGIN("Setup for SQLNumResultCols tests\n"); if(!FullConnect(pTestInfo)){ LogMsg(NONE,_T("Unable to connect\n")); TEST_FAILED; TEST_RETURN; } henv = pTestInfo->henv; hdbc = pTestInfo->hdbc; hstmt = (SQLHANDLE)pTestInfo->hstmt; returncode = SQLAllocStmt((SQLHANDLE)hdbc, &hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLAllocStmt")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; TEST_RETURN; } TESTCASE_END; // end of setup for (l = 0; l < lend; l++){ for (i = 0; i < iend; i++){ //================================================================================== _stprintf(Heading,_T("Test Positive Functionality of SQLNumResultCols ")); _tcscat(Heading, TestCase[l]); _tcscat(Heading, ExecDirStr[i+iend+iend+iend]); _tcscat(Heading, _T("\n")); TESTCASE_BEGINW(Heading); if ((i != (iend-1)) && (l < 5)){ SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i],SQL_NTS); /* cleanup */ returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend],SQL_NTS); /* create table */ if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend],SQL_NTS); /* insert into table */ if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ switch( l ) { case 0: returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } break; case 1 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecute(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecute")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } break; case 2 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecute(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecute")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLFetch(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } } break; case 3 : returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } break; case 4 : returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLFetch(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } break; } if (returncode == SQL_SUCCESS){ returncode = SQLNumResultCols(hstmt, &col); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLNumResultCols")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } if (col == expcol[i]) { LogMsg(NONE,_T("expect: %d and actual: %d are matched\n"),expcol[i], col); } else { TEST_FAILED; LogMsg(NONE,_T("expect: %d and actual: %d are not matched\n"),expcol[i], col); } } SQLFreeStmt(hstmt,SQL_CLOSE); SQLExecDirect(hstmt,(SQLTCHAR*) ExecDirStr[i],SQL_NTS); /* cleanup */ } } } else if ((i == (iend-1)) && (l >= 5)){ SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i],SQL_NTS); /* cleanup */ returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend],SQL_NTS); /* create table */ if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecDirect(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend],SQL_NTS); /* insert into table */ if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ switch( l ){ case 5 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } break; case 6 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("10"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindparameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("20"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindParameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } } break; case 7 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("10"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindparameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("20"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindParameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecute(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecute")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } } } break; case 8 : returncode = SQLPrepare(hstmt,(SQLTCHAR*)ExecDirStr[i+iend+iend+iend], SQL_NTS); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLPrepare")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("10"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindparameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_INTEGER,0,0,(SQLPOINTER)_T("20"),300,&cbIn); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindParameter")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLExecute(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecute")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } else{ returncode = SQLFetch(hstmt); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")){ LogAllErrors(henv,hdbc,hstmt); TEST_FAILED; } } } } } break; } if (returncode == SQL_SUCCESS){ returncode = SQLNumResultCols(hstmt, &col); if(!CHECKRC(SQL_SUCCESS,returncode,"SQLNumResultCols")) { TEST_FAILED; LogAllErrors(henv,hdbc,hstmt); } if (col == expcol[i]) { LogMsg(NONE,_T("expect: %d and actual: %d are matched\n"),expcol[i], col); } else { TEST_FAILED; LogMsg(NONE,_T("expect: %d and actual: %d are not matched\n"),expcol[i], col); } } SQLFreeStmt(hstmt,SQL_CLOSE); SQLExecDirect(hstmt,(SQLTCHAR*) ExecDirStr[i],SQL_NTS); /* cleanup */ } } } TESTCASE_END; } /* iend loop */ } /* lend loop */ FullDisconnect(pTestInfo); LogMsg(SHORTTIMESTAMP+LINEAFTER,_T("End testing API => SQLNumResultcols.\n")); free_list(var_list); TEST_RETURN; }
static SQLLEN WriteBodyNormal( SQLHSTMT hStmt ) { SQLINTEGER nCol = 0; SQLSMALLINT nColumns = 0; SQLLEN nIndicator = 0; SQLCHAR szColumn[MAX_DATA_WIDTH+20]; SQLCHAR szColumnValue[MAX_DATA_WIDTH+1]; SQLRETURN nReturn = 0; SQLLEN nRows = 0; SQLUINTEGER nOptimalDisplayWidth = 10; *szColumn = '\0'; *szColumnValue = '\0'; nReturn = SQLNumResultCols( hStmt, &nColumns ); if ( nReturn != SQL_SUCCESS && nReturn != SQL_SUCCESS_WITH_INFO ) nColumns = -1; /* ROWS */ nReturn = SQLFetch( hStmt ); while ( nReturn == SQL_SUCCESS || nReturn == SQL_SUCCESS_WITH_INFO ) { /* COLS */ for ( nCol = 1; nCol <= nColumns; nCol++ ) { int sret; nOptimalDisplayWidth = OptimalDisplayWidth( hStmt, nCol, nUserWidth ); nReturn = SQLGetData( hStmt, nCol, SQL_C_CHAR, (SQLPOINTER)szColumnValue, sizeof(szColumnValue), &nIndicator ); szColumnValue[MAX_DATA_WIDTH] = '\0'; if ( nReturn == SQL_SUCCESS && nIndicator != SQL_NULL_DATA ) { sret = sprintf( (char*)szColumn, "| %-*.*s", (int)nOptimalDisplayWidth, (int)nOptimalDisplayWidth, szColumnValue ); if (sret < 0) sprintf( (char*)szColumn, "| %-*.*s", (int)nOptimalDisplayWidth, (int)nOptimalDisplayWidth, "**ERROR**" ); } else if ( nReturn == SQL_ERROR ) { break; } else { sprintf( (char*)szColumn, "| %-*s", (int)nOptimalDisplayWidth, "" ); } fputs( (char*)szColumn, stdout ); } /* for columns */ nRows++; printf( "|\n" ); nReturn = SQLFetch( hStmt ); } /* while rows */ if ( nReturn == SQL_ERROR ) { if ( bVerbose ) DumpODBCLog( 0, 0, hStmt ); } return nRows; }
//*********************************************************************** // FETCH ROWS FROM THE TABLE "T1"......select * from T1; int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt) { int ret; int f1=10; // f1 field short int f2=20;//f2 field char f3[50]= "praba"; float f4 = 2.5; float f5 = 10.50; int f9=5; long long f10=15000; SQLINTEGER slen = SQL_NTS; SQL_DATE_STRUCT date,f6Date; SQL_TIME_STRUCT time, f7Time; SQL_TIMESTAMP_STRUCT timestamp, f8Timestamp; date.year=2008; date.month=03; date.day=18; time.hour=5; time.minute=22; time.second=10; timestamp.year=2008; timestamp.month=03; timestamp.day=18; timestamp.hour=5; timestamp.minute = 22; timestamp.second = 10; timestamp.fraction = 764576; int f1temp,f2temp; char f3temp[20]; float f4temp, f5temp; int f9temp; long long f10temp; ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1 WHERE F1=? AND F2=? AND F3=? AND F4=? AND F5=? AND F6=? AND F7=? AND F8=? AND F9=? AND F10=?",SQL_NTS); checkrc(ret,__LINE__); //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,) ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1temp,0,NULL); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_INTEGER,0,0,&f2temp,0,NULL); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f3temp,0,&slen); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,4,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f4temp,0,NULL); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,5,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,0,0,&f5temp,0,NULL); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,6,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_DATE,196,0,&f6Date,sizeof(f6Date),&slen); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,7,SQL_PARAM_INPUT,SQL_C_TYPE_TIME,SQL_TYPE_TIME,196,0,&f7Time,sizeof(f7Time),&slen); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,8,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,196,0,&f8Timestamp,sizeof(f8Timestamp),&slen); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,9,SQL_PARAM_INPUT,SQL_C_TINYINT,SQL_TINYINT,0,0,&f9temp,0,NULL); checkrc(ret,__LINE__); ret = SQLBindParameter(stmt,10,SQL_PARAM_INPUT,SQL_C_SBIGINT,SQL_BIGINT,0,0,&f10temp,0,NULL); checkrc(ret,__LINE__); //******************************************************************************************************* ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,2,SQL_C_SSHORT,&f2,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,4,SQL_C_FLOAT,&f4,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,5,SQL_C_FLOAT,&f5,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,6,SQL_C_TYPE_DATE,&date,sizeof(date),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,7,SQL_C_TYPE_TIME,&time,sizeof(time),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,8,SQL_C_TYPE_TIMESTAMP,×tamp,sizeof(timestamp),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,9,SQL_C_TINYINT,&f9,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,10,SQL_C_SBIGINT,&f10,0,NULL); checkrc(ret,__LINE__); int j, count=0; f6Date=date; f7Time=time; f8Timestamp=timestamp; for(j=0;j<10;j++) { f1temp=f1++; f2temp=f2++; strcpy(f3temp,"praba"); f4temp=f4++; f5temp=f5; f9temp=f9; f10temp=f10; ret = SQLExecute(stmt); checkrc(ret,__LINE__); ret = SQLFetch(stmt); ret = SQLCloseCursor(stmt); checkrc(ret,__LINE__); count++; printf("F1=%d\tF2=%d\tF3=%s\tF4=%f\tF5=%f\tDATE=%d/%d/%d\tTIME=%d-%d-%d\tTIMESTAMP=%d/%d/%d %d-%d-%d\tF9=%d\tF10=%lld\n ",f1,f2,f3,f4,f5,date.year,date.month,date.day,time.hour,time.minute,time.second,timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second,f9,f10); } ret = SQLTransact(env,dbc,SQL_COMMIT); checkrc(ret,__LINE__); printf("Total row fetched=%d\n",count); return 0; }
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_callback_exec_detailed(const char *file, const char *func, int line, switch_odbc_handle_t *handle, const char *sql, switch_core_db_callback_func_t callback, void *pdata, char **err) { #ifdef SWITCH_HAVE_ODBC SQLHSTMT stmt = NULL; SQLSMALLINT c = 0, x = 0; SQLLEN m = 0; char *x_err = NULL, *err_str = NULL; int result; int err_cnt = 0; int done = 0; handle->affected_rows = 0; switch_assert(callback != NULL); if (!db_is_up(handle)) { x_err = "DB is not up!"; goto error; } if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) { x_err = "Unable to SQL allocate handle!"; goto error; } if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) { x_err = "Unable to prepare SQL statement!"; goto error; } result = SQLExecute(stmt); if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) { x_err = "execute error!"; goto error; } SQLNumResultCols(stmt, &c); SQLRowCount(stmt, &m); handle->affected_rows = (int) m; while (!done) { int name_len = 256; char **names; char **vals; int y = 0; result = SQLFetch(stmt); if (result != SQL_SUCCESS) { if (result != SQL_NO_DATA) { err_cnt++; } break; } names = calloc(c, sizeof(*names)); vals = calloc(c, sizeof(*vals)); switch_assert(names && vals); for (x = 1; x <= c; x++) { SQLSMALLINT NameLength = 0, DataType = 0, DecimalDigits = 0, Nullable = 0; SQLULEN ColumnSize = 0; names[y] = malloc(name_len); memset(names[y], 0, name_len); SQLDescribeCol(stmt, x, (SQLCHAR *) names[y], (SQLSMALLINT) name_len, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable); if (!ColumnSize) { ColumnSize = 255; } ColumnSize++; vals[y] = malloc(ColumnSize); memset(vals[y], 0, ColumnSize); SQLGetData(stmt, x, SQL_C_CHAR, (SQLCHAR *) vals[y], ColumnSize, NULL); y++; } if (callback(pdata, y, vals, names)) { done = 1; } for (x = 0; x < y; x++) { free(names[x]); free(vals[x]); } free(names); free(vals); } SQLFreeHandle(SQL_HANDLE_STMT, stmt); stmt = NULL; /* Make sure we don't try to free this handle again */ if (!err_cnt) { return SWITCH_ODBC_SUCCESS; } error: if (stmt) { err_str = switch_odbc_handle_get_error(handle, stmt); } if (zstr(err_str) && !zstr(x_err)) { err_str = strdup(x_err); } if (err_str) { switch_log_printf(SWITCH_CHANNEL_ID_LOG, file, func, line, NULL, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str)); if (err) { *err = err_str; } else { free(err_str); } } if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } #endif return SWITCH_ODBC_FAIL; }
//*********************************************************************** // FETCH ROWS FROM THE TABLE "T1"......select * from T1; int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt) { int ret; int f1=10; // f1 field short int f2=20;//f2 field char f3[50]= "jitendra"; float f4 = 2.5; float f5 = 10.50; int f9 = 5; long long f10 = 15000; int result; SQL_DATE_STRUCT date; char strDate[30]; date.year=2007; date.month=03; date.day=18; // strcpy(strDate,"{d '2008-03-18'}"); //********************************************** SQL_TIME_STRUCT time; time.hour = 5; time.minute = 22; time.second = 10; //********************************************* SQL_TIMESTAMP_STRUCT timestamp; timestamp.year = 2007; timestamp.month = 03; timestamp.day = 18; timestamp.hour = 5; timestamp.minute = 22; timestamp.second = 10; ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1",SQL_NTS); checkrc(ret,__LINE__); //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,) ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,2,SQL_C_SSHORT,&f2,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,4,SQL_C_FLOAT,&f4,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,5,SQL_C_FLOAT,&f5,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,6,SQL_C_TYPE_DATE,&date,sizeof(date),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,7,SQL_C_TYPE_TIME,&time,sizeof(time),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,8,SQL_C_TYPE_TIMESTAMP,×tamp,sizeof(timestamp),NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,9,SQL_C_TINYINT,&f9,0,NULL); checkrc(ret,__LINE__); ret = SQLBindCol(stmt,10,SQL_C_SBIGINT,&f10,0,NULL); checkrc(ret,__LINE__); int j, count=0; ret = SQLExecute(stmt); checkrc(ret,__LINE__); printf("Fetching starts on table T1 :\n"); while(SQL_SUCCEEDED(ret = SQLFetch(stmt))) { printf("F1=%d\tF2=%d\tF3=%s\tF4=%f\tF5=%f\tDATE=%d/%d/%d\tTIME=%d-%d-%d\tTIMESTAMP=%d/%d/%d %d-%d-%d\tF9=%d\tF10=%lld\n ",f1,f2,f3,f4,f5,date.year,date.month,date.day,time.hour,time.minute,time.second,timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second,f9,f10); count++; } ret = SQLCloseCursor(stmt); checkrc(ret,__LINE__); ret = SQLTransact(env,dbc,SQL_COMMIT); checkrc(ret,__LINE__); printf("Total row fetched=%d\n",count); return 0; }
static void testLargeResult(HSTMT hstmt, int betweenstmts) { int rc; int i; /* * Fetch a large result set without cursor (in Declare/fetch mode, it will * be fetched in chunks) */ rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 'foo' || g FROM generate_series(1, 3210) g", SQL_NTS); CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt); /* Fetch the first 10 rows */ for (i = 0; i < 10; i++) { char buf[40]; SQLLEN ind; rc = SQLFetch(hstmt); if (rc != SQL_SUCCESS) { CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt); } rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &ind); CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt); printf("%s ", buf); } /* Now Commit, Rollback or do nothing depending on the argument */ switch (betweenstmts) { case 1: printf("\nCommit\n"); rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); CHECK_STMT_RESULT(rc, "SQLEndTran failed\n", hstmt); break; case 2: printf("\nRollback\n"); rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_ROLLBACK); CHECK_STMT_RESULT(rc, "SQLEndTran failed\n", hstmt); break; default: /* do nothing */ break; } /* * Try to fetch the rest of the result set. * (Will fail in SQL_CB_CLOSE mode and succeed in SQL_CB_PRESERVE). */ for (;; i++) { char buf[40]; SQLLEN ind; rc = SQLFetch(hstmt); if (rc == SQL_NO_DATA) break; if (rc != SQL_SUCCESS) { char sqlstate[32] = ""; SQLINTEGER nativeerror; SQLSMALLINT textlen; SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &nativeerror, NULL, 0, &textlen); if (strcmp(sqlstate, "HY010") == 0) { /* This is expected in SQL_CB_CLOSE mode */ printf("SQLFetch failed with HY010 (which probably means that the cursor was closed at commit/rollback)"); break; } else CHECK_STMT_RESULT(rc, "SQLGetDiagRec failed", hstmt); } rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &ind); CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt); if (i == 20) printf("... "); else if (20 < i && i <= 3205) { /* skip printing, to keep the output short */ } else printf("%s ", buf); } printf("\nFetched %d rows altogether\n\n", i); rc = SQLFreeStmt(hstmt, SQL_CLOSE); CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt); }
static void Test(int level) { SQLRETURN result; SQLSMALLINT InParam = level; SQLSMALLINT OutParam = 1; SQLLEN cbReturnCode = 0, cbInParam = 0, cbOutParam = 0; SQLLEN cbOutString = SQL_NTS; char sql[80]; printf("ODBC %d nocount %s select %s level %d\n", odbc_use_version3 ? 3 : 2, g_nocount ? "yes" : "no", g_second_select ? "yes" : "no", level); ReturnCode = INVALID_RETURN; memset(&OutString, 0, sizeof(OutString)); /* test with SQLExecDirect */ sprintf(sql, "RAISERROR('An error occurred.', %d, 1)", level); result = odbc_command_with_result(odbc_stmt, sql); TestResult(result, level, "SQLExecDirect"); /* test with SQLPrepare/SQLExecute */ if (!SQL_SUCCEEDED(SQLPrepare(odbc_stmt, T(SP_TEXT), strlen(SP_TEXT)))) { fprintf(stderr, "SQLPrepare failure!\n"); exit(1); } SQLBindParameter(odbc_stmt, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &ReturnCode, 0, &cbReturnCode); SQLBindParameter(odbc_stmt, 2, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &InParam, 0, &cbInParam); SQLBindParameter(odbc_stmt, 3, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &OutParam, 0, &cbOutParam); strcpy(OutString, "Invalid!"); SQLBindParameter(odbc_stmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, OUTSTRING_LEN, 0, OutString, OUTSTRING_LEN, &cbOutString); CHKExecute("S"); /* first select, check data are returned. * SET statements before does not affect results */ CheckData(""); CHKFetch("S"); CheckData("Here is the first row"); result = SQLFetch(odbc_stmt); if (odbc_use_version3) { SQLTCHAR SqlState[6]; SQLINTEGER NativeError; SQLTCHAR MessageText[1000]; SQLSMALLINT TextLength; SQLRETURN expected; SQLLEN rows; if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLFetch should return NO DATA"); CHKGetDiagRec(SQL_HANDLE_STMT, odbc_stmt, 1, SqlState, &NativeError, MessageText, ODBC_VECTOR_SIZE(MessageText), &TextLength, "No"); result = SQLMoreResults(odbc_stmt); expected = level > 10 ? SQL_ERROR : SQL_SUCCESS_WITH_INFO; if (result != expected) ODBC_REPORT_ERROR("SQLMoreResults returned unexpected result"); if (!g_second_select && g_nocount) { if (ReturnCode == INVALID_RETURN) { result = SQLMoreResults(odbc_stmt); } else { CheckReturnCode(result, 0); ReturnCode = INVALID_RETURN; TestResult(result, level, "SQLMoreResults"); ReturnCode = 0; } } else { TestResult(result, level, "SQLMoreResults"); } /* a recordset with only warnings/errors do not contains rows */ if (CHKRowCount(&rows, "SE") == SQL_SUCCESS && rows != -1) ODBC_REPORT_ERROR("SQLRowCount returned some rows"); } else { /* in ODBC 2 errors/warnings are not handled as different recordset */ TestResult(result, level, "SQLFetch"); } if (odbc_driver_is_freetds()) CheckData(""); if (!g_second_select) { SQLLEN rows; if (CHKRowCount(&rows, "SE") == SQL_SUCCESS && rows != -1) ODBC_REPORT_ERROR("SQLRowCount returned some rows"); CheckReturnCode(result, g_nocount ? 0 : INVALID_RETURN); result = SQLMoreResults(odbc_stmt); #ifdef ENABLE_DEVELOPING if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLMoreResults should return NO DATA"); ODBC_CHECK_ROWS(-2); #endif CheckReturnCode(result, 0); return; } if (!odbc_use_version3 || !g_nocount) { /* mssql 2008 return SUCCESS_WITH_INFO with previous error */ CHKMoreResults("S"); result = SQL_SUCCESS; } CheckReturnCode(result, INVALID_RETURN); CheckData(""); if (g_nocount && odbc_use_version3 && g_second_select && level >= 10) { if (CHKFetch("SE") == SQL_ERROR) { SQLMoreResults(odbc_stmt); CHKFetch("S"); } } else { CHKFetch("S"); } CheckData("Here is the last row"); CHKFetch("No"); CheckData(""); if (!odbc_use_version3 || g_nocount) CheckReturnCode(result, 0); #ifdef ENABLE_DEVELOPING else CheckReturnCode(result, INVALID_RETURN); #endif /* FIXME how to handle return in store procedure ?? */ result = SQLMoreResults(odbc_stmt); #ifdef ENABLE_DEVELOPING if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLMoreResults return other data"); #endif CheckReturnCode(result, 0); CheckData(""); ODBC_FREE(); }
void test (char* type, int c_type) { int i, n; HSTMT hstmt; SQLRETURN rc; printf ("=====================================================\n"); printf ("%s -> %s\n", type, c_type == SQL_C_BINARY ? "SQL_C_BINARY" : c_type == SQL_C_CHAR ? "SQL_C_CHAR" : c_type == SQL_C_WCHAR ? "SQL_C_WCHAR" : "???"); if (strstr (type, "LONG") != NULL || strstr (type, "IMAGE") != NULL || strstr (type, "TEXT") != NULL) n = 16; else n = 12; create_table (type); for (i = 0; i < n; i++) insert_row (type, i); rc = SQLAllocHandle (SQL_HANDLE_STMT, (SQLHANDLE) hdbc, (SQLHANDLE *) &hstmt); if (rc != SQL_SUCCESS) { err_printf ("SQLAllocHandle() failed.\n"); return; } rc = SQLExecDirect (hstmt, "select id, data from tab", SQL_NTS); if (rc != SQL_SUCCESS) { err_printf ("select failed.\n"); error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt); exit (1); } for (;;) { char buffer[1024]; long id; SQLLEN offset, full_length, length; rc = SQLFetch (hstmt); if (rc != SQL_SUCCESS) { if (rc == SQL_NO_DATA) break; err_printf ("SQLFetch() failed.\n"); error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt); exit (1); } rc = SQLGetData (hstmt, 1, SQL_C_LONG, &id, sizeof id, &length); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { err_printf ("SQLGetData failed.\n"); error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt); exit (1); } printf ("--- id: %ld, size: %ld\n", id, 1L << id); if (SQL_C_WCHAR == c_type) full_length = (1 << id) * sizeof (wchar_t); else full_length = (1 << id); offset = 0; for (;;) { int size_in_buffer; rc = SQLGetData (hstmt, 2, c_type, buffer, sizeof buffer, (void*) &length); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { if (rc == SQL_NO_DATA) break; err_printf ("SQLGetData failed.\n"); error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt); exit (1); } if (offset + length != full_length) { err_printf ("offset: %ld\n", (long) offset); err_printf ("length: %ld (should be %ld)\n", (long) length, (long) (full_length - offset)); err_printf ("***FAILED: SQLGetData() returned wrong length.\n"); exit (1); } if (SQL_C_WCHAR == c_type) size_in_buffer = sizeof buffer - sizeof (wchar_t); else if (SQL_C_CHAR == c_type) size_in_buffer = sizeof buffer - sizeof (char); else size_in_buffer = sizeof buffer; if (size_in_buffer > length) size_in_buffer = length; if ((size_in_buffer < length) != (rc == SQL_SUCCESS_WITH_INFO)) { err_printf ("offset: %ld\n", (long) offset); err_printf ("length: %ld\n", (long) length); err_printf ("rc: %s (should be %s)\n", rc != SQL_SUCCESS ? "SQL_SUCCESS_WITH_INFO" : "SQL_SUCCESS", size_in_buffer < length ? "SQL_SUCCESS_WITH_INFO" : "SQL_SUCCESS"); err_printf ("***FAILED: SQLGetData() returned wrong return code.\n"); exit (1); } n = SQL_C_WCHAR == c_type ? size_in_buffer / sizeof (wchar_t) : size_in_buffer; for (i = 0; i < n; i++) { int index = SQL_C_WCHAR == c_type ? offset / sizeof (wchar_t) + i : offset + i; int exp_c = 32 + index % (127 - 32); int act_c = SQL_C_WCHAR == c_type ? ((wchar_t*)buffer)[i] : buffer[i]; if (act_c != exp_c) { err_printf ("buffer index: %d, data index: %d, data: %d ", i, index, act_c); if (index < (1 << id)) err_printf ("(should be %d)\n", exp_c); else err_printf ("(should be no data)\n"); err_printf ("***FAILED: SQLGetData() returned wrong data.\n"); exit (1); } } offset += size_in_buffer; } } SQLFreeHandle (SQL_HANDLE_STMT, (SQLHANDLE) hstmt); err_printf ("PASSED: %s -> %s\n", type, c_type == SQL_C_BINARY ? "SQL_C_BINARY" : c_type == SQL_C_CHAR ? "SQL_C_CHAR" : c_type == SQL_C_WCHAR ? "SQL_C_WCHAR" : "???"); }
/* list tables that meet specified criteria */ int TbListTables(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ struct { SQLINTEGER ind; SQLCHAR val[129]; } tbQualifier, tbSchema, tbName, tbType; struct { SQLINTEGER ind; SQLCHAR val[255]; } tbRemarks; /* criteria to look for */ SQLCHAR tbSchemaPattern[] = "%"; /* all the schemas */ SQLCHAR tbNamePattern[] = "ST%"; /* all the tables starting with ST */ printf("\n-----------------------------------------------------------"); printf("\nUSE THE CLI FUNCTIONS\n"); printf(" SQLSetConnectAttr\n"); printf(" SQLAllocHandle\n"); printf(" SQLTables\n"); printf(" SQLBindCol\n"); printf(" SQLFetch\n"); printf(" SQLFreeHandle\n"); printf("TO SHOW HOW TO LIST TABLES THAT MEET SPECIFIED CRITERIA:\n"); /* set AUTOCOMMIT on */ cliRC = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS); DBC_HANDLE_CHECK(hdbc, cliRC); /* allocate a statement handle */ cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* call SQLTables */ printf("\n Call SQLTables for:\n"); printf(" schemaPattern = %s\n", tbSchemaPattern); printf(" namePattern = %s\n", tbNamePattern); /* get table information */ cliRC = SQLTables(hstmt, NULL, 0, tbSchemaPattern, SQL_NTS, tbNamePattern, SQL_NTS, NULL, 0); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 1 to variable */ cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, tbQualifier.val, 129, &tbQualifier.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 2 to variable */ cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, tbSchema.val, 129, &tbSchema.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 3 to variable */ cliRC = SQLBindCol(hstmt, 3, SQL_C_CHAR, tbName.val, 129, &tbName.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 4 to variable */ cliRC = SQLBindCol(hstmt, 4, SQL_C_CHAR, tbType.val, 129, &tbType.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 5 to variable */ cliRC = SQLBindCol(hstmt, 5, SQL_C_CHAR, tbRemarks.val, 255, &tbRemarks.ind); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ printf("\n Fetch each row and display.\n"); printf(" TABLE SCHEMA TABLE_NAME TABLE_TYPE\n"); printf(" -------------- -------------- ----------\n"); /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } while (cliRC != SQL_NO_DATA_FOUND) { printf(" %-14s %-14s %-11s\n", tbSchema.val, tbName.val, tbType.val); if (tbRemarks.ind > 0) { printf(" (Remarks) %s\n", tbRemarks.val); } /* fetch next row */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); } /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); return rc; } /* TbListTables */
DB_ROW zbx_db_fetch(DB_RESULT result) { #if defined(HAVE_IBM_DB2) int i; #elif defined(HAVE_ORACLE) sword rc; static char errbuf[512]; sb4 errcode; #endif if (NULL == result) return NULL; #if defined(HAVE_IBM_DB2) if (SUCCEED != zbx_ibm_db2_success(SQLFetch(result->hstmt))) /* e.g., SQL_NO_DATA_FOUND */ return NULL; for (i = 0; i < result->ncolumn; i++) { result->values[i] = (SQL_NULL_DATA == result->values_len[i] ? NULL : result->values_cli[i]); } return result->values; #elif defined(HAVE_MYSQL) return mysql_fetch_row(result); #elif defined(HAVE_ORACLE) if (OCI_NO_DATA == (rc = OCIStmtFetch2(result->stmthp, oracle.errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT))) return NULL; if (OCI_SUCCESS == rc) return result->values; if (OCI_SUCCESS != (rc = OCIErrorGet((dvoid *)oracle.errhp, (ub4)1, (text *)NULL, &errcode, (text *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR))) { zabbix_errlog(ERR_Z3006, rc, zbx_oci_error(rc)); return NULL; } switch (errcode) { case 3113: /* ORA-03113: end-of-file on communication channel */ case 3114: /* ORA-03114: not connected to ORACLE */ zabbix_errlog(ERR_Z3006, errcode, errbuf); return NULL; } return result->values; #elif defined(HAVE_POSTGRESQL) /* free old data */ if (NULL != result->values) zbx_free(result->values); /* EOF */ if (result->cursor == result->row_num) return NULL; /* init result */ result->fld_num = PQnfields(result->pg_result); if (result->fld_num > 0) { int i; result->values = zbx_malloc(result->values, sizeof(char *) * result->fld_num); for (i = 0; i < result->fld_num; i++) { if (PQgetisnull(result->pg_result, result->cursor, i)) { result->values[i] = NULL; } else { result->values[i] = PQgetvalue(result->pg_result, result->cursor, i); if (PQftype(result->pg_result, i) == ZBX_PG_BYTEAOID) /* binary data type BYTEAOID */ zbx_pg_unescape_bytea((u_char *)result->values[i]); } } } result->cursor++; return result->values; #elif defined(HAVE_SQLITE3) /* EOF */ if (result->curow >= result->nrow) return NULL; if (NULL == result->data) return NULL; result->curow++; /* NOTE: first row == header row */ return &(result->data[result->curow * result->ncolumn]); #endif }
int CDataBase::GetAsBinary(LPTSTR lpszStatement, LPBYTE OUT lpszReturnBuffer) {/* add Clear(); before query */ SQLCHAR * pSQLBuf; SQLINTEGER BufLen; SQLCHAR SQLBinary[10000]; SQLINTEGER lOfs = 0; SQLINTEGER SQLLen; SQLRETURN SQLResult; Clear(); // #ShadowKing //SQLAllocHandle(SQL_HANDLE_STMT, m_hConnection, &m_hStmt); OnQuery(lpszStatement); SQLResult = SQLExecDirect(m_hStmt, (SQLTCHAR *)lpszStatement, SQL_NTS); if ( SQLResult != SQL_SUCCESS ) { Diagnostic(); return -1; } while ( true ) { SQLResult = SQLFetch(m_hStmt); if ( SQLResult == SQL_NO_DATA ) break; pSQLBuf = lpszReturnBuffer; while ( true ) { SQLResult = SQLGetData(m_hStmt, 1, SQL_C_BINARY , SQLBinary, sizeof(SQLBinary), &SQLLen); if ( SQLResult == SQL_NO_DATA ) break; if ( SQLLen == -1 ) { Clear(); return 0; } if ( SQLResult == SQL_SUCCESS ) { BufLen = SQLLen; } else { BufLen = sizeof(SQLBinary); } lOfs += BufLen; memcpy(pSQLBuf, SQLBinary, BufLen); pSQLBuf = &pSQLBuf[lOfs]; } } //SQLFreeHandle(SQL_HANDLE_STMT, m_hStmt); return BufLen; }