MySQL Bugs: #12406: db ODBC Drivers[Microsoft][ODBC Driver Manager] Program type out of range.

Bug #12406	db ODBC Drivers[Microsoft][ODBC Driver Manager] Program type out of range.
Submitted:	5 Aug 2005 22:20	Modified:	2 Feb 2007 9:51
Reporter:	Simon Ferragne	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:		OS:	Windows (microsoft server 2000)
Assigned to:		CPU Architecture:	Any

Description:
I migrated from mySql 4.0.1 to 5. I am runnig a ASP apllication from IIS using connector 3.51

The sum of an int is not allowed and return a odbc error db ODBC Drivers[Microsoft][ODBC Driver Manager] Program type out of range. 

I switched the datatype of the field to double and it works. all sum on int are no longer supported probaly because they return datatype such as mediumint and bigint.  Those datatype are not supported by microsoft ODBC I presume. 

How to repeat:
----------
Ex: select sum(points) from tbl_points where idcustomer=938
-----------
tbl_points.points  INT. 

Suggested fix:
How can we choose the datatype of the sum of fieldstype INT. There should be a change mediumInt to int option in the drivers

Could you please provide here full text of asp code ?

I had the same problem, starting a sql query from Visual Basic  6 with ADO. I realized that the function SUM(), applied on an integer type, gives the result in a type 'decimal', which cannot work with VB 6 or ASP.
This happened to me, migrating from MySql server version 5.0.1-alpha-nt to version 5.0.3 beta or newer. 
In order to use the 'old queries' with the latest MySQL versions I used the function CAST() in the queries, Ex:

select CAST(sum(points) AS SIGNED) from tbl_points where idcustomer=938;
(SIGNED=INTEGER)

I am not an expert, but it works.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Any progress? Im having the same problem.

I am also seeing this problem with MyODBC 3.5.11-2.  If I issue
select SUM(i) from t;

where i is an integer column, the ODBC driver returns type DBTYPE_EMPTY for the wType field in the DBCOLUMNINFO describing the rowset (obtained in the usual way by a call to pIColumnsInfo->GetColumnInfo).

In general, it appears that MyODBC does not handle the new DECIMAL type correctly when you try to use it with OLE.

I was not able to reproduce the bug on 3.5.12. Could you please download new myodbc and try ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I had the same problem.

MySQL table called 'orders'

CREATE TABLE `orders` (
  `OrderID` bigint(10) unsigned NOT NULL auto_increment,
  `BasketID` int(10) unsigned NOT NULL default '0',
  `OrderName` varchar(30) default NULL,
  `OrderContact` varchar(30) default NULL,
  `OrderPhone` varchar(30) default NULL,
  `OrderFax` varchar(30) default NULL,
  `OrderEmail` varchar(60) default NULL,
  `OrderAddress1` varchar(30) default NULL,
  `OrderAddress2` varchar(30) default NULL,
  `OrderAddress3` varchar(30) default NULL,
  `OrderAddress4` varchar(30) default NULL,
  `OrderCustomerOrderNo` varchar(30) default NULL,
  `OrderPaymentMethod` varchar(20) default NULL,
  `OrderCardHoldersName` varchar(100) default NULL,
  `OrderCardNumber` varchar(40) default NULL,
  `OrderCardExpiryDate` varchar(8) default NULL,
  PRIMARY KEY  (`OrderID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

'select * from orders' causes this error.

Any idea why this is happening?

Changed the 'OrderID' to a double from a bigint and it works fine.

There is obviously a problem with the MySQL ODBC driver with the bigint datatype.

I have this error too. Connecting from XP to MySQL 5.0.19-nt on 2003.
ODBC Connector 3.51.11.00

The following VBScript can be executed from the windows command line using:
cscript error.vbs
assuming it was saved as error.vbs :-)

Option Explicit
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Dim objConn, objRS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=mysql; UID=root; PWD=myrootpassword"
Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open "SELECT sum(transition_time) as mysum FROM time_zone_transition", objConn, adOpenForwardOnly, adLockReadOnly
WScript.Echo objRS("mysum")

Yields

error.vbs(9, 1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Program type out of range

This script was amended slightly to connect using localhost to make it easier for someone to replicate on a single machine.
Connections using a DSN have the same issue.

Used the suggested workaround of casting, then found installing 3.51.12 fixed the problem. Maybe related to 15294?

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/