Status: Closed (View Workflow)
Affects Version/s: 3.1.9, 3.1.11
Fix Version/s: 3.1.12
Microsoft Windows 10,
Microsoft 365 Excel Version 2102, 64-bit with VBA 7.1 using ActiveX Data Objects 6.1 Library
I'm using Excel/VBA to communicate with my sql database, using an ADODB connection and parameters. In my database, I have a column of type Decimal(13,4). I create a parameter in VBA accordingly:
However, this fails as soon as myvalue cannot be stored as a long anymore, meaning as soon as 1000*myvalue > 2147483647. No error occurs, but the value written to the database is gibberish/overflowing.
The problem occurs independent of the type of myvalue, but vanishes when the parameter type is changed from adDecimal to adDouble.
The query works fine if the MySQL Connector is used instead. Tested with MySQL ODBC 8.0 ANSI Driver and MySQL ODBC 8.0 Unicode Driver.
Here is a minimal working example (given an existing mariaDB database):
In the database:
Outcome in database when using Maria ODBC 3.1 Driver:
In the example above, the dec2 values (inserted as adDouble) are correct, while the dec1 values (inserted as adDecimal) are incorrect for id=1 and id=3.
Attached are the ODBC Traces when running above query with the MariaDB Connector and the MySQL Connector.