Hibernate Stored Procedure Results

Question

I am forced to manage an auto-increment field from code. I've decided to write a stored procedure to do this for me. The idea is to have a procedure that inserts a new row and returns the auto-incremented value to Java so I can work with it further. The following is what I have so far. I don't know what to change to fill the gaps to make it all work.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE addNewMSO
    @sourceApplication char(8), 
    @selectionStatusDate datetime = NULL,
    @sysLstUpdtUserId char(10)
AS
BEGIN
    SET NOCOUNT ON;

    Declare @newVal int
    SET @newVal = (select max(seqNo) from MemberSelectedOptions) + 1

    INSERT INTO MemberSelectedOptions
       ([SourceApplication]
       ,[SeqNo]
       ,[SelectionStatusDate]
       ,[SysLstUpdtUserId])
 VALUES
      (@sourceApplication
       ,@newVal
       ,@selectionStatusDate
       ,@sysLstUpdtUserId)

END
GO

Then in my Java code I have the following.

@NamedNativeQuery(name="addNewMSO", 
  query="exec addNewMSO :sourceApplication :selectionStatusDate :sysLstUpdtUserId", callable=true)

And my DAO calls it like this...

Query q = session.getNamedQuery("addNewMSO");
q.setParameter("sourceApplication", mso.getSourceApplication());
q.setParameter("selectionStatusDate", mso.getSelectionStatusDate());
q.setParameter("sysLstUpdtUserId", mso.getSysLstUpdtUserId());
q.executeUpdate();

Even then, this doesn't work, cause I get a "Pure native scalar queries are not yet supported" error. So I have no idea where to go next. I've read through the Hibernate docs, but I don't know how to get a return value from the SP. But I still can't even call it...ugh!

TIA for all help.

Answer 1

If your SP doesn't return a result set, then I'm afraid you'll have to use session.getConnection() and raw JDBC. According to Hibernate's documentation:

16.2.2. Using stored procedures for querying

Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. (...)

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

(...)

For Sybase or MS SQL server the following rules apply:

• The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.
• If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Answer 2

Disclaimer : I'm in NO WAY familiar with HIBERNATE, nor Java, but from what I read above you seem to need to have at least a single resultset for this to work !? Given the SQL-2005 tag I'm assuming your code is in T-SQL, hence I'd like to point out that your code is 'flawed' in that respect that it MIGHT (yes, very small chance, but still) happen that another connection inserts a new record into the table between the moment you select @newval and the moment you do the actual insert. This can be solved by using locking, or IMHO more simply by putting the INSERT and SELECT in a single statement. Adding an ISNULL() will also make sure that if you ever need to restart with an empty table the stored procedure will still function as expected even though it has no starting value to add to.

CREATE PROCEDURE addNewMSO
@sourceApplication char(8), 
@selectionStatusDate datetime = NULL,
@sysLstUpdtUserId char(10)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO MemberSelectedOptions
   ([SourceApplication]
   ,[SeqNo]
   ,[SelectionStatusDate]
   ,[SysLstUpdtUserId])
OUTPUT (inserted.[SeqNo])
 SELECT [SourceApplication]   = @sourceApplication,
    [SeqNo]               = ISNULL((SELECT max([SeqNo]) FROM MemberSelectedOptions), 0) + 1,
    [SelectionStatusDate] = @selectionStatusDate,
    [SysLstUpdtUserId]    = @sysLstUpdtUserId
END

The OUTPUT clause will return the newly inserted value in a recordset which, if I understand the HIBERNATE limitations correctly, help you out both on what's supported and what's needed.