It is possible to update and delete the row referenced by a cursor. For a row to be updatable, the query corresponding to the cursor must not be read-only. For a description of what makes a query updatable or deletable, refer to the SQL Reference.
To update with a cursor, use the WHERE CURRENT OF clause in an UPDATE statement. Use the FOR UPDATE clause to tell the system that you want to update some columns of the result table. You can specify a column in the FOR UPDATE without it being in the fullselect; therefore, you can update columns that are not explicitly retrieved by the cursor. If the FOR UPDATE clause is specified without column names, all columns of the table or view identified in the first FROM clause of the outer fullselect are considered to be updatable. Do not name more columns than you need in the FOR UPDATE clause. In some cases, naming extra columns in the FOR UPDATE clause can cause DB2 to be less efficient in accessing the data.
Deletion with a cursor is done using the WHERE CURRENT OF clause in a DELETE statement. In general, the FOR UPDATE clause is not required for deletion of the current row of a cursor. The only exception occurs when using dynamic SQL (see Writing Dynamic SQL Programs for information on dynamic SQL) for either the SELECT statement or the DELETE statement in an application which has been precompiled with LANGLEVEL set to SAA1, and bound with BLOCKING ALL. In this case, a FOR UPDATE clause is necessary in the SELECT statement. Refer to the Command Reference for information on the precompiler options.
The DELETE statement causes the row being referenced by the cursor to be deleted. This leaves the cursor positioned before the next row and a FETCH statement must be issued before additional WHERE CURRENT OF operations may be performed against the cursor.
Cursors fall into three categories:
There can be performance advantages for read-only cursors. For more information on read-only cursors, refer to the Administration Guide: Implementation.
An ambiguous cursor is treated as read only if the BLOCKING ALL option is specified when precompiling or binding. Otherwise, it is considered updatable.
| Note: | Cursors processed dynamically are always ambiguous. |
For a complete list of criteria used to determine whether a cursor is read-only, updatable, or ambiguous, refer to the SQL Reference.
This example selects from a table using a cursor, opens the cursor, and fetches rows from the table. For each row fetched, it decides if the row should be deleted or updated (based on a simple criteria). The sample is available in the following programming languages:
The REXX language does not support static SQL, so a sample is not provided.
If an UPDATE is performed, the position of the cursor remains on this row because the UPDATE statement does not change the position of the current row.
If a DELETE statement is performed, a different situation arises, because the current row is deleted. This is equivalent to being positioned before the next row, and a FETCH statement must be issued before additional WHERE CURRENT OF operations are performed.
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:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char pname[10];
short dept;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "Sample C program: OPENFTCH\n" );
if (argc == 1)
{
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 3)
{
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else
{
printf ("\nUSAGE: openftch [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff WHERE job='Mgr'
FOR UPDATE OF job;
EXEC SQL OPEN c1; (2)
EMB_SQL_CHECK("OPEN CURSOR");
do
{
EXEC SQL FETCH c1 INTO :pname, :dept; (3)
if (SQLCODE != 0) break;
if (dept > 40)
{
printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
pname, dept );
EXEC SQL UPDATE staff SET job = 'Clerk' (4)
WHERE CURRENT OF c1;
EMB_SQL_CHECK("UPDATE STAFF");
}
else
{
printf ("%-10.10s in dept. %2d will be DELETED!\n",
pname, dept);
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;
EMB_SQL_CHECK("DELETE");
} /* endif */
} while ( 1 );
EXEC SQL CLOSE c1; (5)
EMB_SQL_CHECK("CLOSE CURSOR");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
printf( "\nOn second thought -- changes rolled back.\n" );
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : OPENFTCH.SQC */
OpF_Curs.sqlj
// PURPOSE : This file, named OpF_Curs.sqlj, contains the definition // of the class OpF_Curs used in the sample program Openftch. import sqlj.runtime.ForUpdate; #sql public iterator OpF_Curs implements ForUpdate (String, short);
Openftch.sqlj
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class Openftch
{ 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 (" Java Openftch Sample");
String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname
Connection con = null;
// Set the connection
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 Openftch [username password]\n");
} // if - else if - else
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
// Enable transactions
con.setAutoCommit(false);
// Executing SQLJ positioned update/delete statements.
try
{ OpF_Curs forUpdateCursor;
String name = null;
short dept=0;
#sql forUpdateCursor =
{ SELECT name, dept
FROM staff
WHERE job='Mgr'
}; // #sql (1)(2)
while (true)
{ #sql
{ FETCH :forUpdateCursor
INTO :name, :dept
}; // #sql (3)
if (forUpdateCursor.endFetch()) break;
if (dept > 40)
{ System.out.println (
name + " in dept. "
+ dept + " will be demoted to Clerk");
#sql
{ UPDATE staff SET job = 'Clerk'
WHERE CURRENT OF :forUpdateCursor
}; // #sql (4)
}
else
{ System.out.println (
name + " in dept. " + dept
+ " will be DELETED!");
#sql
{ DELETE FROM staff
WHERE CURRENT OF :forUpdateCursor
}; // #sql
} // if - else
}
forUpdateCursor.close(); (5)
}
catch( Exception e )
{ throw e;
}
finally
{ // Rollback the transaction
System.out.println("\nRollback the transaction...");
#sql { ROLLBACK };
System.out.println("Rollback done.");
} // try - catch - finally
}
catch( Exception e )
{ System.out.println (e);
} // try - catch
} // main
} // class Openftch
Identification Division.
Program-ID. "openftch".
Data Division.
Working-Storage Section.
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 pname pic x(10).
01 dept pic s9(4) comp-5.
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: OPENFTCH".
* Get database connection information.
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
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff
WHERE job='Mgr'
FOR UPDATE OF job END-EXEC.
EXEC SQL OPEN c1 END-EXEC (2)
move "OPEN" to errloc.
call "checkerr" using SQLCA errloc.
* call the FETCH and UPDATE/DELETE loop.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
EXEC SQL CLOSE c1 END-EXEC. (5)
move "CLOSE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL ROLLBACK END-EXEC.
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-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :pname, :dept END-EXEC. (3)
if SQLCODE not equal 0
go to End-Fetch-Loop.
if dept greater than 40
go to Update-Staff.
Delete-Staff.
display pname, " in dept. ", dept,
" will be DELETED!".
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1 END-EXEC.
move "DELETE" to errloc.
call "checkerr" using SQLCA errloc.
go to End-Fetch-Loop.
Update-Staff.
display pname, " in dept. ", dept,
" will be demoted to Clerk".
EXEC SQL UPDATE staff SET job = 'Clerk' (4)
WHERE CURRENT OF c1 END-EXEC.
move "UPDATE" to errloc.
call "checkerr" using SQLCA errloc.
End-Fetch-Loop. exit.
End-Prog.
stop run.