How to write a java.sql.Array to a java.sql.SQLOutput within SQLData.writeSQL() for Oracle

Question

I have implemented java.sql.SQLData in order to bind UDT objects to prepared statements using ojdbc6. Now, some of my UDT's contain arrays. What I need to do now is this:

class MyType implements SQLData {
  public void writeSQL(SQLOutput stream) throws SQLException {
    Array array = //...
    stream.writeArray(array);
  }
}

In order to construct Oracle arrays, I need a JDBC Connection. Typically, this is done as such:

OracleConnection conn = // ...
Array array = conn.createARRAY("MY_ARRAY_TYPE", new Integer[] { 1, 2, 3 });

However, in that writeSQL(SQLOutput) method, I do not have a connection. Also, for reasons that are hard to explain in a concise question, I cannot maintain a connection reference in MyType. Can I somehow extract that connection from SQLOutput? I'd like to avoid using instable constructs like this:

// In ojdbc6, I have observed a private "conn" member in OracleSQLOutput:
Field field = stream.getClass().getDeclaredField("conn");
field.setAccessible(true);
OracleConnection conn = (OracleConnection) field.get(stream);

Any ideas? Alternatives?

Agent

Did you solve this? I am facing the exact same issue. – Superficies

@PabloSantaCruz: Not so far. I had been thinking about a workaround involving storing the connection locally in a static ThreadLocal before binding the UDT. That would work, but it feels really wrong... – Agent

OK, I will write you what I did in a separate answer. It also feels wrong, but I needed to solve this issue right away. – Superficies

Three years later I am running into this problem as well using Postgres and having the hardest time getting this done. To complicate matters I am using HIbernate and not JDBC directly. – Aurelie

Answer 1

Here's what I did to workaround this issue. It's not pretty, but it works.

I added a method in my class implementing SQLData that receives a java.sql.Connection and setups the corresponding java.sql.ARRAY objects.

Something like this:

public class MyObject01 implements SQLData {
   private String value;
   private MyObject02[] details; // do note that details is a java array
   // ... also added getters and setters for these two properties

   private Array detailsArray;

   public void setupArrays(oracle.jdbc.OracleConnection oconn)
      throws SQLException
   {
       detailsArrays = oconn.createARRAY(MyObject02.ORACLE_OBJECT_ARRAY_NAME, getDetails());
       // MyObject02.ORACLE_OBJECT_ARRAY_NAME must be the name of the oracle "table of" type name
       // Also note that in Oracle you can't use JDBC's default createArray
       // since it's not supported. That's why you need to get a OracleConnection
       // instance here. 
   }       

   @Override
   public void writeSQL(Stream stream) throws SQLException {
       stream.writeString(getValue());
       stream.writeArray(detailsArray); // that's it
   }

   @Override
   public void readSQL(Stream stream) throws SQLException {
       setValue(stream.readString());
       Array array = stream.readArray();
       if (array != null) {
           setDetails((MyObject02[])array.getArray());
       }
   }

That's the first part.

Then, BEFORE using that object in a procedure call, invoke setupArrays method on that object. Example:

public class DB {
    public static String executeProc(Connection conn, MyObject01 obj)
        throws SQLException
    {
        CalllableStatement cs = conn.prepareCall(" { ? = call sch.proc(?) }");
        cs.registerOutParameter(1, Types.VARCHAR);
        obj.setupArrays((oracle.jdbc.Connection)conn);
        cs.setObject(2, obj, Types.STRUCT);
        cs.executeUpdate();
        String ret = cs.getString(1);
        cs.close();
        return ret;
    }
}

Of course, upon connection, you need to register your types properly:

Connection conn = DriverManager.getConnection("jdbc:oracle://localhost:1521/XE", "scott", "tiger" );
conn.getTypeMap().put(MyObject01.ORACLE_OBJECT_NAME, MyObject01.class);
conn.getTypeMap().put(MyObject02.ORACLE_OBJECT_NAME, MyObject02.class);
conn.getTypeMap().put(MyObject02.ORACLE_OBJECT_ARRAY_NAME, MyObject02[].class);

Hope it helps.

Superficies

Yes, that's about the same thing as my ThreadLocal solution, except that the thread local is external to the SQLData object. I guess it's as good as it gets :-/ – Agent

Answer 2

Have a look at this SO answer. One can get the connection from the SQLOutput:

    final OracleSQLOutput out = (OracleSQLOutput) sqlOutput;
    OracleConnection conn = (OracleConnection) out.getSTRUCT().getJavaSqlConnection();

Iain