Example: Using Datatypes not Supported by JDBC

One technique that you can employ to use datatypes not supported by JDBC is to write an anonymous PL/SQL block that converts input types that JDBC supports into the input types that the PL/SQL method uses. Then convert the output types that the PL/SQL method uses into output types that JDBC supports. For more information on this topic, see "Using Datatypes Not Supported by JDBC".

The following steps offer a general outline of how you would do this. The steps assume that you used JPublisher to translate an object type with methods that contain argument types not supported by JDBC. The steps describe the changes you must make. You could make changes by extending the class or modifying the generated files. Extending the classes is a better technique; however, in this example, the generated files are modified.

1. In Java, convert each IN or IN OUT argument having a type that JDBC does not support to a Java type it does support.

2. Pass each IN or IN OUT argument to a PL/SQL block.

3. In the PL/SQL block, convert each IN or IN OUT argument to the correct type for the PL/SQL method.

4. Call the PL/SQL method.

5. Convert each OUT argument or IN OUT argument or function result from the type that JDBC does not support to the corresponding type that JDBC does support in PL/SQL.

6. Return each OUT argument, or IN OUT argument, or function result from the PL/SQL block.

7. In Java, convert each OUT argument or IN OUT argument or function result from the type JDBC does support to the type it does not support.

Here is an example of how to handle an argument type not directly supported by JDBC. The example converts from/to a type that JDBC does not support (Boolean/BOOLEAN) to/from one that JDBC does support (String/VARCHAR2).

The following .sql file defines an object type with methods that use boolean arguments. The methods this program uses are very simple; they serve only to demonstrate that arguments are passed correctly.

CREATE TYPE BOOLEANS AS OBJECT (
 iIn     INTEGER,
 iInOut  INTEGER,
 iOut    INTEGER,
 
 MEMBER PROCEDURE p(i1 IN BOOLEAN,
                    i2 IN OUT BOOLEAN,
                    i3 OUT BOOLEAN),

 MEMBER FUNCTION f(i1 IN BOOLEAN) RETURN BOOLEAN
);

CREATE TYPE BODY BOOLEANS AS

MEMBER PROCEDURE p(i1 IN BOOLEAN,
                   i2 IN OUT BOOLEAN,
                   i3 OUT BOOLEAN) IS
BEGIN
  iOut := iIn;

  IF iInOut IS NULL THEN
    iInOut := 0;
  ELSIF iInOut = 0 THEN
    iInOut := 1;
  ELSE
    iInOut := NULL;
  END IF;

  i3 := i1; 
  i2 := NOT i2;
END;

MEMBER FUNCTION f(i1 IN BOOLEAN) RETURN BOOLEAN IS
BEGIN
  return i1 = (iIn = 1);
END;

END;

The following .sqlj file was first generated by JPublisher, then modified by a user, according to the steps above. The wrapper methods convert each argument from Boolean to String in Java; pass each argument into a PL/SQL block; convert the argument from VARCHAR2 to BOOLEAN in PL/SQL; call the PL/SQL method; convert each OUT argument, or IN OUT argument, or function result from BOOLEAN to VARCHAR2 in PL/SQL; return each OUT argument, or IN OUT argument, or function result from the PL/SQL block; and finally, convert each OUT argument, or IN OUT argument, or function result:

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 sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public class Booleans implements CustomDatum, CustomDatumFactory
{
  public static final String _SQL_NAME = "SCOTT.BOOLEANS";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  #sql static context _Ctx;
  _Ctx _ctx;

  MutableStruct _struct;

  static int[] _sqlType =
  {
    4, 4, 4
  };

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

  static final Booleans _BooleansFactory = new Booleans();
  public static CustomDatumFactory getFactory()
  {
    return _BooleansFactory;
  }

  /* constructors */
  public Booleans()
  {
    _struct = new MutableStruct(new Object[3], _sqlType, _factory);
    try
    {
      _ctx = new _Ctx(DefaultContext.getDefaultContext());
    }
    catch (Exception e)
    {
      _ctx = null;
    }
  }

  public Booleans(ConnectionContext c) throws SQLException
  {
    _struct = new MutableStruct(new Object[3], _sqlType, _factory);
    _ctx = new _Ctx(c == null ? DefaultContext.getDefaultContext()
                              : c);
  }
  public Booleans(Connection c) throws SQLException
  {
    _struct = new MutableStruct(new Object[3], _sqlType, _factory);
    _ctx = new _Ctx(c);
  }

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

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

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

  public void setIin(Integer iin) throws SQLException
  { _struct.setAttribute(0, iin); }


  public Integer getIinout() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setIinout(Integer iinout) throws SQLException
  { _struct.setAttribute(1, iinout); }


  public Integer getIout() throws SQLException
  { return (Integer) _struct.getAttribute(2); }

  public void setIout(Integer iout) throws SQLException
  { _struct.setAttribute(2, iout); }


  public Boolean f (
    Boolean i1)
  throws SQLException
  {
    Booleans _temp = this;
    String _i1 = null;
    String _result = null;

    if (i1 != null) _i1 = i1.toString();

    #sql [_ctx] {
      DECLARE
      i1_ BOOLEAN;
      result_ BOOLEAN;
      t_ VARCHAR2(5);

      BEGIN
      i1_ := :_i1 = 'true';

      result_ := :_temp.F(i1_);

      IF result_ THEN
        t_ := 'true';
      ELSIF NOT result_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _result := t_;

      END;
    };

    if (_result == null)
       return null;
    else
       return new Boolean(_result.equals("true"));
  }

  public Booleans p (
    Boolean i1,
    Boolean i2[],
    Boolean i3[])
  throws SQLException
  {
    String _i1 = (i1 == null) ? null
                              : i1.toString();

    String _i2 = (i2[0] == null) ? null
                                 : i2[0].toString();

    String _i3 = (i3[0] == null) ? null
                                 : i3[0].toString();

    Booleans _temp = this;

    #sql [_ctx] {
      DECLARE
      i1_ BOOLEAN;
      i2_ BOOLEAN;
      i3_ BOOLEAN;
      t_ VARCHAR2(5);

      BEGIN
      i1_ := :_i1 = 'true';
      i2_ := :_i2 = 'true';

      :INOUT _temp.P( i1_, i2_, i3_);

      IF i2_ THEN
        t_ := 'true';
      ELSIF NOT i2_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _i2 := t_;

      IF i3_ THEN
        t_ := 'true';
      ELSIF NOT i3_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _i3 := t_;

      END;
    };

    i2[0] = (_i2 == null) ? null
                          : new Boolean(_i2.equals("true"));
    i3[0] = (_i3 == null) ? null
                          : new Boolean(_i3.equals("true"));
    return _temp;
  }
}

Note: Because of the semantics of SQLJ parameters, it is necessary to assign to each output parameter exactly once within the block.

---