COLDFUSION QUERY

[kingjjx]

Hi, can someone tell me how to query from 2 datasources ??
I want to query from 2 separate database .. i already set up datasources for each.

thank you
-jon

 

[pcorreia]

The easiest way to do it is if the datasources are different databases on the same server. Simply define a datasource that points to the server but does not specify a default database. Then, in your query use the full path to the database table (including the database name and table owner name) in the FROM clause.

   <cfquery name=&quot;myQuery&quot; datasource=&quot;myDSN&quot; dbtype=&quot;ODBC&quot;>
      SELECT O.OrderNumber, O.OrderDate, C.CustomerName
      FROM dbOrders.dbo.tblOrders O
           INNER JOIN dbCustomers.dbo.tblCustomers C ON O.CustomerID = C.CustomerID
      WHERE O.OrderID = #OrderID#
   <cfquery>

Your only other option (that I can see) works if you're running ColdFusion 5 or later. You can use the Query of Queries feature (also known as CFSQL) to combine queries from two separate datasources.

First, query each datasource separately, then use a third <cfquery> with dbtype=&quot;query&quot; and specifying the other query names instead of table names.

   <cfquery name=&quot;qryOrders&quot; datasource=&quot;myDSN1&quot; dbtype=&quot;ODBC&quot;>
      SELECT OrderNumber, OrderDate, CustomerID
      FROM tblOrders
      WHERE OrderID = #OrderID#
   </cfquery>
   <cfquery name=&quot;qryCustomers&quot; datasource=&quot;myDSN2&quot; dbtype=&quot;ODBC&quot;>
      SELECT CustomerID, CustomerName
      FROM tblCustomers
   </cfquery>
   <cfquery name=&quot;qryCombined&quot; dbtype=&quot;query&quot;>
      SELECT OrderNumber, OrderDate, CustomerName
      FROM qryOrders O
           INNER JOIN qryCustomers C ON O.CustomerID = C.CustomerID
   </cfquery>

 

[kingjjx]

Hi, I am running 4.5 version ...
I'm trying to use the first example you posted ...


<cfquery name=&quot;myQuery&quot; datasource=&quot;myDSN&quot; dbtype=&quot;ODBC&quot;>
SELECT O.OrderNumber, O.OrderDate, C.CustomerName
FROM dbOrders.dbo.tblOrders O
INNER JOIN dbCustomers.dbo.tblCustomers C ON O.CustomerID = C.CustomerID
WHERE O.OrderID = #OrderID#
<cfquery>
***********************

when you say:
O.OrderNumber .. O is the table name ??
and when you say:
dbCusomters.dbo.tblCustomers C
C is what ??

Sorry .. im a newbie ... if you can please xplain your code further .. im just confused which values i need to change to which

 

[pcorreia]

No problem... In this case, &quot;O&quot; is just an alias for the table. The real meat of this technique is in the FROM clause.

For example, the first part (FROM dbOrders.dbo.tblOrders O) breaks down like this:
[tt]
dbOrders database name

dbo database owner name

tblOrders table name

O alias for this table so you don't need
to keep writing the whole long string
elsewhere in the query
[/tt]
So you see, doing it this way allows you to query two different databases (as long as they're on the same server) in one query, simply by telling the database server exactly what tables you want the data to come from. It's sort of like using an absolute URL in a web page, or a full path name in a command-line application.

 

[kingjjx]

Hi, this is what my final code looks like ...

<cfquery name=&quot;myQuery&quot; datasource=&quot;myDSN&quot; dbtype=&quot;ODBC&quot;>
SELECT O.name, C.name, C.customer
FROM DynoOwnersRec.dbo.dyno O
INNER JOIN EnCoreCompanyD.dbo.ArCustomer C ON O.name = C.name
WHERE O.name = #name#
</cfquery>

************** but its giving me the error message of :

Error Diagnostic Information
An error occurred while evaluating the expression:

#name#

Error near line 7, column 24.
------------------------------------------------------------
Error resolving parameter NAME

ColdFusion was unable to determine the value of the parameter.


WHAT COULD BE WRONG ??

 

[pcorreia]

This is a ColdFusion error, not a SQL error, so you need to see whether you've properly declared the value of #name# in the local scope. ColdFusion is saying it can't evaluate #name#.

 

[kingjjx]

Hey .. I just changed the WHERE clause to ...

WHERE O.name = c.name

Now, the error message is :

ODBC Error Code = IM002 (Data source not found and no default driver specified)

[Microsoft][ODBC Driver Manager] Data source 'MYDSN' not found and no default driver specified

I guess this is because i wrote that datasource=&quot;MyDSN&quot; like you told me .. and MyDSN does not exist.

any advice ?
thanks

 

[pcorreia]

Since you're specifying the join criteria in the FROM clause, you don't really need a WHERE clause... I just put that in the example to show how you would do it if you only wanted to select one order. This was intended to be a filtering criteria, not a necessary part of the query. You can take it out if you don't need to filter.

As for the datasource name, yes, you need to substitute your datasource name for the datasource=&quot;myDSN&quot; in my example. You're probably going to want to select a more descriptive name for the query than &quot;myQuery&quot;, as well.