istomtom
asked on
SQL query returns different results based on db connection type (ODBC vs OLEDB), help!
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.
<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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ProductID is ambiguous in all tables so you need to specify the column name.
Same as SBennet already said.
Same as SBennet already said.
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
Spike
ASKER
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