Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2002-05-07
5
Medium Priority
?
331 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 53

Accepted Solution

by:
Ryan Chong earned 450 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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 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