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 JDBC Callable Statement JConnect2  Returning null resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Callable Statement JConnect2  Returning null resultset" Watch "JDBC Callable Statement JConnect2  Returning null resultset" New topic
Author

JDBC Callable Statement JConnect2 Returning null resultset

Napa Sreedhar
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
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
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
I could not understand how the temporary table is redundant..

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



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

Please help
Thanks,
Sreedhar Napa
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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:

to this code:

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
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
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
Napa Sreedhar
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
And the ClientName is not a primary key
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
Using Sybase server
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
If all that's no good then I think the JDBC code you need is as simple as:

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

Jules
Napa Sreedhar
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
Thanks Julian..,

I fixed the stuff on that day itself.

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

Sreedhar Napa
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Which solution did you choose? It may be useful for the benefit of others.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Callable Statement JConnect2 Returning null resultset
 
Similar Threads
error in calling a procedure
A Very little problem in h:dataTable implementation
DB2 700 and 701 - Test Sample
Stored Proc w/dynamic sql
how to get out parameter from store procedure?