Database Cursor Functions

Database cursor functions are useful when you want to process few records at a time in a large result set of queries. They help you to get database records, insert database records, and delete database records.

The following database functions are available in the catalog functions under RDBMS:

  • createQuery()
  • getNextPage()
  • getPreviousPage()
  • getNextPageFromOffset()
  • getPreviousPageFromOffset()
  • closeQuery()

Example of Database Cursor Functions

debugOut("Database Cursor Demo Starts");
Database.setCurrentConnection("/SharedResources/HR_DB_Conn.sharedjdbc");
String cursorName=Database.createQuery("/SharedResources/HR_DB_Conn.sharedjdbc", "EmpCursor", "/Concepts/HR/EMPLOYEES", "select * from employees", 10, null);
debugOut("Opened Cursor: " + cursorName);
try{
Concept[] empCepts=Database.getNextPage(cursorName, 10);
debugOut("Database.getNextPage() fetched " + empCepts@length + " rows");
 while(empCepts !=null && empCepts@length > 0){
empCepts=Database.getNextPage(cursorName, 10);
debugOut(" Database.getNextPage() fetched " + empCepts@length + " rows");
for(int i; i < empCepts@length ; i=i+1){
      debugOut(" "+empCepts[i]);
      }
   }
debugOut("@ end");
} finally {
   if(cursorName !=null){
   Database.closeQuery(cursorName);
   }
}
  • createQuery()
    You can open a database cursor for an SQL query by using the Database.createQuery() function. Once the cursor is open, you can retrieve large result sets from the database in pages.
  • getNextPage() and getPreviousPage()
    You can use the Database.getNextPage() and Database.getPreviousPage() functions to access next or previous pages respectively from the database cursor.
  • getNextPageFromOffset() and getPreviousPageFromOffset()
    While paging a result set, you can skip a number of rows or records. This set of records to be skipped is called an Offset. Use the Database.getNextPageFromOffset() and Database.getPreviousPageFromOffset() functions to get the next or previous pages respectively of the database cursor, starting from an offset.
  • closeQuery()
    You must close an open database cursor for an SQL query by using the Database.closeQuery() function.
Related reference