[#ODBC-305] Incorrect insert values for datatype decimal

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:

Set param = dbCmd.CreateParameter("myvalue", adDecimal, adParamInput, 0, myvalue)

param.NumericScale = 4

param.Precision = 13

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:

CREATE TABLE mytable (

  id SMALLINT AUTO_INCREMENT PRIMARY KEY,

  dec1 DECIMAL(13,4),

  dec2 DECIMAL(13,4)

);

In VBA:

Public Sub test()

    WriteToDB 123456789.1234, 123456789.1234

    WriteToDB 214748.3647, 214748.3647

    WriteToDB 214748.3648, 214748.3648

End Sub

Public Sub WriteToDB(dec1 As Variant, dec2 As Variant)

    Dim dbConn As ADODB.Connection

    Dim dbCmd As ADODB.Command

    Dim sql As String

    Dim param As ADODB.Parameter

    'Open database connection

    Set dbConn = New ADODB.Connection

    dbConn.Open "Provider=MSDASQL;DSN=XXX;DATABASE=XXX;UID=XXX;PWD=XXX;"

    Set dbCmd = New ADODB.Command

    dbCmd.ActiveConnection = dbConn

    dbCmd.CommandType = adCmdText

    'Define command and parameters

    sql = "INSERT INTO mytable (dec1, dec2) VALUES (?, ?)"

    Set param = dbCmd.CreateParameter("dec1", adDecimal, adParamInput, 0, dec1)

    param.NumericScale = 4

    param.Precision = 13

    dbCmd.Parameters.Append param

    Set param = dbCmd.CreateParameter("dec2", adDouble, adParamInput, 0, dec2)

    dbCmd.Parameters.Append param

    'Execute command

    dbCmd.CommandText = sql

    dbCmd.Execute , , adExecuteNoRecords

    'Close connection

    dbConn.Close

End Sub

Outcome in database when using Maria ODBC 3.1 Driver:

MariaDB [XXX]> SELECT * FROM mytable;

+----+--------------+----------------+

| id | dec1         | dec2           |

+----+--------------+----------------+

|  1 |  191227.7282 | 123456789.1234 |

|  2 |  214748.3647 |    214748.3647 |

|  3 | -214748.3640 |    214748.3648 |

+----+--------------+----------------+

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.

[ODBC-305] Incorrect insert values for datatype decimal Created: 2021-03-19 Updated: 2021-04-15 Resolved: 2021-04-15
Status:	Closed
Project:	MariaDB Connector/ODBC
Component/s:	General
Affects Version/s:	3.1.9, 3.1.11
Fix Version/s:	3.1.12

[ODBC-305] Incorrect insert values for datatype decimal Created: 2021-03-19 Updated: 2021-04-15 Resolved: 2021-04-15