Connecting to Multiple Progress Database using single .p file

Question

Is there a way to connect to multiple progress database. In current situation what we do is we use multiple .p files to fetch the data. Example: 1st program will fetch the data from customer database and using a run command we use to connect to 2nd database. 2nd program we use input parameter to map the value.

My question is, is there a way we can do this in one single program? Below is the sample program:

/*FIRST Program***/

FIND FIRST customer WHERE customer.cust-id EQ "v456" NO-LOCK NO-ERROR. IF AVAILABLE customer THEN RUN /HOME/dbconnect.p(INPUT customer.cust-id, "ACCOUNTS"). RUN /HOME/program-2.p (INPUT customer.cust-id).

/Second Program**/ DEFINE INPUT PARAMETER ipcCust-id AS CHARACTER.

FOR EACH billing WHERE billing.cust-id EQ ipcCust-id NO-LOCK: DISPLAY billing.DATE. END.

Tom Bascom Tom Bascom · Accepted Answer · 2021-08-05T19:17:44

You can use the CONNECT statement to connect to databases at runtime. You cannot CONNECT and access the newly connected db in the same procedure - the code that uses the new connection must run in a sub-procedure.

You might, for instance, do something like this:

define variable dbList as character no-undo.

define variable i as integer no-undo.
define variable n as integer no-undo.

dbList = "db1,db2,db3".

n = num-entries( dbList ).

do i = 1 to n:

  connect value( entry( i, dbList )) no-error.

  run "./p1.p".
  current-language = current-language.    /* forces the r-code cache to be cleared */

  disconnect value( entry( i, dbList )) no-error.

end.

and p1.p:

/* p1.p
 */

define variable i as integer no-undo.

for each _field no-lock:  /* count the number of fields defined in the schema */
  i = i + 1.
end.

display pdbname(1) i.

pause.

p1.p is just a silly little program to demonstrate that the data access is actually coming from 3 distinct databases.

The "current-language = current-language" thing is important if the same procedure will run against several different databases. Without that little nugget the procedure may be cached and it will remember the previous db that it was connected to.

Or if you prefer Stefan's dynamic query approach:

define variable dbList as character no-undo.

define variable i as integer no-undo.
define variable n as integer no-undo.

define variable b as handle no-undo.
define variable q as handle no-undo.

create query q.

dbList = "db1,db2,db3".

n = num-entries( dbList ).

do i = 1 to n:

  connect value( entry( i, dbList )) no-error.

  create buffer b for table "_field".

  q:set-buffers( b ).
  q:query-prepare( "preselect each _field no-lock" ).
  q:query-open().

  display pdbname( 1 ) q:num-results.
  pause.

  q:query-close.

  delete object b.

  disconnect value( entry( i, dbList )) no-error.

end.

Connecting to Multiple Progress Database using single .p file

2 Answers