OpenEdge Dynamic TempTable

Question

I am creating an application that accesses a database with many tables. To make the coding easier and shorter, I am planning to make one procedure that either gets/sets the data dynamically or executes a procedure for specific data manipulations.

I've got something so far, but I'm kind of stuck now.

What I've done so far is made sure I can dynamically built a temp-table with the same schema as a database table I want to retrieve data from. I then query for a record and add it to the dynamic temp-table. Then this temp-table is passed as an output parameter.

What I want to do now is , when the user has changed the record, save that record dynamically. Therefore, I have to query the table dynamically and find the record the user wants to change. Actually the same as retrieving the record. But saving the changes made requires me to go through the input dynamic temp-table. How is this done?

The normal way of actions is like this: - Get record by passing the table name and key. Then give the record to output parameter. - Update record by getting dynamic temp-table as input parameter and then saving the changes from the correct record to the correct record. This second part is where I fail.

The code supplied here only does the first part, but the second part should be included into this code.

Code:

DEF VAR G-TableBuf              AS HANDLE NO-UNDO.
DEF VAR G-TableBuf-Handle       AS HANDLE NO-UNDO.
DEF VAR G-Query                 AS HANDLE NO-UNDO.
DEF VAR G-Table-FirNr           AS INT    NO-UNDO.
DEF VAR G-Qstring               AS CHAR   NO-UNDO.
DEF VAR G-Heeft-FirNr           AS LOG    NO-UNDO.
DEF VAR G-TempTable             AS HANDLE NO-UNDO.
DEF VAR G-tt-Buffer             AS HANDLE NO-UNDO.
DEF VAR G-MatchZone             AS CHAR   NO-UNDO.
DEF VAR G-prime-field           AS CHAR   NO-UNDO.
DEF VAR G-Zones-Buffer          AS HANDLE NO-UNDO.

{lib/def_tt_ds_Errors.i}

DEF INPUT PARAMETER p_iFirnr    AS INT  NO-UNDO.
DEF INPUT PARAMETER p_iApplNr   AS INT  NO-UNDO.
DEF INPUT PARAMETER p_cUsrCd    AS CHAR NO-UNDO.
DEF INPUT PARAMETER p_cAction   AS CHAR NO-UNDO.
DEF INPUT PARAMETER p_cKeyCd    AS CHAR NO-UNDO. /* Record key */
DEF INPUT PARAMETER p_cTable    AS CHAR NO-UNDO. /* Table name */

DEF INPUT-OUTPUT PARAMETER TABLE-HANDLE hTT. /* INPUT-OUTPUT dynamic temp-table */
DEF OUTPUT PARAMETER DATASET            FOR dsErrors.

    RUN FindRecord.

    RETURN.        

PROCEDURE FindRecord :
/*------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------*/
DEF VAR i AS INT NO-UNDO.

    CREATE TEMP-TABLE G-TempTable.
    CREATE BUFFER     G-TableBuf FOR TABLE p_cTable.
    CREATE QUERY      G-Query.

    ASSIGN G-Table-FirNr  = Get-Fir (p_cTable)
           G-MatchZone    = "kolom,Waarde"
           G-heeft-firnr  = FALSE
           G-Zones-Buffer = BUFFER zones:HANDLE
           G-TableBuf-Handle = G-TableBuf:HANDLE.

    /* SCHEMA BUILDING CODE GOES HERE */

    G-TempTable:TEMP-TABLE-PREPARE("tt" + p_cTable).

    G-tt-Buffer = G-TempTable:DEFAULT-BUFFER-HANDLE.
    G-tt-Buffer:EMPTY-TEMP-TABLE().

    hTT = G-TempTable.

    G-Qstring = "FOR EACH " + p_cTable.

    G-Query:SET-BUFFERS(G-TableBuf).
    G-Query:QUERY-PREPARE(G-Qstring).
    G-Query:QUERY-OPEN().
    G-Query:GET-NEXT().   

    REPEAT:
        IF G-query:QUERY-OFF-END THEN
            LEAVE.

        G-tt-Buffer:BUFFER-CREATE.
        G-tt-Buffer:BUFFER-COPY (G-TableBuf-Handle). 

        G-Query:GET-NEXT(). 
    END.

END PROCEDURE.

Thanks in advance!

In that case you might want to consider rethinking this approach and building a class to implement your logic. That way you wouldn't need to be passing around temp-table parameters and worrying about INPUT vs INPUT-OUTPUT etc. — Tom Bascom
But I don't understand what benefit a class will do? I want this one procedure to work for 90% of all the tables in the DB. — Sander Vanhee

Tim Kuehn Tim Kuehn · Accepted Answer · 2014-10-04T02:26:41

You need to study the ProDataset functionality, it'll make loading a TT and saving it's changed records back to the DB a lot easier.

To create a dynamic TT like a db table (at a high level), get the table's buffer handle, then go through it using b-handle:buffer-field(field-number):name to get a list of fields, and then use add-fields-from to create fields in the TT based on the db table.

Once you've got that, use the Prodataset "FILL" functionality to load data into it, and then pass the PDS back to the calling program for it to use.

when you're ready to save the data out, use the PDS functionity (save-row-changes) to save the changed records back to the database.

The process of loading and saving TT records is largely documented in the Prodatset set of docs. I highly recommend the 11.3 docs, as they're much improved over the prior version PDS docs.

This KB will also give you some ideas on how to build a dynamic TT like a db table. http://knowledgebase.progress.com/articles/Article/000045189?q=how+to+create+dynamic+temp-table&l=en_US&c=Product_Group%3AOpenEdge&type=Article__kav&fs=Search&pn=1

OpenEdge Dynamic TempTable

2 Answers