Re: dbproc + dynamic query result

Hi Felde,

One thing I just thought of which may be a help to you is to store the 
results in a temporary table. Do something like:


CREATE DBPROC DBA.TEST_PROC(IN PARA1 VARCHAR(4), IN

TABLE_NAME VARCHAR(10))
AS
VAR QUERY VARCHAR(200);

SET QUERY = 'CREATE TABLE TEMP.' || TABLE_NAME || ' AS

SELECT ' || PARA1 || ' FROM DBA.DUAL';
EXECUTE QUERY;


This way, you can assign a name to the table from your application 
(using md5 + rand or something) and then know what name to retrieve data 
from. This makes it multi-user safe and the same as returning a cursor.

Hope that helps,

JLS

edlef@xxxxxxxxxxx wrote:

> Hi,
>
> Thanks, but it is important that the select and the cursor should be 
> made within a string and than executed. I write for both question a 
> more specific example why is it so.
>
> Here an example for the result of a dynamic query:
>
> CREATE DBPROC INSERTBEREND_PASZTOR (
>         IN TABLEIDX             VARCHAR(4),
>         IN FOLYAMAT             VARCHAR(30),
>         IN IDO                  TIMESTAMP,
>         IN D501                 VARCHAR(10)
>         )
>         AS
>         VAR
>                 HEADID          INTEGER;
>                 FOLYAMATID      INTEGER;
>                 IQ                      VARCHAR(500);
>                 INS                     VARCHAR(500);
>                 ISSTORED        INTEGER;
>                 DID                     INTEGER;
>         TRY
> 		SET IQ = 'SELECT COUNT(*) FROM 
> DNUSER.DATAHEADER' || TABLEIDX || ' WHERE BERID = 1 AND IDO = ''' 
> || IDO || '''';
>                 EXECUTE IQ;
>                 IF( ISSTORED > 0 ) THEN RETURN;
>                 FETCH MARVAN INTO :ISSTORED
>                 SELECT SEQ_HEADID.NEXTVAL FROM DBA.DUAL;
>                 FETCH INTO :HEADID;
>                 CALL FOLYAMAT_UJ( 1, :FOLYAMAT, :FOLYAMATID );
>
> 		SET IQ = 'INSERT INTO DNUSER.DATAHEADER' || 
> TABLEIDX ||
> 			' ( HEADERID, BERID, IDO, FOLYAMATID ) ' 
> ||
>                         ' VALUES( ' || HEADID || ', 1, ''' ||
>                         IDO || ''', ' || FOLYAMATID || ' )';
>
>                 EXECUTE IQ;
> 		SET INS = 'INSERT INTO DNUSER.DATA' || TABLEIDX 
> || ' ( DATAID, HEADERID, PARAMID, ERTEK )  VALUES ';
>
> 		SELECT SEQ_DATAID.NEXTVAL INTO :DID FROM 
> DBA.DUAL;
> 		SET IQ = INS || '( ' || DID || ', ' || HEADID ||', 1, '''|| 
> D501 || ''')';
>                 EXECUTE IQ;
> 		INSERT INTO DNUSER.TEMPIDOSZAKIJELENTES	( 
> BERID, FOLYAMATID, PARAMID, DATUM, ERTEK )
>                 VALUES( 1, :FOLYAMATID, 1, :IDO, NUM( :D501 ) );
>         CATCH
>                 IF( $RC <> 0 )
>                 THEN STOP ( $RC, $ERRMSG );
>
>
> The example for the returned cursor should be created whitin a string:
>
> CREATE DBPROC MINTAINDITAS_GET_LIST(
>         IN F_STATUS INTEGER,
>         IN F_MINTAKOD VARCHAR,
>    IN F_MVHELYID INTEGER,
>    IN F_MINTAVETELIIDO1 VARCHAR,
>         IN F_MINTAVETELIIDO2 VARCHAR,
>    IN F_VIZTIPUS INTEGER,
>         IN F_TIPUS INTEGER)
>         RETURNS CURSOR AS
>    VAR FILTER VARCHAR;
>    $CURSOR = 'MLIST_CUR';
>         FILTER = ' status = ' || F_STATUS;
>         IF (  F_MINTAKOD!='' ) THEN
>    	FILTER = FILTER || ' AND mintakod like ''%' || F_MINTAKOD 
> || ''' ';
>         IF ( F_MVHELYID != 0 ) THEN
>             FILTER = FILTER || ' AND mvhelyid = ' || F_MVHELYID;
>         IF (  F_MINTAVETELIIDO1 != '' ) THEN
> 		FILTER = FILTER || ' AND mintaveteliido >= ''%' || 
> F_MINTAVETELIIDO1 || ''' ';
>         IF (  F_MINTAVETELIIDO2 != '' ) THEN
>    	FILTER = FILTER || ' AND mintaveteliido <= ''%' || 
> F_MINTAVETELIIDO2 || ''' ';
>         IF ( F_VIZTIPUS != 0 ) THEN
>         FILTER = FILTER || ' AND viztipus = ' || F_VIZTIPUS ;
>         IF ( F_TIPUS = 2 ) THEN
>         FILTER = FILTER || ' AND parhuzamosminta = 1' ;
>         IF ( F_TIPUS = 3 ) THEN
>         FILTER = FILTER || ' AND ellenminta = 1';
>         DECLARE :$CURSOR CURSOR FOR
>    	SELECT * FROM KFCSUSER.view_mintainditas_lista 
> WHERE :FILTER;
>
>
> 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