Board index » delphi » Joining two tables from different databases

Joining two tables from different databases

Hi

I have a problem joining tables from two different database.

AlarmsQuery.Sql.Clear;
AlarmsQuery.Sql.add('select * from alarmqueue aq,  ":DB2:FIFO" fi');
AlarmsQuery.Sql.add('where');
AlarmsQuery.Sql.add('aq.AlarmEntryID not in (select AlarmEntryID from fi
where aq.AlarmEntryID=fi.AlarmEntryID)');
AlarmsQuery.Open;

I have tried different combinations but none worked!

I have logged the errors and they look like this .

10-02-2002 01:45:05 [Fejl       ] SQL Server message 208: Invalid object
name ':WinRapSQLDB:fifo.db'.(line 1)
10-02-2002 01:45:58 [Information] WinRapSQL start
10-02-2002 01:46:01 [Fejl       ] SQL Server message 208: Invalid object
name 'WinRapSQLDB:FIFOTbl'.(line 1)
10-02-2002 01:46:32 [Information] WinRapSQL stop
10-02-2002 01:46:36 [Information] WinRapSQL start
10-02-2002 01:46:42 [Fejl       ] SQL Server message 208: Invalid object
name 'WinRapDB:FIFOTbl'.(line 1)
10-02-2002 01:47:06 [Information] WinRapSQL stop
10-02-2002 01:47:09 [Information] WinRapSQL start
10-02-2002 01:47:15 [Fejl       ] SQL Server message 208: Invalid object
name 'FIFOTbl'.(line 1)
10-02-2002 01:48:24 [Information] WinRapSQL stop

I'm joining a table from a mssql database and a local paradox tabel TTable,
the fifo tablefilename is fifo.db.

Any suggestions?

TIA
Henrik

 

Re:Joining two tables from different databases


AFAIK you can't do that.

Quote
Henrik Nedergaard wrote:
> Hi

> I have a problem joining tables from two different database.

> AlarmsQuery.Sql.Clear;
> AlarmsQuery.Sql.add('select * from alarmqueue aq,  ":DB2:FIFO" fi');
> AlarmsQuery.Sql.add('where');
> AlarmsQuery.Sql.add('aq.AlarmEntryID not in (select AlarmEntryID from fi
> where aq.AlarmEntryID=fi.AlarmEntryID)');
> AlarmsQuery.Open;

> I have tried different combinations but none worked!

> I have logged the errors and they look like this .

> 10-02-2002 01:45:05 [Fejl       ] SQL Server message 208: Invalid object
> name ':WinRapSQLDB:fifo.db'.(line 1)
> 10-02-2002 01:45:58 [Information] WinRapSQL start
> 10-02-2002 01:46:01 [Fejl       ] SQL Server message 208: Invalid object
> name 'WinRapSQLDB:FIFOTbl'.(line 1)
> 10-02-2002 01:46:32 [Information] WinRapSQL stop
> 10-02-2002 01:46:36 [Information] WinRapSQL start
> 10-02-2002 01:46:42 [Fejl       ] SQL Server message 208: Invalid object
> name 'WinRapDB:FIFOTbl'.(line 1)
> 10-02-2002 01:47:06 [Information] WinRapSQL stop
> 10-02-2002 01:47:09 [Information] WinRapSQL start
> 10-02-2002 01:47:15 [Fejl       ] SQL Server message 208: Invalid object
> name 'FIFOTbl'.(line 1)
> 10-02-2002 01:48:24 [Information] WinRapSQL stop

> I'm joining a table from a mssql database and a local paradox tabel TTable,
> the fifo tablefilename is fifo.db.

> Any suggestions?

> TIA
> Henrik

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com
Quid quid latine dictum sit, altum viditur

Re:Joining two tables from different databases


Quote
> AFAIK you can't do that.

According to the manual it's possible, however I'm in doubt bacause it's
different database systems.

regards
Henrik

Re:Joining two tables from different databases


Try not use * in select.

It seems that your SQL query has no sense:

where aq.AlarmEntryID not in (select AlarmEntryID from fi where
aq.AlarmEntryID=fi.AlarmEntryID)

seems to be the same as

where aq.AlarmEntryID <> aq.AlarmEntryID

and will return no rows with any value in aq.AlarmEntryID.

If you are on MS SQL Server than for query from different databases look
Books Online for OpenQuery and OpenRowset.

Regards,
Vassiliev V.V.
http://www.oledbdirect.com

"Henrik Nedergaard" <hen...@hn-itservice.dk> ???Y/???Y ?????
???Y??: news:3c65c50c_2@dnews...

Quote
> Hi

> I have a problem joining tables from two different database.

> AlarmsQuery.Sql.Clear;
> AlarmsQuery.Sql.add('select * from alarmqueue aq,  ":DB2:FIFO" fi');
> AlarmsQuery.Sql.add('where');
> AlarmsQuery.Sql.add('aq.AlarmEntryID not in (select AlarmEntryID from fi
> where aq.AlarmEntryID=fi.AlarmEntryID)');
> AlarmsQuery.Open;

> I have tried different combinations but none worked!

> I have logged the errors and they look like this .

> 10-02-2002 01:45:05 [Fejl       ] SQL Server message 208: Invalid object
> name ':WinRapSQLDB:fifo.db'.(line 1)
> 10-02-2002 01:45:58 [Information] WinRapSQL start
> 10-02-2002 01:46:01 [Fejl       ] SQL Server message 208: Invalid object
> name 'WinRapSQLDB:FIFOTbl'.(line 1)
> 10-02-2002 01:46:32 [Information] WinRapSQL stop
> 10-02-2002 01:46:36 [Information] WinRapSQL start
> 10-02-2002 01:46:42 [Fejl       ] SQL Server message 208: Invalid object
> name 'WinRapDB:FIFOTbl'.(line 1)
> 10-02-2002 01:47:06 [Information] WinRapSQL stop
> 10-02-2002 01:47:09 [Information] WinRapSQL start
> 10-02-2002 01:47:15 [Fejl       ] SQL Server message 208: Invalid object
> name 'FIFOTbl'.(line 1)
> 10-02-2002 01:48:24 [Information] WinRapSQL stop

> I'm joining a table from a mssql database and a local paradox tabel
TTable,
> the fifo tablefilename is fifo.db.

> Any suggestions?

> TIA
> Henrik

Re:Joining two tables from different databases


Quote
> Try not use * in select.

> It seems that your SQL query has no sense:

You are right about that but thats not the issue!

 The issue is, how can I join tables from two different database, one table
from mssql with one paradox table.

regards
Henrik

Re:Joining two tables from different databases


That what i say: you can't do that with different database engines. You can do
it with two different databases of the same engine, but not what you want.

Quote
Henrik Nedergaard wrote:
> > Try not use * in select.

> > It seems that your SQL query has no sense:
> You are right about that but thats not the issue!

>  The issue is, how can I join tables from two different database, one table
> from mssql with one paradox table.

> regards
> Henrik

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com
Quid quid latine dictum sit, altum viditur

Re:Joining two tables from different databases


Quote
> That what i say: you can't do that with different database engines. You
can do
> it with two different databases of the same engine, but not what you want.

Okay, I rest my case!

Guillermo, please read/learn how to qoute (reply) in NG's.

Thanks
Henrik

Re:Joining two tables from different databases


Quote
Guillermo Casta?o A wrote:

> That what i say: you can't do that with different database engines. You can do
> it with two different databases of the same engine, but not what you want.

This is incorrect.  The BDE will join across different DB backends.  The
DatabaseName driving the Query must be a pDox alias though since the
heterogeneous join results in the non pdox Db's table being brought locally in a
temp pDox file for the local SQL engine to work on.  

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts, build
a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a computer,
cook a tasty meal, fight efficiently, die gallantly.  Specialization is for
insects.   (RAH)

Re:Joining two tables from different databases


Quote
Henrik Nedergaard wrote:

> > AFAIK you can't do that.

> According to the manual it's possible, however I'm in doubt bacause it's
> different database systems.

No, it is definitely possible.  That is the whole purpose behind heterogeneous
joins.  Here is SQL joining the customer.db table with the IB ORDERS table in
mastsql.gdb

SELECT DISTINCT C.CustNo, c.company, o.*
FROM ':BCDEMOS:customer.db' C, ':IBDBDemos:ORDERS' O
WHERE (O.CUSTNO = C.CUSTNO)

Note that the TQuery component's DatabaseName must point to a pDox alias
(because your table from the non pDox is brought local and stored as a pDox
table and the local SQL engine is then used to do the join) and the case of the
SQL backend might be sensitive (I.E. for InterBase I got an error message that
the table was not found when I had it typed ':IBDBDemos:orders')

Quote

> regards
> Henrik

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts, build
a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a computer,
cook a tasty meal, fight efficiently, die gallantly.  Specialization is for
insects.   (RAH)

Re:Joining two tables from different databases


I repeat my previous letter - look for OPENROWSET, OPENQUERY, OPENDATASOURCE
statement help in Books Online. Your query can be like this one:

SELECT p.*, v.*
FROM Northwind.dbo.Products AS p INNER JOIN
   OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=C:\Test;Extended properties=Paradox 5.x')...Venues
   AS v
   ON p.ProductID = v.VenueNo

To run it, copy Venues.* from Borland Shared\Data into C:\Test.

Regards,
Vassiliev V.V.
http://www.oledbdirect.com

"Henrik Nedergaard" <itserv...@mail.dk> ???Y/???Y ?????
???Y??: news:3c6688fb$1_2@dnews...

Quote
> > Try not use * in select.

> > It seems that your SQL query has no sense:
> You are right about that but thats not the issue!

>  The issue is, how can I join tables from two different database, one
table
> from mssql with one paradox table.

> regards
> Henrik

Re:Joining two tables from different databases


Ups, didn't knew it... thank for the correction and sorry for the wrong information

Quote
"Jeff Overcash (TeamB)" wrote:
> Guillermo Casta?o A wrote:

> > That what i say: you can't do that with different database engines. You can do
> > it with two different databases of the same engine, but not what you want.

> This is incorrect.  The BDE will join across different DB backends.  The
> DatabaseName driving the Query must be a pDox alias though since the
> heterogeneous join results in the non pdox Db's table being brought locally in a
> temp pDox file for the local SQL engine to work on.

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> A human being should be able to change a diaper, plan an invasion, butcher
> a hog, conn a ship, design a building, write a sonnet, balance accounts, build
> a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
> alone, solve equations, analyze a new problem, pitch manure, program a computer,
> cook a tasty meal, fight efficiently, die gallantly.  Specialization is for
> insects.   (RAH)

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com
Quid quid latine dictum sit, altum viditur

Other Threads