IBM Books

Application Development Guide


Advanced Scrolling Techniques

The following topics on advanced scrolling techniques are discussed in this section:

Scrolling Through Data that has Already Been Retrieved

When an application retrieves data from the database, the FETCH statement allows it to scroll forward through the data, however, the database manager has no embedded SQL statement that allows it scroll backwards through the data, (equivalent to a backward FETCH). DB2 CLI and Java, however, do support a backward FETCH through read-only scrollable cursors. Refer to the CLI Guide and Reference and see Creating Java Applications and Applets for more information on scrollable cursors. For embedded SQL applications, you can use the following techniques to scroll through data that has been retrieved:

  1. Keep a copy of the data that has been fetched and scroll through it by some programming technique.

  2. Use SQL to retrieve the data again, typically by a second SELECT statement.

These options are discussed in more detail in:

Keeping a Copy of the Data

An application can save fetched data in virtual storage. If the data does not fit in virtual storage, the application can write the data to a temporary file. One effect of this approach is that a user, scrolling backward, always sees exactly the same data that was fetched, even if the data in the database was changed in the interim by a transaction.

Using an isolation level of repeatable read, the data you retrieve from a transaction can be retrieved again by closing and opening a cursor. Other applications are prevented from updating the data in your result set. Isolation levels and locking can affect how users update data.

Retrieving the Data a Second Time

This technique depends on the order in which you want to see the data again:

Retrieving from the Beginning

To retrieve the data again from the beginning, merely close the active cursor and reopen it. This action positions the cursor at the beginning of the result table. But, unless the application holds locks on the table, others may have changed it, so what had been the first row of the result table may no longer be.

Retrieving from the Middle

To retrieve data a second time from somewhere in the middle of the result table, execute a second SELECT statement and declare a second cursor on the statement. For example, suppose the first SELECT statement was:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO

Now, suppose that you want to return to the rows that start with DEPTNO = 'M95' and fetch sequentially from that point. Code the following:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       AND DEPTNO >= 'M95' 
       ORDER BY DEPTNO

This statement positions the cursor where you want it.

Order of Rows in the Second Result Table

The rows of the second result table may not be displayed in the same order as in the first. The database manager does not consider the order of rows as significant unless the SELECT statement uses ORDER BY. Thus, if there are several rows with the same DEPTNO value, the second SELECT statement may retrieve them in a different order from the first. The only guarantee is that they will all be in order by department number, as demanded by the clause ORDER BY DEPTNO.

The difference in ordering could occur even if you were to execute the same SQL statement, with the same host variables, a second time. For example, the statistics in the catalog could be updated between executions, or indexes could be created or dropped. You could then execute the SELECT statement again.

The ordering is more likely to change if the second SELECT has a predicate that the first did not have; the database manager could choose to use an index on the new predicate. For example, it could choose an index on LOCATION for the first statement in our example and an index on DEPTNO for the second. Because rows are fetched in order by the index key, the second order need not be the same as the first.

Again, executing two similar SELECT statements can produce a different ordering of rows, even if no statistics change and no indexes are created or dropped. In the example, if there are many different values of LOCATION, the database manager could choose an index on LOCATION for both statements. Yet changing the value of DEPTNO in the second statement to the following, could cause the database manager to choose an index on DEPTNO:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       AND DEPTNO >= 'Z98' 
       ORDER BY DEPTNO

Because of the subtle relationships between the form of an SQL statement and the values in this statement, never assume that two different SQL statements will return rows in the same order unless the order is uniquely determined by an ORDER BY clause.

Retrieving in Reverse Order

Ascending ordering of rows is the default. If there is only one row for each value of DEPTNO, then the following statement specifies a unique ascending ordering of rows:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO

To retrieve the same rows in reverse order, specify that the order is descending, as in the following statement:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO DESC

A cursor on the second statement retrieves rows in exactly the opposite order from a cursor on the first statement. Order of retrieval is guaranteed only if the first statement specifies a unique ordering sequence.

For retrieving rows in reverse order, it can be useful to have two indexes on the DEPTNO column, one in ascending order and the other in descending order.

Establishing a Position at the End of a Table

The database manager does not guarantee an order to data stored in a table; therefore, the end of a table is not defined. However, order is defined on the result of an SQL statement:

     SELECT * FROM DEPARTMENT 
       ORDER BY DEPTNO DESC

For this example, the following statement positions the cursor at the row with the highest DEPTNO value:

     SELECT * FROM DEPARTMENT 
       WHERE DEPTNO = 
       (SELECT MAX(DEPTNO) FROM DEPARTMENT) 

Note, however, that if several rows have the same value, the cursor is positioned on the first of them.

Updating Previously Retrieved Data

