Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

Calling stored procedure from JDBC - Unable to retrieve ResultSet (SELECT after INSERT)

I have a stored procedure like this one:

--

CREATE PROCEDURE example
  @arg1 VARCHAR(16),
  @subarg VARCHAR(16)
AS
  DECLARE @arg2 VARCHAR(16)

  SET NOCOUNT ON

  SELECT @arg2 = myvalue
  FROM table1
  WHERE myid = @subarg

  INSERT INTO table2 (arg1, arg2)
  VALUES (@arg1, @arg2)

  SELECT *
  FROM table2
  WHERE arg1 = @arg1
GO

--

The purpose is to do an insert and return the inserted row
in a single stored procedure.
Works fine with QA, but I can't get it to work with Java.
Here's an example of my java code:

--

statement = connection.prepareCall("{ CALL example( ?, ? ) }");
statement.setString(1, "xyz");
statement.setString(2, "xpto");
result = statement.executeQuery();

--

When I do 'executeQuery' it throws an SQLException: No ResultSet was produced. I have tried doing this:

--

statement.execute();
result = statement.getResultSet();

--

Doesn't throw an exception but the ResultSet is null.
And aparently there aren't any more ResultSets...
( statement.getMoreResults() is false )
Does anyone knows how to solve this?
0
rgafonso
Asked:
rgafonso
1 Solution
 
xxg4813Commented:
Hi,

The executeQuery() method is really a shortcut, as is executeUpdate(). What you should do is call execute().

Check the Javadoc on Statement.execute(). This returns a boolean which indicates if the next result from the statement is a result set. You then call getResultSet() if
it is indeed a result set.

Good luck
0
 
rgafonsoAuthor Commented:

 Well, thanks for your comment.
 I had already tried to use statement.execute() followed by statement.getResultSet() as I posted before. The problem still remains because statement.execute() returns false (meaning it's not a ResultSet). But this doesn't make any sense at all.
0
 
m_ilyaCommented:
This is a guess, but it's possible that your stored procedure is returning two results,
one from the insert statement and the second is the actual result set. In this case you probably need
to do in your java code something like

while(statement.getMoreResults()) {

ResultSet rs = statement.getResultSet();

...

}

this will move past the first result, which is the update count and to the result set.
Also check out function  getUpdateCount() if you want to look at the result of the first insert.
0
 
CleanupPingCommented:
rgafonso:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
arbertCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  

I will leave the following recommendation for this question in the Cleanup topic area:

Accept Answer From m_ilya


Any objections should be posted here in the
next 4 days. After that time, the question will be closed.

Arbert
EE Cleanup Volunteer
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now