I need to call the following stored procedure to generate a unique identifer. Is there a way I can call this directly from the mapping files.


/*
* Stored procedures to generate unique keys
*/

create procedure opc_get_key (@keyname char(10),
@newvalue int = -1 output)
as
BEGIN TRANSACTION

UPDATE unique_keys
SET keyvalue = keyvalue + 1
WHERE keyname = @keyname

SELECT @new
value = keyvalue
FROM unique_keys
WHERE keyname = @keyname

SELECT @newvalue

COMMIT TRANSACTION

return

Hi. Are you using Hibernate 3?

If you are, see instructions and rules of use in the online reference (section 16.3.2 "Using stored procedures for querying") It explains how you can use in your mapping to map to a stored procedure ...... http://www.hibernate.org/hib_docs/v3/re ... /#sp_query


I hope this helps.

the online reference seems like doesn't help much in calling the stored procedure as the example just showing how to configure the <sql-query> for stored function but not procedure, so could anyone can ellaborate more on how to calling the stored procedure?

Besides, as mentioned in the referene, "The stored procedure/function must return a resultset as the first out-parameter".
Does that mean it is only return cursor type instead of string or integer for all type of databases such as MS SQL server, Oracle and MYSQL???

As what I know there is no way for MYSQL stored procedure/function to return cursor type, is there any turn around solution on this?

Sorry for I suppose to answer the question here but it seems I was getting confuse and confuse after read through the reference and now seeking for help from other.

Hope someone can help, thanks in advanced.

Did you identify a way to use <sql-query> to invoke an Oracle Stored Procedure which does NOT return a ref cursor, but a scalar value instead?