|
|||||
|
|
|||||
| ODBC extended fetch and cursor scrolling | |||||
| Introduction: ODBC data manipulation |
Sample
The following code uses the SQLFetchScroll and SQLExtendedFetch functions to perform extended fetch and cursor scrolling on an ODBC data source.
local SQL_Handle_type EnvironmentHandle
local SQL_Handle_type ConnectionHandle
local SQL_Handle_type StatementHandle
local SQL_Array_type EmpName
local SQL_Array_type EmpID
local SQL_Array_type Salary
local SQL_Array_type EmpNameInd
local SQL_Array_type EmpIDInd
local SQL_Array_type SalaryInd
local SQL_Vector_type RowStatus
local counter RowCount
local counter CounterSizeInBytes initial {4}
local counter RetCode
SQLSetArraySize( EmpName, 50, 1 )
SQLSetArraySize( EmpId, 50, 1 )
SQLSetArraySize( Salary, 50, 1 )
SQLSetArraySize( EmpNameInd, CounterSizeInBytes, 1 )
SQLSetArraySize( EmpIdInd, CounterSizeInBytes, 1 )
SQLSetArraySize( SalaryInd, CounterSizeInBytes, 1 )
SQLSetVectorSize( RowStatus, 100 )
set RetCode to SQLAllocEnv (EnvironmentHandle)
output "Allocating environment handle - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLAllocHandle
( SQL_HANDLE_DBC, EnvironmentHandle, ConnectionHandle )
output "Allocating connection handle - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLConnect( ConnectionHandle, "omodbc", 20, "", 0, "", 0 )
output "Connecting to database - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLAllocStmt (ConnectionHandle, StatementHandle)
output "Allocating statement handle - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLExecDirect
( StatementHandle,
"Select EmpName, EmpID, Salary from Employee order by 2",
SQL_NTS
)
output "Combined preparation and execution of a statement - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLBindCol
( StatementHandle, 1, SQL_C_CHAR, EmpName, 50, EmpNameInd )
set RetCode to SQLBindCol
( StatementHandle, 2, SQL_C_CHAR, EmpId, 50, EmpIdInd )
when RetCode = SQL_SUCCESS
set RetCode to SQLBindCol
( StatementHandle, 3, SQL_C_CHAR, Salary, 50, SalaryInd )
when RetCode = SQL_SUCCESS
output "Binding variables to queried fields - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLFetchScroll( StatementHandle, SQL_FETCH_NEXT, 0 )
output "Fetching results using SQLFetchScroll - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
output "- " || SQLGetArrayAsString( EmpName, 0 )
output ", " || SQLGetArrayAsString( EmpId, 0 )
output ", " || SQLGetArrayAsString( Salary, 0 ) || "%n"
done
set RetCode to SQLExtendedFetch( StatementHandle, SQL_FETCH_NEXT, 0, RowCount, RowStatus )
output "Fetching results using SQLExtendedFetch - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
output "- " || SQLGetArrayAsString( EmpName, 0 )
output ", " || SQLGetArrayAsString( EmpId, 0 )
output ", " || SQLGetArrayAsString( Salary, 0 ) || "%n"
done
set RetCode to SQLFreeHandle(SQL_HANDLE_STMT, StatementHandle)
output "Freeing statement handle resources - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLDisconnect( ConnectionHandle )
output "Disconnecting from database - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLFreeHandle(SQL_HANDLE_DBC, ConnectionHandle)
output "Freeing connection handle resources - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
set RetCode to SQLFreeHandle(SQL_HANDLE_ENV, EnvironmentHandle)
output "Freeing environment handle resources - "
do when RetCode != SQL_SUCCESS
output "failed%n"
halt with 1
else
output "passed%n"
done
| ---- |
