extdemo3.java Demonstration Script

The extdemo3.java script demonstrates the class that implements the ODCIIndex 
methods. This class was originally generated using JPUB, based on the type that 
extensible indexing is based The methods were implemented after the class was 
generated.

Class extdemo3

This class provides the implementation for several of the ODCI routines.
The original class was generated via jpub.

import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.CustomDatum;
import oracle.sql.CustomDatumFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

import java.lang.*;
import java.sql.*;
import oracle.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.ODCI.*;
import oracle.CartridgeServices.*;

public class extdemo3 implements CustomDatum, CustomDatumFactory
{
  public static final String _SQL_NAME = "EXTDEMO.EXTDEMO3";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  final static java.math.BigDecimal SUCCESS = new java.math.BigDecimal("0");
  final static java.math.BigDecimal ERROR = new java.math.BigDecimal("1");
  final static int TRUE = 1;
  final static int FALSE = 0;

  static int[] _sqlType =
  {
    4
  };

  static CustomDatumFactory[] _factory = new CustomDatumFactory[1];

  MutableStruct _struct;

  static final extdemo3 _extdemo3Factory = new extdemo3();
  public static CustomDatumFactory getFactory()
  {
    return _extdemo3Factory;
  }

  /* constructor */
  public extdemo3()
  {
    _struct = new MutableStruct(new Object[1], _sqlType, _factory);
  }

  /* CustomDatum interface */
  public Datum toDatum(OracleConnection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* CustomDatumFactory interface */
  public CustomDatum create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    extdemo3 o = new extdemo3();
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* shallow copy method: give object same attributes as argument */
  void shallowCopy(extdemo3 d) throws SQLException
  {
    _struct = d._struct;
  }

  /* accessor methods */
  public Integer getScanctx() throws SQLException
  { return (Integer) _struct.getAttribute(0); }

  public void setScanctx(Integer scanctx) throws SQLException
  { _struct.setAttribute(0, scanctx); }



Implementation of the ODCIIndexStart Routine

The start routine performs the setup for an sbtree index scan. The query 
information in terms of the operator predicate, its arguments and the bounds on
return values are passed in as parameters to this function. The scan context 
that is shared amongst the index scan routines is an instance of the type
extdemo3. 

/* The index implementation type is an object type with a single number
 * attribute which will be used to store the context key value.
 */


This method sets up a ResultSet that scans the index table. The scan retrieves 
the stored rowids for the rows in the index table that satisfy the specified
predicate. The predicate for the index table is generated based on the operator 
predicate information that is passed in as parameters. For example, if the
operator predicate is of the form: 

eq(col, 'joe') = 1


the predicate on the index table is set up to be 

f1 = 'joe'

The ResultSet is stored away and retrieved on the next fetch call. 
The oracle.ODCI.ContextManager class is a final class that holds
the context for the session duration.  Since this class is declared
final, all statements in a session shared the same copy of the 
ContextManager. An object which holds any information that needs
to be saved across calls is passed to the ContextManager. The 
ContextManager class then returns a 4-byte which will be stored
with the object and will be passed in as a
parameter to the next fetch call. 


  public static java.math.BigDecimal ODCIStart(extdemo3 sctx[], 
		ODCIIndexInfo ia, ODCIPredInfo op, 
		ODCIQueryInfo qi, 
		java.math.BigDecimal strt, java.math.BigDecimal stop, 
		String cmpval) 
    throws java.sql.SQLException
  {
    String relop;
    String selstmt;
    int key;
    extdemo3a sbtctx;   // cntxt obj that holds the ResultSet and Statement
    PreparedStatement ps;
    OracleResultSet rset;    

    Connection conn = 	
        sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
 

    CallableStatement cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Start \')}");
    cstmt.executeUpdate();
   
    //***********************************
    //* Check that the bounds are valid *
    //***********************************
    // verify that strtval/stopval are both either 0 or 1 
    if (!(((strt.intValue() == 0) && (stop.intValue() == 0)) || 
	  ((strt.intValue() == 1) && (stop.intValue() == 1))))
      {
	// throw Application_Error
	System.out.println("incorrect predicate for btree operator");
	return ERROR;
      }

    String s = new String("start key:  "+ strt.intValue() + " stop key: " + 
	stop.intValue() + " compare value: " + cmpval);
    cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(?)}");
    cstmt.setString(1, s);
    cstmt.executeUpdate();

    //*********************************************
    //* Generate the SQL statement to be executed *
    //*********************************************
    if ((op.getObjectname()).equals("EQ")){
      if (strt.intValue() == 1)
	relop = new String("=");
      else
	relop = new String("!=");
    }else if ((op.getObjectname()).equals("LT")){
      if (strt.intValue() == 1)
	relop = new String("<");
      else
	relop = new String(">=");
    }else{ 
      if (strt.intValue() == 1)
	relop = new String(">");
      else
	relop = new String("<=");
    }
    
    selstmt = new String("select ROWIDTOCHAR(f2) from "
         + ia.getIndexschema()
	 + "." 
         + ia.getIndexname()
	 + "_sbtree where f1 "
	 + relop + " '" + cmpval + "'");
    cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(?)}");
    cstmt.setString(1, selstmt);
    cstmt.executeUpdate();


