Multi-database queries via Active server Pages

[Recycler]

Advice please on the following.... I believe MySQL supports queries involving tables from more than one database, e.g.
select joe from db1.table1, db2.table3 where table1.fred = table3.bert

Is it possible to create recordsets from this type of query under ASP, please? ASP connection strings only permit one database to be specified, e.g.
con.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=DatabaseName;USER=MyName;PASSWORD=MyPwd;OPTION=3;"

A typical way to return a recordset under ASP might be
objRS.Open strSQL, con, 3,2,1

So each connection string structure defines just one database, and the recordset open syntax allows just one connection to be referenced.

Is there a way out of this, or are multi-database queries not possible under ASP?

Thanks - Mike -

 

[danielhozac]

Why would you want to select data from multiple databases in one query in the first place? If you need to do this, you should rethink the design of your databases. //Daniel

 

[Recycler]

I agree I could work round the issue. But I have a genuine (if academic) interest in whether this can be done or not because the application in mind is a small business system consisting of an Intranet and an Internet site working on common data but obviously through separate servers. I don't need to, and don't want to, put certain data on the Internet site, but keep that stuff nice and tight in-house. Looking at the structure and purpose of my tables, it minimises the synchronisation problems if the tables are divided between two databases. But it's the standard situation when agonising about normalising data, determining just where's the best compromise between theory and practice!

I get the feeling it can't be done (*grin*). - Mike -

 

[PAOwen]

Daniel,

Recycler's question doesn't appear to be too unreasonable. I have exactly the same need where I have a database that contains employee details and I need to access that data in two or more other database eg Sales leads and Engineers Data and do not want to duplicate.

Regards,

Paul

 

[danielhozac]

The question then is, do you really need to have separate databases, or would proper naming of tables work?

//Daniel

 

[PAOwen]

Daniel,

Please, go on.

Regards,

Paul