how to call a sql server function from hibernate with return parameter?

Question

I have a function in SQL server and I want to call it from hibernate. I have tested these 2 methods but no one was successful. First method:

Session sixSession=HibernateUtil.getSessionFactory().openSession();
Query q2=sixSession.createQuery("from dbo.old_remaining(?)").setParameter("paymentVcode", p_Vcode);
        q2.getNamedParameters();
        List list=sixSession.getNamedQuery("{dbo.old_remaining(?)}").setString(1,"p_Vcode").list();

dbo.old_remaining is my function and p_Vcode is an int

the error is:

unexpected token: ( near line 1, column 23 [from dbo.old_remaining(?)]

Second method:

Float var;
List li=session.getNamedQuery("{dbo.old_remaining(?)}")  
    .setString(1, var).list();

The error is: org.hibernate.MappingException: Named query not known: {dbo.old_remaining(?)}

Please Help me...

alexey28 · Accepted Answer · 2012-05-23 10:50:01Z

1

Can propose one work around based on CallableStatement. This standard jdbc statement to call functions:

session.doWork(new Work() {
    public void execute(Connection connection) throws SQLException {
        CallableStatement callable = connection.prepareCall("{? = call dbo.old_remaining(?)}");
        callable.registerOutParameter( 1, Types.FLOAT );
        callable.setString(2, "your string parameter");
        callable.execute();
        float functionResult = callable.getFloat(1);
    }
});

edited May 23, 2012 at 10:50

answered May 23, 2012 at 6:38

alexey28

5,2381 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AFF Over a year ago

Error invoking Action using Hibernate Core Session / Transaction injection com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '{'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:213)

alexey28 Over a year ago

Yes, change it: "{? = call dbo.old_remaining(?)}". Foget add call.

Snake Eye · Accepted Answer · 2012-05-23 11:02:28Z

0

try following code

session.doWork(new Work() {
    public void execute(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("SELECT dbo.old_remaining(?)");
        pstmt.setString(1,p_Vcode);
        ResultSet rs = pstmt.executeQuery();
        while( rs.next() )
        System.out.println( rs.getFloat(1) ) ;
    }
});

answered May 23, 2012 at 11:02

Snake Eye

5351 gold badge3 silver badges13 bronze badges

Collectives™ on Stack Overflow

how to call a sql server function from hibernate with return parameter?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related