File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes No Data returned from Sybase stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "No Data returned from Sybase stored procedure" Watch "No Data returned from Sybase stored procedure" New topic

No Data returned from Sybase stored procedure

amit khanra

Joined: Feb 01, 2012
Posts: 2
Hi Friends,

Has any one faced this problem before?

My application is running on RedHat-3 Unix platform, Apache 2.2.X and Tomcat-5. It connects to Sybase 15.X database through a JDBC connection pool and runs a stored procedure.
Sometimes, the stored procedure is NOT returning any data in the middle layer, but interestingly at the same time if I run the SP with exactly same parameters from a DB Tool like DBArtisan etc, it returns the data.
Once the tomcat is restarted, it behaves normally and expected data is returned from DB.

Surprisingly there is no Java or SQL exception is seen.
I checked the CPU usage of the Unix box and the database, they looked ok.

Any pointer would be highly appreciable,

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

It's really hard to tell, so I'm only guessing, but one possibility would be that there is an implicit conversion taking place somewhere in the stored procedure (most often this is a text-to-date conversion). That conversion works well when using default session settings, but when something changes the default settings (possibly a sparsely used feature of your system), the procedure all of sudden breaks. Since the connections are pooled, the altered session "poisons" the connection pool. Restart of the webserver refreshes the connection pool and resets the behavior to "normal".

As I said, I'm only guessing and also I work with tools different from you (Oracle and OAS), but in my environment this scenario would certainly be possible. I'd suggest carefully inspecting the stored procedure for implicit conversions (and for other suspicious operations too) and maybe adding some logging/tracing code into it.
amit khanra

Joined: Feb 01, 2012
Posts: 2
Thanks Martin for your inputs. I just found out that the Unix host is low in RAM memory, struggling and it is juggling around the available memory, which may have caused the JVM to read some corrupted pages.
I am looking to add some more memory to it, let's see if it resolves the issue.
I agree. Here's the link:
subject: No Data returned from Sybase stored procedure
It's not a secret anymore!