Bulk fetching

The Java IP supports bulk fetching of rows using a Select query. The Java IP populates multiple rows in the result buffer using the ResultBuffer class. The OpenAccess SDK SQL engine then parses the result buffer and populates rows in the result set.

The ResultBuffer class provides various methods, such as putNull, to put the data in the result buffer.

Bulk fetching is performed when IP EXECUTE is called with the DAM_SELECT statement type. If the IP is unable to fetch the entire data during the first iteration, in further iterations, IP EXECUTE is called with the DAM_FETCH statement type to fetch the remaining data.

Note: Bulk Fetching is supported only in Java.

To perform bulk fetching:

1. Allocate the result buffer using dam_allocResultbuffer.

   ResultBuffer resultBuffer;
   resultBuffer = jdam.dam_allocResultbuffer()
   

2. Enter the column values into the result buffer.

   a. Obtain the column handles for the columns specified in the query using dam_getFirstCol(hstmt, DAM_COL_IN_USE) and dam_getNextCol.

   b. Add the column values, for which column handles exist, to the buffer using putXXX() or each row of a table.

   while ( hCol != 0)
   {
   //Add Column details and values to buffer.
   hCol = dam_getNextCol(stmt); 
   }
   

   c. After all the rows or the fetched block size of rows are processed, set the number of columns using ResultBuffer.setNoOfResColumns. And set the number of rows for the values filled in the buffer using ResultBuffer.setNoRowsInBuffer.

   d. Add the buffer rows to the result table using dam_addResultBufferToTable and return a return code for any of the following conditions:

   • If BufferOverflowException occurs, return code must be DAM_SUCCESS_WITH_RESULT_PENDING. This return code notifies OpenAccess that the IP still needs to add some more rows from the result buffer to the table.

   • If all rows are added to the result table and BufferOverflowException does not occur, return code must be DAM_SUCCESS.

   e. Clear the buffer using clear.

   f. Return the return code.

   g. Repeat steps 2 until all the data is fetched from the result buffer to the result table.

   catch(BufferOverflowException e)
   {
   resultBuffer.setNoRowsInBuffer(iNumResRows);
   resultBuffer.setNoOfResColumns(iNoOfResColumns);
   jdam.dam_addResultBufferToTable(dam_hstmt, resultBuffer);
   iRetCode = DAM_SUCCESS_WITH_RESULT_PENDING;
   resultBuffer.clear();
   }
   return iRetCode;
   

3. Release the allocated buffer using dam_freeResultBuffer.

Notes

• When IP EXECUTE is called with the DAM_CLOSE statement type, free the allocated buffer using dam_freeResultBuffer.
• Use a try block for the code from steps from 2(b) to 2 (e).
• See the bulk fetch example at: <install-Dir>\oaserver810\ip\oajava\memory\damip.java.

Example : Bulk fetching of rows from PICTURE_TABLE

Suppose you have PICTURE_TABLE and you want to perform bulk fetch on its rows. To perform bulk fetch, use the ResultBuffer class to fill PICTURE_TABLE rows in the result buffer. OpenAccess then parses the result buffer and fills rows in the result table.

PICTURE_TABLE Columns

Consider the following bulk fetch query example: SELECT NAME, PICTURE, COMMENTS, WCOMMENTS FROM PICTURE_TABLE.

To bulk fetch rows from PICTURE_TABLE:

1. Allocate the buffer using dam_allocResultBuffer.

   dam_allocResultBuffer(stmt, Byte Buffer, String Buffer, LOB buffer)

2. Enter the column values into the result buffer.

   a. Obtain the column handles for the columns specified in the query using dam_getFirstCol(dam_hstmt, DAM_COL_IN_USE) and dam_getNextCol.

   b. Add the column values, for which column handles exist, to the buffer using putXXX() for each row of PICTURE_TABLE.

   c. After all the rows or fetch block size are processed, set the number of columns using ResultBuffer setNoOfResColumns, and set the number of rows that are filled in the buffer using ResultBuffer setNoRowsInBuffer.

   d. Add the buffer rows to the result table using dam_addResultBufferToTable and return a return code for any of the following conditions:

   • If BufferOverflowException occurs, return code must be DAM_SUCCESS_WITH_RESULT_PENDING. This return code notifies OpenAccess that the IP still needs to add some more rows from the result buffer to the table.
   • If all rows are added to the result table and BufferOverflowException does not occur, return code must be DAM_SUCCESS.

   e. Clear the buffer using clear.

   f. Return the return code.

   g. Repeat step 2 untill all data is fetched from the result buffer to the result table.

   The following code snippet illustrates how to implement the steps mentioned above for bulk fetching.

   hcol = hcolName = hcolPicture = hcolComments = hcolWComments=0;
   
   /* initialize row count */
   m_iNumResRows = 0;
   
   /* get the column handles */
   hcol = jdam.dam_getFirstCol(pStmtDA.dam_hstmt, ip.DAM_COL_IN_USE);
   while (hcol != 0)
   {   StringBuffer    sColName;
     sColName = new StringBuffer(ip.DAM_MAX_ID_LEN + 1);
   
     jdam.dam_describeCol(hcol, null, sColName, null, null);
     pStmtDA.m_iNoOfResColumns++;
   
      /* get next column handle */
      hcol =  jdam.dam_getNextCol(pStmtDA.dam_hstmt);
   } 
   …
   
   /* entering column values in buffer using putXXX() */ 
   pStmtDA.m_resultBuffer.putString(pName);
   pStmtDA.m_resultBuffer.putLongBinary(picturexlBuffer);
   pStmtDA.m_resultBuffer.putLongString(pData.toString());
   pStmtDA.m_resultBuffer.putLongString(pData.toString());
   …
   
   /*Handling  BufferOverflowException*/
   catch(BufferOverflowException e)
   {
     pStmtDA.m_resultBuffer.setNoRowsInBuffer(m_iNumResRows);
   
     jdam.dam_addResultBufferToTable(pStmtDA.dam_hstmt,pStmtDA.m_resultBuffer);
     iRetCode = DAM_SUCCESS_WITH_RESULT_PENDING;
   
     resultBuffer.clear() 
   }
   

3. Release the allocated buffer.

   jdam.dam_freeResultBuffer(dam_hstmt,resultBuffer);