aspose file tools*
The moose likes JDBC and the fly likes CallableStatement resultset returns only columns and no data, where data exists. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CallableStatement resultset returns only columns and no data, where data exists." Watch "CallableStatement resultset returns only columns and no data, where data exists." New topic
Author

CallableStatement resultset returns only columns and no data, where data exists.

Jyotsna Pai
Greenhorn

Joined: Aug 07, 2009
Posts: 18
Hi there,

Can someone please help me with this one.
I am trying to retrieve results by invoking a stored procedure - APPCOMMON.MY_PROC.
This procedure has 3 IN parameters of type varchar and 1 OUT parameter of type SYS_REFCURSOR.

The concern is that when I hard-code the 3 above mentioned input parameters in my stored procedures, the java code below works fine and gives me output as a Collection - 'results' which I then use to display an excel sheet. This shows me a table with 88 columns and 39 records.

But when I try to pass the parameters like below -
callStmt.setString(1, district);
callStmt.setString(2, workGroup);
callStmt.setString(3, systemStatus);

I don't get any data from the backend. But I am able to retrieve the column-names into the Collection - results.
Seems like some problems retrieving resultset, but I am not able to identify it.

Please advise.




The Stored procedure is -



Please advise. Any help is much appreciated.

Thanks and Regards,
Jyotsna Pai

Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Are you sure the input values are correct ? Try to hard code the values in your Java source :

(or whatever the values are)


[My Blog]
All roads lead to JavaRanch
Jyotsna Pai
Greenhorn

Joined: Aug 07, 2009
Posts: 18
Hi Christopher,

Thanks for your reply.

I have tried hard-coding the setString exactly as you advised.
It did not help. I could again retrieve only the columns, but no data.

I also tried the below -


Doesn't work either.
Jyotsna Pai
Greenhorn

Joined: Aug 07, 2009
Posts: 18
Hello everybody,

To all those who went through this page, Thanks a lot!
I have finally managed to resolve this issue myself.
The issue was not with the java code, but was with the stored procedure.
I changed the stored procedure to include MY_TYPES_PKG.refCursor instead of SYS_REFCURSOR.-
The stored procedure now is -


I created a package called MY_TYPES_PKG as follows -


I ensured to give GRANT to the user on both the package and the procedure.

And my code works fine.

For those of you, who would wish to see the code again, it is as follows -


Thanks to everyone!

Cheers,
Jyotsna Pai
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Good to hear it is solved. I must admit I had no clue what the problem could have been.
Jyotsna Pai
Greenhorn

Joined: Aug 07, 2009
Posts: 18
No worries Chris!

All we want to achieve is that the issue gets reoslved.
I am thankful to you for looking into it and helping me with it!

Thanks again!
Jyotsna Pai
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: CallableStatement resultset returns only columns and no data, where data exists.