New to Hibernate and coding is a bit confusing. We do everything via Stored Procedures; in our case, against Sybase.

I am writing code to invoke a SP (via named query) and have set the parameters. I need to get the values for the last three output parameters:

public List usersFind (String byCN, String byFirstName, String byLastName, String byEmplId,
int sqlError, int sqlRowCount, String sqlMessage) {
List lo_result = null; Query lo_query = null;

// create a new object to work with
lo_query = getSession().getNamedQuery(CS_NQ_USERS_FIND);
// set the parameters
lo_query.setString (CS_SP_PARM_USER_CN, byCN);
lo_query.setString (CS_SP_PARM_USER_FN, byFirstName);
lo_query.setString (CS_SP_PARM_USER_LN, byLastName);
lo_query.setString (CS_SP_PARM_USER_EID, byEmplId);
// output parms
lo_query.setParameter(CS_DB_SP_PARM_SQLERR, null);
lo_query.setParameter(CS_DB_SP_PARM_SQLROWCOUNT, null);
lo_query.setParameter(CS_DB_SP_PARM_SQLMSG, null);

lo_result = (List)lo_query.list();

sqlError = lo_result.get

return lo_result;
} // usersFind

and I do not see a getString or getParamter off the Query class. How does one get at these parameters?

Hibernate supports output parameters? I didn't know that. If I was trying to do what you are, I'd use sql-query, and specify return-scalar tags for the three return values.

as per the docs: we dont support output parameters (yet)

Yet. Ah, ok. What's the time line on that? To where should I submit a request for enhancements or fixes?

We just got into the whole Java/Web environment and are investing heavily in it but after years with PowerBuilder and Sybase we find that JDBC's native support for SP's is adequate but others (like Hibernate) are horribly lacking.

The real power of a DBMS is in its SP language and we make exclusive heavy use of it, even to the point of restricting direct access to tables with the exception of text and blob fields since JDBC, and in our case Sybase, does not support those types of fields as SP parameters.

The Hibernate requirement that all SPs return a result set is as ridiculous as the statement that only the first result set will be used (the other will be discarded). That put unnecessary work on the programmer when doing detailed, mulit-sectioned reports from one SP.

I would not want to embed the SQL code necessary to do a year-end close in code when it could be handled much faster and more securely via SP code. Yes, that makes the application harder to port to other DB platforms, but the payoff is a more stable and faster application.

I can only hope that complete SP support is forthcoming at a fast pace and would be very willing to help where I can in facilitating that delivery. Should I copy this to another location?

i think you need to rethink what problem you are trying to solve with hibernate....stored procedure is not a natural fit to ORM.

But we do have support for it and it will improve, but calling it ridiculous is not very helpfull ;)

Remember that the stored procedure support is *just* for being able to return resultsets - not so much of getting single rows/values, for that raw jdbc is mostly adequate.

SP's can be used for returning 1 to n rows.

SP's are the core of our data security layer here. Any ad hoc tools users may use access data via views or SP's that they have been granted access to.

yes, and ?

I'm not saying using stored procedures is bad, i'm just saying that probably not all of your stored procedures make sense to use in context of hibernate. Either of the current limitation of hibernate or the fact that the data is not really mappable by their rowish/usecase-nature

It must be a good idea to generate safe JDBC code to call stored procedures using metadata ("java.sql.DatabaseMetaData.getProcedures()"). It can be interesting for hibernate tools project too.

contributins are welcome ;)

I like to code eclipse plugins, probably I will find time for this stuff.