Re: dbproc + dynamic query result

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