Solved

SQL query returns different results based on db connection type (ODBC vs OLEDB), help!

Posted on 2002-05-07
5
314 Views
Last Modified: 2013-12-24
Below is my query...

<CFQUERY DATASOURCE="sparkequity" NAME="viewcart">
SELECT MyCart.*, Products.*, QtyPrice.*
FROM MyCart, Products, QtyPrice
WHERE MyCart.ProductID = Products.ProductID AND
MyCart.BasketNum = '#Cookie.BasketNum#' AND
QtyPrice.theQty = MyCart.Quantity AND
QtyPrice.ProductID = MyCart.ProductID
</cfquery>

The query executes fine on both CF 4.5 and CF 5, but when I do a CFOUTPUT with viewcart as the query on the box running CF 5 the ProductID column is missing. ProductID exists in all 3 tables.

See comment I posted below.. I changed my db connection type to ODBC as it is on the CF 4.5 box and it now works on the CF 5 box. Apparently based on the db connection type the SQL is returning different results.
0
Comment
Question by:istomtom
5 Comments
 

Author Comment

by:istomtom
ID: 6995106
Well.. I figured it out.. but please still answer if you know the reason why.

I guess it has to do with SQL and how it works differently in ODBC and OLEDB. The query is a JOIN and I guess it executes differently in ODBC and OLEDB. Does anyone know why? The OLEDB driver I was using is Microsoft.Jet.OLEDB.4.0

I would really like to use OLEDB for various reasons, does anyone know how I would need to rewrite my SQL so that it will work with OLEDB?

Thanks
0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 6995139
instead of doing "SELECT MyCart.*, Products.*, QtyPrice.*"

you should try specifying each column that you need from each of the tables

"SELECT MyCart.Quantity, MyCart.BasketNum,  Products.ProductID, QtyPrice.theQty, etc."

I think that might help
0
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 150 total points
ID: 6995617
Agree with SBennett, use only those fields that used to generate the query. If there is a condition where you want to use both field like ProductID from more than 1 tables, you can rename the fields like this:

<CFQUERY DATASOURCE="sparkequity" NAME="viewcart">
SELECT MyCart.ProductID As MyCartPID, Products.ProductID As PdtPDI, QtyPrice.ProductID As QtyPricePDI
FROM MyCart, Products, QtyPrice
WHERE MyCart.ProductID = Products.ProductID AND
MyCart.BasketNum = '#Cookie.BasketNum#' AND
QtyPrice.theQty = MyCart.Quantity AND
QtyPrice.ProductID = MyCart.ProductID
</cfquery>

Cheers
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6996052
ProductID is ambiguous in all tables so you need to specify the column name.

Same as SBennet already said.
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 7019982
You might also want to play around with the dbtype attribute of cfquery. It could be that CF Server is making the connection in a generic way. If you specify a dbtype attribute it ought to modify the way it makes the connection and sends the SQL to the database.

Spike
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Redirect website ! 4 54
Coldfusion update database with CFLOOP 5 64
Help Fix A 403 error 13 108
Why is my Splunk Web URL not working? 2 66
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

778 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