Re: dbproc + dynamic query result

Hi Felde,

> I wanted to create a dbproc in which I use the result of a dynamic 
> query. I thought the dbproc should work as follows ( just example ):
>
> CREATE DBPROC PREPTEST (
>  IN SZAM INTEGER,
>  OUT RES VARCHAR
> )
> AS
>  VAR QI VARCHAR(100);
>  SET QI = 'SELECT 5 FROM DBA.DUAL ';
>  EXECUTE QI;
>  FETCH INTO :RES;
Try something like:

CREATE DBPROC PREPTEST (
 IN SZAM INTEGER,
 OUT RES VARCHAR
)
AS

VAR EXPRESSION1 INT;

 SELECT 5  FROM DBA.DUAL;

 FETCH INTO :EXPRESSION1;
 SET RES = EXPRESSION1;

> A second question too. Is it able to return a cursor created with the 
> same method? What I mean:
>
> CREATE DBPROC PREPTEST (
>  IN SZAM INTEGER,
>  OUT RES VARCHAR
> )
> returns cursor AS
>  VAR QI VARCHAR(100);
>  $cursor = 'retcur';
>  SET QI = 'declare :$cursor cursor for SELECT 5 FROM DBA.DUAL ';
>  EXECUTE QI;
>  
Close. Maybe this is what you are looking for:
CREATE DBPROC PREPTEST1 (
 IN SZAM INTEGER,
 OUT RES VARCHAR
)
returns cursor AS
 $cursor = 'retcur';
 declare :$cursor cursor for SELECT 5 FROM DBA.DUAL;





Felde Norbert wrote:

> Hi,
>
> I wanted to create a dbproc in which I use the result of a dynamic 
> query. I thought the dbproc should work as follows ( just example ):
>
> CREATE DBPROC PREPTEST (
>  IN SZAM INTEGER,
>  OUT RES VARCHAR
> )
> AS
>  VAR QI VARCHAR(100);
>  SET QI = 'SELECT 5 FROM DBA.DUAL ';
>  EXECUTE QI;
>  FETCH INTO :RES;
>
> But it dous not. I tried to create it different ways but I had not found 
> the solution.
>
> Dous anybody know the solution?
>
> A second question too. Is it able to return a cursor created with the 
> same method? What I mean:
>
> CREATE DBPROC PREPTEST (
>  IN SZAM INTEGER,
>  OUT RES VARCHAR
> )
> returns cursor AS
>  VAR QI VARCHAR(100);
>  $cursor = 'retcur';
>  SET QI = 'declare :$cursor cursor for SELECT 5 FROM DBA.DUAL ';
>  EXECUTE QI;
>
>
> system:
> winXP
> maxdb 7.5.00.05
>
> Thanks,
> Norbert
>
>
>
>  

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx