Board index » delphi » Connecting to two different tables, which stored in two different IB-databases

Connecting to two different tables, which stored in two different IB-databases

Hi there!
( I'm working with Delphi and Interbase )

I have a problem:

I want to create a stored procedure in InterBase, which uses one table to
bring parameters for another table.
The problem is that the tables were stored in different databases, so
certainly I must connect to the first table, get the parameters, disconnect
to this table then connect to another table, which uses the parameters.

Well, I search the web for an idea, but I can't find anything!
How could I connect to another database IN a database? And how get I data
from different tables in different databases in one stored procedures?

create procedure Test
AS
..
Connect???
For Select ID, NAME from Table1
INTO :_ID, _Name
,,
DO begin
Connect??? OR Set Database???
Update Table2 set ID = :_ID, NAME = :_NAME;
Suspend;
end;

How can I access a table out of my current database?
( Set database?, Connect..?, )
Can anybody help me? Do you have an Idea?
M.Rie...@Starlogik.de
Thanks

 

Re:Connecting to two different tables, which stored in two different IB-databases


Quote
Morris Riedel wrote:

> I want to create a stored procedure in InterBase, which uses one table to
> bring parameters for another table.
> The problem is that the tables were stored in different databases, so
> certainly I must connect to the first table, get the parameters, disconnect
> to this table then connect to another table, which uses the parameters.

        InterBase has no features for connecting to other databases from a
stored procedure.  It's theoretically possible to do this in a UDF, but
I would strongly recommend against it.  The best solution would be to
combine the two databases into one, and the second best solution would
be to do this on your client or middle tier.

        HTH,

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Connecting to two different tables, which stored in two different IB-databases


Quote
"Morris Riedel" <m.rie...@starlogik.de> wrote:
>I want to create a stored procedure in InterBase, which uses one table to
>bring parameters for another table.
>The problem is that the tables were stored in different databases, so
>certainly I must connect to the first table, get the parameters, disconnect
>to this table then connect to another table, which uses the parameters.

>...
>create procedure Test
>AS
>..
>Connect???
>For Select ID, NAME from Table1

Morris,

You can't do this. That is, you cannot connect to any database from within
a stored procedure. One database is not aware of the other and there's no
way to use one database to remotely control another.

You must do this in your Delphi code. Use two TSession components, one for
each database. Then create queries for each database. This way you can have
both databases open at the same time.

You probably want to use separate TDatabase components, too, so that:

TSession1 --> TDatabase1 --> TQuery1
and
TSession2 --> TDatabase2 --> TQuery2

Then, in your code, run TQuery1 and use the results to run TQuery2.

Good luck.

Phil Cain
--

Re:Connecting to two different tables, which stored in two different IB-databases


Quote
Philip Cain wrote:

> You must do this in your Delphi code. Use two TSession components, one for
> each database. Then create queries for each database. This way you can have
> both databases open at the same time.

        You don't need two TSessions unless you want to run them in different
threads.  It won't necessarily break anything to use separate sessions,
but it does increase the amount of shared memory required by the BDE,
and it may prevent you from using TBatchMove (not sure on this last
point).  For most applications, two TDatabases using the same TSession
is enough.

        HTH,

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Other Threads