Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
3.1.9, 3.1.11
-
None
-
10.3.27-MariaDB,
Microsoft Windows 10,
Microsoft 365 Excel Version 2102, 64-bit with VBA 7.1 using ActiveX Data Objects 6.1 Library
Description
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.