Question Scripted Combine Database and compare tables

[epicorsupport]

Hi,
Running vintage 9.1D. Requesting help on the best approach to achieve the following:
- Periodic copy of Live database to MSTR database. Renaming the Tables with a prefix MSTR_xxxx
- Followed by a later 2nd copy of the same Live database merged into the above MSTR database without a Table rename.
The purpose of the above is to then run a procedure to compare the records in each Table , MSTR_xxxx with xxxx in order to find records that have changed.
Upon finding a record that has changed, assign UD Date field to todays date.
- Run Select Query to extract the changed records selecting by the UD Date field. The result is exported to a CSV file,
which are used in another process to apply updated records from Progress to a SQL Database.
- After above Select / Export, the current xxxx Tables are copied to the MSTR_xxxx Tables , and a fresh copy from the LIVE database to xxxx Tables is performed.

The above process most likely ran nightly, resulting in a CSV file of records that have been modified in the Live database by date. Date modified identified by the UD Date field.

Please comment on the best commands to use to achieve the individual tasks. Copy vs Backup / Restore.
I had planned on using Buffer-Compare while cycling through comparing records from MSTR_xxxx and xxxx table.

As I am a newbie to Progress Database commands and syntax, any actual scripts / command example is greatly appreciated.

Due to the Application running against LIVE using .r write triggers., the use of triggers is unavailable.

Thank You...

 

[TomBascom]

I'd say that the "best" way to get a list of changed records would be to upgrade to OpenEdge 11.7 or better and implement change-data-capture

Aside from that...

I don't think that you really need to go through the "rename the tables" shuffle. Just restore the db as "olddb" and then have your comparison program connect to both "olddb" and "freshdb". When you do the BUFFER-COMPARE prefix the table names appropriately. i.e.

/* totally untested psuedo-code
 */

define variable myresult as logical no-undo.

for each olddb.tableName no-lock:

  find freshdb.tableName no-lock where freshdb.tableName.key = olddb.tableName.key no-error.

  if not available( freshdb.tableName ) then
    . /* handle deleted records */

  buffer-compare olddb.tableName to freshdb.tableName save myresult.
  if myresult = false then
    . /* handle changed records */

end.

for each freshdb.tableName no-lock:

  find olddb.tableName no-lock where olddb.tableName.key = freshdb.tableName.key no-error.

  if not available( olddb.tableName ) then
    . /* handle newly created records */

end.

 

[epicorsupport]

Thank You.. Is this called a Dynamic Query ? Happen to come across it while digging .. Can I assign Todays Date to a UD field and update a record on finding a difference in the Buffer-Compare ? Seems logical but I am a SQL guy.. Would it disrupt pointers from the initial Find ? Appreciate the response.

 

[TomBascom]

No, those are static queries but with fully qualified table names. Usually you can omit the dbname because you are either only connected to one db or the table names are unique across the connected databases. When you have two or more connected databases that contain tables that have the tables with the same name you need to prefix the table name with the db name (or an alias).

 

[TomBascom]

Dynamic queries are certainly possible and could potentially be used but I was going for a simple example.

If the list of tables that you plan to compare is short and stable it probably isn’t worth bothering with dynamic queries.

If I were coding this I would start with a static query and one table, get the bugs worked out and then look at expanding the solution (if necessary).

 

[epicorsupport]

Appreciate your time and knowledge ...