//*************************************************************************** // (c) Copyright IBM Corp. 2007 All rights reserved. // // The following sample of source code ("Sample") is owned by International // Business Machines Corporation or one of its subsidiaries ("IBM") and is // copyrighted and licensed, not sold. You may use, copy, modify, and // distribute the Sample in any form without payment to IBM, for the purpose of // assisting you in the development of your applications. // // The Sample code is provided to you on an "AS IS" basis, without warranty of // any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR // IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF // MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do // not allow for the exclusion or limitation of implied warranties, so the above // limitations or exclusions may not apply to you. IBM shall not be liable for // any damages you suffer as a result of using, copying, modifying or // distributing the Sample, even if IBM has been advised of the possibility of // such damages. //*************************************************************************** // // SOURCE FILE NAME: ScrollIterDemo.sqlj // // SAMPLE: How to use Named and Positional Scrollable Iterators in SQLJ // // This program shows how to use Named and Positional Scrollable // Iterators in SQLJ. It also shows how a Scrollable Iterator // can be used for Positioned update by implementing ForUpdate // Clause. However, updates will work only when the program // is customized. // // This sample program uses the DataSource jdbc/DB2SimpleDataSource_ds1 // from JNDI. The DataSource is registered using createRegisterDS.java // and DS1.prop. Refer to the README file for details on how to run // this sample. // // // SQL Statements USED: // SELECT // // Classes used from Util.sqlj are: // Data // // // Output will vary depending on the JDBC driver connectivity used. //*************************************************************************** // // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //**************************************************************************/ import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; #sql context ScrollCtx with (dataSource="jdbc/DB2SimpleDataSource_ds1"); class ScrollIterDemo { // Scrollable // allows the iterator to move both forward and backward relative to the current position, and it can // move to an absolute position // // ForUpdate // It is equivalent to the FOR UPDATE clause in SQL // It identifies the columns that can be updated in a subsequent Positioned UPDATE statement. // Each column name must be unqualified and must identify a column of the table in the FROM clause of the // fullselect. // If the ForUpdate cluase is specified without column names, all updatable columns of the table or view // identified in the frist FROM clause of the fullselect are included // // Sensitivity can be one of SENSITIVE, INSENSITIVE, or ASENSITIVE // SENSITIVE - the result set reflects changes made to the underlying data source while the result set remains open // INSENSITIVE - the result set is insensitive to changes made to the underlying data source while it is open #sql public iterator sensitivePosUpdateIter implements sqlj.runtime.Scrollable, sqlj.runtime.ForUpdate with (sensitivity=sqlj.runtime.ResultSetIterator.SENSITIVE, updateColumns="c1") (int, String); #sql public iterator sensitiveNamedUpdateIter implements sqlj.runtime.Scrollable with (sensitivity=sqlj.runtime.ResultSetIterator.INSENSITIVE) (int c1, String c2); static ScrollCtx ctx1 = null; public static void main(String argv[]) { System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO USE NAMED AND POSITIONAL SCROLLABLE \n" + "ITERATORS IN SQLJ. \n"); ScrollIterDemo sid = new ScrollIterDemo(); try { sid.runThis(); } catch (Exception e) { e.printStackTrace(); } } public void runThis() throws SQLException { // Obtain Connection Context from DataSource jdbc/DB2SimpleDataSource_ds1 ctx1 = new ScrollCtx(); Connection conn = ctx1.getConnection(); conn.setAutoCommit(false); try { System.out.println (" Insert 4 rows into the table Scroll_Test.\n"); #sql[ctx1] { INSERT INTO Scroll_Test VALUES(101, 'row1') }; System.out.println(" INSERT INTO Scroll_Test VALUES(101, 'row1')"); #sql[ctx1] { INSERT INTO Scroll_Test VALUES(102, 'row2') }; System.out.println(" INSERT INTO Scroll_Test VALUES(102, 'row2')"); #sql[ctx1] { INSERT INTO Scroll_Test VALUES(103, 'row3') }; System.out.println(" INSERT INTO Scroll_Test VALUES(103, 'row3')"); #sql[ctx1] { INSERT INTO Scroll_Test VALUES(104, 'row4') }; System.out.println(" INSERT INTO Scroll_Test VALUES(104, 'row4')"); sensitivePosUpdateIter posUpdateIter = null; sensitiveNamedUpdateIter namedUpdateIter = null; int col1 = 0; String col2 = null; #sql [ctx1] posUpdateIter = { SELECT c1, c2 FROM Scroll_Test }; // Retrieve and display the data in the table Scroll_Test System.out.println(""); System.out.println( " Display the content of the table Scroll_Test by performing\n" + " the following SQL statement in 'SQLJ':\n\n" + " SELECT c1, c2 FROM Scroll_Test\n"); System.out.println( " Results:\n" + " COL1 COL2 \n" + " ----- ----- "); while (true) { #sql {FETCH FROM :posUpdateIter INTO :col1, :col2}; if (posUpdateIter.endFetch()) { break; } System.out.println(" "+Data.format(col1, 5) + " " + Data.format(col2, 5)); } System.out.println(""); System.out.println( "----------------------------------------------------------\n\n" + " PART II: Positioned Scrollable Iterator Fetch \n" + " ------------------------------------------------ \n"); System.out.println(" Test scrollability-> Fetch first: \n"); #sql {FETCH FIRST FROM :posUpdateIter INTO :col1, :col2}; System.out.println( " FETCH FIRST FROM :posUpdateIter INTO :col1, :col2\n\n" + " First Row: COL1 = " + col1 + ", COL2 = " + col2 + "\n\n"); System.out.println(" Test scrollability-> Fetch last:\n"); #sql {FETCH LAST FROM :posUpdateIter INTO :col1, :col2}; System.out.println( " FETCH LAST FROM :posUpdateIter INTO :col1, :col2\n\n" + " Last Row: COL1 = " + col1 + ", COL2 = " + col2 + "\n\n"); int abs = 3; System.out.println(" Test scrollability-> Fetch third:\n"); #sql {FETCH ABSOLUTE(:abs) FROM :posUpdateIter INTO :col1, :col2}; System.out.println( " FETCH ABSOLUTE(:abs) FROM :posUpdateIter INTO :col1, :col2\n\n"+ " Third row: COL1 = " + col1 + ", COL2 = " +col2 + "\n\n"); System.out.println (" Test Update of current row: \n "); #sql [ctx1] {UPDATE Scroll_Test SET c1 = 5000 WHERE CURRENT OF :posUpdateIter}; System.out.println(" UPDATE Scroll_Test SET c1 = 5000 WHERE "+ "CURRENT OF :posUpdateIter\n"); #sql {FETCH ABSOLUTE(:abs) FROM :posUpdateIter INTO :col1, :col2}; System.out.println( " FETCH ABSOLUTE(:abs) FROM :posUpdateIter INTO :col1, :col2\n\n"+ " Updated Third row: COL1 = " + col1 + ", COL2 = " +col2+"\n\n"); posUpdateIter.close(); #sql [ctx1] { commit }; System.out.println(""); System.out.println( "----------------------------------------------------------\n\n" + " PART I: Named Scrollable Iterator Fetch \n" + " ------------------------------------------------ \n"); #sql [ctx1] namedUpdateIter = { SELECT c1,c2 FROM Scroll_Test }; System.out.println(" SELECT c1,c2 FROM Scroll_Test\n"); namedUpdateIter.first(); System.out.println(" First Row: COL1 = " + namedUpdateIter.c1() + ", COL2 = " + namedUpdateIter.c2() + "\n"); namedUpdateIter.last(); System.out.println(" Last Row: COL1 = " + namedUpdateIter.c1() + ", COL2 = " + namedUpdateIter.c2() + "\n"); cleanup(); } catch(Exception ex) { if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); System.out.println("error message: " + ex.getMessage()); } ex.printStackTrace(); } } private static void cleanup() throws SQLException { #sql [ctx1] { DELETE FROM Scroll_Test WHERE 1=1 }; } }