?
Solved

JDBC custom mapping

Posted on 2003-02-27
11
Medium Priority
?
352 Views
Last Modified: 2008-03-10
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.
0
Comment
Question by:AndrewRodionov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 92

Expert Comment

by:objects
ID: 8037283
> 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
 
LVL 2

Author Comment

by:AndrewRodionov
ID: 8039391
> 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
 
LVL 92

Expert Comment

by:objects
ID: 8039426
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:AndrewRodionov
ID: 8039470
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
 
LVL 92

Expert Comment

by:objects
ID: 8039559
WHat does getSQLTypeName() return?
0
 
LVL 2

Author Comment

by:AndrewRodionov
ID: 8039571
Damned! It's null!
0
 
LVL 92

Expert Comment

by:objects
ID: 8039597
And is it getting called.
0
 
LVL 2

Author Comment

by:AndrewRodionov
ID: 8039619
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
 
LVL 92

Accepted Solution

by:
objects earned 800 total points
ID: 8039630
> Or my SQLData implementation is incomplete?

Its incomplete, getSQLTypeName() should return your SQL type name.
0
 
LVL 2

Author Comment

by:AndrewRodionov
ID: 8039656
Yes! It works!
Thanks a lot!
0
 
LVL 92

Expert Comment

by:objects
ID: 8039687
No worries :-)
Thanks for the points.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question