Jump to content

Connect to 2 databases


Recommended Posts

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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)?

Link to post
Share on other sites

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?

Link to post
Share on other sites

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?

Link to post
Share on other sites

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...

Link to post
Share on other sites
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
Link to post
Share on other sites
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%.
Link to post
Share on other sites
<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.

Link to post
Share on other sites
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.
Link to post
Share on other sites

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

Link to post
Share on other sites
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.
at least someone read my second post:)* depending of your data schematic of course
Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...