    ps = conn.prepareStatement(selstmt);
    rset = (OracleResultSet) ps.executeQuery();
  
    // set result set in ContextManager.  This stores away the 
    // ResultSet and the Statement handle so that they can
    // be used to fetch the rowids and cleanup at a later time.
    sbtctx = new extdemo3a(rset, ps);
    sctx[0] = new extdemo3();
    
    try{
	key = ContextManager.setContext((Object)sbtctx);
    }catch (CountException ce) {
	System.out.println("ContextManager CountException error");
	return ERROR;
    }

    System.out.println("ContextManager key=" + key);

    // set the key into the self argument so that we can retrieve the
    // context with this key later.
    sctx[0].setScanctx(new Integer(key));

    return SUCCESS;
  }
  


Implementation of the ODCIIndexFetch Routine

The scan context set up by the start routine is passed in as a parameter to the 
fetch routine. This function first retrieves the 4-byte key from the scan 
context. The key is stored in the scanctx variable of this classes corresponding
sql type
Next, the Java context is looked up based on the key. This gives the 
Object that the context was stored in, extdemo3a.

This function returns the next batch of rowids that satisfy the operator 
predicate. It uses the value of the nrows parameter as the size of the batch. It
repeatedly fetches rowids from the open cursor and populates the rowid list with 
them. When the batch is full or when there are no more rowids left, the
function returns them back to the Oracle server. 

  // ODCIIndexFetch
  public java.math.BigDecimal ODCIFetch(
	java.math.BigDecimal nrows, 
	ODCIRidList rids[]) 
    throws java.sql.SQLException
  {
    extdemo3a sbtctx;  // cntxt obj that holds the ResultSet and Statement
    OracleResultSet rset;
    String rid;
    int idx = 1;
    int done = FALSE;
    String[] rlist = new String[nrows.intValue()];
    int key = getScanctx().intValue();

    Connection conn = 	
        sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Fetch \')}");
    cstmt.executeUpdate();

    String s = new String("nrows : " + nrows);
    cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(?)}");
    cstmt.setString(1, s);
    cstmt.executeUpdate();
   
    System.out.println("ContextManager key=" + key);

    // Get the resultSet back from the ContextManager using the key
    try{
    	sbtctx= (extdemo3a)ContextManager.getContext(key);
    }catch(InvalidKeyException ike){
 	System.out.println("ContextManager InvalidKeyException");
	return ERROR;
    }
    rset = (OracleResultSet)(sbtctx.getRs());

    //***************
    // Fetch rowids *
    //***************
    for(int i=0; done != TRUE; i++)
      {
	if (idx > nrows.intValue()){
	  done = TRUE;
	}else {
	  if (rset.next()){
	    // append rowid to collection 
	    rid =  rset.getString(1);
	    rlist[i] = new String(rid);
	    idx++; 
	  }else{
	    // append null rowid to collection
	    rlist[i] = null;
	    done = TRUE; 
	  }
	}
      }

    // Since rids is an out parameter we need to set the ODCIRidList
    // object into the first position to be passed out.
    rids[0] = new ODCIRidList(rlist);

    return SUCCESS;
  }
  


