If I have two databases on the same server, how can I write a query on one database that accesses object on the other database?

For example, a view that unions contents of tableA on DB1 with tableA on DB2.

Ideal

>If I have two databases on the same server, how can I write a query on one database that accesses object on the other database?
>
>For example, a view that unions contents of tableA on DB1 with tableA on DB2.

That depends. If they are defined using the same session then its as simple as prefacing the table name with the database name eg DB1.tableA and DB2.tableA. If they are under different sessions then you have to transport the data from one table and use it to populate a table in the other session.

Roy Lambert

So the client application must be aware of all databases at compile time?

Roy Lambert wrote:

Ideal

>If I have two databases on the same server, how can I write a query on one database that accesses object on the other database?
>
>For example, a view that unions contents of tableA on DB1 with tableA on DB2.

That depends. If they are defined using the same session then its as simple as prefacing the table name with the database name eg DB1.tableA and DB2.tableA. If they are under different sessions then you have to transport the data from one table and use it to populate a table in the other session.

Roy Lambert

Ideal

>So the client application must be aware of all databases at compile time?

Not at all. You can supply information at run time via user input, OS files or data held in a table the application is aware of to allow the creation of additional database components.

The architecture of ElevateDB is such that there is a barrier between sessions. A single session can manage many databases, either on disk or in-memory and sql operations can be performed across databases as long as they are managed by a single session.

This approach is the main mechanism used to provide isolation between threads in a single process.. Its very easy to create a database at run time.

From the manual the sql is:

CREATE DATABASE "Tutorial"
PATH 'C:\Tutorial\DB'
DESCRIPTION 'Tutorial Database'

and in Delphi it would be easily executed using the Session.Execute function.


Roy Lambert

But there is no way to allow the sql statement to define the databases that need access, even with those databases registered on the same server that my session is connected to?

I will always need to instantiate a database component for any database that might be referred to from my client application.  Correct?



Roy Lambert wrote:

Ideal

>So the client application must be aware of all databases at compile time?

Not at all. You can supply information at run time via user input, OS files or data held in a table the application is aware of to allow the creation of additional database components.

The architecture of ElevateDB is such that there is a barrier between sessions. A single session can manage many databases, either on disk or in-memory and sql operations can be performed across databases as long as they are managed by a single session.

This approach is the main mechanism used to provide isolation between threads in a single process.. Its very easy to create a database at run time.

From the manual the sql is:

CREATE DATABASE "Tutorial"
PATH 'C:\Tutorial\DB'
DESCRIPTION 'Tutorial Database'

and in Delphi it would be easily executed using the Session.Execute function.


Roy Lambert

>But there is no way to allow the sql statement to define the databases that need access, even with those databases registered on the same server that my session is connected to?

I am very confused about just what you're trying to achieve. Can you give examples? I'd also be interested in what environment you're coming from.


>I will always need to instantiate a database component for any database that might be referred to from my client application. Correct?

NO. What you will need is the reference in the session to the databases. You can then instantiate a query, set its database to Configuration and its sql to your query with fully qualified table names eg in my case

SELECT * FROM TfRData.EMails

and that will work providing that the session knows about TfRData which is where

CREATE DATABASE "Tutorial"
PATH 'C:\Tutorial\DB'
DESCRIPTION 'Tutorial Database'

comes in.

Roy Lambert

In SQL Server, you can reference any other database on the same server by prefixing the database to the object.  

For example, select * from db1.tbl1 a join db2.tbl2 b on a.id = b.tbl1_id

The client application knows nothing about db1 or db2, only that a server connection is made and it is passing a query along through that connection that should be executed on the server.


Roy Lambert wrote:


>But there is no way to allow the sql statement to define the databases that need access, even with those databases registered on the same server that my session is connected to?

I am very confused about just what you're trying to achieve. Can you give examples? I'd also be interested in what environment you're coming from.


>I will always need to instantiate a database component for any database that might be referred to from my client application. Correct?

NO. What you will need is the reference in the session to the databases. You can then instantiate a query, set its database to Configuration and its sql to your query with fully qualified table names eg in my case

