void classTables::LoadTables() { CursorScoper s(listView()) ; SQLRETURN nReturn ; SQLCHAR szTableName[MAX_COLUMN_WIDTH]; SQLCHAR szTableType[MAX_COLUMN_WIDTH]; SQLCHAR szTableRemarks[MAX_COLUMN_WIDTH]; SQLLEN nIndicatorName; SQLLEN nIndicatorType; SQLLEN nIndicatorRemarks; classTable *pTable = NULL; // CREATE A STATEMENT StatementScoper stmt( hDbc ) ; if ( !stmt() ) return ; // EXECUTE OUR SQL/CALL if (!SQL_SUCCEEDED(nReturn=SQLTables( stmt(), 0, 0, (SQLCHAR*)qsLibrary.ascii(), SQL_NTS, 0, 0, 0, 0 ) ) ) return my_msgBox( "classTables", "SQLTables", nReturn, NULL, NULL, stmt() ) ; SQLBindCol( stmt(), SQLTables_TABLE_NAME, SQL_C_CHAR, szTableName, sizeof(szTableName), &nIndicatorName ); SQLBindCol( stmt(), SQLTables_TABLE_TYPE, SQL_C_CHAR, szTableType, sizeof(szTableType), &nIndicatorType ); SQLBindCol( stmt(), SQLTables_REMARKS, SQL_C_CHAR, szTableRemarks, sizeof(szTableRemarks), &nIndicatorRemarks ); // GET RESULTS while ( SQL_SUCCEEDED(SQLFetch( stmt() ) ) ) { if ( nIndicatorName == SQL_NULL_DATA ) listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, "Unknown" ) ); else listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, QString((char*)szTableName).stripWhiteSpace(), QString((char*)szTableType).stripWhiteSpace(), QString((char*)szTableRemarks).stripWhiteSpace(), qsLibrary ) ); } }
int main() { //定义句柄 SQLHENV serverhenv; SQLHDBC serverhdbc; SQLHSTMT serverstmt; SQLRETURN ret; //初始化环境 ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &serverhenv); ret = SQLSetEnvAttr(serverhenv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); if (ret == SQL_SUCCESS) { printf(" ------------------------ - \n"); printf(" 环境分配成功\n"); printf(" ------------------------ - \n"); } //建立连接 ret = SQLAllocHandle(SQL_HANDLE_DBC, serverhenv, &serverhdbc); ret = SQLConnectW(serverhdbc, (SQLWCHAR *)"David_MySQL" , SQL_NTS, (SQLWCHAR*)"root" , SQL_NTS, (SQLWCHAR*)"Dyc19930418", SQL_NTS); if (!SQL_SUCCEEDED(ret)) { printf(" 数据连接失败\n"); return -1; } //初始化语句句柄 ret = SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &serverstmt); ret = SQLExecDirect(serverstmt, (SQLWCHAR*)" select * from my_test" , SQL_NTS); //处理结果集 ret = SQLBindCol(serverstmt, 1, SQL_C_CHAR, ID, Name_Len, &userID); ret = SQLBindCol(serverstmt, 2, SQL_C_CHAR, PWD, Pwd_Len, &userPassword); printf(" userID\t userPassword\t\n"); while ((ret = SQLFetch(serverstmt)) != SQL_NO_DATA_FOUND) { if (ret == SQL_ERROR) { printf(" Fetch data error!\n"); } else { printf(" %s\t %s\t\n" , ID, PWD); } } //中止处理 SQLFreeHandle(SQL_HANDLE_STMT, serverstmt); SQLDisconnect(serverhdbc); SQLFreeHandle(SQL_HANDLE_DBC, serverhdbc); SQLFreeHandle(SQL_HANDLE_ENV, serverhenv); printf(" ---------------------------- - \n"); return 0; }
//*********************************************************************** // FETCH ROWS FROM THE TABLE "T1"......select * from T1; int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt) { int ret; int f1=10; // f1 field int f2=20;//f2 field int rettype ; ret = SQLPrepare(stmt,(unsigned char*)"SELECT F3,F2 FROM T1 ",SQL_NTS); rettype = ret; if(rettype!=0)return 1; //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,) ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL); ret = SQLBindCol(stmt,2,SQL_C_SLONG,&f2,0,NULL); int j, count=0; ret = SQLExecute(stmt); SQLSMALLINT noc; ret = SQLNumResultCols(stmt,&noc); rettype=ret; if(rettype != 0 ) { printf("SQLNumResultCol() returns = %d\n",noc); return 1; } while(SQL_SUCCEEDED(ret = SQLFetch(stmt))) { count++; } /*SQLSMALLINT noc; ret =SQLNumResultCols(stmt,&noc); printf("SQLNumResultCols() returns=%d\n",noc);*/ ret = SQLCloseCursor(stmt); checkrc(ret,__LINE__); ret = SQLTransact(env,dbc,SQL_COMMIT); checkrc(ret,__LINE__); printf("Total row fetched=%d\n",count); return 0; }
/* display foreign keys, from this table to others */ bool fetchForeign(char *tname) { char farschema[40], fartab[40]; char farcol[40]; char nearcol[40]; SQLLEN nearcolOut, farschemaOut, fartabOut, farcolOut; char *dot; newStatement(); stmt_text = "foreign keys"; debugStatement(); dot = strchr(tname, '.'); if(dot) *dot++ = 0; rc = SQLForeignKeys(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, (dot ? tname : NULL), SQL_NTS, (dot ? dot : tname), SQL_NTS); if(dot) dot[-1] = '.'; if(rc) goto abort; SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) farschema, sizeof (farschema), &farschemaOut); SQLBindCol(hstmt, 3, SQL_CHAR, (SQLPOINTER) fartab, sizeof (fartab), &fartabOut); SQLBindCol(hstmt, 4, SQL_CHAR, (SQLPOINTER) farcol, sizeof (farcol), &farcolOut); SQLBindCol(hstmt, 8, SQL_CHAR, (SQLPOINTER) nearcol, sizeof (nearcol), &nearcolOut); while(SQLFetch(hstmt) == SQL_SUCCESS) { printf("%s > ", nearcol); if(farschema[0]) printf("%s.", farschema); printf("%s.%s\n", fartab, farcol); } SQLFreeHandle(SQL_HANDLE_STMT, hstmt); return true; abort: SQLFreeHandle(SQL_HANDLE_STMT, hstmt); return false; } /* fetchForeign */
bool showTables(void) { char tabname[40]; char tabtype[40]; char tabowner[40]; SQLLEN tabnameOut, tabtypeOut, tabownerOut; char *buf; int buflen, cx; int truevalue = SQL_TRUE; /* SQLSetConnectAttr(hdbc, SQL_ATTR_METADATA_ID, &truevalue, SQL_IS_INTEGER); */ newStatement(); stmt_text = "get tables"; debugStatement(); rc = SQLTables(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS); if(rc) goto abort; SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) tabowner, sizeof (tabowner), &tabownerOut); SQLBindCol(hstmt, 3, SQL_CHAR, (SQLPOINTER) tabname, sizeof (tabname), &tabnameOut); SQLBindCol(hstmt, 4, SQL_CHAR, (SQLPOINTER) tabtype, sizeof (tabtype), &tabtypeOut); buf = initString(&buflen); while(SQLFetch(hstmt) == SQL_SUCCESS) { char tabline[140]; sprintf(tabline, "%s.%s|%s\n", tabowner, tabname, tabtype); stringAndString(&buf, &buflen, tabline); } cx = sideBuffer(0, buf, buflen, 0, false); nzFree(buf); i_printf(MSG_ShowTables, cx); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); return true; abort: SQLFreeHandle(SQL_HANDLE_STMT, hstmt); return false; } /* showTables */
/*------------------------------------------------------------------------- * AddCol *------------------------------------------------------------------------- * Purpose: * bind a variable to a column of the sql statement * * Parameters: * pvBuff: where to bind the column data to * * Returns: * 0. Returns a value only so we can call it during global initialization, * before any code blocks execute. */ int AddCol(void * pvBuff, SQLSMALLINT ssiCType, SQLPARM parmtype, int cbBuff) { SQLRETURN sqlret; static SQLINTEGER cbWhoCares; switch (parmtype) { case SQL_OUT_PARM: sqlret = SQLBindCol(g_rghstmt[g_iSql - 1], ++g_iCol, ssiCType, pvBuff, cbBuff, &cbWhoCares); if (SQL_SUCCESS != sqlret) SQLWhatsWrong(SQL_HANDLE_STMT, g_rghstmt[g_iSql - 1]); break; case SQL_IN_PARM: // we're using the same type for C and SQL, so they must match sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_INPUT, ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, NULL); break; case SQL_OUT_PROC: //out value from stored procedure // we're using the same type for C and SQL, so they must match sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_OUTPUT, ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, &cbWhoCares); break; case SQL_INOUT_PROC: //out value from stored procedure // we're using the same type for C and SQL, so they must match sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_INPUT_OUTPUT, ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, &cbWhoCares); break; } return 0; }
static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query) { rlm_sql_unixodbc_conn_t *conn = handle->conn; SQLINTEGER i; SQLLEN len; int colcount; int state; /* Only state = 0 means success */ if ((state = sql_query(handle, config, query))) { return state; } colcount = sql_num_fields(handle, config); if (colcount < 0) { return RLM_SQL_ERROR; } /* Reserving memory for result */ conn->row = talloc_zero_array(conn, char *, colcount + 1); /* Space for pointers */ for (i = 1; i <= colcount; i++) { SQLColAttributes(conn->stmt, ((SQLUSMALLINT) i), SQL_COLUMN_LENGTH, NULL, 0, NULL, &len); conn->row[i - 1] = talloc_array(conn->row, char, ++len); SQLBindCol(conn->stmt, i, SQL_C_CHAR, (SQLCHAR *)conn->row[i - 1], len, NULL); } return RLM_SQL_OK; }
unsigned int SelectCount(SQLHANDLE hstmt, char *TableName) { char SelCountStatement[50]; SQLINTEGER RecCount; SQLINTEGER RecCountInd; RETCODE rc; RecCount = 0; strcpy(SelCountStatement, "select count(*) from "); strcat(SelCountStatement, TableName); rc = SQLExecDirect(hstmt, (SQLCHAR *)SelCountStatement, strlen(SelCountStatement)); if (rc != SQL_SUCCESS) { printf("...select count(*) from %s statement failed\n", TableName); displayError(SQL_HANDLE_STMT, hstmt); } rc = SQLBindCol(hstmt, 1, SQL_C_ULONG, &RecCount, 0, &RecCountInd); if (rc != SQL_SUCCESS) { printf("...SQLBindCol failed\n"); displayError(SQL_HANDLE_STMT, hstmt); } rc = SQLFetch(hstmt); rc = SQLFreeStmt(hstmt, SQL_CLOSE); return(RecCount); }
BOOL CQuery::Exec(LPCTSTR szSQL) { int c; if( S_TYPE != ST_CASHSHOPSERVER ) { g_Window.ServerLogAdd(S_TYPE,"%s", szSQL); } ret=SQLExecDirect(hStmt,(SQLCHAR *)szSQL,SQL_NTS); if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_NO_DATA)) { PrintDiag(); return FALSE; } SQLRowCount(hStmt,&AffectCount); SQLNumResultCols(hStmt,&nCol); if (nCol > MAXCOL) { //::MessageBox(NULL,"nCol > MAXCOL","CQuery error",MB_OK); g_Window.ServerLogAdd(S_TYPE,"CQuery error : nCol > MAXCOL"); return FALSE; } if (nCol == 0) { Clear(); return TRUE; } for (c=0;c<nCol;c++) { SQLBindCol(hStmt,c+1,SQL_C_CHAR,Col[c],255,&lCol[c]); SQLDescribeCol(hStmt,c+1,ColName[c],30,NULL,NULL,NULL,NULL,NULL); } return TRUE; }
SQLRETURN unixodbc_backend_debug::do_bind_column(SQLHSTMT statement_handle, SQLUSMALLINT column_id, SQLSMALLINT target_type, SQLPOINTER target_value_ptr, SQLLEN buffer_length, SQLLEN * length_indicator_buffer) const { std::cout << " *DEBUG* bind_column"; auto const return_code = SQLBindCol(statement_handle, column_id, target_type, target_value_ptr, buffer_length, length_indicator_buffer); std::cout << " (return code " << return_code << ")" << std::endl; return return_code; }
void OdbcQuery::execQuery(const char *sql) { pOdbc->execDML(sql); SQLSMALLINT cols = 0; struct COL { char *name; int name_size; char *buffer; int buffer_size; SQLSMALLINT dataType; } *pCol; SQLNumResultCols(pOdbc->sqlstatementhandle, &cols); pCol = new COL[cols]; for (int i = 0; i < cols; i++) { pCol[i].name = new char[128]; SQLColAttribute(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_DESC_LABEL, pCol[i].name, 128, (SQLSMALLINT *) &pCol[i].name_size, NULL); SQLColAttribute(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_COLUMN_TYPE, NULL, 0, NULL, (SQLLEN *) &pCol[i].dataType); pCol[i].buffer = new char[MAX_BUFFER_SIZE]; SQLBindCol(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_C_CHAR, pCol[i].buffer, MAX_BUFFER_SIZE, (SQLLEN *) &pCol[i].buffer_size); // printf("%s\t", pCol[i].name); } // printf("\n"); }
/************************************************************************* * * Function: sql_select_query * * Purpose: Issue a select query to the database * *************************************************************************/ static int sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char *querystr) { rlm_sql_unixodbc_conn_t *conn = handle->conn; SQLINTEGER column; SQLLEN len; int numfields; int state; /* Only state = 0 means success */ if ((state = sql_query(handle, config, querystr))) { return state; } numfields=sql_num_fields(handle, config); if (numfields < 0) { return -1; } /* Reserving memory for result */ conn->row = (char **) rad_malloc((numfields+1)*sizeof(char *)); conn->row[numfields] = NULL; for(column = 1; column <= numfields; column++) { SQLColAttributes(conn->statement,((SQLUSMALLINT) column),SQL_COLUMN_LENGTH,NULL,0,NULL,&len); conn->row[column-1] = (char*)rad_malloc((int)++len); SQLBindCol(conn->statement, column, SQL_C_CHAR, (SQLCHAR *)conn->row[column-1], len, NULL); } return 0; }
void DSN_Set_Database(SQLHANDLE Connection) { MADB_Stmt *Stmt= NULL; SQLRETURN ret= SQL_ERROR; char Database[65]; MADB_Dsn *Dsn= (MADB_Dsn *)GetWindowLongPtr(GetParent(hwndTab[0]), DWLP_USER); if (DBFilled) return; GetDialogFields(); if (SQLAllocHandle(SQL_HANDLE_STMT, Connection, (SQLHANDLE *)&Stmt) != SQL_SUCCESS) goto end; if (SQLExecDirect((SQLHSTMT)Stmt, (SQLCHAR *)"SHOW DATABASES", SQL_NTS) != SQL_SUCCESS) goto end; SQLBindCol(Stmt, 1, SQL_C_CHAR, Database, 65, 0); ComboBox_ResetContent(GetDlgItem(hwndTab[1], cbDatabase)); while (SQLFetch(Stmt) == SQL_SUCCESS) ComboBox_InsertString(GetDlgItem(hwndTab[1], cbDatabase), -1, Database); if (Dsn->Catalog) { int Idx= ComboBox_FindString(GetDlgItem(hwndTab[2], cbDatabase), 0, Dsn->Catalog); ComboBox_SetCurSel(GetDlgItem(hwndTab[2], cbDatabase), Idx); } ComboBox_SetMinVisible(GetDlgItem(hwndTab[1], cbDatabase),5); DBFilled= TRUE; end: if (Stmt) SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE)Stmt); }
// Remove the update queue tables associated with every data source. void remove_update_queue_tables(void) { SQLRETURN retcode; SQLHSTMT stmtSelect; SQLHSTMT stmtDelete; SQLCHAR tabname[DB_MAX_SIZE]; SQLINTEGER cbTabname; char sql_stmt[2 * DB_MAX_SIZE]; SQLAllocHandle(SQL_HANDLE_STMT, sql_db_connection->hdbc, &stmtSelect); SQLSetCursorName(stmtSelect, (SQLCHAR *)"C1", SQL_NTS); SQLExecDirect(stmtSelect, (SQLCHAR *)"select updqueuename from vl_datasrc", SQL_NTS); SQLBindCol(stmtSelect, 1, SQL_C_CHAR, tabname, DB_MAX_SIZE, &cbTabname); retcode = SQLFetch(stmtSelect); while (retcode != SQL_NO_DATA && retcode != SQL_ERROR) { sprintf(sql_stmt, "drop table %s", tabname); SQLAllocHandle(SQL_HANDLE_STMT, sql_db_connection->hdbc, &stmtDelete); SQLExecDirect(stmtDelete, (SQLCHAR *)sql_stmt, SQL_NTS); SQLFreeHandle(SQL_HANDLE_STMT, stmtDelete); retcode = SQLFetch(stmtSelect); } SQLFreeHandle(SQL_HANDLE_STMT, stmtSelect); }
unsigned CMssqlConnection::GetInsertIdentity(const char *table_hint) { HSTMT hStmt; m_lasterror=SQLAllocStmt(m_hDbc,&hStmt); if(!SQL_SUCCEEDED(m_lasterror)) return 0; m_lasterror=SQLExecDirect(hStmt,(SQLWCHAR*)L"SELECT @@IDENTITY",SQL_NTS); if(!SQL_SUCCEEDED(m_lasterror)) { SQLFreeStmt(hStmt,SQL_DROP); return 0; } long id; SQLINTEGER len; m_lasterror=SQLBindCol(hStmt,1,SQL_C_LONG,&id,sizeof(id),&len); if(!SQL_SUCCEEDED(m_lasterror)) { SQLFreeStmt(hStmt,SQL_DROP); return 0; } m_lasterror=SQLFetch(hStmt); if(!SQL_SUCCEEDED(m_lasterror)) return 0; SQLFreeStmt(hStmt,SQL_DROP); return (unsigned)id; }
static t_sql_row* odbc_alloc_row(t_odbc_res *result, SQLINTEGER *sizes) { int i, fieldCount; HSTMT stmt = result->stmt; t_sql_row *row; fieldCount = sql_odbc_num_fields(result); /* Create a new row. */ row = xcalloc(sizeof *row, fieldCount+1); if(!row) { return NULL; } row[fieldCount] = NULL; sizes = xcalloc(sizeof *sizes, fieldCount); for(i=0; i<fieldCount; i++) { TCHAR *cell; SQLLEN cellSz; SQLColAttribute(stmt, i+1, SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &cellSz); cell = xcalloc(sizeof *cell, ++cellSz); if(!cell) { return NULL; } SQLBindCol(stmt, i+1, SQL_C_CHAR, cell, cellSz, &sizes[i]); row[i] = cell; } return row; }
int OrderCustDetails(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ SQLVARCHAR xmldata[3000]; /* query to be executed */ SQLCHAR *stmt = (SQLCHAR *)"for $custinfo in db2-fn:xmlcolumn('CUSTOMER.INFO')" "/customerinfo[addr/@country=\"Canada\"]" " order by $custinfo/name" " return $custinfo"; cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* Set the attribute SQL_ATTR_XQUERY_STATEMENT to indicate that the query is an XQuery */ rc = SQLSetStmtAttr(hstmt, SQL_ATTR_XQUERY_STATEMENT, (SQLPOINTER)SQL_TRUE, SQL_NTS); if (rc != 0) { return rc; } printf("\n Directly execute the statement\n"); printf(" %s\n", stmt); /* directly execute the statement */ cliRC = SQLExecDirect(hstmt, stmt, SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* bind column 1 to variable */ cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* fetch each row and display */ 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("%s \n\n",xmldata); /* 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; } /* OrderCustDetails */
static void Test(const char *type, const char *value_to_convert, SQLSMALLINT out_c_type, const char *expected) { unsigned char out_buf[256]; SQLLEN out_len = 0; SQL_NUMERIC_STRUCT *num; int i; SQLFreeStmt(Statement, SQL_UNBIND); SQLFreeStmt(Statement, SQL_RESET_PARAMS); /* execute a select to get data as wire */ sprintf(sbuf, "SELECT CONVERT(%s, '%s') AS data", type, value_to_convert); Command(Statement, sbuf); SQLBindCol(Statement, 1, out_c_type, out_buf, sizeof(out_buf), &out_len); if (SQLFetch(Statement) != SQL_SUCCESS) { fprintf(stderr, "Expected row\n"); exit(1); } if (SQLFetch(Statement) != SQL_NO_DATA) { fprintf(stderr, "Row not expected\n"); exit(1); } if (SQLMoreResults(Statement) != SQL_NO_DATA) { fprintf(stderr, "Recordset not expected\n"); exit(1); } /* test results */ sbuf[0] = 0; switch (out_c_type) { case SQL_C_NUMERIC: num = (SQL_NUMERIC_STRUCT *) out_buf; sprintf(sbuf, "%d %d %d ", num->precision, num->scale, num->sign); i = SQL_MAX_NUMERIC_LEN; for (; i > 0 && !num->val[--i];); for (; i >= 0; --i) sprintf(strchr(sbuf, 0), "%02X", num->val[i]); break; case SQL_C_BINARY: assert(out_len >= 0); for (i = 0; i < out_len; ++i) sprintf(strchr(sbuf, 0), "%02X", (int) out_buf[i]); break; case SQL_C_CHAR: out_buf[sizeof(out_buf) - 1] = 0; sprintf(sbuf,"%u %s", (unsigned int) strlen((char *) out_buf), out_buf); break; default: /* not supported */ assert(0); break; } if (strcmp(sbuf, expected) != 0) { fprintf(stderr, "Wrong result\n Got: %s\n Expected: %s\n", sbuf, expected); result = 1; } }
int basicproduct(SQLHANDLE hdbc,float price) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ SQLVARCHAR xmldata[3000]; /* SQL/XML statement to be executed */ SQLCHAR *stmt = (SQLCHAR *) "select xmlquery('for $prod in db2-fn:xmlcolumn(\"PRODUCT.DESCRIPTION\")" "/product/description" " return ( if ($prod/price < $price)" " then <product basic = \"true\">{fn:data($prod/name)}</product>" " else <product basic = \"false\">{fn:data($prod/name)}</product>)'" " passing by ref cast(? as float) as \"price\")" " from SYSIBM.SYSDUMMY1"; cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); printf(" %s\n", stmt); cliRC = SQLPrepare(hstmt,(SQLCHAR *)stmt,SQL_NTS); /* Bind the parameter marker */ printf("\nBind the parameter marker with the value %f", price); SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,8,0,&price,8,NULL); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); printf("\nExecute the Statement....."); cliRC = SQLExecute(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Bind column 1 to variable */ cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Fetch each row and display */ 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("%s \n\n",xmldata); /* 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; } /* basicproduct */
int conditionalCustDetails1(SQLHANDLE hdbc,sqlint32 cid) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ SQLVARCHAR xmldata[3000]; /* SQL/XML statement to be executed */ SQLCHAR *stmt = (SQLCHAR *) "select xmlquery('for $customer in $cust/customerinfo" " where ($customer/@Cid gt $id)" " return <customer id=\"{$customer/@Cid}\">" " {$customer/name} {$customer/addr} </customer>'" " passing by ref customer.info as \"cust\", cast(? as integer) as \"id\")" " from customer"; cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); printf(" %s\n", stmt); cliRC = SQLPrepare(hstmt,(SQLCHAR *)stmt,SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Bind the parameter value */ printf("\nBind the parameter markers with the value %d", cid); SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,4,0,&cid,4,NULL); printf("\nExecute the Statement.....\n"); cliRC = SQLExecute(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Bind column 1 to variable */ cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 3000, NULL); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Fetch each row and display */ 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(strcmp((char *)xmldata,"")!=0) printf("%s \n\n",xmldata); /* 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; } /* conditionalCustDetails1 */
void DisplayResults(HSTMT hStmt, SQLSMALLINT cCols, bool silent) { SQLSMALLINT cDisplaySize; RETCODE RetCode = SQL_SUCCESS; int iCount = 0; long long numReceived = 0; // Allocate memory for each column SQLCHAR buffer[MAXCOLS][BUFFERLEN]; SQLLEN indPtr[MAXCOLS]; int iCol; for (iCol = 0; iCol < cCols; iCol++) { TRYODBC(hStmt, SQL_HANDLE_STMT, SQLBindCol(hStmt, iCol+1, SQL_C_CHAR, (SQLPOINTER) buffer[iCol], (BUFFERLEN) * sizeof(char), &indPtr[iCol])); } // Fetch and display the data bool fNoData = false; do { // Fetch a row TRYODBC(hStmt, SQL_HANDLE_STMT, RetCode = SQLFetch(hStmt)); if (RetCode == SQL_NO_DATA_FOUND) { fNoData = true; } else { if (!silent) { // Display the data. Ignore truncations printf("%s", buffer[0]); for (iCol = 1; iCol < cCols; iCol++) { printf(",%s", buffer[iCol]); } printf("\n"); } numReceived++; } } while (!fNoData); Exit: printf("numRecieved = %lld\n", numReceived); }
main() { SQLHENV env; SQLHDBC dbc; SQLHSTMT stmt; SQLRETURN ret; /* ODBC API return status */ SQLSMALLINT columns; /* number of columns in result-set */ SQLCHAR table[ 64 ]; SQLCHAR column[ 64 ]; SQLINTEGER type; SQLLEN indicator[ 3 ]; int i; /* Allocate an environment handle */ SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); /* We want ODBC 3 support */ SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0); /* Allocate a connection handle */ SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc); /* Connect to the DSN mydsn */ /* You will need to change mydsn to one you have created and tested */ SQLDriverConnect(dbc, NULL, "DSN=PostgreSQL-rupen-aws;uid=poc;pwd=****;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE); /* Allocate a statement handle */ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); /* Retrieve a list of columns */ SQLColumns(stmt, NULL, 0, NULL, 0, "t1", SQL_NTS, NULL, 0); ret = SQLBindCol( stmt, 3, SQL_C_CHAR, table, sizeof( table ), &indicator[ 0 ] ); ret = SQLBindCol( stmt, 4, SQL_C_CHAR, column, sizeof( column ), &indicator[ 1 ] ); ret = SQLBindCol( stmt, 5, SQL_C_LONG, &type, 0, &indicator[ 2 ] ); /* Fetch the data */ while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) { printf( "%s.%s type %d\n", table, column, type ); } }
void DbHelper::BindResultColumnInt(int* r) { SQLLEN len = 0; SQLRETURN ret = SQLBindCol(mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_LONG, r, 4, &len); if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret) { PrintSqlStmtError(); } }
void DbHelper::BindResultColumnBool(bool* r) { SQLLEN len = 0; SQLRETURN ret = SQLBindCol(mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_TINYINT, r, 1, &len); if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret) { PrintSqlStmtError(); } }
int maxpriceproduct(SQLHANDLE hdbc) { SQLRETURN cliRC = SQL_SUCCESS; int rc = 0; SQLHANDLE hstmt; /* statement handle */ SQLVARCHAR xmldata[3000]; /* query to be executed */ SQLCHAR *stmt = (SQLCHAR *)" let $prod := for $product in db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')" " /product/description" " order by $product/price return $product" " return <product> {$prod[1]/name} </product>"; cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); DBC_HANDLE_CHECK(hdbc, cliRC); /* Set the attribute SQL_ATTR_XQUERY_STATEMENT to indicate that the query is an XQuery */ rc = SQLSetStmtAttr(hstmt, SQL_ATTR_XQUERY_STATEMENT, (SQLPOINTER)SQL_TRUE, SQL_NTS); printf("%d", rc); if (rc != 0) { return rc; } printf("\n Directly execute the statement\n"); printf(" %s\n", stmt); /* directly execute the statement */ cliRC = SQLExecDirect(hstmt, stmt, SQL_NTS); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Bind column 1 to variable */ cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Fetch each row and display */ cliRC = SQLFetch(hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); if (cliRC == SQL_NO_DATA_FOUND) { printf("\n Data not found.\n"); } else printf("%s \n\n",xmldata); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* free the statement handle */ cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); return rc; } /* maxpriceproduct */
void DbHelper::BindResultColumnText(wchar_t* text, size_t count) { SQLLEN len = 0; //todo: wchar_t*형 결과 컬럼 바인딩 SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_WCHAR, text, count * 2, &len ); ///# 버퍼 크기를 넣어줘야지... count*2 if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret) { PrintSqlStmtError(); } }
static SQLRETURN doSelect(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT shdl, SQLCHAR sql[], int *rcnt) { SQLCHAR name[33]; SQLINTEGER empno, col2sz, col1sz; SQLRETURN rc = SQLExecDirect(shdl, sql, SQL_NTS); CHECK_HANDLE(SQL_HANDLE_STMT, shdl, rc, "SQLExecDirect", sql); /* bind empno and name to columns 1 and 2 of the fetch */ SQLBindCol(shdl, 1, SQL_C_LONG, (SQLPOINTER) &empno, (SQLINTEGER) sizeof (SQLINTEGER), (SQLINTEGER *) &col1sz); SQLBindCol(shdl, 2, SQL_C_CHAR, (SQLPOINTER) name, (SQLINTEGER) sizeof (name), &col2sz); *rcnt = 0; while (SQLFetch(shdl) == SQL_SUCCESS) { *rcnt += 1; /* btlogger_debug("(%ld,%s)\n", empno, name);*/ } return SQL_SUCCESS; }
void DbHelper::BindResultColumnFloat(float* r) { SQLLEN len = 0; //todo: float형 결과 컬럼 바인딩 SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_FLOAT, r, sizeof( float ), &len ); ///# 왜 7?? 버퍼 크기를 넣어줘야 함 // 버퍼 크기 = sizeof( float ) = 4 by sm9 if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret) { PrintSqlStmtError(); } }
void DbHelper::BindResultColumnFloat(float* r) { SQLLEN len = 0; //todo: float형 결과 컬럼 바인딩 SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_FLOAT, r, 15, &len ); ///# 왜 15?? // WIP if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret) { PrintSqlStmtError(); } }
// SQL문을 실행한다. 실패시 진단 정보를 출력하고 FALSE를 리턴한다. BOOL CQuery::Exec(LPCTSTR szSQL) { int c; while( true ) { gWindow.PrintLog("%s", szSQL); // SQL문을 실행한다. SQL_NO_DATA를 리턴한 경우도 일단 성공으로 취급한다. // 이 경우 Fetch에서 EOF를 리턴하도록 되어 있기 때문에 진단 정보를 출력할 필요는 없다. ret=SQLExecDirect(hStmt,(SQLCHAR *)szSQL,SQL_NTS); if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_NO_DATA)) { bool bReConnect = false; PrintDiag(bReConnect); Clear(); if(bReConnect == true) { ::Sleep(1); continue; } return FALSE; } // Update, Delete, Insert 명령시 영향받은 레코드 개수를 구해 놓는다. SQLRowCount(hStmt,&AffectCount); SQLNumResultCols(hStmt,&nCol); if (nCol > MAXCOL) { //::MessageBox(NULL,"최대 컬럼 수를 초과했습니다","CQuery 에러",MB_OK); gWindow.PrintLog("CQuery error :최대 컬럼 수를 초과했습니다"); return FALSE; } // nCol이 0인 경우는 Select문 이외의 다른 명령을 실행한 경우이므로 // 바인딩을 할 필요가 없다. if (nCol == 0) { Clear(); return TRUE; } // 모든 컬럼을 문자열로 바인딩해 놓는다. Col배열은 zero base, // 컬럼 번호는 one base임에 유의할 것 for (c=0;c<nCol;c++) { SQLBindCol(hStmt,c+1,SQL_C_CHAR,Col[c],255,&lCol[c]); SQLDescribeCol(hStmt,c+1,ColName[c],30,NULL,NULL,NULL,NULL,NULL); } return TRUE; } }