Run queries on different databases with same connection

[rdekker]

Is it possible to use a single ODBC connection to query different Progress databases (running in different ports)? With SQL Server I can run queries over different databases as long as the account used for the connection has sufficient rights. Is this in any way possible for a Progress ODBC connection?

 

[MaximMonin]

through odbc - no

through appserver or wsa - yes, but it is not sql queries

 

[rdekker]

through odbc - no

through appserver or wsa - yes, but it is not sql queries

I thought so, thanks!

 

[bigwill]

Yes, this is possible.

All our odbc-connections are connected to 2 databases at the same time (database "master" is always available.

First the connection string like this:
connectionString="Driver={Progress OpenEdge 10.1B driver};uid=root;host=xx.xx.xxx.xx;port=xxxx;db=DB1[-mdbqB1ma];pwd=xxxxxx;DIL=READ UNCOMMITTED" />

On the db area for DB1 you have to create a parameter file called:
"DB1.oesql.properties"

Contens of this file:

[sql-configuration]
configuration-names-list=DB1ma

[configuration.DB1ma]
database-id-list=MA

[database.MA]
Name=Master
Catalog=master
Location=/ii5/master/db/MASTER.db
(EOF)


Now you can select DB1.table1.field1 Master.table2.field2 from .....etc etc

 

[tamhas]

I have a vague memory that the ability to do this is version dependent.

 

[Casper]

This was implemented in OE10.1B if I recall correctly. All databases should reside on the same machine though. (At least that was then a requirement).

 

[rdekker]

The 3 stars are actually the characters A S and S but they are automatically replaced by the forumsoftware.

Thanks for all your comments. I can't get it working however.

My primary database is called "mutatie". In the directory where the database is located I created a file named "mutatie.oesql.properties"
with the following content:

[sql-configuration]
configuration-names-list=allDB
[configuration.allDB]
database-id-list=AS,RE
[database.AS]
Name=assur
Catalog=***
Location=/db/assur
[database.RE]
Name=register
Catalog=reg
Location=/db/register

The physical database names of the other databases are "assur" and "register". Both databases are located in the same directory as the "main" database "mutatie".

Connectionstring looks like this:

con.Open "Driver={Progress OpenEdge 10.2B driver};uid=odbc;host=10.100.100.75;port=12010;db=mutatie[-mdbq:allDB];pwd=odbc;DIL=READ UNCOMMITTED"

If I want to run a query with something FROM reg.pub.tablename I end up with an error "Catalog name REG is an unknown catalog". The connection with the main database is ok. But the other two are not. All three databases have an SQL-server running

With statements like:
con.Execute "CONNECT 'h:/db/register' AS CATALOG reg"
con.Execute "CONNECT 'h:/db/assur' AS CATALOG ***"

I can run the query without problems but those two statements are not necessary with such a properties file I believe?

Anyone who can help me fixing this problem?

Edit:

At this moment I see some activity in the primary database log-file:
[2011/03/10@15:59:31.053+0100] P-3748 T-5976 I SQLSRV2 2: (-----) User 273 Connection warning: Auxiliary databases not connected

In the auxiliary database logfiles I can see things like this:
[2011/03/10@15:59:23.134+0100] P-528 T-5108 I SRV 3: (8873) Login usernum 275, remote SQL client.
[2011/03/10@15:59:23.134+0100] P-528 T-5108 I SRV 3: (7129) Usr 275 set name to odbc.