BOOL CODBC::WriteScorcoData(char* SQL, BYTE* pData, int Length)
{
SQLRETURN retcode;
SQLINTEGER cbObjectParam;
SQLPOINTER pToken;
char* pSQL = new char[strlen(SQL)];
sprintf(pSQL, SQL, "?");
SQLFreeStmt(hstmt,SQL_CLOSE);
retcode = SQLPrepare(hstmt, (unsigned char *)pSQL, SQL_NTS);
if (MYSQLSUCCESS(rc))
{
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,
0, 0, (SQLPOINTER) 2, 0, &cbObjectParam);
cbObjectParam = SQL_LEN_DATA_AT_EXEC(0);
retcode = SQLExecute(hstmt);
while (retcode == SQL_NEED_DATA)
{
retcode = SQLParamData(hstmt, &pToken);
if (retcode == SQL_NEED_DATA)
SQLPutData(hstmt, pData, Length);
}
return TRUE;
}
else
{
SaveSQLError();
return FALSE;
}
}
void create_table()
{
printf("create_table\n");
SQLHSTMT stmt;
RETCODE r;
char errorMsg[LOG_MSG_WIDTH];
connect_to_database();
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
r = SQLPrepare(stmt, (SQLCHAR *)"CREATE TABLE process_log (process_id bigint NOT NULL, order_id integer NOT NULL, finish_date timestamp, PRIMARY KEY (process_id));", SQL_NTS);
if (r != SQL_SUCCESS) {
printf("SQLPrepare: executeSqlStatement failed\n");
}
else
{
r = SQLExecute(stmt);
if (!((r == SQL_SUCCESS) || (r == SQL_SUCCESS_WITH_INFO)))
{
printf("return value: %d\n", r);
printf("Error:%s\n", dbErrorMsg(env, dbc, stmt, &errorMsg[0], LOG_MSG_WIDTH));
printf("SQLExecute: executeSqlStatement failed\n");
}
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
disconnect_from_database();
}
SQLRETURN ODBCArgExecute(CHAR *Mess,...)
{
va_list Ah;
va_start(Ah,Mess);
vsprintf(EhOdbc.lpSQLCommand,Mess,Ah); // Messaggio finale
// win_infoarg("[%s]",EhOdbc.lpSQLCommand);
// Preparo
EhOdbc.sqlLastError=SQLPrepare(EhOdbc.hStmt, EhOdbc.lpSQLCommand, SQL_NTS);
if ((EhOdbc.sqlLastError!=SQL_SUCCESS)&&(EhOdbc.sqlLastError!=SQL_SUCCESS_WITH_INFO))
{if (!EhOdbc.fNoErrorView) ODBCError("AE:Prepare");
goto FINE;
}
// Creo il dizionario
ODBCDoDictionary(); // Creo il dizionario del risultato
// Eseguo il comando
EhOdbc.sqlLastError=SQLExecute(EhOdbc.hStmt);
if (EhOdbc.sqlLastError!=SQL_SUCCESS) {if (!EhOdbc.fNoErrorView) ODBCError("AE:Execute");}
/*
EhOdbc.sqlLastError=SQLSetPos(EhOdbc.hStmt,1,SQL_POSITION,SQL_LOCK_NO_CHANGE);
if (EhOdbc.sqlLastError!=SQL_SUCCESS)
{
if (!EhOdbc.fNoErrorView) ODBCError("AE:SetPos");
}
*/
FINE:
va_end(Ah);
return EhOdbc.sqlLastError;
}
int MADB_KeyTypeCount(MADB_Dbc *Connection, char *TableName, int KeyFlag)
{
int Count= 0;
unsigned int i;
char StmtStr[1024];
char *p= StmtStr;
char Database[65];
SQLHSTMT Stmt= NULL;
MADB_Stmt *KeyStmt;
SQLGetConnectAttr((SQLHDBC)Connection, SQL_ATTR_CURRENT_CATALOG, Database, 65, NULL);
p+= my_snprintf(p, 1024, "SELECT * FROM ");
if (Database)
p+= my_snprintf(p, 1024 - strlen(p), "`%s`.", Database);
p+= my_snprintf(p, 1024 - strlen(p), "%s LIMIT 0", TableName);
if (SQLAllocStmt((SQLHDBC)Connection, &Stmt) == SQL_ERROR ||
SQLPrepare(Stmt, (SQLCHAR *)StmtStr, SQL_NTS) == SQL_ERROR ||
SQLExecute(Stmt) == SQL_ERROR ||
SQLFetch(Stmt) == SQL_ERROR)
goto end;
KeyStmt= (MADB_Stmt *)Stmt;
for (i=0; i < mysql_stmt_field_count(KeyStmt->stmt); i++)
if (KeyStmt->stmt->fields[i].flags & KeyFlag)
Count++;
end:
if (Stmt)
SQLFreeHandle(SQL_HANDLE_STMT, Stmt);
return Count;
}
SQLRETURN unixodbc_backend_debug::do_prepare_statement(SQLHSTMT statement_handle, SQLCHAR * statement_text, SQLINTEGER text_length) const
{
std::cout << " *DEBUG* prepare_statement";
auto const return_code = SQLPrepare(statement_handle, statement_text, text_length);
std::cout << " (return code " << return_code << ")" << std::endl;
return return_code;
}
ResultSet_T SqlServerConnection_executeQuery(T C, const char *sql, va_list ap) {
va_list ap_copy;
const char *tail;
SQLHSTMT hstmt;
assert(C);
StringBuffer_clear(C->sb);
va_copy(ap_copy, ap);
StringBuffer_vappend(C->sb, sql, ap_copy);
va_end(ap_copy);
C->lastError = SQLAllocStmt(C->db->hdbc,&hstmt);
C->lastError = SQLPrepare(hstmt,
StringBuffer_toString(C->sb), StringBuffer_length(C->sb));
if(!SQLSERVERSUCCESS(C->lastError)) {
getSqlErr(C,hstmt);
return NULL;
}
C->lastError = SQLExecute(hstmt);
if(SQLSERVERSUCCESS(C->lastError))
return ResultSet_new(SqlServerResultSet_new(hstmt, C->maxRows, false), (Rop_T)&sqlserverrops);
else {
getSqlErr(C,hstmt);
}
return NULL;
}
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_exec(switch_odbc_handle_t *handle, const char *sql, switch_odbc_statement_handle_t *rstmt,
char **err)
{
#ifdef SWITCH_HAVE_ODBC
SQLHSTMT stmt = NULL;
int result;
char *err_str = NULL;
if (!db_is_up(handle)) {
goto error;
}
if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) {
goto error;
}
if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) {
goto error;
}
result = SQLExecute(stmt);
if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) {
goto error;
}
if (rstmt) {
*rstmt = stmt;
} else {
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
return SWITCH_ODBC_SUCCESS;
error:
if (stmt) {
err_str = switch_odbc_handle_get_error(handle, stmt);
}
if (err_str) {
if (!switch_stristr("already exists", err_str) && !switch_stristr("duplicate key name", err_str)) {
switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str));
}
if (err) {
*err = err_str;
} else {
free(err_str);
}
}
if (rstmt) {
*rstmt = stmt;
} else if (stmt) {
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
#endif
return SWITCH_ODBC_FAIL;
}
static void
Test(int use_indicator)
{
char buf[128];
SQLLEN ind;
SQLLEN *pind = use_indicator ? &ind : NULL;
strcpy(buf, "I don't exist");
ind = strlen(buf);
if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0, buf, 128, pind) != SQL_SUCCESS) {
printf("Unable to bind parameter\n");
exit(1);
}
if (SQLPrepare(Statement, (SQLCHAR *) "SELECT id, name FROM master..sysobjects WHERE name = ?", SQL_NTS) != SQL_SUCCESS) {
printf("Unable to prepare statement\n");
exit(1);
}
if (SQLExecute(Statement) != SQL_SUCCESS) {
printf("Unable to execute statement\n");
exit(1);
}
if (SQLFetch(Statement) != SQL_NO_DATA) {
printf("Data not expected\n");
exit(1);
}
if (SQLMoreResults(Statement) != SQL_NO_DATA) {
printf("Not expected another recordset\n");
exit(1);
}
/* use same binding above */
strcpy(buf, "sysobjects");
ind = strlen(buf);
if (SQLExecute(Statement) != SQL_SUCCESS) {
printf("Unable to execute statement\n");
exit(1);
}
if (SQLFetch(Statement) != SQL_SUCCESS) {
printf("Data expected\n");
exit(1);
}
if (SQLFetch(Statement) != SQL_NO_DATA) {
printf("Data not expected\n");
exit(1);
}
if (SQLMoreResults(Statement) != SQL_NO_DATA) {
printf("Not expected another recordset\n");
exit(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 */
int
main(int argc, char **argv)
{
SQLRETURN rcode;
SQLHSTMT m_hstmt1;
SQLSMALLINT cnt = 0;
int failed = 0;
use_odbc_version3 = 1;
Connect();
Command(Statement, "CREATE TABLE #t1 ( k INT, c CHAR(10), vc VARCHAR(10) )");
Command(Statement, "CREATE TABLE #tmp1 (i NUMERIC(10,0) IDENTITY PRIMARY KEY, b VARCHAR(20) NULL, c INT NOT NULL)");
/* test hidden column with FOR BROWSE */
ResetStatement();
m_hstmt1 = Statement;
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "SELECT c, b FROM #tmp1", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLExecDirect");
rcode = SQLNumResultCols(m_hstmt1, &cnt);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLNumResultCols 1");
if (cnt != 2) {
fprintf(stderr, "Wrong number of columns in result set: %d\n", (int) cnt);
failed = 1;
}
ResetStatement();
/* test hidden column with cursors*/
CheckCursor();
m_hstmt1 = Statement;
rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLSetStmtAttr SQL_ATTR_CURSOR_SCROLLABLE");
rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLSetStmtAttr SQL_ATTR_CURSOR_SENSITIVITY");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "SELECT * FROM #t1", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLPrepare 1");
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLExecute 1");
rcode = SQLNumResultCols(m_hstmt1, &cnt);
CHECK_RCODE(SQL_HANDLE_STMT, m_hstmt1, "SQLNumResultCols 1");
if (cnt != 3) {
fprintf(stderr, "Wrong number of columns in result set: %d\n", (int) cnt);
failed = 1;
}
Disconnect();
return failed ? 1: 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;
}
void Prepare(const std::wstring& sql)
{
std::string message;
SQLRETURN rc = SQLPrepare(mStmt, (SQLWCHAR*)sql.c_str(), SQL_NTS);
if (!SQL_SUCCEEDED(rc))
{
HANAException::GetHANAErrorMessage(SQL_HANDLE_STMT, mStmt, rc, message);
throw HANAException(message.c_str());
}
}
/*-----------------------------------------------------------------------------*/
void Parse()
{
int j;
struct Cursor *cur = (struct Cursor *)ptoc_int(2);
RETCODE rc;
if (cur->Status == 2) { /* reusing opened cursor*/
rc = SQLFreeStmt(cur->hstmt,SQL_CLOSE);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
ctop_int(3, PrintErrorMsg(cur));
SetCursorClose(cur);
return;
}
/* reset just char select vars, since they store addr of chars*/
for (j = 0; j < cur->NumBindVars; j++) {
if (cur->BindTypes[j] == 2)
rc = SQLBindParameter(cur->hstmt, (short)(j+1), SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0,(char *) cur->BindList[j], 0, &SQL_NTSval);
}
} else {
if (SQLPrepare(cur->hstmt, cur->Sql, SQL_NTS) != SQL_SUCCESS) {
ctop_int(3,PrintErrorMsg(cur));
SetCursorClose(cur);
return;
}
/* set the bind variables*/
for (j = 0; j < cur->NumBindVars; j++) {
if (cur->BindTypes[j] == 2)
/* we're sloppy here. it's ok for us to use the default values*/
rc = SQLBindParameter(cur->hstmt, (short)(j+1), SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0,(char *)cur->BindList[j], 0, &SQL_NTSval);
else if (cur->BindTypes[j] == 1) {
rc = SQLBindParameter(cur->hstmt, (short)(j+1), SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT,
0, 0, (float *)cur->BindList[j], 0, NULL);
} else
rc = SQLBindParameter(cur->hstmt, (short)(j+1), SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
0, 0, (int *)(cur->BindList[j]), 0, NULL);
if (rc != SQL_SUCCESS) {
ctop_int(3,PrintErrorMsg(cur));
SetCursorClose(cur);
return;
}
}
}
/* submit it for execution*/
if (SQLExecute(cur->hstmt) != SQL_SUCCESS) {
ctop_int(3,PrintErrorMsg(cur));
SetCursorClose(cur);
return;
}
ctop_int(3,0);
return;
}
std::shared_ptr<detail::prepared_statement_handle_t> prepare_statement(detail::connection_handle_t& handle, const std::string& statement) {
if(handle.debug) {
std::cerr << "ODBC debug: Preparing: " << statement << std::endl;
}
SQLHSTMT stmt;
SQLAllocHandle(SQL_HANDLE_STMT, handle.dbc, &stmt);
std::shared_ptr<detail::prepared_statement_handle_t> ret = std::make_shared<detail::prepared_statement_handle_t>(stmt, handle.debug);
if(SQL_SUCCEEDED(SQLPrepare(stmt, make_sqlchar(statement), statement.length()))){
return ret;
} else {
throw sqlpp::exception("ODBC error: couldn't SQLPrepare " + statement + ": "+detail::odbc_error(stmt, SQL_HANDLE_STMT));
}
}
static SQLRETURN doSql(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT shdl, SQLCHAR sql[])
{
SQLRETURN rc;
btlogger_debug("doSql %s\n", sql);
rc = SQLPrepare(shdl, sql, SQL_NTS);
CHECK_HANDLE(SQL_HANDLE_STMT, shdl, rc, "SQLPrepare", sql);
SQLExecDirect(shdl, sql, SQL_NTS);
CHECK_HANDLE(SQL_HANDLE_STMT, shdl, rc, "SQLExecDirect", sql);
return SQL_SUCCESS;
}
void
test ()
{
HSTMT hstmt;
SQLRETURN rc;
rc = SQLAllocHandle (SQL_HANDLE_STMT, (SQLHANDLE) hdbc,
(SQLHANDLE *) & hstmt);
if (rc != SQL_SUCCESS)
{
err_printf ("SQLAllocHandle() failed.\n");
return;
}
rc = SQLPrepare (hstmt, "burstoff_rs_proc ()", SQL_NTS);
if (rc != SQL_SUCCESS)
{
err_printf ("prepare failed.\n");
error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt);
exit (1);
}
call_proc (10);
printf ("test: SQLExecute\n");
rc = SQLExecute (hstmt);
if (rc != SQL_SUCCESS)
{
err_printf ("exec failed.\n");
error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt);
exit (1);
}
printf ("test: SQLFetch\n");
rc = SQLFetch (hstmt);
if (rc != SQL_SUCCESS)
{
err_printf ("fetch failed.\n");
error (SQL_HANDLE_STMT, (SQLHANDLE) hstmt);
exit (1);
}
printf ("test: SQLCancel\n");
rc = SQLCancel (hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
err_printf ("SQLCancel() failed.\n");
exit (-1);
}
printf ("test: SQLFreeHandle\n");
rc = SQLFreeHandle (SQL_HANDLE_STMT, (SQLHANDLE) hstmt);
}
/*
** Executes a SQL statement.
** Returns
** cursor object: if there are results or
** row count: number of rows affected by statement if no results
*/
static int conn_execute (lua_State *L) {
conn_data *conn = (conn_data *) getconnection (L);
const char *statement = luaL_checkstring(L, 2);
SQLHDBC hdbc = conn->hdbc;
SQLHSTMT hstmt;
SQLSMALLINT numcols;
SQLRETURN ret;
ret = SQLAllocHandle(hSTMT, hdbc, &hstmt);
if (error(ret))
return fail(L, hDBC, hdbc);
ret = SQLPrepare(hstmt, (char *) statement, SQL_NTS);
if (error(ret)) {
ret = fail(L, hSTMT, hstmt);
SQLFreeHandle(hSTMT, hstmt);
return ret;
}
/* execute the statement */
ret = SQLExecute (hstmt);
if (error(ret)) {
ret = fail(L, hSTMT, hstmt);
SQLFreeHandle(hSTMT, hstmt);
return ret;
}
/* determine the number of results */
ret = SQLNumResultCols (hstmt, &numcols);
if (error(ret)) {
ret = fail(L, hSTMT, hstmt);
SQLFreeHandle(hSTMT, hstmt);
return ret;
}
if (numcols > 0)
/* if there is a results table (e.g., SELECT) */
return create_cursor (L, 1, conn, hstmt, numcols);
else {
/* if action has no results (e.g., UPDATE) */
SQLINTEGER numrows;
ret = SQLRowCount(hstmt, &numrows);
if (error(ret)) {
ret = fail(L, hSTMT, hstmt);
SQLFreeHandle(hSTMT, hstmt);
return ret;
}
lua_pushnumber(L, numrows);
SQLFreeHandle(hSTMT, hstmt);
return 1;
}
}
string ODBCHandler::execute_query(unsigned char* query, ssize_t threashold,
off_t *row_count, ssize_t *len, ssize_t *last_row_len)
{
SQLHSTMT stmt;
SQLRETURN ret;
SQLSMALLINT nr_columns;
string ODBC_error;
stringstream result_str;
bool row_bound = false;
cout<<"Query: "<<query<<endl;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
ret = SQLPrepare(stmt, query , SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
ODBC_error = extract_error(stmt, SQL_HANDLE_STMT);
cout<<ODBC_error<<endl;
}
ret = SQLExecute(stmt);
if (!SQL_SUCCEEDED(ret)) {
ODBC_error = extract_error(stmt, SQL_HANDLE_STMT);
cout<<ODBC_error<<endl;
}
SQLNumResultCols(stmt, &nr_columns);
*row_count = 0;
*len = 0;
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
SQLUSMALLINT i;
*len += *last_row_len;
(*last_row_len) = 0;
for (i = 1; i <= nr_columns; i++) {
SQLLEN indicator;
char buf[512];
ret = SQLGetData(stmt, i, SQL_C_CHAR,
buf, sizeof(buf), &indicator);
if (i == nr_columns)
row_bound = true;
if (SQL_SUCCEEDED(ret)) {
if (indicator == SQL_NULL_DATA)
strcpy(buf, "NULL");
*last_row_len += encode_results(result_str, buf, row_bound);
}
}
if (*len + *last_row_len >= threashold)
break;
(*row_count)++;
row_bound = false;
}
return result_str.str();
}
/************************************************************************
* name: SQLPrepareW
* arguments:
* returns/side-effects:
* description:
* NOTE:
************************************************************************/
ODBC_INTERFACE RETCODE SQL_API
SQLPrepareW (SQLHSTMT hstmt, SQLWCHAR *str, SQLINTEGER str_len)
{
RETCODE ret = ODBC_ERROR;
char* sql_state = NULL;
int sql_state_len = 0;
OutputDebugString ("SQLPrepareW called.\n");
wide_char_to_bytes (str, str_len, &sql_state, &sql_state_len, NULL);
ret = SQLPrepare(hstmt, sql_state, sql_state_len);
UT_FREE(sql_state);
return ret;
}
static int odbc_dispatch13(void)
{
unsigned long retval;
PWord rval; int rtype;
PWord arg1; int type1;
PWord arg2; int type2;
PWord arg3; int type3;
PWord arg4; int type4;
PWord arg5; int type5;
PI_getan(&arg1,&type1,1);
if (type1 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg1,type1))
PI_FAIL;
PI_getan(&arg2,&type2,2);
if (type2 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg2,type2))
PI_FAIL;
PI_getan(&arg3,&type3,3);
if (type3 == PI_SYM)
arg3 = (unsigned long) PI_getsymname(0,arg3,0);
else if (!CI_get_integer((unsigned long *)&arg3,type3))
PI_FAIL;
PI_getan(&arg4,&type4,4);
if (type4 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg4,type4))
PI_FAIL;
PI_getan(&arg5,&type5,5);
switch(arg1)
{
case 0:
retval = (unsigned long) SQLSetCursorName(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4));
break;
case 1:
retval = (unsigned long) SQLPrepare(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLINTEGER ) arg4));
break;
case 2:
retval = (unsigned long) SQLExecDirect(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLINTEGER ) arg4));
break;
default:
PI_FAIL;
}
PI_makedouble(&rval,&rtype,(double) retval);
if (PI_unify(arg5,type5,rval,rtype))
PI_SUCCEED;
PI_FAIL;
}
int odbc_command(ClipMachine* cm,SQLSTMT* s,ClipVar* ap){
ODBC_STMT* stmt = (ODBC_STMT*)s;
SQLRETURN er;
SDWORD count;
odbc_bindpars(stmt,ap);
if((er = SQLAllocStmt(stmt->conn->conn,&stmt->hstmt))) goto err;
if((er = SQLPrepare(stmt->hstmt,stmt->sql,strlen(stmt->sql)))) goto err;
if((er = SQLExecute(stmt->hstmt))) goto err;
if((er = SQLRowCount(stmt->hstmt,&count))) goto err;
if(count < 0)
count = 0;
return count;
err:
return odbc_error(cm,stmt->conn->loc,0,stmt->conn->conn,stmt->hstmt,__LINE__,er_execute);
}
static void
Test(const char *query)
{
int res;
if (SQLPrepare(Statement, (SQLCHAR *) query, SQL_NTS) != SQL_SUCCESS) {
fprintf(stderr, "Unable to prepare statement\n");
exit(1);
}
if (SQLExecute(Statement) != SQL_SUCCESS) {
fprintf(stderr, "Unable to execute statement\n");
exit(1);
}
res = SQLFetch(Statement);
if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
fprintf(stderr, "Unable to fetch row.\n");
CheckReturn();
exit(1);
}
if (SQLFetch(Statement) != SQL_NO_DATA) {
fprintf(stderr, "Warning was returned as a result row -- bad!\n");
exit(1);
}
/*
* Microsoft SQL Server 2000 provides a diagnostic record
* associated with the second SQLFetch (which returns
* SQL_NO_DATA) saying "Warning: Null value is eliminated by
* an aggregate or other SET operation."
* We check for "NO DM" cause unixODBC till 2.2.11 do not read
* errors on SQL_NO_DATA
*/
if (db_is_microsoft() && tds_no_dm) {
SQLCHAR output[256];
if (!SQL_SUCCEEDED(SQLGetDiagRec(SQL_HANDLE_STMT, Statement, 1, NULL, NULL, output, sizeof(output), NULL))) {
fprintf(stderr, "SQLGetDiagRec should not fail\n");
exit(1);
}
printf("Message: %s\n", (char *) output);
}
ResetStatement();
}
int dbd_db2_statement_create(lua_State *L, connection_t *conn, const char *sql_query) {
SQLRETURN rc = SQL_SUCCESS;
statement_t *statement = NULL;
SQLHANDLE stmt;
SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length;
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn->db2, &stmt);
if (rc != SQL_SUCCESS) {
SQLGetDiagRec(SQL_HANDLE_DBC, conn->db2, 1, sqlstate, &sqlcode, message, SQL_MAX_MESSAGE_LENGTH + 1, &length);
lua_pushnil(L);
lua_pushfstring(L, DBI_ERR_ALLOC_STATEMENT, message);
return 2;
}
/*
* turn off deferred prepare
* statements will be sent to the server at prepare time,
* and therefore we can catch errors now rather
* than at execute time
*/
rc = SQLSetStmtAttr(stmt,SQL_ATTR_DEFERRED_PREPARE,(SQLPOINTER)SQL_DEFERRED_PREPARE_OFF,0);
rc = SQLPrepare(stmt, (SQLCHAR *)sql_query, SQL_NTS);
if (rc != SQL_SUCCESS) {
SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, sqlstate, &sqlcode, message, SQL_MAX_MESSAGE_LENGTH + 1, &length);
lua_pushnil(L);
lua_pushfstring(L, DBI_ERR_PREP_STATEMENT, message);
return 2;
}
statement = (statement_t *)lua_newuserdata(L, sizeof(statement_t));
statement->stmt = stmt;
statement->db2 = conn->db2;
statement->resultset = NULL;
statement->bind = NULL;
luaL_getmetatable(L, DBD_DB2_STATEMENT);
lua_setmetatable(L, -2);
return 1;
}
/**
* Prepare a query for execution.
*
* @param chan R ODBC handle
* @param query character string with query
* @retval 1 on success, -1 on error
*/
SEXP RODBCPrepare(SEXP chan, SEXP query)
{
pRODBCHandle thisHandle = R_ExternalPtrAddr(chan);
const char *cquery;
SQLRETURN res = 0;
FreeHandleResources(thisHandle);
res = SQLAllocHandle(SQL_HANDLE_STMT, thisHandle->hDbc, &thisHandle->hStmt);
SQL_RESULT_CHECK(res, thisHandle, _("[RODBCext] Error: SQLAllocStmt failed"), ScalarInteger(-1));
cquery = translateChar(STRING_ELT(query, 0));
res = SQLPrepare(thisHandle->hStmt, (SQLCHAR *) cquery,
strlen(cquery) );
SQL_RESULT_CHECK(res, thisHandle, _("[RODBCext] Error: SQLPrepare failed"), ScalarInteger(-1));
return ScalarInteger(1);
}
bool CSQLEx::Execute(char* szQuery,...)
{
if(this->m_bConnected == 0)
{
return false;
}
if(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT,this->m_SQLConnection,&this->m_STMT)) == 0)
{
return false;
}
char szTemp[1024];
va_list pArguments;
va_start(pArguments,szQuery);
vsprintf(szTemp,szQuery,pArguments);
va_end(pArguments);
if(SQL_SUCCEEDED(SQLPrepare(this->m_STMT,(unsigned char*)szTemp,strlen(szTemp))) == 0)
{
return false;
}
SQLRETURN Execute = SQLExecute(this->m_STMT);
if(Execute == SQL_SUCCESS || Execute == SQL_SUCCESS_WITH_INFO || Execute == SQL_NO_DATA)
{
if(SQLRowCount(this->m_STMT,&this->m_RowCount) != SQL_SUCCESS)
{
this->m_RowCount = 0;
}
if(SQLNumResultCols(this->m_STMT,(SQLSMALLINT*)&this->m_ColCount) != SQL_SUCCESS)
{
this->m_ColCount = 0;
}
return true;
}
return false;
}
// Prepare statement
bool statement::prepare(connection & _conn, const _tstring & _stmt)
{
RETCODE rc;
// Close previous
close();
// open a new one
if (!open(_conn))
return false;
// Prepare statement
SQLTCHAR_buf buff = ybstring2sqltchar(_stmt, "");
rc = SQLPrepare(stmt_h, buff.data, SQL_NTS);
if (!TIODBC_SUCCESS_CODE(rc))
return false;
return true;
}
bool OdbcCommand::Prepare(const tstring & szSQL)
{
if (!Open())
return false;
#ifdef USE_SQL_TRACE
TRACE((szSQL + _T("\n")).c_str());
#endif
if (!SQL_SUCCEEDED(SQLPrepare(m_hStmt, (SQLTCHAR *)szSQL.c_str(), szSQL.length())))
{
if (m_odbcConnection != nullptr)
m_szError = m_odbcConnection->ReportSQLError(SQL_HANDLE_STMT, m_hStmt, _T("SQLPrepare"), _T("Failed to prepare statement."));
else
m_szError = OdbcConnection::GetSQLError(SQL_HANDLE_STMT, m_hStmt);
Close();
return false;
}
if (!BindParameters())
return false;
SQLRETURN result = SQLExecute(m_hStmt);
if (!(result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO || result == SQL_NO_DATA))
{
if (m_odbcConnection != nullptr)
m_szError = m_odbcConnection->ReportSQLError(SQL_HANDLE_STMT, m_hStmt, (TCHAR *)szSQL.c_str(), _T("Failed to execute prepared statement."));
else
m_szError = OdbcConnection::GetSQLError(SQL_HANDLE_STMT, m_hStmt);
Close();
return false;
}
// If there's no rows to move through, skip to the next result set.
if (!MoveNext())
MoveNextSet();
ClearParameters();
return true;
}
bool OdbcCommand::Prepare(const tstring & szSQL)
{
if (!Open())
return false;
#ifdef _DEBUG
OutputDebugString((szSQL + _T("\n")).c_str());
#endif
if (!SQL_SUCCEEDED(SQLPrepare(m_hStmt, (SQLTCHAR *)szSQL.c_str(), szSQL.length())))
{
if (m_odbcConnection != NULL)
m_szError = m_odbcConnection->ReportSQLError(SQL_HANDLE_STMT, m_hStmt, _T("SQLPrepare"), _T("Failed to prepare statement."));
else
m_szError = OdbcConnection::GetSQLError(SQL_HANDLE_STMT, m_hStmt);
Close();
return false;
}
if (!BindParameters())
return false;
if (!SQL_SUCCEEDED(SQLExecute(m_hStmt)))
{
if (m_odbcConnection != NULL)
m_szError = m_odbcConnection->ReportSQLError(SQL_HANDLE_STMT, m_hStmt, (TCHAR *)szSQL.c_str(), _T("Failed to execute prepared statement."));
else
m_szError = OdbcConnection::GetSQLError(SQL_HANDLE_STMT, m_hStmt);
Close();
return false;
}
// If there's no rows to move through, skip to the next result set.
if (!MoveNext())
MoveNextSet();
ClearParameters();
return true;
}
int odbc_refresh(ClipMachine* cm,SQLROWSET* rs,SQLSTMT* s,ClipVar* ap){
ODBC_STMT* stmt = (ODBC_STMT*)s;
SQLRETURN er;
ODBC_ROWSET* rowset = (ODBC_ROWSET*)rs;
SQLSMALLINT cols;
int i;
void** rec;
odbc_bindpars(stmt,ap);
if((er = SQLAllocStmt(stmt->conn->conn,&stmt->hstmt))) goto err;
if((er = SQLPrepare(stmt->hstmt,stmt->sql,strlen(stmt->sql)))) goto err;
if((er = SQLExecute(stmt->hstmt))) goto err;
if((er = SQLNumResultCols(stmt->hstmt,&cols))) goto err;
rec = malloc(sizeof(void*)*rowset->nfields);
for(i=0;i<rowset->nfields;i++){
rec[i] = malloc(rowset->fields[i].buflen+4+_TERM_ZERO);
if((er = SQLBindCol(stmt->hstmt,i+1,SQL_DEFAULT,
rec[i]+4,rowset->fields[i].buflen+_TERM_ZERO,rec[i]))) goto err;
}
er = SQLFetch(stmt->hstmt);
if(er != SQL_NO_DATA){
for(i=0;i<rowset->nfields;i++)
free(rowset->data[rowset->recno-1][i]);
free(rowset->data[rowset->recno-1]);
rowset->data[rowset->recno-1] = rec;
} else {
for(i=0;i<rowset->nfields;i++)
free(rec[i]);
free(rec);
odbc_delete((SQLROWSET*)rowset);
rowset->lastrec--;
if(rowset->recno > rowset->lastrec)
rowset->recno = rowset->lastrec;
}
return 0;
err:
return odbc_error(cm,stmt->conn->loc,0,stmt->conn->conn,stmt->hstmt,__LINE__,er_refresh);
}