JDBC custom mapping

Hi,
I'm trying to map my custom database (Oracle) types to classes in Java. I've read some tutorials. I'm following the guidelines. But I've an error. What I've done:
1. Created Oracle type looks like this
create type Oper as object (
  iType integer,
  dDocument date,
  cPurpose varchar2( 1024 )
);
2. Created package with procedure like this
...
procedure dump( oOper in Oper );
...
3. Tested a call for the procedure in PL/SQL like this
exec mypack.dump( Oper( 1, sysdate, 'Test...' ) )

All works fine.
Now I'm trying to call it from Java. And I...
4. Implemeneted SQLData interface like this
class Oper implements SQLData {
  public Oper( int iType, Date datDocument, String strPurpose ) {
  ...
  }
  public void readSQL( SQLInput siStream, String strTypeName )
  throws SQLException {
  ...
  }
  public void writeSQL( SQLOutput soStream )
  throws SQLException {
  ...
  }
  ...
}
5. Set the corresponding type map like this
  ...
  m_mapType = m_conn.getTypeMap();
  m_mapType.put( "DEV.OPER", ( new Oper() ).getClass() );
  m_conn.setTypeMap( m_mapType );
  ...

But when I'm trying to call the procedure
  ...
  m_cstmt = m_conn.prepareCall( "{ call mypack.dump( ? ) }" );
  Oper operation = new Oper(
    1, new Date( System.currentTimeMillis() ), "Test..." );
  m_cstmt.setObject( 1, operation );
  m_cstmt.execute();

I'm getting the error

java.sql.SQLException: Error creating descriptor: Invalid arguments
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
     at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1129)
     at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:125)
     at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:613)
     at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3069)
     at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3210)

As far as I understand my SQLData interface is not used.
Oracle driver cannot find my mapping.
Why? What's wrong?

Thanks in advance.
Andrew.
LVL 2
Andrei RodionovAsked:
Who is Participating?
 
objectsConnect With a Mentor Commented:
> Or my SQLData implementation is incomplete?

Its incomplete, getSQLTypeName() should return your SQL type name.
0
 
objectsCommented:
> m_mapType.put( "DEV.OPER", ( new Oper() ).getClass() );

Why do you use "DEV.OPER" as the key, if the type name is "Oper" then use that.

Also you don't need to create an Oper instance to get class you can use Class.forName("Oper");

m_mapType.put( "Oper", Class.forName("Oper"));

> m_conn.setTypeMap( m_mapType );

This is unnecessary.
0
 
Andrei RodionovAuthor Commented:
> Why do you use "DEV.OPER" as the key, if the type name is "Oper" then use that.
Although I've declared my database type as 'create type Oper as object (...)' it means 'OPER' for Oracle. To make an Oracle name case sensitive one should use double quotes (i.e. 'create type "Oper" as object (...)').
And the 'DEV' is my schema name. I'd tried to omit the schema name, but result is the same :(

> > m_conn.setTypeMap( m_mapType );
> This is unnecessary.
I know... I'd tried _with_ this line and _without_ this line. It doesn't matter. It does not work.

Andrew.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
objectsCommented:
Actually thinking about it further, the mapping is not even used when going from Java to database. It is only used when going the other way.

So have you confirmed that writeSQL is not getting called?
0
 
Andrei RodionovAuthor Commented:
Here is a small quote from Oracle8i JDBC Developer's Guide and Reference
"...the JDBC driver calls your writeSQL() method to write a sequence of data values from an instance of your custom object class to a stream that can be written to the database. Typically, the driver would use this method as part of an OraclePreparedStatement object setObject() call."

I've added a System.out.println() call into my writeSQL method. And I'm sure it's _not_ getting called.
0
 
objectsCommented:
WHat does getSQLTypeName() return?
0
 
Andrei RodionovAuthor Commented:
Damned! It's null!
0
 
objectsCommented:
And is it getting called.
0
 
Andrei RodionovAuthor Commented:
Yes, getSQLTypeName() is getting called.
Should I get my DB object from Oracle in order to pass my Java object back to Oracle?
Or my SQLData implementation is incomplete?
0
 
Andrei RodionovAuthor Commented:
Yes! It works!
Thanks a lot!
0
 
objectsCommented:
No worries :-)
Thanks for the points.

http://www.objects.com.au/staff/mick
Brainbench MVP for Java 1
http://www.brainbench.com
0
All Courses

From novice to tech pro — start learning today.