"Bulk Fetch" using the SQL API "SQLFetch" fails for string data.

Hi All,

In my VB.NET application, I am trying to do "bulk fetching" into an array from the ORACLE database with a single call to ODBC API "SQLFetch". But I am not able to fetch "String" data.

All my table columns are of 'VARCHAR2' datatype. My buffer is an array of arrays --> strDataColArray()() as String.

I follow the following steps:
- declare the rowset size with the SQL_ATTR_ROW_ARRAY_SIZE statement
attribute,
- set the SQL_ATTR_ROW_STATUS_PTR statement attribute to point to the
row status array,
- set the SQL_ATTR_ROW_BIND_TYPE statement attribute to use
column-wise binding,
- Bind all the columns,

• Execute the SQLFetch statement.

Here's the code snippet of what I am trying to do: Let's say, I have 3 columns from which I want to fetch data and there are 100 records in each column.

Dim RowStatusArray(99) as Integer
Dim indicatorArray(3)() As Integer
Dim strDataColArray(3)() As String 'Buffer - arrays to fetch the data into
For I = 0 to 2

   ReDim indicatorArray(I)(99)
   ReDim strDataColArray(I)(99)
Next I

For I = 0 To 2

   For J = 0 To 99

      strDataColArray(I)(J) = New String(Chr(0), 256)    Next J
Next I

'Set the SQL statement attributes
iResult = SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_ARRAY_SIZE, 100, 0) iResult = SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray(0), 0)
iResult = SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0) 'Bind all the columns
For I = 0 To 2

   iResult = SQLBindCol(m_hStmt, CShort(iCol + 1), SQL_C_CHAR, strDataColArray(I)(0), CInt(100 * 255), indicatorArray(I)(0)) Next I

'Fetch all the records
iResult = SQLFetch(m_hStmt)

"Bulk Fetching" works fine if I have numeric data in the columns and bind the Integer/Double arrays to columns, but it doesn't work with String data.
In order to get "String" data, I fetch each row one at a time and it takes a lot of processing time since my database is huge.

Any kind of help (with code examples in VB only, if possible) would be appreciated.

Thanks,
Bijal. Received on Sun Oct 13 2002 - 17:13:08 CDT