Connect to 2 databases

[vchris]

I'm using ColdFusion and SQL. I need to connect to 2 SQL databases on the same server with the same user name and password. Is the best way to do this to create 2 separate queries, independent of each other? This query is used to populate 1 html table.

[Skemcin]

I'd first decide which on is your parent - then define them as linked tables in SQL. Then, I'd build a stored procedure (in the parent) - build a temp table, select and insert the results form database A, select and insert database B, select from your temp table - so your results are returned in one result set so coldfusion has to do less work. The more database related work you can keep on the database side, the more efficient your applications will run.But if its just a matter of having two results sets - one from each database, then I'd just create ODBC definitions for each and write two queries. But again, you could follow the same recommendation I stated early, return two results sets if tey are different.Does that help any?

[vchris]

Well I can't do much on the DB side since I don't have the rights. In about a year I'm supposed to redo this query site so this is sorta like a small update. So how would I put the 2 queries together (seem like 1 query/table)?

[Skemcin]

You can use a SQL UNION statement - is that what you need?Sorry - I'm not completely following your situation. If ^ is not the answer, can you describe your requirement a little more?

[vchris]

But doesn't a union statement create a query with 2 tables not 2 databases? Basically I have a column in a database that I need for my query and the other columns are in another database. How can I create a query that will display them in a single HTML table? I'm guessing 2 cfquery since 1 cfquery can only connect to 1 database but then how can I make sure the results from one query are correctly associated to the other query?

[Andrew K.]

You just need to select the data from each database and use a query of queries to join them.This seemed to work for me when I tested it.

<cfquery name="query1" datasource="query1datasource" cachedwithin="#CreateTimeSpan(0,1,0,0)#">SELECT *FROM table1</cfquery><cfquery name="query2" datasource="query2datasource">SELECT *FROM table2</cfquery><cfquery name="joinqueries" dbtype="query">SELECT *FROM query1, query2</cfquery>

Of course you'll probably want to play around with it a bit so it returns the right data that you want...

[vchris]

I had no idea I could query a query. Thanks for the info. I'll try that as soon as I have the time.

[aspnetguy]

You just need to select the data from each database and use a query of queries to join them.This seemed to work for me when I tested it.

<cfquery name="query1" datasource="query1datasource" cachedwithin="#CreateTimeSpan(0,1,0,0)#"> SELECT * FROM table1 </cfquery> <cfquery name="query2" datasource="query2datasource"> SELECT * FROM table2 </cfquery> <cfquery name="joinqueries" dbtype="query"> SELECT * FROM query1, query2 </cfquery>

Of course you'll probably want to play around with it a bit so it returns the right data that you want...

that is an interesting way to do that. combining the 2 queries into 1 with a union all would probably be more efficient though

[Skemcin]

You just need to select the data from each database and use a query of queries to join them.This seemed to work for me when I tested it.

<cfquery name="query1" datasource="query1datasource" cachedwithin="#CreateTimeSpan(0,1,0,0)#">SELECT *FROM table1</cfquery><cfquery name="query2" datasource="query2datasource">SELECT *FROM table2</cfquery><cfquery name="joinqueries" dbtype="query">SELECT *FROM query1, query2</cfquery>

Of course you'll probably want to play around with it a bit so it returns the right data that you want...

That would most definitely work, and there is nothing wrong with that approach. But if the two tables have the same columns then he could have the database do all the work by using a UNION statement. This way ColdFusion only has to make one call to the database and then process that just once. This technique would require ColdFusion to make two calls to he database, process them both and then run one more process to combine the two - not to mention the output processing.Again, the technique will work fine, but if volume or scalability is a concern, then it will not perform very well.[edit] if the data doesn't change that often, you might consider caching the query - caching a query for even as little as a minute can improve performance by 90%.

[vchris]

The data will change a bit. What is the UNION you mean?

[aspnetguy]

http://www.w3schools.com/sql/sql_union.asp

[vchris]

http://www.w3schools.com/sql/sql_union.asp

But how would I do a UNION with 2 databases (not 2 tables)? Still 2 queries right?

[Skemcin]

<cfquery name="qryUnion" databsource="xxxxxx">SELECT E_Name FROM dbserver_xxxxxx.dbname_xxxxxx.dbo.Employees_xxxxxxUNIONSELECT E_Name FROM dbserver_yyyyyy.dbname_yyyyyy.dbo.Employees_yyyyyy</cfquery>

The only thing you would need to do is make sure that both are linked databases.

[aspnetguy]

But how would I do a UNION with 2 databases (not 2 tables)? Still 2 queries right?

sorry I didn't see that it was 2 different databases. I don't know if you can still use UNION.

[vchris]

So even though the datasource name is xxxxxx in cfquery, I could still connect to datasource yyyyyy when linked?

[Skemcin]

So even though the datasource name is xxxxxx in cfquery, I could still connect to datasource yyyyyy when linked?

Correct. In MS SQL if you have the databases set up as linked, then your SQL can call them through what is really like an absolute path. It just uses the first datasource as a bridge. The only thing you cannot do in these situation is have a <cftransaction> wrapped around everything - and thats simply because transaction sql is not permitted across servers. There shouldn't be any reason why that won't work* - thats how I do my user authentication across many web sites in my network.

http://www.w3schools.com/sql/sql_union.asp

at least someone read my second post:)* depending of your data schematic of course

[vchris]

So after talking with the database admin and the CF admin. It seems like there is 2 DBs that are almost the same (some tables are not in the other). I'm able to use only 1 DB, when in dev I use one and when live I use another. I used a simple if statement in my application.cfm. Works great! :)Thanks for the help anyway guys.