[Eclipsesql-bugs] [ eclipsesql-Bugs-1176689 ] Sybase stored proc with multiple result sets

[SourceForge.net <no...@so...>]

Bugs item #1176689, was opened at 2005-04-04 23:13
Message generated for change (Comment added) made by spackers
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=1176689&group_id=132863

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: Eric Munn (emunn)
Assigned to: Nobody/Anonymous (nobody)
Summary: Sybase stored proc with multiple result sets

Initial Comment:
When executing a Sybase ASE stored procedure which 
returns more than one result set, only the first result set 
displays, e.g.

create proc test
as
select 'blah'
select 'yechh'
;

will only return the 'blah' result when proc "test" is 
executed.

Also may be affecting execution of Sybase procs which 
have inserts/updates within them, as Sybase evidently 
returns a result set with the rowcount for each update.  
If one of these procs is executed, no result set is shown 
in SQLExplorer.  Changing the 
IGNORE_DONE_IN_PROC parameter in jConnect 6 
connection URL takes care of this problem (no 
comparable parameter exists in jConnect 5.5. or jTDS) - 
which leads me to believe that it is just another 
manifestation of the same problem.

Tested with 
- SQLExplorer 2.2.3
- Sybase ASE 12.0 and 12.5.3
- Sybase jConnect 5.5. and 6.0
- jTDS

----------------------------------------------------------------------

>Comment By: spackers (spackers)
Date: 2007-10-16 11:07

Message:
Logged In: YES 
user_id=1335081
Originator: NO

Multiple result sets are supported since 3.5.0

----------------------------------------------------------------------

Comment By: Mark Halpaap (yaoops)
Date: 2007-03-30 14:18

Message:
Logged In: YES 
user_id=270037
Originator: NO

Hmm, on top of multiple result sets Sybase procs may have a (meaningful,
not just error-indicating) return code and they may have 'output
parameters', i.e. procedure 
parameters that are overwritten inside the proc and the changed values
will be returned
and can then be read by appropriate Sybase API calls; whether there is
JDBC support
for these, I don not know.

----------------------------------------------------------------------

Comment By: Keenan Brock (kbrock)
Date: 2005-09-08 17:02

Message:
Logged In: YES 
user_id=936796

This is not sybase specific.

if you put 2 sql statements into one query, multiple result
sets come back from the database.

Most sql clients use:

ResultSet rs =statement.executeQuery(sql);
get metadata
create output table
add column headers to output table
while (rs.next())
{
  add data to output
}

But that will only display the first result set.

If you want to handle multiple results sets you need to do:

if (statement.execute(sql))
{
  ResultSet rs;
  while ((rs = statement.resultSet()) != null)
  {
    get meta data
    create output table
    add column headers to output
    while(rs.next())
    {
      add column to output
    }
  }
}

The sample seems quite simple, but the implementation may be
much more difficult. Haven't looked into the code yet.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=1176689&group_id=132863