Oracle8i JPublisher User's Guide Release 2 (8.1.6) Part Number A81357-01 |
Library | Service | Contents | Index

## 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index