Does hibernate 3 supports "read" in Stored procedure. I know that hibernate has provided support for Stored procedures in 3.0, but is it only for <sql-create callable = true> <sql -update callable = true> and <sql-delete callable = true>.
but what about 'Read' how can I use a stored procedure to read from the DB2 database ?

any help is appretiated .

Thanks

Currently we haven't implemented read support for Stored procdures, primarily because the different vendors require different code for doing it.

The best option at the moment is to write a custom persister/loader for it.

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#execute(java.lang.String)



This is standard way, you need trivial adapter if driver provider fails to implement it.

AFAIK Oracle doesn't support that one - that's the problem ;)

and the adapter is not trivial for oracle as one has to call a method on a native oracle class - and we don't wanna have code in H2 dependent on oracle jdbc.jar

Yes, do not add any dependancies on drivers, just add pluggable adapter factory:

interface ConnectionAdapterFactory {

Connection getAdapter(Connection cnn);

}

default implemantation can be

Connection getAdapter(Connection cnn){

return cnn;

}

It must be better than to fork pool implementations or depend on drivers,
it will work for user provided connections too.

well - yes, but the reason i haven't done this yet is that returning resultset's from stored procedures is done AFAIK 3 different ways...standard, db2 and oracle....and all three vary in different aspects in the 3 areas: how to prepare the callable statement, how to execute and how to retreive the result from it....and it should be done so it is compatible with executing normal sql.....

...and yes, an adapter could be written - and probably will - but it is not the primary focus right now ;) ....patches are welcome!

As I understand callable statements are important for "out" parametrs only, but this is valid way to call stored procedure too:

PreparedStatement ps = con.prepareStatement( "{call myProc(?)}" );

if(ps.execute()){
ps.getResultSet();
}

"{call ? = myProc(?)}" this must return result set as "out" parameter in callable statement.

Probably you need to define adapter conventions for hibernate too, it can not be clear from JDBC and ODBC specifications how to implementa adapter for hibernate (adapter interface doe's not need to be JDBC connection too, I think it is better to define custom adapter interface )

yes - but "{call ? = myProc(?)}" is exactly the syntax (at least) oracle want for returning something (here a ResultSet) from a SP.

I hope it is possible use stored procedure as "inline view"
SELECT * FROM myProc(?,?) in oracle too, but probably it is not so important, user can implement adapter himself.

hmm .... i haven't seen that kind of syntax suggested before...is it as effiecient as { call ? = myView(x,y) } ?

I have not tried it on oracle, it works on postgresql, but most databases suppor inline views. I know oracle supports inline views, but I am not sure it can be a procedure result. It must be better to try it, but I do not have oracle instance at this time.

If there are more than one way to return a result set from a stored proc, don't you think you need something similar to SQL Dialect,i.e. StoredProc Dialect, ?

In my opinion, there are 2 types of return when executing a stored proc :
1. return a non-result-set, where the last SQL statement is not a SQL SELECT statement.
2. return a result-set, where the last SQL statement is a SQL SELECT.

I could be wrong ...

nusa

Are you sure ?
Download the Oracle JDBC demo file ( demo.tar ), untar it, then have a look these files : samples/generic/PLSQL*.java

nusa.

hmm - could you give me a link to that demo.tar ?