How to re-use same code for 2 databases.

[manojchhaya]

Hello All,

Please help me. I want to re-use same code for two database having same structure. I try these steps but not working.
Step 1: Create 2 copies of the sports database with different
physical names, for example testdb1 and testdb2.
Step 2: Connect a session to one of them, using the logical
name x:
pro testdb1 -ld x
Step 3: Create the following program and save it as up.p.
/* up.p */
FIND FIRST x.customer.
UPDATE x.customer.name.
Step 4: Compile and save up.p as follows:
COMPILE up.p SAVE.
Step 5. Exit the Progress editor, and start again, connecting
the 2 databases, the first with logical name aaa, and the
second with logical name bbb.
pro testdb1 -ld aaa testdb2 -ld bbb
Step 6. You can now access both databases with the up.p code as
follows:
CREATE ALIAS x FOR DATABASE aaa.
RUN up.p.
DELETE ALIAS x.
CREATE ALIAS x FOR DATABASE bbb.
RUN up.p.

But this shows an Error of Ambiguity.
Because the Customer table exists in both the database.
Please help me.

Regards
Manoj Kumar

 

[sphipp]

Try:
CONNECT aaa.
CREATE ALIAS x FOR DATABASE aaa.
RUN up.p.
DELETE ALIAS x.
DISCONNECT aaa.
CONNECT bbb.
CREATE ALIAS x FOR DATABASE bbb.
RUN up.p.

The problem is that you have both databases connected at the same time and the code can't tell which set of tables you mean.

If you start with (ideally) no databases connected, then connect/process/disconnect each in turn then you should be OK.

 

[manojchhaya]

Hello Sphipp,
Thanks for your response.
have u try this code?
I have two DB. One is connected all time and another one is connect on run time do some update.
After that we can disconnect the second DB.
I think now you understand the problem.

Thanks
Manoj

 

[sphipp]

OK, in that case you need to ensure that up.p contains no ambiguous references to tables (so every table or field reference is x.table.field). Then you need to do something like:

CREATE ALIAS x FOR DATABASE aaa.
RUN up.p.
DELETE ALIAS x.
CONNECT bbb.
CREATE ALIAS x FOR DATABASE bbb.
RUN up.p.
DISCONNECT bbb.

Also, you need to ensure that this code is in a separate program that doesn't refer to any tables as this may confuse Progress.

The following code works on V9.1E:

DEFINE VARIABLE icount AS INTEGER    NO-UNDO EXTENT 2.
 
CONNECT -db db1 -H host1 -S 0000 -N TCP -ld db2.

RUN pxxx ("db1",1).
RUN pxxx ("db2",2).

DISCONNECT db2.
DISPLAY icount.
 
PROCEDURE pxxx:
    DEFINE INPUT  PARAMETER pi_cdb AS CHARACTER  NO-UNDO.
    DEFINE INPUT  PARAMETER pi_iseq AS INTEGER    NO-UNDO.
    CREATE ALIAS xxx FOR DATABASE VALUE (pi_cdb).
    RUN xxx.p (OUTPUT icount[pi_iseq]).
    DELETE ALIAS xxx.
END PROCEDURE.

xxx.p:

DEFINE OUTPUT PARAMETER po_icount AS INTEGER    NO-UNDO.
FOR EACH xxx.systimes WHERE xxx.table.field = "Test" NO-LOCK:
    ASSIGN po_icount = po_icount + 1.
END.

 

[manojchhaya]

Hello Sphipp,

Thanks for your response.
This code is working fine.but there is one problem you cann't update any record. when you are updating any record its shows ambiguity error.

Regards
Manoj

 

[taqvia]

/*manoj.p*/

DEF VAR arshad12 AS CHAR.
CONNECT -db c:\arshad .
arshad12= LDBNAME(1).
MESSAGE arshad12.
RUN c:\test1.p arshad12 .
DISCONNECT arshad.
CONNECT -db c:\ars .
arshad12=LDBNAME(1).
RUN c:\test1.p arshad12.
DISCONNECT ars.


/*test1.p*/
/*DEF INPUT PARAMETER taqvi AS CHAR.*/
DEF VAR t1 AS CHAR.
DISABLE TRIGGERS FOR LOAD OF {1}.customer. /*because trigger compiled against sports and i have created it as arshad and ars */
FIND FIRST {1}.customer WHERE NAME = "ManojKumar1" EXCLUSIVE-LOCK NO-ERROR.
IF NOT AVAILABLE {1}.customer THEN
DO:

CREATE {1}.customer.
{1}.customer.NAME ="ManojKumar1".
{1}.customer.cust-num = 240002.
MESSAGE " from first created " .
PAUSE 5.
END.
ELSE
MESSAGE "Taqvi Already Exists".
MESSAGE " {1} ".
FOR FIRST {1}.customer WHERE NAME = "taqvimanoj" NO-LOCK:
MESSAGE {1}.customer.NAME.
MESSAGE " why the hell control not coming".
END.
/*FOR FIRST {1}.customer NO-LOCK:
MESSAGE " from first" .
END.
*/


Regards,

Arshad

 

[sphipp]

his code is working fine.but there is one problem you cann't update any record. when you are updating any record its shows ambiguity error.

Are you trying to update records in up.p? If so, you need to refer to tables as x.tablename.

If you are not updating records in up.p and you still have the second database connected then you will have problems later on when you try to access records.

You need to disconnect the second database as soon as you have finished in order to avoid ambiguity. What I normally do is have a separate program that connects to a database, sets up an alias, runs a query/program and disconnects the database. That way, I can run that program and I know that the database will not be connected once the program finishes.