Excel VBA向SQL数据库传递adDecimal参数出现值溢出的问题咨询

原问题描述 #

我在Excel/VBA中通过ADODB以参数化方式连接MariaDB数据库，数据库表存在Decimal(13,4)类型的列，因此尝试使用VBA的adDecimal类型创建参数：

Set param = dbCmd.CreateParameter("myvalue", adDecimal, adParamInput, 0, myvalue)
param.NumericScale = 4
param.Precision = 13

但当myvalue超出Long类型可存储范围（即1000 * myvalue > 2147483647）时，代码不会抛出任何运行错误，但写入数据库的数值会出现乱码或溢出问题。我尝试在传入CreateParameter函数时，将myvalue通过CDbl(myvalue)转成Double、或CDec(myvalue)转成Decimal类型，问题依然存在。
目前发现将参数类型替换为adDouble即可正常写入，虽然后者是可用的临时方案，但该异常的根源始终没有明确：是否是ADODB内部隐式转换导致的？还是我对adDecimal类型的使用方式存在误解？VBA对接数据库Decimal类型字段的正确处理方式是什么？

复现步骤 #

1. 在数据库中执行建表语句：

CREATE TABLE mytable ( 
  id SMALLINT AUTO_INCREMENT PRIMARY KEY, 
  dec1 DECIMAL(13,4),
  dec2 DECIMAL(13,4)
); 

2. 运行对应的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

3. 数据库查询结果如下：

MariaDB [XXX]> SELECT * FROM mytable;
+----+--------------+----------------+
| id | dec1         | dec2           |
+----+--------------+----------------+
|  1 |  191227.7282 | 123456789.1234 |
|  2 |  214748.3647 |    214748.3647 |
|  3 | -214748.3640 |    214748.3648 |
+----+--------------+----------------+

可以看到id为1和3的行中，通过adDecimal参数写入的dec1字段数值完全错误。 #

问题解答 #

异常产生的原因 #

该问题是通过通用ODBC桥（MSDASQL）连接MySQL/MariaDB时，ADODB对adDecimal参数的已知实现缺陷，并非VBA端对Decimal类型的使用方式错误：

• ADODB通过ODBC桥传输adDecimal类型参数时，驱动内部会自动将入参乘以10^NumericScale，转换为整数格式传给数据库，再由数据库端反向还原为目标Decimal类型。
• 这个转换环节在驱动层没有使用64位整数做中间存储，而是直接复用32位有符号Long类型承载转换后的整数值。一旦乘以10^4（对应配置的NumericScale=4）后的结果超过Long类型上限2147483647，就会触发32位整数溢出、补码翻转；驱动层没有对这个溢出做错误抛出，而是直接把错乱的字节流传给了数据库，所以上层VBA完全捕获不到异常，只能看到写入的数值完全不对。
• 测试中观察到的数值临界点214748.3647，刚好就是Long上限 / 10000的计算结果，和临界表现完全吻合；超过这个值的入参，都会出现类似的数值错乱、符号反转问题。
• 之前尝试在VBA端用CDec、CDbl转换入参没有效果，是因为这个错误转换发生在ODBC驱动的内部链路，和VBA端传入的原始值类型无关。

数据库Decimal字段的正确对接方案 #

• 针对MySQL/MariaDB的通用ODBC连接场景，直接使用adDouble类型作为输入参数是经过大量验证的可行方案：Double类型的精度完全覆盖Decimal(13,4)的全部取值范围（最多9位整数位+4位小数位），不会出现精度丢失，也不会触发上述的桥接层转换溢出问题。
• 如果需要严格匹配Decimal类型、避免后续可能的精度边界问题，可以替换为MySQL官方提供的原生ODBC驱动，替代原有的通用MSDASQL桥接组件。该驱动对adDecimal参数的内部转换做了64位整数适配，不存在32位溢出问题，此时原有的参数配置方式即可正常工作。
• 不建议通过传入字符串形式的数值绕过类型定义：这种方式会触发数据库端的隐式类型转换，可能影响索引命中、拉低查询性能。

内容的提问来源于stack exchange，提问作者alma_pi