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()
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.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.sql.StructDescripto
at oracle.sql.STRUCT.toSTRUCT
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
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.
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()
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.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.sql.StructDescripto
at oracle.sql.STRUCT.toSTRUCT
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
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.
ASKER
> 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.
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.
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?
So have you confirmed that writeSQL is not getting called?
ASKER
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.
"...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.
WHat does getSQLTypeName() return?
ASKER
Damned! It's null!
And is it getting called.
ASKER
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?
Should I get my DB object from Oracle in order to pass my Java object back to Oracle?
Or my SQLData implementation is incomplete?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes! It works!
Thanks a lot!
Thanks a lot!
No worries :-)
Thanks for the points.
http://www.objects.com.au/staff/mick
Brainbench MVP for Java 1
http://www.brainbench.com
Thanks for the points.
http://www.objects.com.au/staff/mick
Brainbench MVP for Java 1
http://www.brainbench.com
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.