Implementation of the ODCIIndexClose Routine

The scan context set up by the start routine is passed in as a parameter to the 
close routine. This function first retrieves the 4-byte key from the scan 
context.The key is stored in the scanctx variable of this classes corresponding
sql type
Next, the Java context is looked up based on the key. This gives the 
Object that the context was stored in, extdemo3a.

The method closes the ResultSet and PreparedStatement objects. 


  public java.math.BigDecimal ODCIClose() 
		throws java.sql.SQLException
  {
    extdemo3a sbtctx;   // contxt obj that holds the ResultSet and Statement
    OracleResultSet rset;
    PreparedStatement ps;
    System.out.println("in odciclose");

    int key = getScanctx().intValue();
    System.out.println("in odciclose2");

    Connection conn = 	
        sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement  cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Close\')}");
    cstmt.executeUpdate();

    System.out.println("key=" + key);

    // Get the resultSet and statement back from the ContextManager
    // so that we can close them.
    try{
        sbtctx = (extdemo3a)ContextManager.clearContext(key);
    }catch(InvalidKeyException ike){
 	System.out.println("ContextManager InvalidKeyException");
	return ERROR;
    }

    rset = (OracleResultSet)sbtctx.getRs();
    ps = (PreparedStatement)sbtctx.getStmt();
    rset.close();    
    ps.close();

    return SUCCESS;
  }
  

Implementation Of The ODCIIndexInsert Routine

The insert routine parses and executes a statement that inserts a new row into 
the index table. The new row consists of the new value of the indexed column
and the rowid that have been passed in as parameters. 


  public static java.math.BigDecimal ODCIInsert(
	ODCIIndexInfo ia, String rid, String newval)
    throws java.sql.SQLException
  {
    String insstmt;
    
    Connection conn = 	
      sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Insert\')}");
    cstmt.executeUpdate();

    /******************************
     * Construct insert Statement *
     ******************************/
    insstmt = new String("INSERT into "
        + ia.getIndexschema() + "." + ia.getIndexname() 
	+"_sbtree values ('" + newval + "','" + rid + "')" );

    Statement stmt = conn.createStatement();
    stmt.executeUpdate(insstmt);
    stmt.close();
    
    return SUCCESS;
  }
  


Implementation of the ODCIIndexDelete Routine

The delete routine constructs a SQL statement to delete a row from the index 
table corresponding to the row being deleted from the base table. The row in the
index table is identified by the value of rowid that is passed in as a parameter 
to this routine. 


  // ODCIIndexDelete 
  public static java.math.BigDecimal ODCIDelete(
	ODCIIndexInfo ia, String rid, String oldval)
    throws java.sql.SQLException
  {
    
    String delstmt;
    
    Connection conn = 	
      sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Delete\')}");
    cstmt.executeUpdate();
    
    /******************************
     * Construct delete Statement *
     ******************************/
    delstmt = new String("DELETE from "
           + ia.getIndexschema() + "." + ia.getIndexname() 
	   +"_sbtree where f1= '" + oldval +"'" );

    Statement stmt = conn.createStatement();
    stmt.executeUpdate(delstmt);
    stmt.close();
    
    return SUCCESS;
  }
  


Implementation of the ODCIIndexUpdate Routine

The update routine constructs a SQL statement to update a row in the index table 
corresponding to the row being updated in the base table. The row in the
index table is identified by the value of rowid that is passed in as a parameter 
to this routine. The old column value (oldval) is replaced by the new value
(newval). 

  public static java.math.BigDecimal ODCIUpdate(
	ODCIIndexInfo ia, String rid, String oldval, 
	String newval) 
    throws java.sql.SQLException
  {
    String updstmt;
    
    Connection conn = 	
      sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Update\')}");
    cstmt.executeUpdate();
    
    /******************************
     * Construct update Statement *
     ******************************/
    updstmt = new String("UPDATE "
            + ia.getIndexschema() + "." + ia.getIndexname() 
	    +"_sbtree SET f1= '" + newval + "' WHERE f1 = '" 
            + oldval +"'");
    
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(updstmt);
    stmt.close();
    
    return SUCCESS;
  }
 
}