Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-305

Incorrect insert values for datatype decimal



    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 3.1.9, 3.1.11
    • 3.1.12
    • General
    • 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


      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 ( 
        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
      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.




            Lawrin Lawrin Novitsky
            alma_pi Almut Pingel
            0 Vote for this issue
            2 Start watching this issue



              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.