89149

Retrieve the returned value from sql server stored procedure using java

Question:

Here is my store procedure that returns a value. I need to call this procedure and get that return value into my java program.

CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml AS begin declare @psrg_idi idi, @adv_cd cd, @CurrDate cdt set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor)) set @psrg_idi = 0 set @CurrDate = getdate() BEGIN TRY exec my_proc_2 @CurrDate,@psrg_idi output insert into ADVICE (psrg_idi, adv_cd, psra_original_xml) values (@psrg_idi, @adv_cd, @adv_xml) select @psrg_idi as psrg_idi END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); return -1 END CATCH end

Here is how I am trying to get this value into my java program returned by the above stored procedure. When I call this procedure from java, all the expected values are stored into database tables. But I am receiving the returned value groupId as '0'.. any idea or help will be highly appreciated.

CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}"); int i = 0; cs.registerOutParameter(++i, java.sql.Types.INTEGER); cs.setString(++i, advisor); cs.setString(++i, adviceXml); cs.execute(); int groupId = cs.getInt(1);

I have already reviewed the accepted answer <a href="https://stackoverflow.com/a/1948518/674476" rel="nofollow">https://stackoverflow.com/a/1948518/674476</a> . I am also trying in the same way, but somehow not able to get returned value

Answer1:

See accepted answer <a href="https://stackoverflow.com/questions/1947754/getting-the-return-value-from-jdbc-mssql" rel="nofollow">here</a>.

First, you should decide if your SP will return a ResultSet or not. I mean, when you catch an error, you do RETURN -1. But, if all works fine, you perform a SELECT, which returns a ResultSet.

Said that, if you choose to return a value, you have to do:

CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}"); int i = 0; cs.registerOutParameter(++i, java.sql.Types.INTEGER); cs.setString(++i, advisor); cs.setString(++i, adviceXml); cs.execute(); int groupId = cs.getInt(1);

But, if you return a ResultSet, you have to do:

CallableStatement cs = con.prepareCall("{call my_procedure (?,?)}"); int i = 0; cs.setString(++i, advisor); cs.setString(++i, adviceXml); ResultSet rs = cs.executeQuery(); if (rs.next()) int groupId = rs.getInt(1);

I've tested both methods using a toy SP:

CREATE PROCEDURE my_procedure @v1 int, @v2 int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --SELECT @v1 + @v2 as sumVal -- Comment this and uncomment previous line to test Return RETURN @v1+@v2 -- Comment this and uncomment previous line to test ResultSet END

Answer2:

Below is the solution to the problem and it works perfectly fine in my case.

CallableStatement cs = con.prepareCall("{call my_procedure(?,?)}"); int i = 0; cs.setString(++i, advisor); cs.setString(++i, adviceXml); boolean isRs = cs.execute(); int updateCount = cs.getUpdateCount(); // cs.getUpdateCount() will return -1 if the current result is a ResultSet object // or there are no more results // cs.getMoreResults() will return true if the next result is a ResultSet object; // false if it is an update count or there are no more results while (!isRs && (cs.getUpdateCount() != -1)) { isRs = cs.getMoreResults(); } if (isRs) { ResultSet rs = cs.getResultSet(); try { if (rs.next()) { groupId = rs.getString("psrg_idi"); } } finally { rs.close(); } }

Recommend

  • how to include .pl (PERL) file in PHP
  • T-SQL Move From Test To Production Dynamically
  • Date cannot be in the future SQL
  • Why Is My Azure SQL Database Table Permanently Locked?
  • JPA/Hibernate - Entity name seems to be important. If I rename to “Bob” works fine
  • What is the best way to debug Bootstrap.groovy?
  • What is this strange character in chrome's resource css viewer?
  • Application bar icon text length
  • How to use tag-it
  • read values from form post in jquery or javascript
  • Responsive left sidebar open close
  • ckeditor and jquery UI dialog not working
  • Prevent page break in text block with iText, XMLWorker
  • How to log in to an HTTPS website with Jsoup?
  • C++ friend class std::vector
  • How can I run DataNucleus Bytecode Enhancer from SBT?
  • Access user's phone number on iOS 7
  • SyntaxError: (irb):26: both block arg and actual block given
  • Servlet stops working on Tomcat server after some hits or time
  • Can't remove headers after they are sent
  • Redshift Querying: error xx000 disk full redshift
  • Scipy Leastsq Optional Output Variable (Mesg)
  • Bash if statement with multiple conditions
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • How to remove a SwiftyJSON element?
  • Eliminate partial duplicate rows from result set
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Keep this build forever option - Jenkins
  • Swift: Switch statement fallthrough behavior
  • iOS: Detect app start via notification press
  • Lost migrations and Azure database is now out of sync
  • How would I use PHP exceptions to define a redirect?
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass