Solved

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

Posted on 2002-05-07
5
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 52

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use System DSN 6 95
.dwt files not viewable in browser - why? 2 144
WEB Farm 6 85
Internal to DMZ IIS Authentication. 3 94
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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