How does one reference across SQL DataBases? 6

[delphiman]

I am using Delphi 6 Enterprise and I have two DataBases.
ONE.gdb which encapsulates a Table called TBLME
TWO.gdb which encapsulates a table called TBLYOU

I need to achieve the following using

Select *
from TBLYOU t1, TBLME t2,
where t1.Father = t2.Father

But both TIBDataSet (and TIBQuery) only permit that one has access to all the tables in one DataBase at a time.

Does anyone know a way whereby one can access the tables in two seperate DataBases together and at the same time?

Is there perhaps a fancy DataSet (something?) somewhere which will permit this? Or do I have to create my own? :-(

Thanks in advance.

 

[LucieLastic]

You could try using 2 TDatabase components and set the Tquery property Databasename to point to the appropriate database comp.

 

[delphiman]

Thanks weez I have tried that already. LOL

The problem being that there is only provision for one Databasename property in a TQuery component. So
that one can only reference one of the two required tables at a time. Which is then also the only one to appear in the Tables window of the Command Text Editor.

Which means that a second TQuery cannot reference both tables.

The point being that if one cannot see both TBLYOU AND TBLME in the Tables window of the Command Text Editor one will get an exception from

from TBLYOU t1, TBLME t2

Which leaves me to suspect that I may have to make my own component. :-(

But I find it hard to beleive that this kind of cross-referencing between two data-bases can be so uncommon. There must surely be a way (by now)
- since Adam and Eve bought their PC - whereby one can do this.

Hoy! Mine Guru SvanEls! Where are you?

 

[svanels]

Terry the only way I see this is to import one of the tables in the other database, make a temporary table with a batch move component, although I have never done such thing before. Another way is to create a temporary paradox table, if you don't want to stuff things in the databases. Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Hi Steve!

I have tried to create my own IBDatabase component but this is fraught with difficulties (and exceptions) so I gave that up

It seems your idea of make a temporary table with a batch move component is the way to go.

Can you please refer me somewhere to learn how to create a temporary table. And what is a batch move component?

>Another way is to create a temporary paradox table,

Doesn't this mean I am back to using the BDE again? I am already involved with Interbase SQLServer to avoid that.

Regards
TERRY

 

[nvb]

Without using the BDE, and without having to create a temporary table, try using the (in my humble opinion brilliant) TkbmMemTable component. I know it is not a TQuery , but the filtering works perfectly. Maybe worth to take a look at.

The URL:

http://www.optical.dk/delphi/prod01.htm

Cheers,
Nico

 

[towerbase]

While the solutions suggested by Steven and Nico will work, the proper way to access a query across two databases is follows:

Leave the DatabaseName property of the TQuery component blank.

Specify the name of the BDE Alias for the database in the SQL as in the following example where I have used ALIAS1 and ALIAS2 as the two aliases.

SELECT Table1.col1, Table2.col2
FROM ":ALIAS1:Table1", ":ALIAS2:Table2"
WHERE Table1.Customer = Table2.Customer

Andrew

 

[nvb]

A star for you, Andrew!

There always seems to be an easier way... Cheers,
Nico

 

[delphiman]

Andrew haven't you have lost the plot? LOL

I am very specifically not using the BDE (perish the thing). As can be seen from the following I am using [TIBDataSet and TIBQuery) both being Interbase components using a .GDB DataBase. Especially so that I can use Interbase Server and not use the BDE.

>But both TIBDataSet (and TIBQuery) only permit that one >has access to all the tables in one DataBase at a time.

So unless I want to mess with the BDE again I can't be fiddeling with Aliases .....

Or is it me who has lost the plot? LOL

What am I missing?

 

[towerbase]

Terry (delphiman),

This was your question to which I (and others) provided answers.

"Does anyone know a way whereby one can access the tables in two seperate DataBases together and at the same time?"

and in a subsequent posting

"The problem being that there is only provision for one Databasename property in a TQuery component."

So I thought it reasonable to respond it terms of TQuery as it seemed to be what you could use.

The solution I provided is the standard Borland way of tackling the problem of accessing two databases from a TQuery. I don't have any experience of using InterBase so I can't help you there.

Sorry.

Andrew

 

[delphiman]

Thanks Andrew!

Back to nvb (Programmer). Greetings from downunder again. LOL

Mate...!!

I have downloaded TkbmMemTable component and have run Setup. But there are numerous files. Which one is supposed to be the component which I can install to the VCL?

Another concern I have is the idea of this being a "temporary" file.

The point being I want to make certain registrations into it whilst running the first .EXE But I trigger ANOTHER .EXE from the first and the successful operation of the second will be totally relient on the values in this temporary file as registered in it by running the first .EXE to work properly.

But methinks the moment I drop out of the first .EXE I'll lose the values in the temporary file.

Or will I?

What is your experience?

 

[Robertio]

Delphiman, IMHO you want to be setting up a view (I'm assuming you can create views in Interbase) then using a query against that view. Robertio
Alias: Robbie Calder
Software Developer
urc@walkermartyn.co.uk

 

[svanels]

Robertio what is the meaning of the expression IMHO? (sideline) Steven van Els
SAvanEls@cq-link.sr

 

[Robertio]

Sorry I spend too much time on forums, it is used for: In My Humble Opinion Robertio
Alias: Robbie Calder
Software Developer
urc@walkermartyn.co.uk

 

[svanels]

Well I have seen this a lot too, but now I know it, thanks Steven van Els
SAvanEls@cq-link.sr

 

[nvb]

About the memory tables: While the table is open, the contents stay there. Calling close destroys the data. But, updating from another EXE? I don't know whether it is possible. Maybe the best is to check the news group. The component to use is TkbmMemTable, from the unit with the name kbmMemTable. I installed the package kbmMemD5Des.bpl, and this gave me four components, of which the first, with an icon with "mem" written on it, is the one to use. Cheers,
Nico

 

[delphiman]

>Calling close destroys the data.

What I feared! :-(

>But, updating from another EXE? I don't know whether it >is possible.

It doesnt look like it. Meanwhile I am so surprised that the notion of accessing tables across different data-bases has apparently never been addressed. Needed even.!! Perhaps with a suitable DataSet component.

Thanks for your interest ad time.

 

[towerbase]

Terry,

The notion of accessing tables across different databases has been addressed by Borland. The mechanism to use is the BDE. According to the documentation, you can use the BDE to access InterBase tables.

It's up to you whether you use the BDE or not. You might not like the BDE. But it is a bit unfair to say that your problem has not been addressed.

If you use the BDE you can query tables in multiple databases. I used to find the BDE a bit flakey but I've had no problems with it in the last couple of years.

Andrew

 

[delphiman]

>The notion of accessing tables across different databases >has been addressed by Borland. The mechanism to use is >the BDE.

Quite right!

But whilst the whole question of deploying and installing the BDE to a user's machine after some (years of!) hard work remains a big hassle (not to mention having to make sure that all your Aliases have been installed on the user's machine as well) I now stay far away from the BDE.

Too much pain man ...too much pain. LOL

Anyway whilst using a .GDB DataBase and Interbase (giving much more advantages and power through the use of SQL)who wants/needs) to be married to the BDE?

I still reckon we need a DataSet designed to cross reference with tables in other GDB DataBases.

How about it ?

 

[towerbase]

Question: "... who wants/needs to be married to the BDE? "

Answer: Someone who wants to access two databases from a single a query.

I have used both InstallShield Express (free with Delphi) and Wise for installing BDE based applications and found no hassle or pain with deployment or setting Aliases.

If you develop your own installation code for BDE applications without using a proper installation program then it probably is a nightmare.


Andrew