3 more ideas come to mind...
1) If the databases are on the same server, believe it or not, you can
point to two different databases in a SINGLE DataWindow (using only one
SQLCA transaction) by simply prefixing the table name with the server/db
name. I've never done this personally, but I have seen it done (using big
Sybase). I couldn't believe it when I heard it the first time, so I tried
it in a quickie DW and it worked! I can ask them for details if you want.
2) Check out "Gateway" tools that facilitate the integration of
databases. "Gateway" products make several databases appear as a single
database for you. (See news://forums.sybase.com/sybase.public.gateways -
Sybase Gateway Products: General Topics Newsgroup -- BTW, there is also an
"Omni" Gateway product.)
3) Try asking the appropriate database newsgroups for some ideas and
suggestions. They are experts at such things. Just as an abstract
example, with EAS, you can set up several transactions and control them
OUTSIDE of your program. Those DB experts would be on top of tips similar
to that that are new. (See
news://forums.sybase.com/sybase.public.sqlserver.general - SQLServer:
General Topics)
P.S. Going back to just sticking with two transactions and two
DataWindows/DataStores, do you realize that you can use the PFC 6.0+
Logical Unit of Work (LUW) service to update across multiple transactions?
Have fun,
~Sharon
--
Sharon Weinstrom Buntz | mailto:[EMAIL PROTECTED]
Cheat Sheet for PFC/PB Help | http://www.pfccheatsheet.com/
"Barr, Eric" wrote:
>
> 4 ways come to mind
>
> a) Nested datawindows that are just use sharedata to 2 datastores connected
> to the different databases.
>
> b) Retrieve separately into datastores and use the dot notation method to
> extract the data into array variables and then plop it into the visible
> datawindow. This of course requires you to have the same row count and row
> order for both which is difficult considering they don't know about each
> other.
>
> c) Use a stored procedure that spans the 2 databases.
>
> d) Just before retrieving the datawindow. Pipe the data from the second
> database into a prebuilt temporary table in the first. Then using a regular
> join to this table you can combine the data.
>
> > -----Original Message-----
> > From: Jill M. Claus [SMTP:[EMAIL PROTECTED]]
> > Sent: Monday, November 08, 1999 7:58 PM
> > 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]
