Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

executeQuery( ) method

 
Tuna Shankar
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
The following piece of code works fine when I use execute() method on the CallableStatement but raises an SQLException when I use executeQuery() method. Has anyone faced similar problems? Do you have any clue to make it work?
Appreciate your help.
---- Code starts ---
<pre>
import java.sql.*;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
public class OracleSPTest {

public void findEmpDetails (String strEmpNum)
{
try
{
Class.forName("Oracle.JDBC.driver"); //Assume appropriate driver has been
// Load the JDBC driver
Connection dbConnection = DriverManager.getConnection("ConnectionString");
//Assume it has proper connection string parameters and it works fine...
CallableStatement cs = dbConnection.prepareCall("{call Package_name.Oracle_Procedure_name(?,?,?,?)}");
//register the result parameter first and set the IN parameter
cs.setString(1, strEmpNum);
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.registerOutParameter(3, java.sql.Types.VARCHAR);
cs.registerOutParameter(4, java.sql.Types.DATE);

//cs.execute(); // ------- 1
ResultSet rs = cs.executeQuery(); // ------ 2 /Why is it failing here??? raises SQL exception..
// Execute the stored procedure
/*String returnValue2 = ""; // ----------- 1a : uncomment this block if you uncomment "1"
String returnValue3 = "";// and it works fine...
double returnValue4 = 0.0;
returnValue2 = cs.getString(2);
returnValue3 = cs.getString(3);
returnValue4 = cs.getDouble(4);
System.out.println("returnValue2: " + returnValue2);
System.out.println("returnValue3: " + returnValue3);
System.out.println("returnValue4: " + returnValue4);
*/
dbConnection.close();
while (rs.next())
{
// Do some processing here.. get the data from the result set and
// populate appropriatevariables.
}
}
catch (SQLException e)
{
System.out.println("SQL Exception raised...");
}
}
}
<pre>
---- Code Ends -----

 
Desai Sandeep
Ranch Hand
Posts: 1157
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
The problem is with the Stored Procedure you are executing.The OUT parameters of your Stored Procedure is returning two VARCHARS and a DATE and is not a ResultSet object.Hence Statement.executeQuery(), which returns a ResultSet object will not work.
So you have to use CallableStatement.execute() to execute the Stored Procedure and then retrieve the values using getXXX() methods.
Note that, this does not mean you can not return a ResultSet using a CallableStatement.Infact, execute(String) method of the Statement interface, allows you to do just that!
The following code may be useful for you to evaluate a stored procedure with unknown results :

Hope this helps,
Sandeep
[This message has been edited by Desai Sandeep (edited June 03, 2001).]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic