Changing parameter from returned SP argument?

[patricksand]

I'm trying to get a value back from a stored procedure in Sybase...

Here's my code...

 

In the database, the stored procedure gps_test1:

 

Code:

CREATE PROCEDURE dbo.gps_test1 
  @cls int = 0, @nr_svrs int = -1 output  

AS 

BEGIN 
SELECT
     *
FROM
     master..sysservers
WHERE
     srvclass = @cls
select @nr_svrs = @@rowcount
END 

 

(for a class, typically 0 for the server name, or 7 for connecting servers, it lists the database server information)

The @nr_svrs int parameter is the return value I want...(the number of servers found)

 

Now in my report I have defined the following parameters:

 

$P{svr_class} java.lang.Integer with default of 0 (okay, new java.lang.Integer(0))

$P{nr_svrs} java.lang.Integer with default of -1

(ditto, new java.lang.Integer(-1))

 

and the report query is:

 

Code:

[code]exec gps_test1 $P{svr_class}, $P{nr_svrs} output

Things execute nicely(:woohoo:), but $P{nr_svrs} stays set to -1 instead of being set to 1 (with svr_class set to 0) or 3 (when svr_class is set to 7)...

Since Sybase supports output values through arguments, and it works in standard SQL like:

 

Code:

[code]declare @in int declare @out int select @in = 0, @out = -1 exec gps_test1 @in, @out output select @in, @out

 

How can I get iReports or JasperReports to support returning a value to a parameter from a stored procedure argument returned?

 

This would help me sell my firm on switching to this product, since it greatly simplifies the coding for the database down to defining parameters, setting values, and getting values to use along with the results returned. This shifts the focus to designing the form and the contents, which is were the value adds from JR and our folks can shine, respectively.

 

Or is there a way to use a report variable as an argument and get it set from the returned argument value?

 

Any help greatly appreciated...

[patricksand]

A bit of an update...

 

While searching through the forum, I found the following response to a similar problem in Oracle (trying to return a value through a procedure argument):

 

And as we already told you, JasperReports cannot deal with such stored procedures. It can only use SELECT queries or at least stored procedures that return result sets and have no OUT parameters at all.

 

[OUT parameters are like Sybase OUTPUT parameters/arguments to stored procedures]

 

Sigh...:dry:

 

I suspect it is also true for Sybase. Can anybody from JasperSoft verify this? Not a show-stopper but something they should consider fixing in a new release to allow better use of SQL...:(

 

You may also want to allow the return value of the stored procedure to be captured in a variable or parameter--a status or row count returned is a typical part of such code and is typically done for a good business reason.

 

A lot of stored procedure code is done like this:

 

Code:

declare @status int
.
.
.
exec @status = proc @arg1, @arg2, ...
if ( @status <> 0 )
...

 

Thanks in advance...