Re: NullPointerExceptions using CallableStatement

davidgodf@xxxxxxxxx wrote:

Ok, basically as follows;

CallableStatement stmt = conn.prepareCall("{Call MyStoredProc(?,?)}");

stmt.setString(1, null);
stmt.setString(2, "Test Value");
stmt.execute;

ResultSet rs = stmt.getResultSet();

if (rs.next()) {
System.out.println("RS1=" + rs.getString(1));
}

Unless implicit transactions are enabled in the stored proc SQL, this
code fails when rs.next is called. If I turn off implicit transactions
in the proc, and rather use a Statement class and directly execute the
sql, it works fine.

Much appreciate your help.

You are getting an NPE because getResultSet() can rightly
return null. It is quite likely that your procedure is
returning update counts before the query results, and to
be correct you must process all returns in order. Here is
an optimal algorithm for processing all the inline returns
of any Microsoft stored procedure. Let me know if it works
for you:

CallableStatement ps = conn.prepareCall("{Call MyStoredProc(?,?)}");
ps.setString(1, null);
ps.setString(2, "Test Value");
boolean getResultSet = ps.execute();
int updateCount = -1;

while (true) { // handle all in-line results from any procedure
if (getResultSet) {
ResultSet r = ps.getResultSet();
while (r.next()) {
// fully process result set before calling getMoreResults()
System.out.println("RS1=" + rs.getString(1));
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
;// process update count if you care about it
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
getResultSet = ps.getMoreResults();
}
// if there are output parameters, get them now.

Joe Weinstein at BEA Systems




joeNOSPAM@xxxxxxx wrote:

Show us the JDBC code that declares the statement, sets the parameters,
and executes it. Also indicate the exact line of code that produces an
NPE.
GetResultSet() is allowed to return null. I think I know how to help
you....

Joe Weinstein at BEA Systems



.

Loading