To scroll backward and update data that was retrieved previously, you can use a combination of the techniques discussed in Scrolling Through Data that has Already Been Retrieved and Updating Retrieved Data. You can do one of two things:

  1. If you have a second cursor on the data to be updated and if the SELECT statement uses none of the restricted elements, you can use a cursor-controlled UPDATE statement. Name the second cursor in the WHERE CURRENT OF clause.

  2. In other cases, use UPDATE with a WHERE clause that names all the values in the row or specifies the primary key of the table. You can execute one statement many times with different values of the variables.

Example: UPDAT Program

The UPDAT program uses dynamic SQL to access the STAFF table in the SAMPLE database and changes all managers to clerks. Then the program reverses the changes by rolling back the unit of work. The sample is available in the following programming languages:

C
updat.sqc

Java
Updat.sqlj

COBOL
updat.sqb

FORTRAN
updat.sqf

REXX
updat.cmd

How the UPDAT Program Works

  1. Define an SQLCA structure. The INCLUDE SQLCA statement defines and declares an SQLCA structure, and defines SQLCODE as an element within the structure. The SQLCODE field of the SQLCA structure is updated with error information by the database manager after execution of SQL statements and database manager API calls.

    Java applications access SQLCODE and SQLSTATE through the methods defined for the SQLException object, and therefore do not need an equivalent "include SQLCA" statement.

    REXX applications have one occurrence of an SQLCA structure, named SQLCA, predefined for application use. It can be referenced without application definition.

  2. Declare host variables. The BEGIN DECLARE SECTION and END DECLARE SECTION statements delimit the host variable declarations. Host variables are used to pass data to and from the database manager. They are prefixed with a colon (:) when referenced in an SQL statement.

    Java and REXX applications do not need to declare host variables, except (for REXX) in the case of LOB file reference variables and locators. Host variable data types and sizes are determined at run time when the variables are referenced.

  3. Connect to database. The program connects to the sample database, and requests shared access to it. (It is assumed that a START DATABASE MANAGER API call or db2start command has been issued.) Other programs that connect to the same database using shared access are also granted access.

  4. Execute the UPDATE SQL statement. The SQL statement is executed statically with the use of a host variable. The job column of the staff tables is set to the value of the host variable, where the job column has a value of Mgr.

  5. Execute the DELETE SQL statement The SQL statement is executed statically with the use of a host variable. All rows that have a job column value equal to that of the specified host variable, (jobUpdate/job-update/job_update) are deleted.

  6. Execute the INSERT SQL statement A row is inserted into the STAFF table. This insertion implements the use of a host variable which was set prior to the execution of this SQL statement.

  7. End the transaction. End the unit of work with a ROLLBACK statement. The result of the SQL statement executed previously can be either made permanent using the COMMIT statement, or undone using the ROLLBACK statement. All SQL statements within the unit of work are affected.

The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:

C
check_error is redefined as CHECKERR and is located in the util.c file.

Java
Any SQL error is thrown as an SQLException and handled in the catch block of the application.

COBOL
CHECKERR is an external program named checkerr.cbl.

FORTRAN
CHECKERR is a subroutine located in the util.f file.

REXX
CHECKERR is a procedure located at bottom of the current program.

See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.

C Example: UPDAT.SQC

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlenv.h>
#include "util.h"
 
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
 
EXEC SQL INCLUDE SQLCA;  (1)
 
#define  CHECKERR(CE_STR)   if (check_error (CE_STR, &sqlca) != 0) return 1;
 
int main(int argc, char *argv[]) {
 
   EXEC SQL BEGIN DECLARE SECTION;  (2)
      char statement[256];
      char userid[9];
      char passwd[19];
      char jobUpdate[6];
   EXEC SQL END DECLARE SECTION;
 
#ifdef DB268K
   /* Before making any API calls for 68K environment,
      need to initial the Library Manager */
   InitLibraryManager(0,kCurrentZone,kNormalMemory);
   atexit(CleanupLibraryManager);
#endif
 
   printf( "\nSample C program:  UPDAT \n");
 
   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
	  CHECKERR ("CONNECT TO SAMPLE");
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd; (3)
      CHECKERR ("CONNECT TO SAMPLE");
   }
   else {
      printf ("\nUSAGE: updat [userid passwd]\n\n");
      return 1;
   } /* endif */
 
   strcpy (jobUpdate, "Clerk");
   EXEC SQL UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'; (4)
   CHECKERR ("UPDATE STAFF");
   printf ("All 'Mgr' have been demoted to 'Clerk'!\n" );
 
   strcpy (jobUpdate, "Sales");
   EXEC SQL DELETE FROM staff WHERE job = :jobUpdate; (5)
   CHECKERR ("DELETE FROM STAFF");
   printf ("All 'Sales' people have been deleted!\n");
 
   EXEC SQL INSERT INTO staff 
      VALUES (999, 'Testing', 99, :jobUpdate, 0, 0, 0);  (6)
   CHECKERR ("INSERT INTO STAFF");
   printf ("New data has been inserted\n");
 
   EXEC SQL ROLLBACK; (7)
   CHECKERR ("ROLLBACK");
   printf( "On second thought -- changes rolled back.\n" );
 
   EXEC SQL CONNECT RESET;
   CHECKERR ("CONNECT RESET");
   return 0;
}
/* end of program : UPDAT.SQC */

