Gavin, thanks a lot for the prompt responses. I really appreciate it.

OK, Now I've got it to use a Stored Proc for the load.
And it works!!
Just want to make sure I'm not breaking something else in the process.

This is what I did
1) I created my own copy of the Loader class, MyLoader. In the doFind () I replaced the following section of code
final PreparedStatement st = prepareQueryStatement(
applyLocks( getSQLString(), lockModes, session.getFactory().getDialect() ),
values, types, namedParams, selection, false, session
);
final ResultSet rs = getResultSet(st, selection, session);

WITH

CallableStatement st = null;
ResultSet rs = null;
st = session.connection().prepareCall("{call ? := ...}");
st.registerOutParameter(1, OracleTypes.CURSOR);
st.setObject(2, optionalID);
st.execute();
rs = (ResultSet)st.getObject(1);


2) I created my own copy of the SimpleEntityLoader - MySimpleEntityLoader (extending MyLoader). In this I removed the member variable 'sql' and removed the sql argument from the constructor.

3) In my Custom Persister this is what I put in the load method
return new MySimpleEntityLoader (this, lockMode) .load(session, id, optionalObject);

Questions
-----------
1) In step 1 above when i replaced the prepared statement with the Callable Statement I'm ignoring the following parameters that the existing prepared statement uses in some way

lockmodes, values, types, namedparams, selection.

I basically just use the optionalId to load the specific instance.

What is the impact of this?

2) The doFind is being called from the load () in the CutomPersister in this scenario.
Does something else also call the doFind () method in a way that would break my code because I'm ignoring some parameters when i create my callable statement and do a select from the database for the given id.

I apologize for my long drawn detailed questions. Based on my experience with it so far, I'm very inclined to propose Hibernate as a persistence solution for a fairly large govenment project I'm working on. My questions are such, because i'm in the process of implementing a proof of concept which would mitigate the biggest risks.

Thanks
jaideep

What I suggest you do is produce a patch for Hibernate that changes Loader so that you can override just one method.

Now, why would it not be enough to simply override prepareQueryStatement() ? (CallableStatement extends PreparedStatement) Why did you need to also override getResultSet() ?

Is it ONLY because prepareQueryStatement() is declared final?

If so, is it enough to just change this line:



in prepareQueryStatement() so that it calls another (nonfinal) method? Then you would only need to override that.





That stuff is used for queries.




It is probably better style to implement a load() method on the Loader that calls up to doFind() on the superclass.





You have to guide me here, because I've never triued to implement this myself.

The getResultSet() also needs to be overriden since the way in which the jdbc resultset is extracted from the statement is different as compared to the scenario where i'm just using a prepared statement. (At least with oracle, it may be different with sybase, sqlserver etc.). So, there could be some proprietory-ness with the way the resultset is obtained.

What if i make the prepareQueryStatement () getResultSet () non-final?

If that works that what i can do is in my extension of the Loader just override those two methods (I think this is what you were suggesting.).

If this works, how do i send you the patch?

Thanks

Jaideep

How is it different?


Is this some Oracle non-JDBC-compliant thing?

oh. is it cos of registerOutParameter() ?


hmmmm.

Yes, Also i'm using the statement.execute() not executeQuery (). (I'm not absolutely sure though if executeQuery will also work, i can try this). Moreover, the execute() does not return a result set, i have to get the resultset by doing the following.

rs = (ResultSet)st.getObject(1);

after the execute call.

thanks

Jaideep

Hmmm. This is not really the way it should work, I think .... you *should* just be able to call executeQuery(), and then you would get back a normal ResultSet ... it would look the same as with a PreparedStatement.


This may be some Oracle bug, though.

This is probably because unlike SQlServer or Sybase where you can 'return' a resultset from a stored procedure, in Oracle you have to send back the result set as an 'out' parameter.

Jaideep

Hi,

Could you please send me your CustomPersister class?

I also need to be able to call stored procedures from Hibernate.

Thanks,
-bnl

Hi jaideep ganguli,

Can I know more about how u managed to get the stored procedure working.

Thanks

-Gana.

Hi Jaideep,
I am not sure if you have already posted the code somewhere. But incase if you haven't, could you post the code for your CustomPersistor ?. We all are trying to do the same here with Store Procedure, but you seem to have got it working. So, could you ?.

Appreciate your help.

thanks.