SELECT * FROM TfRData.EMails

and that will work providing that the session knows about TfRData which is where

CREATE DATABASE "Tutorial"
PATH 'C:\Tutorial\DB'
DESCRIPTION 'Tutorial Database'

comes in.

Roy Lambert

>In SQL Server, you can reference any other database on the same server by prefixing the database to the object.
>
>For example, select * from db1.tbl1 a join db2.tbl2 b on a.id = b.tbl1_id
>
>The client application knows nothing about db1 or db2, only that a server connection is made and it is passing a query along through that connection that should be executed on the server.

Its very much the same in ElevateDB. The application does not have to have the database information coded directly in, however, the database engine does need to know about them. It doesn't matter wether you're in f/s or c/s mode somehow the engine has to know those databases are there. In ElevateDB the mechanism for that is the configuration file. If you're using the standard names this will be EDBConfig.EDBCfg.

This can be set up / altered  in code at runtime or using the provided utility (EDBManager). Essentially you are your own DBA.

Along with the Microsoft flavour help that comes with ElevateDB there is a pdf file. In there it talks you through setting up a project from scratch. As someone coming from a different enviornment you would almost certainly find it helpful to read the first few chapters at least.

Roy Lambert

On 2/3/2015 11:55 PM, Ideal Software Systems wrote:
> In SQL Server, you can reference any other database on the same server by prefixing the database to the object.
> For example, select * from db1.tbl1 a join db2.tbl2 b on a.id = b.tbl1_id

Sure - this works fine in EDB also.

> The client application knows nothing about db1 or db2, only that a server connection is made and it is passing a query along through that connection that should be executed on the server.

At some point the client application (or user) has to know something of
course (in order to know what db name and table names to use) but lets
step back a bit.

In general you need to setup few components for all this to work.

Initially your app needs to have a session and open it. At this point
with an active session you can start querying the EDB configuration. For
example you can use GetDatabases() function to get a list of databases
defined in config file or on server.

You can also use session Execute() function to execute actual sql. This
one is bit limited though as it returns number of rows affected - it's
great for things like checking for config objects (databases, tables,
etc) and creating them if not found.

Once this is done you do need to connect to a database - note that this
can be the configuration database which always exists ( not unlike
MS-SQL where you can connect to master as part of your connect string).

So easiest way to do this is to connect to Configuration database
(either by configuring a database component or using sessions
OpenDatabase function (i.e. OpenDatabase('Configuration')) and then
attach a query component and run any sql you want (just remember to
fully qualify the database names as you're doing above (i.e. select *
from db1.tbl1 a join db2.tbl2 b on a.id = b.tbl1_id will work just fine
at this point).

So no your app does not need to know anything about the databases at
all. It can can simply connect the session and configuration database
and then start querying and/or executing away.

If your app has multiple threads then each thread needs its own session
etc
(http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin32&version=XE7&topic=Multi_Threaded_Applications)

Raul

Raul


>So easiest way to do this is to connect to Configuration database
>(either by configuring a database component or using sessions
>OpenDatabase function (i.e. OpenDatabase('Configuration')) and then
>attach a query component and run any sql you want (just remember to
>fully qualify the database names as you're doing above (i.e. select *
>from db1.tbl1 a join db2.tbl2 b on a.id = b.tbl1_id will work just fine
>at this point).

Almost right You don't need to open the configuration database, just make sure that the TEDBQuery you use has Configuration set as the database. Or at least that's what happened when I tried it.

>So no your app does not need to know anything about the databases at
>all. It can can simply connect the session and configuration database
>and then start querying and/or executing away.

Not too sure I fully agree with this statement. Whilst you don't need to instantiate database components the app does need to know about the databases and is told about them by the configuration file. The original posts were making me think ElevateDB was meant to just go "oh here's a query I'll have a look at all the disks I can see and see if I can spot a database that might go with the query" rather than "a DBA set it up and I know nothing about that part of it". IE the databases have to be set up and configured to be available to the app before starting (ok I'm ignoring the fact that you can start with a blank configuration file and set everything up at run time)

Roy Lambert
..