Subject Re: [ib-support] Cross-db query
Author Antonio Parrotta
Woody,
I know that way used from you, I started using lookup field. But since I have to use a grid to view the join query, then user need to sort the data by clicking on the column header. So this approach, as you know, does not work, because the related column field is an id and not the actual data (I use the lookup approach). This is the reason that constrain me to use a query to join the tables.
I appreciate so much your help, I hope we can find a way to solve this problem.
thanks again
Antonio
ps: I'm using Delphi 6
Woody <woody.tmw@...> wrote:>
> Hi Woody,
> how can I link the tables? can you explain me more clairly please? take
in mind that I need for queries that join two or more table that resides in
different database. Suppose to have a table "product" in the master db and a
table "customer" in the service db. How would you make a query that list the
customer name and the related product name (the "customer" has a "productID"
field)?

What programming language are you using? As I stated, I use Delphi so I'll
explain a little by using examples for it. Bear in mind that you can not
create joins at this time, but you can link the queries such that one
"drives" the other.

On my main datamodule, I have 2 TIBDatabase components. One is set to point
to the main database and the other is set to point to an archive database.
Most lookup information is only contained in the main database, so whenever
I am working with information from the archive database, I simply have 2
connections open. The master query is pointing to information in the archive
database and the detail query is pointing to information in the master
database. In Delphi, you create a master/detail link by associating the
dataset of the detail query to the datasource of the master query. It
doesn't matter what the master query is referencing nor does it matter if
they reference different databases. As long as the fields are linked in a
master/detail relationship, everything works fine.

If you want to create the master/detail "link" yourself, it's almost as
easy. Simply build the detail query with the appropriate parameters and you
can fill those in each time the master record changes. Depending on what
components you use, you may or may not need to open and close the detail
query to get the correct results.

For my own purposes, I built a class where I can pass a database name, user,
password and query to perform and the class will create all the components
necessary to do the work and open the query. I can create as many queries as
I want and work with them without the need for creating them at design time.
They can either have their own transaction or they can all share the same
transaction. I have started to build a heterogeneous join functionality into
it but haven't completed it yet. When it's done, it will allow me to do
joins on tables in different databases by simulating the join without
needing to control it from outside. I will happily share the class with the
outside world once it is complete, for free even! :) Maybe someone will be
able to modify it such that it could use any type of connection component
besides IBX, such as IBO or whatever.

HTH
Woody (TMW)

----------------------
"I don't know the key to success, but the key to failure is to try to please
everyone."
-Bill Cosby


Yahoo! Groups SponsorADVERTISEMENT

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



---------------------------------
MIo Yahoo! : personalizza Yahoo! come piace a te

[Non-text portions of this message have been removed]