Java Example: Updat.sqlj

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
 
class Updat 
{   static
  {   try
    {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
    }
    catch (Exception e)
    {   System.out.println ("\n  Error loading DB2 Driver...\n");
      System.out.println (e);
      System.exit(1);
    }
}
 
 
public static void main(String argv[])
{   try 
    {   System.out.println ("\n  Java Updat Sample");
 
      String url = "jdbc:db2:sample";       // URL is jdbc:db2:dbname
      Connection con = null;          
 
      // Set the connection                  (3)
      if (argv.length == 0) 
      {  // connect with default id/password
        con = DriverManager.getConnection(url);  
      }
      else if (argv.length == 2)
      {   String userid = argv[0];
        String passwd = argv[1];
 
        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd); 
      }
      else 
      { throw new Exception("\nUsage: java Updat [username password]\n");
      } 
 
      // Set the default context
      DefaultContext ctx = new DefaultContext(con);            
      DefaultContext.setDefaultContext(ctx);
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // UPDATE/DELETE/INSERT
      try
      {
        String jobUpdate = null;
        jobUpdate="Clerk";
        #sql {UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'};  (4)
        System.out.println("\nAll 'Mgr' have been demoted to 'Clerk'!");
 
        jobUpdate="Sales";
        #sql {DELETE FROM staff WHERE job = :jobUpdate};
        System.out.println("All 'Sales' people have been deleted!"); (5)
 
        #sql {INSERT INTO staff VALUES (999,'Testing',99,:jobUpdate,0,0,0)};(6)
        System.out.println("New data has been inserted");
      }
      catch( Exception e )
      {   throw e; 
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        #sql { ROLLBACK };  (7)
        System.out.println("Rollback done."); 
      }
    }
    catch (Exception e)
    {   System.out.println (e);
    }
  }
}

COBOL Example: UPDAT.SQB

       Identification Division.
       Program-ID. "updat".
 
       Data Division.
       Working-Storage Section.
 
           copy "sql.cbl".
           copy "sqlenv.cbl".
           copy "sqlca.cbl".                                            (1)
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.                     (2)
       01 statement         pic x(80).
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 job-update        pic x(5).
           EXEC SQL END DECLARE SECTION END-EXEC.
 
      * Local variables
       77 errloc          pic x(80).
       77 error-rc        pic s9(9) comp-5.
       77 state-rc        pic s9(9) comp-5.
 
      * Variables for the GET ERROR MESSAGE API
      * Use application specific bound instead of BUFFER-SZ
       77 buffer-size     pic s9(4) comp-5 value 1024.
       77 line-width      pic s9(4) comp-5 value 80.
       77 error-buffer    pic x(1024).
       77 state-buffer    pic x(1024).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program:  UPDAT".
 
           display "Enter your user id (default none): " 
                with no advancing.
           accept userid.
 
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
 
      * Passwords in a CONNECT statement must be entered in a VARCHAR format
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd        (3)
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "Clerk" to job-update.
           EXEC SQL UPDATE staff SET job=:job-update                    (4)
                    WHERE job='Mgr' END-EXEC.
           move "UPDATE STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "All 'Mgr' have been demoted to 'Clerk'!".
 
           move "Sales" to job-update.
           EXEC SQL DELETE FROM staff WHERE job=:job-update END-EXEC.   (5)
           move "DELETE FROM STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "All 'Sales' people have been deleted!".
 
           EXEC SQL INSERT INTO staff VALUES (999, 'Testing', 99,       (6)
                    :job-update, 0, 0, 0) END-EXEC.
           move "INSERT INTO STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "New data has been inserted".
 
           EXEC SQL ROLLBACK END-EXEC.                                  (7)
           move "ROLLBACK" to errloc.
           call "checkerr" using SQLCA errloc.
 
           DISPLAY "On second thought -- changes rolled back."
 
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Prog.
           stop run.

FORTRAN Example: UPDAT.SQF

      program updat
      implicit none
 
      include 'sql.f'
      include 'sqlenv.f'
      EXEC SQL INCLUDE SQLCA (1)
 
      EXEC SQL BEGIN DECLARE SECTION (2)
        character*80   statement
        character*8    userid
        character*18   passwd
        character*5    job_update
      EXEC SQL END DECLARE SECTION
 
      character*80     errloc
 
      print *, 'Sample FORTRAN program: UPDAT'
 
      print *, 'Enter your user id (default none):'
      read 100, userid
