Solved

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

Posted on 2002-05-07
5
326 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

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…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

634 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