/* Copyright (c) 1997 Oracle Corporation */
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;

#sql context QueryDemoCtx ;

#sql iterator SalByName (double sal, String ename) ;

#sql iterator SalByPos (double, String ) ;

/**
  This sample program demonstrates the various constructs that may be
  used to fetch a row of data using SQLJ.  It also demonstrates the
  use of explicit and default connection contexts.
  **/
public class QueryDemo
{
  public static void main(String[] args) throws SQLException
  {
    if (args.length != 2) {
      System.out.println("usage: QueryDemo ename newSal");
      System.exit(1);
    }

    /* if you're using a non-Oracle JDBC Driver, add a call here to
	DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(QueryDemo.class, "connect.properties");

    QueryDemoCtx ctx = 
      new QueryDemoCtx(DefaultContext.getDefaultContext().getConnection());
    String ename = args[0];
    int newSal = Integer.parseInt(args[1]);

    System.out.println("before update:");
    getSalByName(ename, ctx);
    getSalByPos(ename);

    updateSal(ename, newSal, ctx);

    System.out.println("after update:");
    getSalByCall(ename, ctx);
    getSalByInto(ename);
    ctx.close(ctx.KEEP_CONNECTION);
  }

  public static void getSalByName(String ename, QueryDemoCtx ctx) 
    throws SQLException
  {
    SalByName iter = null;
    #sql [ctx] iter = { SELECT ename, sal FROM emp WHERE ename = :ename };
    while (iter.next()) {
      printSal(iter.ename(), iter.sal());
    }
    iter.close();
  }

  public static void getSalByPos(String ename) throws SQLException
  {
    SalByPos iter = null;
    double sal = 0;
    #sql iter = { SELECT sal, ename FROM emp WHERE ename = :ename };
    while (true) {
      #sql { FETCH :iter INTO :sal, :ename };
      if (iter.endFetch()) break;
      printSal(ename, sal);
    }
    iter.close();
  }

  public static void updateSal(String ename, int newSal, QueryDemoCtx ctx)
    throws SQLException
  {
    #sql [ctx] { UPDATE emp SET sal = :newSal WHERE ename = :ename }; 
  }

  public static void getSalByCall(String ename, QueryDemoCtx ctx) 
    throws SQLException
  {
    double sal = 0;
    #sql [ctx] sal = { VALUES(get_sal(:ename)) };
    printSal(ename, sal);
  }

  public static void getSalByInto(String ename)
    throws SQLException
  {
    double sal = 0;
    #sql { SELECT sal INTO :sal FROM emp WHERE ename = :ename };
    printSal(ename, sal);
  }

  public static void printSal(String ename, double sal)
  {
    System.out.println("salary of " + ename + " is " + sal);
  }
}