chiradee chiradee - 2 months ago 8
Java Question

Error in rendering data using ResultSet

I'm having an error while rendering data using ResultSet. I'm having this error java.sql.SQLException: ResultSet is from UPDATE. No Data.
Here's my code snippet

ArrayList<String> arrayList = new ArrayList<String>();
Session session = null;
Connection conn = null;
CallableStatement callableStatement = null;
try {
// test
BeanLocator beanLocator = PortletBeanLocatorUtil
.getBeanLocator("Mrcos-services-portlet");
BasicDataSource bds = (BasicDataSource) beanLocator
.locate("mrcosDataSourceTarget");
conn = bds.getConnection();
String sp = "{call TINChkSP(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
callableStatement = conn.prepareCall(sp);
callableStatement.setString(1, tin);
callableStatement.setString(2, branchCode);
callableStatement.setString(3, rdoCode);
callableStatement.registerOutParameter(4, Types.VARCHAR);
callableStatement.registerOutParameter(5, Types.VARCHAR);
callableStatement.registerOutParameter(6, Types.VARCHAR);
// all other callablestatement until all the 27 params are complete)
callableStatement.executeQuery();

ResultSet resultSet = callableStatement.executeQuery();

while (resultSet.next()) {
for (int i = 0; i <= 27; i++) {
arrayList.add(resultSet.getString(i));
}
}


The output of my ResultSet is null and gives me java.sql.SQLException: ResultSet is from UPDATE. No Data. error. Please help thanks.

EDIT

I checked the System.out of each parameters using
callableStatement.getString(4);

callableStatement.getString(5);

callableStatement.getString(6);


and the outputs are:

42141


000


126

Tom Tom
Answer

The result of your OUT parameters, after your callableExecution.executeQuery() should be in the callableStatement variable.

To get them after the execution you must get by its type, like this:

String valueForOutput4 = callableStatement.getString(4);
String valueForOutput5 = callableStatement.getString(5); 
String valueForOutput6 = callableStatement.getString(6);

I assume that you want to have all the 27 fixed parameters passed to TINChkSP database function/procedure in your List. I assume that they are all of type String. I also assume that the first 3 parameters are not OUT parameters, but all of the others are OUT or IN/OUT.

Here's a possible solution.

callableStatement.executeQuery();

// The first 3 elements are not OUT parameters (I guess)
List<String> values = Arrays.asList(tin, branchCode, rdoCode);

for (int i = 4; i <= 27; i++) {
    values.add(callableStatement.getString(i));
}

System.out.println("The list: " + values.toString);

You can then remove the use of ResultSet, because it's gonna be useless.

Hope it helps.