Feedback
Did this article resolve your question/issue?

   

Article

How to dynamically construct a query PREPARE-STRING using the buffer objects KEYS Attribute

« Go Back

Information

 
TitleHow to dynamically construct a query PREPARE-STRING using the buffer objects KEYS Attribute
URL NameHow-to-dynamically-construct-a-query-PREPARE-STRING-using-the-buffer-objects-KEYS-Attribute
Article Number000169891
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All Supported Platforms
Question/Problem Description
How to dynamically construct a query PREPARE-STRING using the buffer objects KEYS Attribute
Based on a buffer's KEYS attribute for one table, how to build a query string on another table ?
 
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
The following code sample shows how to take the KEYS attribute of a source table's buffer, and build a query with a WHERE phrase on a target table's buffer. This requires a connection to Sports2000 database to run.

This example assumes:
- Both source and target temp-tables are dynamic. It can be expanded to work with static temp-tables/buffers/queries as long as the handle references are set to the correct static objects.
- Both source and taget tables have the same schema. As written, it will work as long as the target table has matching fields (same name and data type) for each field in the source table's primary unique index.
DEFINE VARIABLE httSourceTable       AS HANDLE NO-UNDO.
DEFINE VARIABLE httSourceTableBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE httTargetTable       AS HANDLE NO-UNDO.
DEFINE VARIABLE httTargetTableBuffer AS HANDLE NO-UNDO.

DEFINE VARIABLE iLoopCounter            AS INTEGER   NO-UNDO.
DEFINE VARIABLE hSourceTableBufferField AS HANDLE    NO-UNDO.
DEFINE VARIABLE cSourceKeysList         AS CHARACTER NO-UNDO.
DEFINE VARIABLE iSourceNumKeys          AS INTEGER   NO-UNDO.

DEFINE VARIABLE cWhereString            AS CHARACTER NO-UNDO.
DEFINE VARIABLE httTargetQuery          AS HANDLE    NO-UNDO.

/* PREPARATION */
/* Create ttSourceTable dynamic temp-table */
CREATE TEMP-TABLE httSourceTable.
httSourceTable:ADD-NEW-FIELD("CustNum","INTEGER", 0,"99999").
httSourceTable:ADD-NEW-FIELD("Name","CHARACTER", 0, "x(20)").
httSourceTable:ADD-NEW-FIELD("City","CHARACTER", 0, "x(20)").
httSourceTable:ADD-NEW-FIELD("Balance","DECIMAL", 0, "999,999.99").
httSourceTable:ADD-NEW-INDEX("TableIndex", YES, YES, NO).
httSourceTable:ADD-INDEX-FIELD("TableIndex", "CustNum").
httSourceTable:ADD-INDEX-FIELD("TableIndex", "Name").
httSourceTable:ADD-INDEX-FIELD("TableIndex", "City").
httSourceTable:TEMP-TABLE-PREPARE("ttSourceTable").

/* Create ttTargetTable dynamic temp-table - use same schema as source table for convenience */
CREATE TEMP-TABLE httTargetTable.
httTargetTable:CREATE-LIKE(httSourceTable).
httTargetTable:TEMP-TABLE-PREPARE("ttTargetTable").

/* Populate temp-tables with data sample from Customer table */
ASSIGN httSourceTableBuffer = httSourceTable:DEFAULT-BUFFER-HANDLE
       httTargetTableBuffer = httTargetTable:DEFAULT-BUFFER-HANDLE.

FOR EACH customer NO-LOCK WHERE customer.custnum < 10:
    httSourceTableBuffer:BUFFER-CREATE.
    ASSIGN httSourceTableBuffer::CustNum = Customer.CustNum
           httSourceTableBuffer::NAME = Customer.Name
           httSourceTableBuffer::City = Customer.City
           httSourceTableBuffer::Balance = Customer.Balance.
  
    IF customer.custnum < 5 THEN DO:
        httTargetTableBuffer:BUFFER-CREATE.
        ASSIGN httTargetTableBuffer::CustNum = Customer.CustNum
               httTargetTableBuffer::NAME = Customer.Name
               httTargetTableBuffer::City = Customer.City
               httTargetTableBuffer::Balance = Customer.Balance.
    END.
END.

/* MAIN LOGIC */
/* Find a ttSourceTable record to build WHERE phrase from */
httSourceTableBuffer:FIND-FIRST("WHERE ttSourceTable.custnum > 0").

ASSIGN cSourceKeysList     = httSourceTableBuffer:KEYS
       iSourceNumKeys      = NUM-ENTRIES(cSourceKeysList).

DO iLoopCounter = 1 TO iSourceNumKeys:
    hSourceTableBufferField = httSourceTableBuffer:BUFFER-FIELD(ENTRY(iLoopCounter,cSourceKeysList)).  
    IF  hSourceTableBufferField:DATA-TYPE = "CHARACTER" THEN
        cWhereString =  cWhereString + htttargetTableBuffer:NAME + "." + hSourceTableBufferField:NAME + " = " + QUOTER(hSourceTableBufferField:BUFFER-VALUE) + " AND ".
    ELSE
        cWhereString =  cWhereString + htttargetTableBuffer:NAME + "." + hSourceTableBufferField:NAME + " = " + hSourceTableBufferField:BUFFER-VALUE + " AND ".
END.
cWhereString = "WHERE " + RIGHT-TRIM(cWhereString, " AND ").

/* Create ttTargetTable query object */
CREATE QUERY httTargetQuery.
httTargetQuery:SET-BUFFERS(httTargetTableBuffer).
httTargetQuery:QUERY-PREPARE("PRESELECT EACH ttTargetTable " + cWhereString).
httTargetQuery:QUERY-OPEN().

MESSAGE "Prepare String:    " SKIP httTargetQuery:PREPARE-STRING  SKIP
        "Number of results: " httTargetQuery:NUM-RESULTS                 
    VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.

 
Workaround
Notes
Last Modified Date9/24/2020 6:11 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.