Dynamically selecting records in temp-table

[Kladkul]

I am fairly new to working with dynamic temp-tables and, for testing purposes, need to select specific records in the temp-table. What is the best way to go about this?

-Klad

 

[Casper]

you will need a query to find those records.
Just create a dynamic query with the temp-tables default buffer handle as buffer for the query and query the temp-table as you like.

Casper.

 

[palthe]

I am fairly new to working with dynamic temp-tables and, for testing purposes, need to select specific records in the temp-table. What is the best way to go about this?

-Klad

/* 
Test case for Kladkul
*/
 
DEF VAR tt      AS HANDLE   NO-UNDO.
DEF VAR btt     AS HANDLE   NO-UNDO.
DEF VAR ttquery AS HANDLE   NO-UNDO.
DEF VAR ctable  AS CHAR     NO-UNDO. 
DEF VAR btable  AS HANDLE   NO-UNDO.
DEF VAR rrow    AS ROWID    NO-UNDO. /* optional */
 
/*
Creating temp-table
*/
ctable = "customer".
CREATE BUFFER btable FOR TABLE ctable.
CREATE TEMP-TABLE tt.
tt:CREATE-LIKE(btable).
tt:ADD-NEW-FIELD("Rowid","rowid"). /* this is optional, but comes in handy in some cases */
tt:TEMP-TABLE-PREPARE("temp").
btt = tt:DEFAULT-BUFFER-HANDLE.
 
/*
Populating temp-table
*/
CREATE QUERY ttquery.
ttquery:SET-BUFFERS(btable).
ttquery:QUERY-PREPARE(SUBSTITUTE("for each &1",ctable)).
ttquery:QUERY-OPEN.
ttquery:GET-FIRST(NO-LOCK).
DO WHILE NOT ttquery:QUERY-OFF-END:
    btt:BUFFER-CREATE.
    btt:BUFFER-COPY(btable).
    btt:BUFFER-FIELD("rowid"):BUFFER-VALUE = btable:ROWID. /* again optional, you create a unique handle to the original table buffer */
    ttquery:GET-NEXT(NO-LOCK).
END.
ttquery:QUERY-CLOSE.
DELETE OBJECT ttquery.
 
/*
Now you have a populated temp-table for the table customer.
You can access the data in the tt with a new query or just do a dynamic find.
Query:
*/
CREATE QUERY ttquery.
ttquery:SET-BUFFERS(btt).
ttquery:QUERY-PREPARE("for each temp").
ttquery:QUERY-OPEN.
ttquery:GET-FIRST(NO-LOCK).
DO WHILE NOT ttquery:QUERY-OFF-END:
    IF btt:BUFFER-FIELD("Name"):BUFFER-VALUE MATCHES "*Kl*" THEN
    MESSAGE "Name: " + btt:BUFFER-FIELD("Name"):BUFFER-VALUE SKIP
            "Customer: " + btt:BUFFER-FIELD("Custnum"):BUFFER-VALUE VIEW-AS ALERT-BOX.
    ttquery:GET-NEXT(NO-LOCK).
END.
 
/* 
If you're not going to use the query anymore be sure to clean it up.
*/
ttquery:QUERY-CLOSE.
DELETE OBJECT ttquery.
 
/*
You can of course also do a find on the table instead of querying, for example if you're sure that the find will return one result:
*/
btt:FIND-UNIQUE(SUBSTITUTE("where name matches &1",QUOTER("*Kl*"))) NO-ERROR.
IF btt:AVAILABLE THEN MESSAGE   "Name: " + btt:BUFFER-FIELD("Name"):BUFFER-VALUE SKIP
                                "Customer: " + btt:BUFFER-FIELD("Custnum"):BUFFER-VALUE VIEW-AS ALERT-BOX.
IF NOT btt:AVAILABLE THEN
DO:
    IF btt:AMBIGUOUS THEN MESSAGE "More than one record found with the given unique constraint" VIEW-AS ALERT-BOX ERROR.
    ELSE MESSAGE "Record not found!" VIEW-AS ALERT-BOX ERROR.
    RETURN.
END.
 
/* 
Optional: you can find the original buffer-handle by using a find on the rowid
*/
IF btt:AVAILABLE THEN
DO: 
    btt:BUFFER-FIELD("Name"):BUFFER-VALUE = "Progress test case".
    rrow = btt:BUFFER-FIELD("rowid"):BUFFER-VALUE.
    btable:FIND-BY-ROWID(rrow).
    MESSAGE "New Name :" + btt:BUFFER-FIELD("Name"):BUFFER-VALUE SKIP
            "Original Name: " + btable:BUFFER-FIELD("Name"):BUFFER-VALUE SKIP
            "Are you sure you want to accept the changes?" VIEW-AS ALERT-BOX QUESTION UPDATE lOk AS LOGICAL.
    /* BE AWARE!!!! It's just a testcase but with the following you actually make changes to the original table!! 
    IF lOk THEN
    DO TRANSACTION:
        btable:DISABLE-LOAD-TRIGGERS(YES).
        btable:BUFFER-FIELD("Name"):BUFFER-VALUE = btt:BUFFER-FIELD("Name"):BUFFER-VALUE.
    END.
    */
END.

By the way, it looks like a whole bunch of dynamic stuff, but I typed it in a few minutes. If you get used to dynamic coding, imho it's a lot easier to work with than the "normal" way of coding.
2 cents...

 

[Kladkul]

Thanks alot for your help thats a good example that I can use. I'm trying to get more used to using dynamic temp-tables, at first I didn't like them because it just seemed like extra code to write which seemed foolish. But I am rather new to Progress (only a few months experience with it).

Thanks again.

 

[palthe]

Thanks alot for your help thats a good example that I can use. I'm trying to get more used to using dynamic temp-tables, at first I didn't like them because it just seemed like extra code to write which seemed foolish. But I am rather new to Progress (only a few months experience with it).

Thanks again.

Programming dynamically is useful because you're not directly compiling it against a database. So the compiling is always done at runtime.

I guess you didn't mean that using temptables seemed foolish but just the dynamic use of them?

 

[Kladkul]

Yea, the dynamic use of them is what I thought was foolish. Only because I didn't fully understand their purpose (IE: the fact that it doesn't compile against the DB).