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

Incorrect insert values for datatype decimal

    XMLWordPrintable

Details

    • 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

    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.

      Attachments

        1. odbctrace_mariaDB_SQL.LOG
          129 kB
          Almut Pingel
        2. odbcTrace_MySQL_SQL.LOG
          127 kB
          Almut Pingel

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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