I want to report a bug in Macromedia's JDBC driver for Sybase
I can make it fail using the MM driver and it works fine using a
Sybase-supplied JDBC driver.
This is a query i have used many times with different dbs:
My problem is that I am using CFMX on Mac OS X -- an unsupported
platform -- but the bug is in Macromedia's JDBC driver.
Here's the db layout:
Product Supplier
ProductID SupplierID
Product Supplier
ProductAndSupplier
SupplierID
ProductID
Here's the query
SELECT P.Product, Count(S.SupplierID) AS NumberOfSuppliers
FROM Product P
LEFT OUTER JOIN ProductAndSupplier L ON P.ProductID =
L.ProductID
LEFT OUTER JOIN Supplier S ON L.SupplierID =
S.SupplierID
GROUP BY P.Product
ORDER BY P.Product
here's some typical outout
Product Number of Suppliers
Product A 2
Product B 1
Product C 0
Product D 2
here's the layout
The query works fine with a Sybase-supplied driver.
The query fails with the Macromedia Sybase JDBC driver if there are
any products with no Suppliers (a valid condition). -- Product C.
Here's the error I get:
Error Executing Database Query.
[Macromedia][Sybase JDBC Driver]Unexpected token type: 0xe5 The
error occurred in /opt/coldfusionmx/wwwroot/
mycfmxapps/SybaseMMJDBCDriverBug.cfm: line 80
78 : ORDER BY P.Product
79 :
80 : </cfquery>
81 :
82 : <cfdump var="#MakeItBreak#">
SQL SELECT P.Product, Count(S.SupplierID) AS NumberOfSuppliers
FROM Product P LEFT OUTER JOIN
ProductAndSupplier L ON P.ProductID = L.ProductID LEFT OUTER JOIN
Supplier S ON L.SupplierID = S.SupplierID
GROUP BY P.Product ORDER BY P.Product
Anyway, since I have no standing (unsupported platform) i can't
officially report the bug.
Below is a complete, self-contained, CFMX program to create and
populate the tables, then cause the error.
I hope somebody using Sybase on a supported platform will try it and
report the bug to Macromrdia.
Dick
========================================================================
==================
<cfsetting EnableCFOutputOnly="YES">
<cfset attributes.datasource = "SybaseMMJDBCDriverBug">
<cfset attributes.username = "sa">
<cfset attributes.password = "">
<cfquery name="CreateTables"
datasource="#attributes.datasource#"
username= "#attributes.username#"
password= "#attributes.password#"
>
Create Table Product
(
ProductID Numeric(10,0) IDENTITY PRIMARY KEY NOT NULL,
Product VarChar(250) NOT NULL
)
Create Table Supplier
(
SupplierID Numeric(10,0) IDENTITY PRIMARY KEY NOT NULL,
Supplier VarChar(250) NOT NULL
)
Create Table ProductAndSupplier
(
SupplierID Numeric(10,0) References Supplier(SupplierID) NOT NULL,
ProductID Numeric(10,0) References Product (ProductID) NOT NULL
)
</cfquery>
<cfquery name="PopulateTables"
datasource= "#attributes.datasource#"
username= "#attributes.username#"
password= "#attributes.password#"
>
SET IDENTITY_INSERT Product ON
INSERT INTO Product(ProductID, Product) VALUES(1, 'Product A')
INSERT INTO Product(ProductID, Product) VALUES(2, 'Product B')
INSERT INTO Product(ProductID, Product) VALUES(3, 'Product C')
INSERT INTO Product(ProductID, Product) VALUES(4, 'Product D')
SET IDENTITY_INSERT Product OFF
SET IDENTITY_INSERT Supplier ON
INSERT INTO Supplier(SupplierID, Supplier) VALUES(1, 'Supplier 1')
INSERT INTO Supplier(SupplierID, Supplier) VALUES(2, 'Supplier 2')
INSERT INTO Supplier(SupplierID, Supplier) VALUES(3, 'Supplier 3')
SET IDENTITY_INSERT Supplier OFF
INSERT INTO ProductAndSupplier(SupplierID, ProductID) VALUES(1, 1)
INSERT INTO ProductAndSupplier(SupplierID, ProductID) VALUES(1, 2)
INSERT INTO ProductAndSupplier(SupplierID, ProductID) VALUES(1, 4)
INSERT INTO ProductAndSupplier(SupplierID, ProductID) VALUES(2, 1)
INSERT INTO ProductAndSupplier(SupplierID, ProductID) VALUES(2, 4)
CHECKPOINT
</cfquery>
<cfquery name="MakeItBreak"
datasource= "#attributes.datasource#"
username= "#attributes.username#"
password= "#attributes.password#"
>
SELECT P.Product, Count(S.SupplierID) AS NumberOfSuppliers
FROM Product P
LEFT OUTER JOIN ProductAndSupplier L ON P.ProductID =
L.ProductID
LEFT OUTER JOIN Supplier S ON L.SupplierID =
S.SupplierID
GROUP BY P.Product
ORDER BY P.Product
</cfquery>
<cfdump var="#MakeItBreak#">
<cfsetting EnableCFOutputOnly="NO">
========================================================================
==================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
