CODEWITHSUNDEEP
javasql-serverhibernatestored-proceduresreturn-value
lm2s
lm2s

Reputation: 388

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

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.

Upvotes: 2

Views: 7726

Answers (3)

Ron DeSantis
Ron DeSantis

Reputation: 935

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.

Upvotes: 0

lm2s
lm2s

Reputation: 388

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;
    }
}

Upvotes: 2

Nathaniel Ford
Nathaniel Ford

Reputation: 21220

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?

Upvotes: 2

Related Questions