(Yes, I do reference PFC in here once, if weakly.)
AFAIK, it's either a task you have to program or find a third-party
"middle-ware" product that will perform the "virtual join" for you and act
as if it's really one DB connection.
Unfortunately, DataWindows don't act like views or snapshots, in the
sense that you can't easily extract only certain columns from them and treat
them as a DBMS result set.
An approach that might save at least some headaches is:
1. Create a DWO against one of the DB's. This should include any
retrieval arguments and all of the columns you want in the result set, as
well as any columns you need to perform the join.
2. Create a DWO against the other DB. This should include any
retrieval arguments and all of the columns you want in the result set, as
well as any columns you need to perform the join.
3. Create a external DWO that has the columns that you want in the
virtual result set (i.e., the join of the two DB's). (Strictly speaking,
this doesn't have to be an external DW; you might could also make it based
on one of the other DWO's and add columns to the result set to act as
placeholders for the fields you want from the second DBMS.)
4. Create an n_ds descendant and associate it with this external DW.
(You can do this either at design time or in the constructor event; I prefer
the latter.)
5. Extend the pfc_retrieve event:
5.1 Declare and instantiate two local n_ds'.
5.2 SetPointer(HourGlass!).
5.3 Set their dataObject attribute to the names of the DWO's
created in #1 and #2.
5.4 Associate them with the appropriate transaction objects.
5.5 Retrieve the first one. If error or no rows, return -1 or 0,
respectively.
5.6 Use a loop to go through the result set.
5.6.1 Retrieve the second DWO. Handle error and no rows as
appropriate to your app.
5.6.2 For each row in the second result set
5.6.2.1 ll_mergerow = this.InsertRow(0).
5.6.2.2 Copy the data from the row you're on in the first DWO to
the ll_mergerow row of the this buffer
5.6.2.3 Copy the data from the second result set to the ll_mergerow
row of the this buffer. Yes, these copies are basically a couple of "for
each column" loops (you may be able to use column numbers rather than names,
but this is risky if your design isn't stable, and it sounds like it isn't).
5.7 Destroy the two local n_ds'.
(Note--if the merged result is a 1-to-1 join, you may be able to optimize
step 5.6.2 by retrieving both result sets and using Find instead of a nested
Retrieve of the second DWO.)
This looks like a headache, but consider what if you need to update both
databases... By having this intermediary n_ds, you "simply" need to modify
its pfc_update event. (Be sure to set the update flags accordingly in the
DWO you created in step 3 above.) Note that if your client PC's have
sufficient memory to hold both results as well as the merged one, you may
want to make the n_ds' you created in step 5 instance variables, initialize
them (dataObject and transObject) in the constructor event and destroy them
in the destructor event, rather than in the pfc_retrieve event.
I've not done this, but I suspect the PFC multi-table update service may
be of use here if you need to perform updates. On the other hand, it might
be simpler to farm the data back from the merged set to the independent
buffers and update them independently.
Of course, with multiple connections, there's no simple and reliable way
to ensure the integrity of the entire transaction across both servers.
There are ways, but the simple ways aren't reliable, and the reliable ones
aren't simple. (Get a book or two on multi-phase commits.)
This is all assuming your client is the only connection between the
servers that is available to your program. If the servers can talk to each
other, you may other other options to consider.
--dang
-----Original Message-----
From: Jill M. Claus [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 08, 1999 13:58
To: Klebe, Paul E.; [EMAIL PROTECTED]
Subject: RE: PFCSIG: Connecting to Two Databases
Although I would find it extremely useful - I have not figured out a way to
combine information from two different databases into one datawindow.
Maybe someone else in the user group may have a suggestion. If you do get a
solution from someone addressed to your personal email address, could you
please forward it to me? This is something I really need as well.
>>> "Klebe, Paul E." <[EMAIL PROTECTED]> 11/08/99 10:15AM >>>
Thanks. This means that although you can display information from two
different databases in two different datawindows in a window, you cannot
directly combine two tables from different databases on the same
datawindow.
How do you combine information from the two different databases into the
same window? Can you use a common id column multiple times and a cached
dropdown_datawindow to display various fields from the other database?
I would like to develop a single common name-address database that is
used throughout the commission rather than using separate tables in each
database for our 8 divisions.
-----Original Message-----
From: Jill M. Claus [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 04, 1999 12:13 PM
To: [EMAIL PROTECTED]
Subject: RE: PFCSIG: Connecting to Two Databases
Hi Paul,
This is the first time I did it, so it may not be the best way... but it
works!
I added some comments below that may help you ...
>>> "Klebe, Paul E." <[EMAIL PROTECTED]> 11/03/99 03:12PM >>>
My name is Paul Klebe, I work for the North Dakota Public Service
Commission. I have an upcoming project where I will be attempting to
connect to two different databases. Would you be willing to talk to me
about doing this. I am not getting where you are getting the connection
to two different databases.
-----Original Message-----
From: Jill M. Claus [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 03, 1999 1:39 PM
To: [EMAIL PROTECTED]
Subject: RE: PFCSIG: Connecting to Two Databases
I got it working - thanks everyone!
For anyone curious, I changed the n_tr instance variable to be global
and edited the application manager pfc_open event code to be:
// enable transaction registration (the registration service is need
when using more than one database)
gnv_app.of_SetTrRegistration(TRUE)
// register SQLCA (the connection to the first database which was
connect to the usual way)
gnv_app.inv_trregistration.of_Register(SQLCA)
// connect to and register ABRN_MRS database (the second database)
// create the second transaction object - note that you must have "n_tr
gtr_amrs" identified as a global variable
gtr_amrs = CREATE n_tr
// copy connection info from the first database called SQLCA to the
second database called gtr_amrs - it copies the user id, password,
database name, server name, etc. from the definition of the first
database connection to the definition of the second database connection
il_return = SQLCA.of_CopyTo(gtr_amrs)
// re-assign the database name to the second definition
gtr_amrs.Database = "your_2nd_database_name"
// re-assign the server name to the second definition - if it's
different
gtr_amrs.ServerName = "your_2nd_server_name"
// connect to the second database
ll_return = gtr_amrs.of_Connect()
If ll_return <> 0 then MessageBox("Error", "Unable to connect.")
// register the second database with the registration service
gnv_app.inv_trregistration.of_Register(gtr_amrs)
Then when ever you want to retrive data from the 2nd database, you would
say... (in the window's open event)
li_return = dw_1.of_SetTransObject(gtr_amrs)
dw_1.retrieve()
instead of...
li_return = dw_1.of_SetTransObject(SQLCA)
dw_1.retrieve()
Hope this helps!
Jill
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS,
ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS, ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
