Dynamic Query

P

Progress_Learner

Member
Hello All,

I have two databases and I want to compare table by table and records by records on the basis of unique index/key only and later I have to record the changes found.

I know it can be done by using some dynamic query only (have very less idea of that), I tried to create dynamic query for this but things are not going in the right direction. Below is the code that I tried:

Code: 
define var h1 as handle no-undo.

define var h2 as handle no-undo.

define var h3 as handle no-undo.

define var fh as handle no-undo.

define var bh as handle no-undo.

define var ch-tname as char no-undo.

define var ch-uidx-field as char no-undo.



for each db1._file where _tbl-type = "T" no-lock,

                first _index of _file where _index.unique = true no-lock,

                first _index.field of _index no-lock,

                first _field of _index-field no-lock:



/* This will give first unique index field name */



ch-uidx-field = _field._field-name.

ch-tname = _file-name.

create buffer bh for table ch-tname.  /* Is it fine to put create dq here */

create query h1.

h1:set-buffers(bh).

h1:QUERY-PREPARE("FOR EACH" + ch-name + "NO-LOCK")



create query h2.

h2:set-buffer(db2.bh). /* like this? */

h2:query-prepare("find first" + ch-name + use-index + ch-uidx-field).  /* seems wrorng */

end. /* end of first for each loop */

Kind thanks and regards for any help.

Learner
 
When you are connected to two databases with the same or similar definitions, you will need to provide the database alias when creating buffers - see documentation.
If table-name is ambiguous, you must qualify the database table name with a database name or rename the temp-table. Otherwise, if the database table exists in multiple connected databases, the AVM creates the buffer in the first connected database.
Click to expand...

This works out to something like:
Code: 
def var hb as handle no-undo extent 2.

create buffer hb[1] for table substitute( "&1.&2", ldbname(1), ctable ).
create buffer hb[2] for table substitute( "&1.&2", ldbname(2), ctable ).
You now have two dynamic buffers that point to the two tables in your two databases.
 
I thought that a posting/article existed that showed how to do this with dynamic queries but can only find one that does a copy:
Code: 
DEF VAR tablename as CHAR INITIAL "table1,table2,table3" /* and so on */
DEF VAR cTraverseOldTable AS CHARACTER.
DEF VAR iTableArrayIndex AS INT.

DO iTableArrayIndex = 1 TO NUM-ENTRIES(tablename,","):
    DEF VAR hQueryOldDatabase AS HANDLE.
    DEF VAR hOldDatabaseBufferData AS HANDLE.
    DEF VAR hNewDatabaseBuffer AS HANDLE.

    cTraverseOldTable = "for each " + otsName + "." + ENTRY(iTableArrayIndex,tablename,",").
    CREATE QUERY hQueryOldDatabase.
    CREATE BUFFER hOldDatabaseBufferData FOR TABLE otsName + "." + ENTRY(iTableArrayIndex,tablename,",").
    CREATE BUFFER hNewDatabaseBuffer FOR TABLE ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").

    hQueryOldDatabase:SET-BUFFERS(hOldDatabaseBufferData).
    hQueryOldDatabase:QUERY-PREPARE(cTraverseOldTable).
    hQueryOldDatabase:QUERY-OPEN.
    hNewDatabaseBuffer:DISABLE-LOAD-TRIGGERS(FALSE).

    COPY-BLOCK:
    REPEAT :
       hQueryOldDatabase:GET-NEXT().
       IF hQueryOldDatabase:QUERY-OFF-END THEN LEAVE.

       DEF VAR cTraverse AS CHARACTER.
       DEF VAR cTableIndex AS CHARACTER.
       cTraverse = "for each " + ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").
       DEF VAR hQuery AS HANDLE.
       DEF VAR hBufferData AS HANDLE.
       CREATE QUERY hQuery.
       CREATE BUFFER hBufferData FOR TABLE ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").
       hQuery:SET-BUFFERS(hBufferData).
      hQuery:QUERY-PREPARE(cTraverse).
                hQuery:QUERY-OPEN.

      /* and the block that copies data from old and creates records in new: */
      CREATE-BLOCK:
      DO ON ERROR UNDO, THROW:
         hNewDatabaseBuffer:BUFFER-CREATE() NO-ERROR.
         hNewDatabaseBuffer:BUFFER-COPY(hOldDatabaseBufferData) NO-ERROR.
         IF ERROR-STATUS:ERROR THEN DO:
            LEAVE COPY-BLOCK.
         END.
         CATCH someError AS PROGRESS.Lang.ERROR:
            MESSAGE someError
            VIEW-AS ALERT-BOX INFO BUTTONS OK.
         END CATCH.
/* end CREATE-BLOCK DO */
                END.

To compare you would need to extract the primary index information for the table, find the relevant record on the other database using that index information and BUFFER-COMPARE for the differences:
Code: 
hBufferTarget:FIND-UNIQUE(vFindCriteria, NO-LOCK) NO-ERROR.
IF hBufferTarget:AVAILABLE THEN DO:
   IF NOT hBufferSource:BUFFER-COMPARE(hBufferTarget,"CASE-SENSITIVE") THEN DO:
      ...
   END.
END.

These articles may help:

Progress Customer Community

knowledgebase.progress.com knowledgebase.progress.com

Progress Customer Community

knowledgebase.progress.com knowledgebase.progress.com
 
Last edited:
Dear All,

Thanks for your valuable replies!

I want to compare all transnational tables from two different databases not few (table by table and record by record) and on the basis of unique index.
 
Last edited:
Hi,
If you are using "hBufferTarget:FIND-UNIQUE(vFindCriteria, NO-LOCK)",
you could have a big activity on the VST _field (on ldbname(2) ), 1 read per field on the index and per record in the table .
(for an index with 4 field and 10 000 records to compare , you will read 40 000 _field and 10 000 "table").
If you are using a REMC it will be worst.
( same example , I think at least 40 000 40 000 packet sent/received only for the VST, …)

You could probably use some things like that .
Outer-Join are not so good for performance but i think it could be better than using :FIND-UNIQUE.

Code: 
/*
DB1 & DB2 are the logical dbname for your 2 DB
*/
DEFINE VARIABLE hb_db1              AS HANDLE NO-UNDO .
DEFINE VARIABLE hb_db2              AS HANDLE NO-UNDO .
DEFINE VARIABLE h1                  AS HANDLE NO-UNDO.
DEFINE VARIABLE v-qprepare          AS CHAR   NO-UNDO.
DEFINE VARIABLE FIND-UNIQ-INDEX     AS LOG    NO-UNDO.

FOR EACH DB1._file NO-LOCK WHERE _tbl-type = "T" :
      FIND-UNIQ-INDEX = FALSE .
      v-qprepare = "".
      FOR FIRST DB1._index NO-LOCK WHERE _index._File-recid = RECID(DB1._file) AND _index._unique = TRUE AND _active = TRUE ,
          EACH DB1._index-field NO-LOCK WHERE _Index-recid  = RECID(DB1._index ),
          FIRST DB1._field      NO-LOCK WHERE RECID (DB1._field) = DB1._index-field._Field-recid :
           v-qprepare = v-qprepare + " AND buffer-db1." + DB1._field._field-name + " = buffer-db2." + DB1._field._field-name .
           FIND-UNIQ-INDEX = TRUE .
      END.
      v-qprepare = "FOR EACH buffer-db1 No-LOCK , First buffer-db2 No-LOCK WHERE " +
                    SUBSTRING( Trim(v-qprepare) , 4 )+  " OUTER-JOIN ." .

      IF FIND-UNIQ-INDEX AND _file-name = "tabgco" THEN DO :
          CREATE BUFFER hb_db1 FOR TABLE "DB1." + DB1._file._file-name BUFFER-NAME "buffer-db1" NO-ERROR .
          CREATE BUFFER hb_db2 FOR TABLE "DB2." + DB1._file._file-name BUFFER-NAME "buffer-db2" NO-ERROR .

         CREATE QUERY h1.
         h1:set-buffers(hb_db1 , hb_db2 ) .
         h1:QUERY-PREPARE( v-qprepare  ) .
         h1:QUERY-OPEN.
         h1:GET-NEXT(NO-LOCK).
         DO WHILE NOT h1:QUERY-OFF-END :
             IF hb_db2:AVAIL = FALSE 
             THEN DO :
                 /* ...*/
             END.
             ELSE
                 IF NOT hb_db1:BUFFER-COMPARE(hb_db2,"CASE-SENSITIVE") THEN DO:
                     MESSAGE "DIFF" VIEW-AS ALERT-BOX INFO BUTTONS OK.
                 END.
             h1:GET-NEXT(NO-LOCK).
         END.
         h1:QUERY-CLOSE.
         DELETE OBJECT hb_db1  .
         DELETE OBJECT hb_db2  .
         DELETE OBJECT h1      .
      END.


END.
Patrice
 
You must log in or register to reply here.
Back
Top