• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Callable Statement JConnect2 Returning null resultset

 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And the ClientName is not a primary key
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Using Sybase server
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Julian..,

I fixed the stuff on that day itself.

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

Sreedhar Napa
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which solution did you choose? It may be useful for the benefit of others.

Jules
 
reply
    Bookmark Topic Watch Topic
  • New Topic