//***************************************************************************
// (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 };
  }
}