If your going to use idea one, you may choose to simply use views to the
tables in the other database. It could make life simpler for the DBA.
> -----Original Message-----
> From: Sharon Buntz [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, November 09, 1999 9:44 AM
> To: Jill M. Claus; Klebe, Paul E.
> Cc: Barr, Eric; [EMAIL PROTECTED]
> Subject: Re: PFCSIG: Connecting to Two Databases
>
> 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]
> [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]
