• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA and MySQL Stored Procedure

 
Marco Di Baggio
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,

In an attempt to learn something new I am try to understand how to call a mysql
stored procedure using JPA. I created a simple copyTable mysql stored procedure.


The following code does not appear to do anything or work. I was wondering if
anyone code spot my problem. I have read and search everywhere but
nothing seems to help my cause:

Here is the sample code:



Any help would be greatly appreciated.
Thanks
 
Rob Spoor
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us the stored procedure code?

I'll move this thread to our ORM forum.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How do you know it does not do anything or work? What error do you get? Is the procedure called?
The row count may not be returned correctly for a stored procedure, so don't use that to verify if it was called.

What JPA provider are you using? If you are using EclipseLink, you could try using a @NamedStoredProcedureCallQuery, or a StoredProcedureCall object.

 
Marco Di Baggio
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank-you for the reply.

I can certainly share my stored procedure. I must mention that it does work perfectly
well with a straight JDBC call to it (using a callable statement), so I can't see the my
problem being with the stored procedure.

Stored Procedure:


In answer to your question, I am not using the log output as my flag to determine its not working. I simply
know its not working because, truthfully the table does not get copied over when I make the call with the JPA
code pasted above. However, no errors pop-up either.


Thanks James, I am using EclipseLink ... unfortunatley I was afraid someone was gonna mention @NamedStoredProcedureCallQuery,
or a StoredProcedureCall. I tried my hardest to understand them but my efforts to find an example that related
to stand alone Java Program turned limited.

I have no objections using @NamedStoredProcedureCallQuery, or a StoredProcedureCall just need some coaching
... I believe a session object is required, but I am not, or lack the understanding of how to define one.

Thanks once again everyone




 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If truly no errors are occurring then it is working, but more likely you are ignoring or handling the error.
I could also be that it is not executing on the database you think it is, or in a transaction context and you are rolling back the transaction.

Turning logging on finest, and inspect the log.

For an example of using @NamedStoredProcedureCall or StoredProcedureCall see,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures

You do not need anything special to call them, you can call the query the same as an other JPA named query.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic