Dynamic Query

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
 

Stefan

Well-Known Member
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.

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.
 

Osborne

Active Member
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:

 
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
 
Top