CODEWITHSUNDEEP
javasql-serverhibernate
user3449214
user3449214

Reputation: 73

Calling a SQL sever stored procedure using hibernate

I am trying to call SQL Server stored procedure using createSQLQuery method in hibernate ,but getting exception.please help as i am new to hibernate.

Exception:

   org.hibernate.exception.SQLGrammarException: could not execute query

SQL Server Stored Procedure-

  CREATE PROCEDURE [dbo].[sqlServerProc]
  (@SOURCE_AID NUMERIC(18,0),
  @DESTI_BID NUMERIC(18,0),
  @E_CID NUMERIC(18,0),
  @MOVE_DID BIT,
  @RST_ADDED VARCHAR(MAX) = '' OUTPUT,
  @LST_ADDED VARCHAR(MAX) = ''  OUTPUT,
  @PST_ADDED VARCHAR(MAX) = '' OUTPUT,
  @IS_VALUE BIT = 0 OUTPUT
 )

HIBERNATE CODE:

 public String Function(String AId,String BId,String CId,String DID ) throws Exception
{ 

try{
            session = getSessionFactory().openSession();
            session.beginTransaction();
            Query query = session.createSQLQuery(
            "CALL sqlServerProc(:AId,:BId,:CId,:DID)").setParameter("AId",  AId).
            setParameter("BId", BId).
            setParameter("CId", CId).
            setParameter("DID", DID).
            ;

            ListOfEnvData=  query.list();

            for (Object[] aRow :ListOfEnvData) {

                String a = (String) aRow[0];
                String b = (String) aRow[1];
                String c = (String) aRow[2];
                String d = (String) aRow[3];
                System.out.println("a"+a+"b"+b+"c"+c+"d"+d);
            //  value=d;
                break;
             }


          }catch(Exception e){
              session.close();
              e.printStackTrace();

          }}

Upvotes: 3

Views: 3044

Answers (1)

Dherik
Dherik

Reputation: 19060

Try:

Query query = em.createNativeQuery("EXECUTE sp_log :objectName, :log, :additionalInfo");
            query.setParameter("objectName", name);
            query.setParameter("log", log);
            query.setParameter("additionalInfo", info);
            query.executeUpdate();

But, you can do this without Hibernate, like:

public void callMyProc(SomeEntity someEntity) {
        Connection connection = null;

        try {
            connection = getConnection();
            CallableStatement cstmt = connection.prepareCall("{call dbo.my_procedure(?)}");
            cstmt.setLong(1, someEntity.getId());
            cstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection);
        }
    }

Upvotes: 3

Related Questions