?
Solved

Sybase jconnect stored procedure temporary tables issue

Posted on 2005-03-03
5
Medium Priority
?
1,281 Views
Last Modified: 2008-03-06
From isql I run my stored procedure and it returns the expected results.  However, from Java the

java.sql.Statement.getResultSet()

method returns nothing.  The stored procedure contains temporary tables and two select statements.  If I instead replace this with just one select statement and no temporary tables I get results from java.  Any ideas?
0
Comment
Question by:r2umam
  • 3
5 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13456002
Not very helpful to you I'm afraid, but I've seen a lot of applications and connectivity tools expect only one result set per statement.

If that's the case for Java as well, do you have the ability to split your procedure into two subprocedures and call them one per statement? Of course, you wouldn't be able to share information between them with the #temp tables anymore, though.

I'm not a Java person but I'm guessing there might be some way to concatenate result sets...?
0
 

Author Comment

by:r2umam
ID: 13456032
actually, in both cases there is only one result set. In the first sp I create a temp table and then select from it, returning one result set but java says no results, whereas isql returns many lines of results.  In the second case there is also one result set, but this time no temp table, this time java has no problem and returns results.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 13456053
Huh. Ok, so it isn't that.

Java doesn't like temp tables? Hmm.

What version of ASE? If you're in any of the recent versions (12.5.1+, I think), you can use a derived table rather than a temp table, ie. treat the query that populates the temp table as a table in the FROM clause:

SELECT   ...
FROM     (SELECT ... FROM ... WHERE ...) as derived_table
,            other_table
[...]

If the inner query doesn't have to be built programatically (ie. is the same every time it runs), you could use a VIEW instead.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13460293
Does you application have the same rights as then you are running it from isql?

As a test create a procedure to insert a single value into a temp table and select it out. Do you get it from Java?
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13463816
Glad that helped!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
The number of companies understanding the potential of IoT on B2B market is growing with each day. And yet only a small share of IoT developers have managed to equalize incomes and stay competitive in the international market.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question