100   format (a8)      
 
      if( userid(1:1) .eq. ' ' ) then
        EXEC SQL CONNECT TO sample 
      else
        print *, 'Enter your password :'
        read 100, passwd
 
        EXEC SQL CONNECT TO sample USER :userid USING :passwd
      end if (3)
      errloc = 'CONNECT'
      call checkerr (sqlca, errloc, *999)
 
      job_update='Clerk'
      EXEC SQL UPDATE staff SET job = :job_update
     +         WHERE job = 'Mgr' (4)
      errloc = 'UPDATE STAFF'
      call checkerr (sqlca, errloc, *999)
      print *, 'All ''Mgr'' have been demoted to ''Clerk!'''
 
      job_update='Sales'
      EXEC SQL DELETE FROM STAFF WHERE job = :job_update (5)
      errloc = 'DELETE FROM STAFF'
      call checkerr (sqlca, errloc, *999)
      print *, 'All ''Sales'' people have been deleted!'
 
      EXEC SQL INSERT INTO staff VALUES (999, 'Testing', 99, (6)
     +         :job_update, 0, 0, 0)
      errloc = 'INSERT INTO STAFF'
      call checkerr (sqlca, errloc, *999)
      print *, 'New data has been inserted'
 
      EXEC SQL ROLLBACK (7)
      errloc = 'ROLLBACK'
      call checkerr (sqlca, errloc, *999)
      print *, 'On second thought -- changes rolled back.'
 
      EXEC SQL CONNECT RESET
      errloc = 'CONNECT RESET'
      call checkerr (sqlca, errloc, *999)
 
  999 stop
      end

REXX Example: UPDAT.CMD

Note:REXX programs cannot contain static SQL. This program is written with dynamic SQL.
/* this variable (SYSTEM) must be user defined */
SYSTEM = AIX
if SYSTEM = OS2 then do
  if RxFuncQuery('SQLDBS') <> 0 then
    rcy = RxFuncAdd( 'SQLDBS',  'DB2AR', 'SQLDBS'  )
 
  if RxFuncQuery('SQLEXEC') <> 0 then
    rcy = RxFuncAdd( 'SQLEXEC', 'DB2AR', 'SQLEXEC' )
end
 
if SYSTEM = AIX then
  rcy = SysAddFuncPkg("db2rexx")
 
/* pull in command line arguments */
parse arg userid passwd .
 
/* check to see if the proper number of arguments have been passed in */
   PARSE ARG dbname userid password .
   if ((dbname = "" ) | ,
       (userid <> "" & password = "") ,
      ) then do
      SAY "USAGE: updat.cmd <dbname> [<userid> <password>]"
 
       exit -1
   end
 
   /* connect to database */
   SAY
   SAY 'Connect to' dbname
   IF password= "" THEN
      CALL SQLEXEC 'CONNECT TO' dbname
   ELSE
      CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password (3)
 
   CALL CHECKERR 'Connect to '
   SAY "Connected"
 
say 'Sample REXX program: UPDAT.CMD'
 
jobupdate = "'Clerk'"
st = "UPDATE staff SET job =" jobupdate "WHERE job = 'Mgr'"
call SQLEXEC 'EXECUTE IMMEDIATE :st' (4)
call CHECKERR 'UPDATE'
say "All 'Mgr' have been demoted to 'Clerk'!"
 
jobupdate = "'Sales'"
st = "DELETE FROM staff WHERE job =" jobupdate
call SQLEXEC 'EXECUTE IMMEDIATE :st' (5)
call CHECKERR 'DELETE'
say "All 'Sales' people have been deleted!"
 
st = "INSERT INTO staff VALUES (999, 'Testing', 99," jobupdate ", 0, 0, 0)"
call SQLEXEC 'EXECUTE IMMEDIATE :st' (6)
call CHECKERR 'INSERT'
say 'New data has been inserted'
 
call SQLEXEC 'ROLLBACK' (7)
call CHECKERR 'ROLLBACK'
say 'On second thought...changes rolled back.'
 
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
 
CHECKERR:
  arg errloc
 
  if  ( SQLCA.SQLCODE = 0 ) then
    return 0
  else do
    say '--- error report ---'
    say 'ERROR occurred :' errloc
    say 'SQLCODE :' SQLCA.SQLCODE
 
    /******************************\
    * GET ERROR MESSAGE API called *
    \******************************/
    call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
    say errmsg
    say '--- end error report ---'
 
    if (SQLCA.SQLCODE < 0 ) then
      exit
    else do
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
      return 0
    end
  end
return 0


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]