• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

executeQuery( ) method

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 -----

 
Ranch Hand
Posts: 1157
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic