Get return value (scalar), not result table, from stored procedure using Java and Hibernate

Question

I'm having a hard time getting the return value (integer) from a stored procedure using Hibernate and Java.

My stored procedure is as follows:

create proc dbo.CheckEquipmentAuthorization
    @ReplicaId int
as
    declare @IDAuthType int
    select @IDAuthType = AuthorizationType.IDAuthorizationType from AuthorizationType
    inner join ReplicaAuthorization on ReplicaAuthorization.RefIDAuthorizationType = AuthorizationType.IDAuthorizationType
    inner join Replica_ReplicaAuthorization on Replica_ReplicaAuthorization.RefIDAuthorization = ReplicaAuthorization.IDAuthorization
    inner join Replica on Replica.IDReplica = Replica_ReplicaAuthorization.RefIDReplica
    where Replica.IDReplica = @ReplicaId
    and GETDATE() between ReplicaAuthorization.AuthBegin and ReplicaAuthorization.AuthEnd

    declare @AuthIntValue int
    set @AuthIntValue = 10
    if (@IDAuthType is not null)
    begin
        select @AuthIntValue = AuthorizationType.IntValue from AuthorizationType
        where AuthorizationType.IDAuthorizationType = @IDAuthType
    end
    print @AuthIntValue
    return @AuthIntValue

I'm trying to get the return value using:

    query = session.createSQLQuery(
    "exec CheckEquipmentAuthorization(:replicaId)")
    .setParameter("replicaId", replicaId);

But it seems I can only get a result table using this, and since no result table is generate from my procedure, nor do I want one to be, it fails.

Is there a way to get that returned value using that createSQLQuery() method?

I'm using Hibernate, Java and SQL Server. The stored procedure is working correctly (I have tested it with a sql client).

Thank you.

I added my final working solution below to help anyone that has this same problem. — lm2s, May 15 '12 at 21:47

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

This seems to be a duplicate of this. Have you mapped a scalar return type of your stored procedure? And are you translating into that return type?

edited May 23 '17 at 12:06

Community

1
1

answered May 13 '12 at 18:33

Nathaniel Ford

20,545
20
91
102

I don't believe it is duplicated, I'm trying to get the returned value not the result table. I can't find anything on this subject, only on the result table. What I have found is that usually the returned value is the number of rows in the result table, and it's that returned value that I want to get. It seems really odd that something so trivial can be so hard to do. – lm2s May 14 '12 at 10:48
The correct names are Return Code and Result Set, I want the Return Code. (http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx) – lm2s May 14 '12 at 11:17
Have you defined a result class? Per the linked post/answers there has to be a class you're returning the stored procedure into. Otherwise, you should just get the jdbc connection from the session factory and execute the stored procedure normally. – Nathaniel Ford May 14 '12 at 17:40
I've decided to go with a custom table mapping, and instead of using RETURN @ RetVal, I'll use SELECT @ RetVal AS RetVal and grab it from that table. It just feels odd that there is no quick and easy way to get the Result Code of a query. It's always an Integer, so why there isn't a getResultCode() method in Query leaves me a bit perplex. Thanks for your help :) – lm2s May 14 '12 at 18:01

score 2 · Answer 2 · answered May 15 '12 at 21:46

In the stored procedure, I replaced

return @AuthIntValue

with

select @AuthIntValue as RetVat

The way I call the stored procedure and transform the result into an object I created.

StoredProcResult o = (StoredProcResult)session.createSQLQuery("exec CheckEquipmentAuthorization :replicaId")
.addScalar("retVal", Hibernate.INTEGER)
.setParameter("replicaId", replicaId)
.setResultTransformer(Transformers.aliasToBean(StoredProcResult.class))
.setCacheMode(CacheMode.GET)
.uniqueResult();

int xpto = o.getRetVal();

The StoredProcResult object:

public class StoredProcResult {
    public int retVal;

    public int getRetVal() {
        return retVal;
    }

    public void setRetVal(int retVal) {
        this.retVal = retVal;
    }
}

score 0 · Answer 3 · answered Jun 26 '20 at 03:59

I know this is very old, but I just found myself faced with the same problem and found a simple solution when working with MS SQL Server. Modify your original query as follows:

query = session.createSQLQuery(
    "declare @result int; exec @result = CheckEquipmentAuthorization(:replicaId); select @result")
    .setParameter("replicaId", replicaId);

Now you get the return code as the query.uniqueResult() without the need to modify the original stored procedure. This can be handy when you aren't able (or don't want) to modify the oriignal stored procedure.

Get return value (scalar), not result table, from stored procedure using Java and Hibernate

3 Answers3