Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Retrieve storedprocedure messages using JPA
Retrieve storedprocedure messages using JPA [message #782392] Mon, 23 January 2012 16:35 Go to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hello All:

I have a stored procedure which prints messages. I am using JPA named query to execute the same. However, I could not find a construct within JPA that will allow me to capture the messages from the stored procedure.

Here is my sample procedure:
********************************
create proc test
@text varchar(255)
AS
declare
@col int
SELECT @col=col
FROM table
WHERE text=@text

IF @@error != 0 BEGIN
print 'Invalid text'
return -100
END
return 1
go
********************************

How do I get the message 'Invalid text' using JPA?

I appreciate all your help.

Thanks
Sridhar
Re: Retrieve storedprocedure messages using JPA [message #783863 is a reply to message #782392] Thu, 26 January 2012 15:48 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What database are you using? I don't thin print is in any way returned, it just prints something on the server. You need to either throw an error, or return an error message using an output parameter.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/


James : Wiki : Book : Blog : Twitter
Re: Retrieve storedprocedure messages using JPA [message #783960 is a reply to message #783863] Thu, 26 January 2012 20:51 Go to previous messageGo to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hi James,

We use Sybase, SQLServer and Oracle. We have the same stored procedure on all these db vendors printing messages based on various action codes. In our legacy application, we were able to get these messages using the appropriate db calls like ct_diag() for Sybase using CtLib or SQLGetDiagRec for MSSQL using ODBC.

We need a similar mechanism for JPA to retrieve the messages from the DB Server. As you may have noticed, printing messages within sotred procedures is supported in most DB vendors. On Oracle, it is via dbms_output.put_line and on MSSQL/Sybase, it is via the print statement.

Thanks
Sridhar
Re: Retrieve storedprocedure messages using JPA [message #786533 is a reply to message #783960] Mon, 30 January 2012 14:40 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

If you have database functions that you can call to return the output, then you can call these from EclipseLink. This will be a separate call, as it is a separate function.


James : Wiki : Book : Blog : Twitter
Re: Retrieve storedprocedure messages using JPA [message #786534 is a reply to message #783960] Mon, 30 January 2012 14:40 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

If you have database functions that you can call to return the output, then you can call these from EclipseLink. This will be a separate call, as it is a separate function.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/


James : Wiki : Book : Blog : Twitter
Re: Retrieve storedprocedure messages using JPA [message #786689 is a reply to message #786534] Mon, 30 January 2012 18:11 Go to previous messageGo to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hi James,

These are not database functions. These are the native database programming API similar to JPA. Do you think this is an enhancement request to JPA?

Please advise.

Thanks
Sridhar
Re: Retrieve storedprocedure messages using JPA [message #789202 is a reply to message #786689] Thu, 02 February 2012 18:27 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I think in JDBC these may be accessible from statement.getWarnings().

EclipseLink will log these warnings to the SessionLog if logging is on finest, so you can check this.

If you need access to these other than through logging, then you should probably use JDBC directly.
It may be possible for EclipseLink to provide access to them through some sort of event, if you log an enhancement for it.


James : Wiki : Book : Blog : Twitter
Re: Retrieve storedprocedure messages using JPA [message #789206 is a reply to message #786689] Thu, 02 February 2012 18:27 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I think in JDBC these may be accessible from statement.getWarnings().

EclipseLink will log these warnings to the SessionLog if logging is on finest, so you can check this.

If you need access to these other than through logging, then you should probably use JDBC directly.
It may be possible for EclipseLink to provide access to them through some sort of event, if you log an enhancement for it.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/


James : Wiki : Book : Blog : Twitter
Re: Retrieve storedprocedure messages using JPA [message #790060 is a reply to message #789206] Fri, 03 February 2012 19:15 Go to previous message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Thanks James.
Re: Retrieve storedprocedure messages using JPA [message #790062 is a reply to message #789202] Fri, 03 February 2012 19:15 Go to previous message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Thanks James.
Previous Topic:MOXy: xml-mapping-metadata-complete problem
Next Topic:MOXy: <xml-attribute> and JSON
Goto Forum:
  


Current Time: Fri Sep 19 07:57:25 GMT 2014

Powered by FUDForum. Page generated in 0.04018 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software