MySQL Bugs: #96642: Incorrect handling of SQL_C_NUMERIC results causes errors in VB 6.0 / ADO

Bug #96642	Incorrect handling of SQL_C_NUMERIC results causes errors in VB 6.0 / ADO
Submitted:	24 Aug 2019 23:01	Modified:	25 Jun 2020 22:47
Reporter:	M. F.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.x and 8.x	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	ADO, MSQL_TYPE_NEWDECIMAL, ODBC

Description:
The same bug that was fixed in this bug report
https://bugs.mysql.com/bug.php?id=10527
is also present in the 5.x and 8.x releases

The SUM aggregate function doesn't work when using VB 6.0 / VB Script with ADO to connect to a MySQL.
The driver always returns the following error message:
"Multiple-step operation generated errors."

How to repeat:
Here is a VB Script to repeat the error.
Rename strCon2 to strCon to test different ODBC Drivers.

Option Explicit

Dim rs 'As ADODB.Recordset
Dim con 'As ADODB.Connection
Dim strCon 'As String
Dim strCon2 'As String
Dim SQL 'As String
Dim SQL2 'As String

Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1

Set con = CreateObject("ADODB.Connection")

strCon = "Driver={MySQL ODBC 8.0 Unicode Driver}" & _
            ";Server=localhost" & _
            ";Database=mydb" & _
            ";Uid=root" & _
            ";Pwd=" & _
            ";DSN=''"
            '";COLUMN_SIZE_S32=1"

strCon2 = "Driver={MariaDB ODBC 3.1 Driver}" & _
            ";Server=localhost" & _
            ";Database=mydb" & _
            ";Uid=root" & _
            ";Pwd=" & _
            ";DSN=''"
            '";COLUMN_SIZE_S32=1"

strCon2 = "Driver={MySQL ODBC 3.51 Driver}" & _
            ";Server=localhost" & _
            ";Database=mydb" & _
            ";Uid=root" & _
            ";Pwd=" & _
            ";DSN=''"
            '";COLUMN_SIZE_S32=1"

con.Open strCon

SQL = "create temporary table testtemp ( ID bigint AUTO_INCREMENT primary key, PRICE decimal(38,20) )"
con.Execute SQL

Dim L
For L = 1 To 100
    SQL = "insert into testtemp ( PRICE ) values ( 10000000.0 )"
    Call con.Execute( SQL )
Next

SQL = "SELECT SUM( PRICE ) FROM testtemp"

Set rs = CreateObject("ADODB.Recordset")
rs.Open SQL, con, adOpenStatic, adLockReadOnly, adCmdText

msgbox rs.Fields(0).Value

con.Close
Set rs = Nothing
Set con = Nothing

Suggested fix:
Repeat the fix from the 3.51 series ODBC driver in the 5.x and 8.x series.

Thank you for the bug report.

Posted by developer:
 
The patch is pushed into the source three.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.21 release, and here's the proposed changelog entry from the documentation team:

The SUM aggregate function did not function with ADO.

Thank you for the bug report.