Jump to content
Changes to the Jaspersoft community edition download ×

Changing parameter from returned SP argument?


patricksand

Recommended Posts

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...

Link to comment
Share on other sites

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...