| Sybase Stored Proc Issue [message #145489] |
Fri, 17 March 2006 12:22  |
Eclipse User |
|
|
|
Originally posted by: csc94ag.hotmail.com
I am trying to setup a stored proc dataset in BIRT. I can see the SP in
the 'Available Items' section of the dataset edit window, so I know my
conn is good. However, my queries return no data and no errors.
Here is what the 'Available Items' window shows:
cpep_email_rpt_ret_proc
RETURN_VALUE(int,Return Value)
@mode(varchar, Input)
@report_name(varchar, Input)
These are the syntaxes I have tried:
execute cpep_email_rpt_ret_proc 'value1','value2'
execute cpep_email_rpt_ret_proc ?,?
{call cpep_email_rpt_ret_proc(?,?)}
The last two I setup Input parameters with default values. Still, no data
and no error. I even tried an Output param, but I don't know the proper
syntax, so I don't know if I had that right.
Is it my syntax? Do I need an output param set? Something to catch the
RETURN_VALUE param?
|
|
|
| Re: Sybase Stored Proc Issue [message #145670 is a reply to message #145489] |
Fri, 17 March 2006 15:10  |
| Eclipse User |
|
|
|
Since your SP has a return value, you can use BIRT data set output parameter
to capture that value. Try this statement (which is the JDBC standard
syntax):
{? = call cpep_email_rpt_ret_proc(?,?)}
You should see an output parameter in the data set's parameter definition.
--
Gary Xue
Actuate Corporation - Product Development
BIRT Committer
"Mike Vogeli" <csc94ag@hotmail.com> wrote in message
news:e712c600ef4b914b5c785dbe3d2d4d3c$1@www.eclipse.org...
> I am trying to setup a stored proc dataset in BIRT. I can see the SP in
> the 'Available Items' section of the dataset edit window, so I know my
> conn is good. However, my queries return no data and no errors.
>
> Here is what the 'Available Items' window shows:
>
> cpep_email_rpt_ret_proc
> RETURN_VALUE(int,Return Value)
> @mode(varchar, Input)
> @report_name(varchar, Input)
>
>
>
>
> These are the syntaxes I have tried:
> execute cpep_email_rpt_ret_proc 'value1','value2'
> execute cpep_email_rpt_ret_proc ?,?
> {call cpep_email_rpt_ret_proc(?,?)}
>
>
> The last two I setup Input parameters with default values. Still, no data
> and no error. I even tried an Output param, but I don't know the proper
> syntax, so I don't know if I had that right.
>
> Is it my syntax? Do I need an output param set? Something to catch the
> RETURN_VALUE param?
>
|
|
|
| Re: Sybase Stored Proc Issue [message #145679 is a reply to message #145670] |
Fri, 17 March 2006 15:34 |
| Eclipse User |
|
|
|
Originally posted by: csc94ag.hotmail.com
Gary Xue wrote:
> {? = call cpep_email_rpt_ret_proc(?,?)}
I tried that and still get no data or error back.
Here are my parameters settings:
Name Data Type Direction Default Value
RETURN_VALUE Any Output
@mode String Input 'report'
@report_name String Input ''
And my query string:
{? = call cpep_email_rpt_ret_proc(?,?)}
I now get a RETURN_VALUE showing in my Data Explorer, but there is nothing
in it.
I found the source of the proc and verified that it is taking the right
params:
...
CREATE PROC cpep_email_rpt_ret_proc
@mode varchar(8) = 'user',
@report_name varchar(40) = ''
AS
...
I also tried setting the two output columns as Output Params, like
{? = call cpep_email_rpt_ret_proc(?,?,?,?)}
and set params as:
Name Data Type Direction Default Value
RETURN_VALUE Any Output
@mode String Input 'report'
@report_name String Input ''
mag_report Any Output
mag_id Any Output
But again, I get the params in the data explorer with no data.
|
|
|
| Re: Sybase Stored Proc Issue - SOLUTION FOUND [message #145713 is a reply to message #145670] |
Fri, 17 March 2006 16:39 |
| Eclipse User |
|
|
|
Originally posted by: csc94ag.hotmail.com
I found a solution:
If I setup the dataset as a regular Query (NOT stored proc query), and
then run the default sybase SP syntax of:
execute <sp_name> ?,?
And using the same setup for Input params, then I get a data set back.
FINALLY! This was really racking my brain for a long time.
|
|
|
Powered by
FUDForum. Page generated in 0.48390 seconds
Home