Passing Dynamic Temp-table

[ajaysheth]

Hello All:

Need Help urgently. I have the below scenario:

program x.p
Initialize variables.
Do this.
Do that.
accept source table input.
run x2..p "Source Table".
buffer-copy tt target-db.

program x2.p
create dynamic table d1-table like {1}.
create dynamic temp-table t2-table.
buffer-copy d1-table t2-table.

Query : - I want to get the dynamic temp-table that is created and populated in "x2.p" back in my calling program "x.p" and write it to a target DB ?

Thanks in advance.
AJ.

 

[FrancoisL]

Program x.p

DEF VAR hTable AS HANDLE.

RUN x2.p ("Table Name", OUTPUT TABLE-HANDLE hTable).

Program x2.p

DEF INPUT PARAMETER pcTableName AS CHAR.
DEFINE OUTPUT PARAMETER TABLE-HANDLE ttHandle.


DEF VAR hQuery AS HANDLE.
DEF VAR hBufDest AS HANDLE.
DEF VAR hBufSource AS HANDLE.

CREATE TEMP-TABLE ttHandle.

ttHandle:CREATE-LIKE(pcTableName).
ttHandle:TEMP-TABLE-PREPARE(pcTableName).
hBufDest = ttHandle:DEFAULT-BUFFER-HANDLE.

CREATE BUFFER hBufSource FOR TABLE pcTableName.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBufSource).
hQuery:QUERY-PREPARE("FOR EACH " + pcTableName).


hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
REPEAT :            
    IF hQuery:QUERY-OFF-END THEN LEAVE.

    hBufDest:BUFFER-CREATE().
    hBufDest:BUFFER-COPY(hQuery:GET-BUFFER-HANDLE()).

    hQuery:GET-NEXT().

END.

 

[FrancoisL]

Here is a version where x.p copies the data back to the target DB.

Program x.p

DEF VAR hTable AS HANDLE.
DEF VAR hQuery AS HANDLE.
DEF VAR hBufferDest AS HANDLE.

DEF VAR cTableName AS CHAR INIT "TableName".
DEF VAR srcDB AS CHAR INIT "db1".
DEF VAR targetDB AS CHAR INIT "db2".



RUN x2.p (cTableName, srcDB, OUTPUT TABLE-HANDLE hTable).

CREATE BUFFER hBufferDest FOR TABLE targetDB + "." + srcTable.

CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hTable:DEFAULT-BUFFER-HANDLE).
hQuery:QUERY-PREPARE("FOR EACH " + srcTable).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
REPEAT:
    IF hQuery:QUERY-OFF-END THEN
        LEAVE.
    hBufferDest:BUFFER-CREATE().
    hBufferDest:BUFFER-COPY(hQuery:GET-BUFFER-HANDLE()).
        
    hQuery:GET-NEXT().    
END.

Program x2.p

DEF INPUT PARAMETER pcTableName AS CHAR.
DEF INPUT PARAMETER pcSrcDB AS CHAR.
DEFINE OUTPUT PARAMETER TABLE-HANDLE ttHandle.


DEF VAR hQuery AS HANDLE.
DEF VAR hBufDest AS HANDLE.
DEF VAR hBufSource AS HANDLE.

CREATE TEMP-TABLE ttHandle.

ttHandle:CREATE-LIKE(pcSrcDB + "." + pcTableName).
ttHandle:TEMP-TABLE-PREPARE(pcTableName).
hBufDest = ttHandle:DEFAULT-BUFFER-HANDLE.

CREATE BUFFER hBufSource FOR TABLE pcSrcDB + "." + pcTableName.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBufSource).
hQuery:QUERY-PREPARE("FOR EACH " + pcSrcDB + "." + pcTableName).


hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
REPEAT :            
    IF hQuery:QUERY-OFF-END THEN LEAVE.

    hBufDest:BUFFER-CREATE().
    hBufDest:BUFFER-COPY(hQuery:GET-BUFFER-HANDLE()).

    hQuery:GET-NEXT().

END.

 

[FrancoisL]

You can even do it without using a temp-table.

DEF INPUT PARAMETER pcTableName AS CHAR.
DEF INPUT PARAMETER pcDbSrcName AS CHAR.
DEF INPUT PARAMETER pcDbDestName AS CHAR.

DEF VAR hQuery AS HANDLE.
DEF VAR hBufSrc AS HANDLE.
DEF VAR hBufDest AS HANDLE.



CREATE BUFFER hBufSrc FOR TABLE pcDbSrcName + "." + pcTableName.
CREATE BUFFER hBufDest FOR TABLE pcDbDestName + "." + pcTableName.
DO TRANSACTION:

CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBufSrc).
hQuery:QUERY-PREPARE("FOR EACH " + pcDbSrcName + "." + pcTableName).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
REPEAT:
    IF hQuery:QUERY-OFF-END THEN
        LEAVE.
    hBufDest:BUFFER-CREATE().
    hBufDest:BUFFER-COPY(hQuery:GET-BUFFER-HANDLE()).
        
    hQuery:GET-NEXT().    
END.

END.

 

[ajaysheth]

Thanks for all your valuable inputs. It worked !!!! .

FrancoisL : Am using temp-table because am checking against some set of rules and only if qualified populating the values in temp-table. This is temp-table is finally written to the target DB.

Thanks,
AJ.