JPA -SQL Server Stored Procedure ?

Question

In Sql server stored procedure i have insert statement while calling from JPA using below code :

em.createNativeQuery("{call dbo.sp_xxx(:v1,:v2)}");
finQuery.setParameter("v1", 123); 
finQuery.setParameter("v2", "durga");
finQuery.getResultList();

In my stored procedure

ALTER PROCEDURE [dbo].[sp_xxxx](
@v1 int,
@v2 nvarchar(10)
)
AS

BEGIN TRANSACTION   
BEGIN         

    INSERT INTO dbo.XXXX
           (v1,v2)
        VALUES
           (@v1,@v2)     
COMMIT TRANSACTION 
END

After executing the stored procedure

Exception sp_xxxx...javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query.

Iam able to run in sql server window. Bur from JPA iam getting above error.

Did you get a chance to look at this: http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa? — Vikdor, Sep 01 '12 at 15:27
we are able to get list . when we are using insert stmt in stored procedure. iam getiing above error. — Esh, Sep 01 '12 at 15:31
sorry,didn't understand when you said `we are able to get list`? Do you mean the finQuery.getResultList() is returning you the list? — Vikdor, Sep 01 '12 at 15:32
i dont need any result set. Iam using SP for inserting records. To execute that Stored Procedure, we used getResultlist. finQuery.getResultList(); . But iam getting above error while calling from JPA — Esh, Sep 01 '12 at 15:40
instead of `getResultList()`, try `executeUpdate()` which would expect the SQL code to return update counts (the result of INSERT/UPDATE/DELETE operations). `getResultList()` would expect the SQL code to return result set (result of a SELECT operation). — Vikdor, Sep 01 '12 at 15:43

score 2 · Answer 1 · answered Sep 01 '12 at 15:59

2

Instead of getResultList(), try executeUpdate() which would expect the SQL code to return update counts (the result of INSERT/UPDATE/DELETE operations). getResultList() would expect the SQL code to return result set (result of a SELECT operation)

answered Sep 01 '12 at 15:59

Vikdor

23,934
10
61
84

score 1 · Answer 2 · edited Feb 02 '15 at 08:47

1

Try this code snippet. Note: I'm using SQL Server 2005 and OpenJPA

Query query = entityManager.createNativeQuery("EXEC ps_GeneraSecuenciaClaveCentrales ?, ?, ? ");
query.setParameter(1, "VE");
query.setParameter(2, new Integer(48));
query.setParameter(3, new Integer(42));
String res = (String)query.getSingleResult();

edited Feb 02 '15 at 08:47

Tchami

4,647
1
32
45

answered Feb 28 '13 at 22:46

Miguel

11
1

score 0 · Answer 3 · edited Feb 02 '15 at 09:05

0

Try this:

finQuery = em.createNativeQuery("exec sp_xxx :v1,:v2");
finQuery.setParameter("v1", 123);
finQuery.setParameter("v2", "durga");
finQuery.getResultList();

edited Feb 02 '15 at 09:05

Tombart

30,520
16
123
136

answered Jan 31 '13 at 16:21

sertan

1
1

JPA -SQL Server Stored Procedure ?

3 Answers3