File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes executeQuery( ) method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "executeQuery( ) method" Watch "executeQuery( ) method" New topic
Author

executeQuery( ) method

Tuna Shankar
Greenhorn

Joined: Jun 02, 2001
Posts: 1
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

Joined: Apr 02, 2001
Posts: 1157
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).]


<b>Sandeep</b> <br /> <br /><b>Sun Certified Programmer for Java 2 Platform</b><br /> <br /><b>Oracle Certified Solution Developer - JDeveloper</b><br /><b>-- Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java </b><br /><b>-- Object-Oriented Analysis and Design with UML</b><br /> <br /><b>Oracle Certified Enterprise Developer - Oracle Internet Platform</b><br /><b>-- Enterprise Connectivity with J2EE </b><br /><b>-- Enterprise Development on the Oracle Internet Platform </b>
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: executeQuery( ) method
 
Similar Threads
DB2 connection
Problem with IN clause
Trouble executing stored procedure and getting a value back
issue in CallableStatement.execute()
How to use IN OUT parameter