?
Solved

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

Posted on 2003-02-19
6
Medium Priority
?
617 Views
Last Modified: 2012-05-04
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
Comment
Question by:rgafonso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:xxg4813
ID: 7983090
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
 

Author Comment

by:rgafonso
ID: 7983220

 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
 

Accepted Solution

by:
m_ilya earned 1000 total points
ID: 8751029
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
 

Expert Comment

by:CleanupPing
ID: 9276557
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
 
LVL 34

Expert Comment

by:arbert
ID: 10910479
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question