JDBC Callable Statement JConnect2 Returning null resultset

[Napa Sreedhar]

Stored Procedure for implementing pagination

Transact-SQL
CREATE PROCEDURE dbo.pr__Get_Employees
@endindex Int,
@pagesize Int
AS

BEGIN
Create table #temp_cws(id numeric(5) identity, name varchar(100))

Insert into #temp_cws
Select name
From EMPLOYEE

Select e.name, e.* from #temp_cws t, EMPLOYEE e
WHERE (t.id > @endindex - @pagesize And t.id <= @endindex)
AND e.name = t.name
END


When I call this procedure from Java program I am getting a null pointer exception at result set

// JAVA CODE SAMPLE

conn = DBConnection.getConnection();
cstmt = conn.prepareCall(sql);
cstmt.setInt(1, endIndex);
cstmt.setInt(2, pageSize);
boolean bresult = cstmt.execute();
rs = cstmt.getResultSet(); // Resultset giving null
while (rs.next()) { // Null pointer exception raised here

Please help

Thanks
Sreedhar Napa

[Julian Kennedy]

Sreedhar Napa,

I think your problem is that the first "result" returned by your SP is not the ResultSet generated by the query. You'll probably get a result for the number of rows affected by the INSERT at least, if not something for the CREATE TABLE as well.

As the temporary table is completely redundant in your example, the simple solution is to just get rid of it.

The rest of the code looks fine (though it's hard to be 100% sure in the absence of the "sql" string. You should be checking bresult to see if your execute() actually returns a ResultSet, rather than allowing your code to blow up with a NullPointerException when you don't find one.

If you really want to proceed with your SP as it is you can use cstmt.getMoreResults() to find the ResultSet. Also setting "NOCOUNT ON" in your SP will probably do the trick.

Hope this helps.

Jules

[Napa Sreedhar]

I could not understand how the temporary table is redundant..

I changed my Java code now to check for the insert statements

 while (true) {
rs = cstmt.getResultSet();
int updateCount = cstmt.getUpdateCount();
LogWriter.write(" Update count " + updateCount);
if (rs == null && updateCount == -1) {
break;
}
if (rs != null) {
                                     // process the result set
} else {

System.out.println("Update count = " + cstmt.getUpdateCount());
}
cstmt.getMoreResults();
} 

But my code is not catching the create table statement, first line in my stored procedure

Please help
Thanks,
Sreedhar Napa

[Julian Kennedy]

OK, I only scanned your SP the first time and I see why you need the temp table now when you do it this way. However, you still don't need it.if your Employee table has a primary key (it should). I'm going to assume, for this example, that it's emp_id.

If so, change this code:

create table #temp_cws(id numeric(5) identity, name varchar(100))
 
Insert into #temp_cws
Select name 
From EMPLOYEE
 
Select e.name, e.* from #temp_cws t, EMPLOYEE e
WHERE (t.id > @endindex - @pagesize And t.id <= @endindex)
AND e.name = t.name

to this code:

SET ROWCOUNT @pagesize
 
SELECT *
FROM   employee e
WHERE  e.emp_id > @end_emp_id
 
SET ROWCOUNT 0

Adding a row for each employeeinto a temporary table every time your SP is called is incredibly inefficient and gets worse the more employees you get.

I've only glanced at your Java code to handle the ResultSet, but it looks over-complicated.

Jules

[Napa Sreedhar]

Is there an alternativw with the Java code to fix the stuff..

This is because the rowcount will be a variable in my case

The no of rows that needs to be returned depend on the rows present in temporary table which I am joining with the main table

Stored Procedure

  Create table #temp_cws(id numeric(5) identity, ClientName varchar(100))
 

 
Insert into #temp_cws
Select distinct ClientName 
From IF_CWS Order by ClientName 
 

 
Select c.ClientName, c.ClientCode, c.CreditConcernDate, c.AccountId, c.MasterId, c.LegalEntity, c.SnPRating, c.MoodyRating 
From #temp_cws t, IF_CWS c 
Where t.ClientName = c.ClientName 
And (t.id > @endindex - @pagesize And t.id <= @endindex)

[Napa Sreedhar]

And the ClientName is not a primary key

[Julian Kennedy]

You may still be able to do it without the temp table, which is the best solution. Are you using Sybase or MS SQL Server?

Jules

[Napa Sreedhar]

Using Sybase server

[Julian Kennedy]

Does your query return more than one row for each ClientName or do you always get between 0 and @pagesize rows, depending on whether there's a full page?

If there's never more than one row per ClientName then you can still do it without the temp table.

Jules

[Julian Kennedy]

Did you try using "SET NOCOUNT ON" in your SP? As I said, that should do what you want without requiring any SP or Java changes.

Jules

[Julian Kennedy]

If all that's no good then I think the JDBC code you need is as simple as:

cstmt.execute(); // Ignore returned boolean
int updateCount = 0;
// While there are more results...
while ((updateCount = cstmt.getUpdateCount()) != -1) || cstmt.getMoreResults()) {
    // If there was an update count, log it
    if (updateCount != -1)
        LogWriter.write(" Update count " + updateCount);
    // Otherwise we know there *is* a ResultSet, so process it
    else {
        rs = cstmt.getResultSet();
        // Process the ResultSet
    }
} 

I should point out that I haven't tested it.

Jules

[Napa Sreedhar]

Thanks Julian..,

I fixed the stuff on that day itself.

Sorry.. I didnot check there were these many posts.

Sreedhar Napa

[Julian Kennedy]

Which solution did you choose? It may be useful for the benefit of others.

Jules