SQLRETURN unixodbc_backend_debug::do_fetch_scroll(SQLHSTMT statement_handle, SQLSMALLINT fetch_orientation, SQLLEN fetch_offset) const
{
std::cout << " *DEBUG* fetch_scroll";
auto const return_code = SQLFetchScroll(statement_handle, fetch_orientation, fetch_offset);
std::cout << " (return code " << return_code << ")" << std::endl;
return return_code;
}
/* ------------------------------------------------------------- */
PJXNODE jx_sqlFetchRelative (PJXSQL pSQL, LONG fromRow)
{
if (pSQL == NULL) return (NULL);
pSQL->rc = SQLFetchScroll (pSQL->hstmt, SQL_FETCH_RELATIVE , fromRow);
return jx_sqlFormatRow(pSQL);
}
bool DBHelper::moveCursor(SQLHSTMT& stmt, char* output)
{
bool isMoved = false;
int choice;
SQLINTEGER rowCount;
SQLRowCount(stmt, &rowCount);
for (;; cin.clear(), cin.ignore(100, '\n'))
{
cout << output << "(0을 입력하면 취소): ";
cin >> choice;
if (choice >= CHOICE_QUIT && choice <= rowCount)
{
if (choice != CHOICE_QUIT &&
SQLFetchScroll(stmt, SQL_FETCH_ABSOLUTE, choice) == SQL_SUCCESS)
{
isMoved = true;
}
break;
}
else
{
cout << "잘못된 입력입니다.\n";
}
}
return isMoved;
}
BOOL CODBCRecordset::MovePrevious()
{
SQLRETURN ret;
ret = SQLFetchScroll(m_hStmt, SQL_FETCH_PRIOR, 0);
m_bIsBOF = ret == SQL_NO_DATA;
m_bIsEOF = FALSE;
return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}
BOOL CODBCRecordset::MoveLast()
{
SQLRETURN ret;
ret = SQLFetchScroll(m_hStmt, SQL_FETCH_LAST, 0);
m_bIsEOF = ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
if(m_bIsEOF)
m_bIsBOF = FALSE;
return m_bIsEOF;
}
static bool
execInternal(const char *stmt, int mode)
{
bool notfound = false;
newStatement();
if(!prepareInternal(stmt))
return false; /* error */
if(!rv_numRets) {
if(!(mode & 1)) {
showStatement();
errorPrint("2SQL select statement returns no values");
}
notfound = true;
} else { /* end no return values */
if(!(mode & 2)) {
showStatement();
errorPrint("2SQL statement returns %d values", rv_numRets);
}
}
if(!openfirst)
rc = SQLExecute(hstmt);
if(!rc) { /* statement succeeded */
/* fetch the data, or get a count of the rows affected */
if(rv_numRets) {
stmt_text = "fetch";
debugStatement();
rc = SQLFetchScroll(hstmt, (ushort) SQL_FD_FETCH_NEXT, 1);
if(rc == SQL_NO_DATA) {
rc = SQL_SUCCESS;
notfound = true;
} else
everything_null = false;
} else {
rc = SQLRowCount(hstmt, &rv_lastNrows);
if(sql_debug)
appendFile(sql_debuglog, "%d rows affected", rv_lastNrows);
if(sql_debug2)
printf("%d rows affected\n", rv_lastNrows);
}
}
if(errorTrap(0))
return false;
if(!rv_numRets)
return true;
return !notfound;
} /* execInternal */
bool CDbStmt::Skip(int n)
{
SQLRETURN nReturn;
if (n < 0)
return false;
if (n == 0)
return true;
if (myDone)
return false;
if (myRow == -1) {
Bind();
if (myRow == -1) {
myDone = true;
return false;
}
}
if (myBufIndex < (myRowsetSize-n)) {
myBufIndex+=n;
myRow+=n;
return true;
} else {
if (myHSTMT) {
nReturn = SQLFetchScroll(*myHSTMT,SQL_FETCH_RELATIVE,n-myRowsetSize+myBufIndex);
if (SQL_SUCCEEDED(nReturn)) {
myBufIndex = 0;
myRow+=n;
return true;
} else {
SetError(nReturn, false);
if (memcmp(myHSTMT->GetErrorState().Data(),"HY106",5)) {
myDone = true;
} else {
while (n-- > 0 && Next()) {
;
}
return true;
}
}
}
return false;
}
}
static int odbc_stmt_fetch(pdo_stmt_t *stmt,
enum pdo_fetch_orientation ori, zend_long offset)
{
RETCODE rc;
SQLSMALLINT odbcori;
pdo_odbc_stmt *S = (pdo_odbc_stmt*)stmt->driver_data;
switch (ori) {
case PDO_FETCH_ORI_NEXT: odbcori = SQL_FETCH_NEXT; break;
case PDO_FETCH_ORI_PRIOR: odbcori = SQL_FETCH_PRIOR; break;
case PDO_FETCH_ORI_FIRST: odbcori = SQL_FETCH_FIRST; break;
case PDO_FETCH_ORI_LAST: odbcori = SQL_FETCH_LAST; break;
case PDO_FETCH_ORI_ABS: odbcori = SQL_FETCH_ABSOLUTE; break;
case PDO_FETCH_ORI_REL: odbcori = SQL_FETCH_RELATIVE; break;
default:
strcpy(stmt->error_code, "HY106");
return 0;
}
rc = SQLFetchScroll(S->stmt, odbcori, offset);
if (rc == SQL_SUCCESS) {
return 1;
}
if (rc == SQL_SUCCESS_WITH_INFO) {
pdo_odbc_stmt_error("SQLFetchScroll");
return 1;
}
if (rc == SQL_NO_DATA) {
/* pdo_odbc_stmt_error("SQLFetchScroll"); */
return 0;
}
pdo_odbc_stmt_error("SQLFetchScroll");
return 0;
}
/*
* 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;
}
SQLRETURN unixodbc_backend::do_fetch_scroll(SQLHSTMT statement_handle, SQLSMALLINT fetch_orientation, SQLLEN fetch_offset) const
{
return SQLFetchScroll(statement_handle, fetch_orientation, fetch_offset);
}
void cursor_test()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt1, hstmt2;
SQLRETURN retcode;
SQLCHAR szStatus[ROWS][STATUS_LEN], szOpenDate[ROWS][OPENDATE_LEN];
SQLCHAR szNewStatus[STATUS_LEN], szNewOpenDate[OPENDATE_LEN];
SQLSMALLINT sOrderID[ROWS], sNewOrderID[ROWS];
SQLINTEGER cbStatus[ROWS], cbOrderID[ROWS], cbOpenDate[ROWS];
SQLUINTEGER FetchOrientation, crow, FetchOffset, irowUpdt;
SQLUSMALLINT RowStatusArray[ROWS];
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv );
SQLSetEnvAttr( henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0 );
SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc );
/* Specify the ODBC Cursor Library is always used then connect. */
SQLSetConnectAttr( hdbc, SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, 0 );
retcode = SQLConnect( hdbc, "postgres", SQL_NTS,
"", SQL_NTS,
"", SQL_NTS );
DumpODBCLog( NULL, hdbc, NULL );
if ( retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO )
{
/* Allocate a statement handle for the result set and a statement */
/* handle for a positioned update statement */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt2 );
/*
* create the data
*/
create_file( hstmt1 );
/* Specify an updateable statis cursor with 20 rows of data. Set */
/* the cursor name, execute the SELECT statement, and bind the */
/* rowset buffer to result set columns in column-wise fashion */
SQLSetStmtAttr( hstmt1, SQL_ATTR_CONCURRENCY, SQL_CONCUR_VALUES, 0 );
SQLSetStmtAttr( hstmt1, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, 0 );
SQLSetStmtAttr( hstmt1, SQL_ATTR_ROW_ARRAY_SIZE, ROWS, 20 );
SQLSetStmtAttr( hstmt1, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0 );
SQLSetStmtAttr( hstmt1, SQL_ATTR_ROWS_FETCHED_PTR, &crow, 0 );
SQLSetCursorName( hstmt1, "ORDERCURSOR", SQL_NTS );
SQLPrepare( hstmt1,
"select id, dt, status from ctest",
SQL_NTS );
{
char cname[ 30 ];
retcode = SQLDescribeCol( hstmt1, 1, cname, sizeof( cname ),
NULL, NULL, NULL, NULL, NULL );
printf( "ret = %d %s\n", retcode, cname );
DumpODBCLog( NULL, NULL, hstmt1 );
}
SQLExecute( hstmt1 );
SQLBindCol( hstmt1, 1, SQL_C_SSHORT, sOrderID, 0, cbOrderID );
SQLBindCol( hstmt1, 2, SQL_C_CHAR, szOpenDate, OPENDATE_LEN, cbOpenDate );
SQLBindCol( hstmt1, 3, SQL_C_CHAR, szStatus, STATUS_LEN, cbStatus );
FetchOrientation = SQL_FETCH_FIRST;
FetchOffset = 0;
do
{
int ret;
int count;
printf( "fetch %d %d\n", FetchOrientation, FetchOffset );
ret = SQLFetchScroll( hstmt1,
FetchOrientation,
FetchOffset );
SQLRowCount( hstmt1, &count );
printf( "ret = %d count = %d\n", ret, count );
Display( RowStatusArray, crow, sOrderID, cbOrderID,
szOpenDate, cbOpenDate, szStatus, cbStatus );
if ( SQL_SUCCEEDED( ret ))
{
char txt[ 50 ];
SQLINTEGER len;
ret = SQLSetPos( hstmt1, 5, SQL_POSITION,
SQL_LOCK_NO_CHANGE );
ret = SQLGetData( hstmt1, 2, SQL_C_CHAR, txt, sizeof( txt ),
&len );
printf( "ret = %d %s:%d\n", ret, txt, len );
}
} while( PromptScroll( &FetchOrientation, &FetchOffset ) != DONE );
SQLCloseCursor( hstmt1 );
SQLFreeStmt( hstmt1, SQL_DROP );
SQLFreeStmt( hstmt2, SQL_DROP );
SQLDisconnect( hdbc );
SQLFreeHandle( SQL_HANDLE_DBC, hdbc );
}
SQLFreeHandle( SQL_HANDLE_ENV, henv );
}
int main(int argc, char **argv)
{
int rc;
HSTMT hstmt = SQL_NULL_HSTMT;
int i;
SQLINTEGER colvalue1;
SQLINTEGER colvalue2;
SQLLEN indColvalue1;
SQLLEN indColvalue2;
char bookmark[8];
SQLLEN bookmark_ind;
char saved_bookmarks[3][8];
SQLLEN saved_bookmark_inds[3];
SQLINTEGER colvalues1[3];
SQLINTEGER colvalues2[3];
SQLLEN indColvalues1[3];
SQLLEN indColvalues2[3];
memset(bookmark, 0x7F, sizeof(bookmark));
memset(saved_bookmarks, 0xF7, sizeof(saved_bookmarks));
test_connect_ext("UpdatableCursors=1;UseDeclareFetch=0");
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
/*
* Initialize a table with some test data.
*/
printf("Creating test table bulkoperations_test\n");
rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE bulkoperations_test(i int4, orig int4)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLExecDirect(hstmt, (SQLCHAR *) "INSERT INTO bulkoperations_test SELECT g, g FROM generate_series(1, 10) g", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
printf("Opening a cursor for update, and fetching 10 rows\n");
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
(SQLPOINTER) SQL_CONCUR_ROWVER, 0);
CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0);
CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
/* Enable bookmarks */
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE, SQL_IS_UINTEGER);
CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
rc = SQLBindCol(hstmt, 0, SQL_C_VARBOOKMARK, &bookmark, sizeof(bookmark), &bookmark_ind);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &colvalue1, 0, &indColvalue1);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLBindCol(hstmt, 2, SQL_C_LONG, &colvalue2, 0, &indColvalue2);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM bulkoperations_test ORDER BY orig", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
for (i = 1; i <= 5; i++)
{
rc = SQLFetch(hstmt);
if (rc == SQL_NO_DATA)
break;
if (rc == SQL_SUCCESS)
printCurrentRow(hstmt);
else
{
print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt);
exit(1);
}
/* Save row # 2's bookmark for fetch test */
if (i == 2)
{
memcpy(saved_bookmarks[0], bookmark, bookmark_ind);
saved_bookmark_inds[0] = bookmark_ind;
}
}
/* Do a positioned update and delete */
printf("\nUpdating result set\n");
colvalue1 += 100;
rc = SQLBulkOperations(hstmt, SQL_UPDATE_BY_BOOKMARK);
CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);
/* Have to use an absolute position after SQLBulkOperations. */
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 8);
CHECK_STMT_RESULT(rc, "SQLFetchScroll failed", hstmt);
rc = SQLBulkOperations(hstmt, SQL_DELETE_BY_BOOKMARK);
CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);
/* Have to use an absolute position after SQLBulkOperations. */
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 5);
CHECK_STMT_RESULT(rc, "SQLFetchScroll failed", hstmt);
/* Print the updated row */
printCurrentRow(hstmt);
/* remember its bookmark for later fetch */
memcpy(saved_bookmarks[1], bookmark, bookmark_ind);
saved_bookmark_inds[1] = bookmark_ind;
/* Perform an insertion */
colvalue1 = 1234;
colvalue2 = 5678;
rc = SQLBulkOperations(hstmt, SQL_ADD);
CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);
/* Remember the bookmark of the inserted row */
memcpy(saved_bookmarks[2], bookmark, bookmark_ind);
saved_bookmark_inds[2] = bookmark_ind;
/**** Test bulk fetch *****/
printf("Testing bulk fetch of original, updated, and inserted rows\n");
rc = SQLBindCol(hstmt, 0, SQL_C_VARBOOKMARK, saved_bookmarks, sizeof(bookmark), saved_bookmark_inds);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLBindCol(hstmt, 1, SQL_C_LONG, colvalues1, 0, indColvalues1);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLBindCol(hstmt, 2, SQL_C_LONG, colvalues2, 0, indColvalues2);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 3, 0);
CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
/*
* FIXME: Disabled, because this doesn't currently seem to produce the
* right results.
*/
#ifdef BROKEN
rc = SQLBulkOperations(hstmt, SQL_FETCH_BY_BOOKMARK);
CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);
printf ("row no #2: %d - %d\n", colvalues1[0], colvalues2[0]);
printf ("updated row: %d - %d\n", colvalues1[1], colvalues2[1]);
printf ("inserted row: %d - %d\n", colvalues1[2], colvalues2[2]);
#endif
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 1, 0);
CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
/**** See if the updates really took effect ****/
printf("\nQuerying the table again\n");
rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM bulkoperations_test ORDER BY orig", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
print_result(hstmt);
/* Clean up */
test_disconnect();
return 0;
}
int db__driver_drop_table(dbString * name)
{
char cmd[200];
cursor *c;
SQLRETURN ret;
char msg[OD_MSG];
char *emsg = NULL;
SQLINTEGER err;
SQLCHAR ttype[50], *tname;
SQLINTEGER nrow = 0;
/* allocate cursor */
c = alloc_cursor();
if (c == NULL)
return DB_FAILED;
tname = db_get_string(name);
ret = SQLTables(c->stmt, NULL, 0, NULL, 0, tname, sizeof(tname), NULL, 0);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
report_error("SQLTables()");
return DB_FAILED;
}
/* Get number of rows */
ret = SQLRowCount(c->stmt, &nrow);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
report_error("SQLRowCount()");
return DB_FAILED;
}
if (nrow == 0) {
G_asprintf(&emsg, "Table %s doesn't exist\n", tname);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
ret = SQLFetchScroll(c->stmt, SQL_FETCH_NEXT, 0);
ret = SQLGetData(c->stmt, 4, SQL_C_CHAR, ttype, sizeof(ttype), NULL);
if (strcmp(ttype, "TABLE") == 0) {
sprintf(cmd, "DROP TABLE %s", tname);
}
else if (strcmp(ttype, "VIEW") == 0) {
sprintf(cmd, "DROP VIEW %s", tname);
}
else {
G_asprintf(&emsg, "Table %s isn't 'TABLE' or 'VIEW' but %s\n", tname,
ttype);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
SQLCloseCursor(c->stmt);
ret = SQLExecDirect(c->stmt, cmd, SQL_NTS);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
SQLGetDiagRec(SQL_HANDLE_STMT, c->stmt, 1, NULL, &err, msg,
sizeof(msg), NULL);
G_asprintf(&emsg, "SQLExecDirect():\n%s\n%s (%d)\n", cmd, msg,
(int)err);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
free_cursor(c);
return DB_OK;
}
int
main(int argc, char **argv)
{
SQLHANDLE env;
SQLHANDLE dbc;
SQLHANDLE stmt, stmt2;
char *dsn = "MonetDB";
char *user = "******";
char *pass = "******";
SQLRETURN ret;
int i;
SQLSMALLINT f1;
char f2[30];
SQLDOUBLE f3;
SQL_DATE_STRUCT f4;
SQL_TIME_STRUCT f5;
if (argc > 1)
dsn = argv[1];
if (argc > 2)
user = argv[2];
if (argc > 3)
pass = argv[3];
if (argc > 4 || *dsn == '-') {
fprintf(stderr, "Usage: %s [datasource [user [password]]]\n", argv[0]);
exit(1);
}
if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &env) != SQL_SUCCESS) {
fprintf(stderr, "Cannot allocate ODBC environment handle\n");
exit(1);
}
ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) (size_t) SQL_OV_ODBC3, 0);
check(ret, SQL_HANDLE_ENV, env, "SQLSetEnvAttr");
ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
check(ret, SQL_HANDLE_ENV, env, "SQLAllocHandle 1");
ret = SQLConnect(dbc, (SQLCHAR *) dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS);
check(ret, SQL_HANDLE_DBC, dbc, "SQLConnect");
ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (size_t) SQL_AUTOCOMMIT_OFF, 0);
check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
/* create a test table to be filled with values */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 2");
ret = SQLExecDirect(stmt, (SQLCHAR *)
"CREATE TABLE odbcsampletest (\n"
" i INT DEFAULT '0' NOT NULL,\n"
" s VARCHAR(200),\n"
" f FLOAT,\n"
" d DATE,\n"
" t TIME\n"
")", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 1");
/* figure out the type of the columns and bind parameters */
{
SQLSMALLINT coltype;
SQLSMALLINT colno;
ret = SQLColumns(stmt, NULL, 0, NULL, 0, (SQLCHAR *) "odbcsampletest", SQL_NTS, NULL, 0);
check(ret, SQL_HANDLE_STMT, stmt, "SQLColumns");
ret = SQLBindCol(stmt, 5, SQL_C_SSHORT, &coltype, 0, NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
ret = SQLBindCol(stmt, 17, SQL_C_SSHORT, &colno, 0, NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
for (;;) {
ret = SQLFetch(stmt);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch");
switch (colno) {
case 1:
ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, coltype, 0, 0, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 1");
break;
case 2:
ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, coltype, 0, 0, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 2");
break;
case 3:
ret = SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_DOUBLE, coltype, 0, 0, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 3");
break;
case 4:
ret = SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, coltype, 0, 0, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 4");
break;
case 5:
ret = SQLBindParameter(stmt, 5, SQL_PARAM_INPUT, SQL_C_TYPE_TIME, coltype, 0, 0, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 5");
break;
}
}
ret = SQLFreeStmt(stmt, SQL_UNBIND);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeStmt");
ret = SQLCloseCursor(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
}
/* prepare for filling the test table */
/* we use a single statement with parameters whose values vary */
ret = SQLPrepare(stmt, (SQLCHAR *)
"INSERT INTO odbcsampletest VALUES (?, ?, ?, ?, ?)", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 1");
/* do the actual filling of the test table */
f4.year = 2003;
f4.month = 1;
f4.day = 1;
f5.hour = 0;
f5.minute = 0;
f5.second = 0;
for (i = 0; i < 2000; i++) {
f1 = i;
/* \342\200\230 is the UTF-8 encoding of U+2018 Left Single Quotation Mark;
\342\200\231 is the UTF-8 encoding of U+2019 Right Single Quotation Mark */
snprintf(f2, sizeof(f2), "value \342\200\230%d\342\200\231", i);
f3 = i * 1.5;
f4.day++;
if ((f4.day == 29 && f4.month == 2) || (f4.day == 31 && (f4.month == 4 || f4.month == 6 || f4.month == 9 || f4.month == 11)) || f4.day == 32) {
f4.day = 1;
f4.month++;
if (f4.month == 13) {
f4.month = 1;
f4.year++;
}
}
f5.second++;
if (f5.second == 60) {
f5.second = 0;
f5.minute++;
if (f5.minute == 60) {
f5.minute = 0;
f5.hour++;
if (f5.hour == 25)
f5.hour = 0;
}
}
ret = SQLExecute(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 1");
}
ret = SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
check(ret, SQL_HANDLE_DBC, dbc, "SQLEndTran");
ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (size_t) SQL_AUTOCOMMIT_ON, 0);
check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
/* Now we are going to read back the values from the test table.
We create two statment handles, one of which will be used
to read the even table entries and the other for the odd
table entries. */
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
/* first the handle for the even entries */
/* bind the columns before preparing the statement */
ret = SQLBindCol(stmt, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
ret = SQLBindCol(stmt, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 3");
ret = SQLBindCol(stmt, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 4");
ret = SQLBindCol(stmt, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 5");
ret = SQLPrepare(stmt, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) = i", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 2");
ret = SQLExecute(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 2");
/* now the handle for the odd entries */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt2);
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
ret = SQLPrepare(stmt2, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) <> i", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLPrepare 3");
/* bind the columns after preparing the statement */
ret = SQLBindCol(stmt2, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 6");
ret = SQLBindCol(stmt2, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 7");
ret = SQLBindCol(stmt2, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 8");
ret = SQLBindCol(stmt2, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 9");
ret = SQLBindCol(stmt2, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 10");
ret = SQLExecute(stmt2);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLExecute 3");
for (;;) {
/* Alternate fetching an even and an odd entry. The
end result should be that we get all entries in the
correct order. */
ret = SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch 1");
printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
ret = SQLFetch(stmt2);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt2, "SQLFetch 2");
printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
}
ret = SQLCloseCursor(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
/* cleanup and disconnect */
ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt2);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLFreeHandle 1");
/* drop the test table */
ret = SQLExecDirect(stmt, (SQLCHAR *) "DROP TABLE odbcsampletest", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 3");
ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle 2");
ret = SQLDisconnect(dbc);
check(ret, SQL_HANDLE_DBC, dbc, "SQLDisconnect");
ret = SQLFreeHandle(SQL_HANDLE_DBC, dbc);
check(ret, SQL_HANDLE_DBC, dbc, "SQLFreeHandle 3");
ret = SQLFreeHandle(SQL_HANDLE_ENV, env);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle 4");
return 0;
}
SQLRETURN DataBase::fetchScroll(SQLSMALLINT FetchOrientation, SQLLEN FetchOffset)
{
return SQLFetchScroll(_hStmt, FetchOrientation, FetchOffset);
}
/* fetch row n from the open cursor.
* Flag can be used to fetch first, last, next, or previous. */
static bool
fetchInternal(int cid, long n, int flag)
{
long nextrow, lastrow;
struct OCURS *o = findCursor(cid);
everything_null = true;
/* don't do the fetch if we're looking for row 0 absolute,
* that just nulls out the return values */
if(flag == SQL_FD_FETCH_ABSOLUTE && !n) {
o->rownum = 0;
fetchZero:
return false;
}
lastrow = nextrow = o->rownum;
if(flag == SQL_FD_FETCH_ABSOLUTE)
nextrow = n;
if(flag == SQL_FD_FETCH_FIRST)
nextrow = 1;
if(isnotnull(lastrow)) { /* we haven't lost track yet */
if(flag == SQL_FD_FETCH_NEXT)
++nextrow;
if(flag == SQL_FD_FETCH_PREV && nextrow)
--nextrow;
}
if(flag == SQL_FD_FETCH_LAST) {
nextrow = nullint; /* we just lost track */
}
if(!nextrow)
goto fetchZero;
if(o->flag != CURSOR_OPENED)
errorPrint("2cannot fetch from cursor %d, not yet opened", cid);
/* The next line of code is very subtle.
I use to declare all cursors as scroll cursors.
It's a little inefficient, but who cares.
Then I discovered you can't fetch blobs from scroll cursors.
You can however fetch them from regular cursors,
even with an order by clause.
So cursors became non-scrolling by default.
If the programmer chooses to fetch by absolute number,
but he is really going in sequence, I turn them into
fetch-next statements, so that the cursor need not be a scroll cursor. */
if(flag == SQL_FD_FETCH_ABSOLUTE) {
if(isnull(nextrow))
errorPrint("2sql fetches absolute row using null index");
if(isnotnull(lastrow) && nextrow == lastrow + 1)
flag = SQL_FD_FETCH_NEXT;
}
stmt_text = "fetch";
debugStatement();
hstmt = o->hstmt;
rc = SQLFetchScroll(hstmt, (ushort) flag, nextrow);
if(rc == SQL_NO_DATA)
return false;
if(errorTrap(0))
return false;
o->rownum = nextrow;
everything_null = false;
return true;
} /* fetchInternal */
static int odbc_dispatch11(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_INT)
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) SQLAllocHandleStd(((SQLSMALLINT ) arg2),((SQLHANDLE ) arg3),((SQLHANDLE * ) arg4));
break;
case 1:
retval = (unsigned long) SQLParamOptions(((SQLHSTMT ) arg2),((SQLUINTEGER ) arg3),((SQLUINTEGER * ) arg4));
break;
case 2:
retval = (unsigned long) SQLTransact(((SQLHENV ) arg2),((SQLHDBC ) arg3),((SQLUSMALLINT ) arg4));
break;
case 3:
retval = (unsigned long) SQLSetStmtOption(((SQLHSTMT ) arg2),((SQLUSMALLINT ) arg3),((SQLUINTEGER ) arg4));
break;
case 4:
retval = (unsigned long) SQLSetConnectOption(((SQLHDBC ) arg2),((SQLUSMALLINT ) arg3),((SQLUINTEGER ) arg4));
break;
case 5:
retval = (unsigned long) SQLPutData(((SQLHSTMT ) arg2),((SQLPOINTER ) arg3),((SQLINTEGER ) arg4));
break;
case 6:
retval = (unsigned long) SQLGetStmtOption(((SQLHSTMT ) arg2),((SQLUSMALLINT ) arg3),((SQLPOINTER ) arg4));
break;
case 7:
retval = (unsigned long) SQLGetFunctions(((SQLHDBC ) arg2),((SQLUSMALLINT ) arg3),((SQLUSMALLINT * ) arg4));
break;
case 8:
retval = (unsigned long) SQLGetConnectOption(((SQLHDBC ) arg2),((SQLUSMALLINT ) arg3),((SQLPOINTER ) arg4));
break;
case 9:
retval = (unsigned long) SQLFetchScroll(((SQLHSTMT ) arg2),((SQLSMALLINT ) arg3),((SQLINTEGER ) arg4));
break;
case 10:
retval = (unsigned long) SQLEndTran(((SQLSMALLINT ) arg2),((SQLHANDLE ) arg3),((SQLSMALLINT ) arg4));
break;
case 11:
retval = (unsigned long) SQLAllocHandle(((SQLSMALLINT ) arg2),((SQLHANDLE ) arg3),((SQLHANDLE * ) arg4));
break;
default:
PI_FAIL;
}
PI_makedouble(&rval,&rtype,(double) retval);
if (PI_unify(arg5,type5,rval,rtype))
PI_SUCCEED;
PI_FAIL;
}
