executeQuery( ) method

[Tuna Shankar]

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]

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 :

stmt.execute([I]queryStringwithUnknownResults[/I]);
while (true) {
    int rowCount = stmt.getUpdateCount();
    if (rowCount > 0) {      // this is a update count  
        System.out.println("Row changed = " + count);
        stmt.getMoreResults(); //if true, more ResultSets are there.If false, it means an update count is returned or there are no more results
        continue;  
    } 
    if (rowCount == 0) {     // DDL command or 0 updates
        System.out.println("No rows changed or statement was a DDL command");
        stmt.getMoreResults();
        continue;
    }
    //If we are here, we either have a ResultSet or there are no more results
    ResultSet rs = stmt.getResultSet();
    if (rs != null) {
        while (rs.next()) {
          //process the ResultSet here 
        } 
        stmt.getMoreResults();
        continue;
    }  
    break;   //there are no more results
}

Hope this helps,
Sandeep
[This message has been edited by Desai Sandeep (edited June 03, 2001).]