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:Microsoft Windows (microsoft server 2000)
Assigned to: CPU Architecture:Any

[5 Aug 2005 22:20] Simon Ferragne
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
[7 Aug 2005 9:23] Vasily Kishkin
Could you please provide here full text of asp code ?
[1 Sep 2005 14:12] Massimo Endrizzi
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.
[7 Sep 2005 23:00] Bugs System
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".
[19 Nov 2005 15:05] Fabian Mossberg
Any progress? Im having the same problem.
[23 Nov 2005 22:07] Richard Wesley
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.
[29 Nov 2005 8:04] Vasily Kishkin
I was not able to reproduce the bug on 3.5.12. Could you please download new myodbc and try ?
[30 Dec 2005 0:00] Bugs System
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".
[13 Mar 2006 8:15] Mark
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?
[13 Mar 2006 8:35] Mark
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.
[27 Mar 2006 13:04] Philip Owen
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.
[27 Mar 2006 14:50] Philip Owen
Used the suggested workaround of casting, then found installing 3.51.12 fixed the problem. Maybe related to 15294?
[2 Feb 2007 9:51] Tonci Grgin
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/