CODEWITHSUNDEEP
javahibernatejpa
FreshMike
FreshMike

Reputation: 551

@NamedStoredProcedureQuery not working with Hibernate 5.1.x and JPA2.1

I seem to have a strange issue I can't seem to resolve when using Hibernate, JPA 2.1 and Sql Server 2008R2. Hope anyone could guide me into the right direction. I'm not even sure if this is a bug or an issue with the configuration. 

    @Entity
@NamedStoredProcedureQuery(
        name = "getProc", 
        procedureName = "proc_get_country_of",
        resultClasses = { ProcGetCountryOf.class }, 
        parameters = {
            @StoredProcedureParameter(name = "a_currency", type = String.class, mode = ParameterMode.IN),
            @StoredProcedureParameter(name = "b_currency",type = String.class, mode = ParameterMode.IN)
        })

public class ProcGetCountryOf implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -8655437017378046511L;
    @Id
    @Column(name = "country_of")
    private String country_of;

    public String getCountry_of() {
        return country_of;
    }

    public void setCountry_of(String country_of) {
        this.country_of = country_of;
    }
}

Now I'm trying to call the Store procedure like this: 

    try {

         EntityManagerFactory emf = Persistence.createEntityManagerFactory("mcm-jpa");
            EntityManager em = emf.createEntityManager();

            StoredProcedureQuery sp = em.createNamedStoredProcedureQuery("getProc")
                .setParameter("a_currency", "USD")
                .setParameter("b_currency", "CHF");
            sp.execute();

            ProcGetFxoCountryOf res = (ProcGetFxoCountryOf) sp.getSingleResult();
            System.out.println(res);

            em.close();
            emf.close();

    } catch (Exception e) {
        e.printStackTrace();
        return e.getMessage();
    }
    return "sucess";

Unfortunately this seems not to work as I'm getting the following exception: 

[8/3/17 7:21:41:054 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,054 HHH000204: Processing PersistenceUnitInfo [
        name: mcm-jpa
        ...]
[8/3/17 7:21:41:116 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,115 HHH000412: Hibernate Core {5.1.7.Final}
[8/3/17 7:21:41:117 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,117 HHH000206: hibernate.properties not found
[8/3/17 7:21:41:118 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,118 HHH000021: Bytecode provider name : javassist
[8/3/17 7:21:41:149 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,149 HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
[8/3/17 7:21:41:240 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,240 HHH000400: Using dialect: org.hibernate.dialect.SQLServer2008Dialect
[8/3/17 7:21:41:780 EDT] 00000d3a SystemOut     O   INFO  2017-08-03 07:21:41,780 HHH000397: Using ASTQueryTranslatorFactory
[8/3/17 7:21:41:972 EDT] 00000d3a SystemOut     O   Hibernate: {call proc_get_fxo_country_of(?,?)}
[8/3/17 7:21:42:166 EDT] 00000d3a SystemOut     O   WARN  2017-08-03 07:21:42,166 SQL Error: 0, SQLState: S1093
[8/3/17 7:21:42:167 EDT] 00000d3a SystemOut     O   ERROR 2017-08-03 07:21:42,167 Parameter a_currency was not defined for stored procedure proc_get_fxo_country_of.

The Store Procedure definitely has these attributes: 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[proc_get_fxo_country_of]

@a_currency varchar(3),
@b_currency varchar(3)

as
begin
...
end

Upvotes: 1

Views: 601

Answers (1)

FreshMike
FreshMike

Reputation: 551

It was a permission issue on the Proc, unfortunately the error message doesn't show that and is misleading in this case.

Upvotes: 1

Related Questions