FWIW...
At my shop we have datawindows that connect to multiple databases on Sybase and have
been doing so for years (since PB 3.0).
The only caveat is that the databases have to be on the same Sybase Server. To
connect to the two databases this is what we do:
1. Using the database administration painter or some other SQL Development tool
construct the query with the proper joins and the desired results.
2. Go to the Datawindow painter.
3. Select your data source and presentation style
4. Select one of the tables that you would like to join.
5. Once the table displays select any column.
6. In the menu, select Design/Convert To Syntax. You'll see a select statement
created for you. Highlight it and delete it.
7. Go back to the Database administration painter and copy the SQL created in step one
onto your clipboard (CTL C).
8. Go back to the datawindow painter window and paste (CTL V) the sql. Make sure you
qualify the tables in your select clause and the databases in the from clause of your
select statement.
For example:
select empDepartment..deptId,
department..descr
from employee..empDepartment, company..department
9. You can use the menu to add retrieval arguments and adjust your sql accordingly.
10. At this point you should be able to go back to preview mode and adjust the
presentation of your window accordingly.
Hope this helps.
Note to Jill: We don't use Oracle so I don't know if this will work but give it a shot
it might!
-Deanna
>>> Zehrung Joe R COCA <[EMAIL PROTECTED]> 11/09/99 08:38AM >>>
No I don't know anything about Sybase 11. Sorry
Maybe someone else out there knows if Sybase has database links.
-----Original Message-----
From: Jill M. Claus [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 08, 1999 3:47 PM
To: Zehrung Joe R COCA
Subject: RE: PFCSIG: Connecting to Two Databases
We are using Sybase 11 - do you know if it can do that?
>>> Zehrung Joe R COCA <[EMAIL PROTECTED]> 11/08/99 04:45PM >>>
Jill - What database are you using?
We get data into the same data window from two different data bases but they
are both ORACLE data bases. The data bases are linked with a dblink.
-----Original Message-----
From: Jill M. Claus [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 08, 1999 10:58 AM
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]
> [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]
