Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to query from two different databases/datasources ?

Posted on 2006-10-22
9
Medium Priority
?
1,255 Views
Last Modified: 2013-12-24
I'm new at this. I'm trying to create a query that brings data in from two different databases/datasources. (My queries with just one datasource work just fine.)
This works in my test environment (local pc, IIS, Windows XP, Dreamweaver & Coldfusion):

<cfquery name="rs_ratings" datasource="connCWArecipes">
SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings as r8, cwarecipes.tblrecipes as r, cwamembers.tblmembers as m
WHERE r8.recipeID = r.recipeID
AND r8.memberID=m.memberID
ORDER BY recipeID, daterated desc
</cfquery>

But on my hosting system (Apache/Unix) it generates this error:
Error Executing Database Query.  
General error: SELECT command denied to user 'CWARecipes'@'10.10.1.231' for table 'tblmembers'  
 
The error occurred in /vservers/cooki7xp/htdocs/admin/showratings.cfm: line 1
 1 : <cfquery name="rs_ratings" datasource="connCWArecipes">
2 : SELECT r8.*, r.recipename, m.username
3 : FROM CWARecipes.tblratings as r8, CWARecipes.tblrecipes as r, CWAMembers.tblmembers as m
--------------------------------------------------------------------------------
SQL    SELECT r8.*, r.recipename, m.username FROM CWARecipes.tblratings as r8, CWARecipes.tblrecipes as r, CWAMembers.tblmembers as m WHERE r8.recipeID = r.recipeID AND r8.memberID=m.memberID ORDER BY recipeID, daterated desc  
DATASOURCE   connCWArecipes
 
0
Comment
Question by:alicia1234
9 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 17784021
"General error: SELECT command denied to user 'CWARecipes'@'10.10.1.231' for table 'tblmembers'"

this tells you that there is an issue with permissions on the db itself, not anything wrong with your query. Assuming it's a mysql db, check out GRANT http://dev.mysql.com/doc/refman/5.0/en/grant.html to set permissions or contact your host to get them to do it.
0
 

Author Comment

by:alicia1234
ID: 17785636
I have no trouble accessing the db cwarecipes by itself ... no access problems at all .. it's only when I try to access both databases ... weird ...
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 17788266
some issues after closer look at your query

don't use "as" for a table alias afaik that is not proper syntax in mysql, mssql or access.

SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings r8, cwarecipes.tblrecipes r, cwamembers.tblmembers m
WHERE r8.recipeID = r.recipeID
AND r8.memberID=m.memberID
ORDER BY recipeID, daterated desc

It would also be better to use a join

SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings r8
inner join  cwarecipes.tblrecipes r on  r8.recipeID = r.recipeID,
inner join cwamembers.tblmembers m on r8.memberID=m.memberID
ORDER BY r.recipeID, r8.daterated desc


(i guessed on the tables for the order by clause adjust as needed)
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17788331
you may also want to try adding the username and password attributes to your query which runs on the host...

Example:

<cfquery name="rs_ratings" datasource="connCWArecipes" username="YourDBUserName" password="YourDBPassword">
......
</cfquery>
0
 

Author Comment

by:alicia1234
ID: 17788643
I've been using "as" all along and have never had a problem with it.
0
 

Author Comment

by:alicia1234
ID: 17788739
.. and I double checked the MySQL documentation and "AS" is correct
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 375 total points
ID: 17788861
i stand corrected ...

again, after closer inspection (hey it's really early here) I think the issue is that you'll need to do 2 selects and then a query of query in order to access the 2 datasources

since I can't tell which tables are in which datasource I'll just give you the outline

<cfquery name="Q1" datasource="DS1">
SELECT * from tbOne, tblTwo
</cfquery

<cfquery name="Q2" datasource="DS2">
SELECT * from  tblThree
</cfquery

<cfquery name="rs_ratings" dbtype="query">
SELECT * from q1 inner join q2 on q1.keyid = q2.keyid
</cfquery

0
 
LVL 2

Assisted Solution

by:nmarano
nmarano earned 375 total points
ID: 17791902
<cfquery name ="getratings_recipes" datasource="connCWArecipes">
Select *
From tblratings, tblrecipes
</cfquery>

<cfquery name ="getmembers" datasource="connCWArecipes">
Select *
From tblmembers
</cfquery>

<cfquery name="rs_ratings" dbtype="query">
SELECT *
FROM #getratings_recipes# innerjoin #getmembers#
WHERE #getratings_recipes.recipeID# = #getmembers.recipeID#
AND #getratings_recipes.memberID#=#getmembers.memberID#
ORDER BY recipeID, daterated desc
</cfquery>
0
 

Author Comment

by:alicia1234
ID: 17792046
Following is what ultimately worked. I had no luck with the inner joins that were recommended.
And the ## in nmarano's answer were incorrect. But SidFishes and nmarano's answers did get
me on the right track. Thanks.

<cfquery name ="getratings_recipes" datasource="connCWArecipes">
Select r8.*, r.recipename
From tblratings as r8, tblrecipes as r
where r8.recipeID = r.recipeID
</cfquery>

<cfquery name ="getmembers" datasource="connCWAmembers">
Select username, memberID
From tblmembers
</cfquery>

<cfquery name="rs_ratings" dbtype="query">
SELECT *
FROM getratings_recipes, getmembers
WHERE getratings_recipes.memberID = getmembers.memberID
ORDER BY recipeID, daterated desc
</cfquery>
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question