system.db.execSProcCall system.db.DECIMAL bug

Ignition
1

Reference: support ticket 59493.

A while back a user reported that a value was not being stored properly on the database, so I checked and it was true.

The field in question is of type DECIMAL(15, 8). The value in question was 0.00060606. And the value inserted was 0.00060000.

At first I thought the value was being truncated, but instead it is being rounded to four digits after the decimal point.

Additional info.
Ignition: 8.0.14 (b2020062220)
DB: Microsoft SQL Server 2017
JDBC Driver: mssql-jdbc-8.2.2

To prove this I ran the following code:

call = system.db.createSProcCall(procedureName="[dbo].[decimal_test_insert]", database="MBAMSSQL")
call.registerInParam("val", system.db.DECIMAL, 0.00069606)
system.db.execSProcCall(call)

# And also this:
from java.math import BigDecimal

call = system.db.createSProcCall(procedureName="[dbo].[decimal_test_insert]", database="MBAMSSQL")
call.registerInParam("val", system.db.DECIMAL, BigDecimal("0.00069606"))
system.db.execSProcCall(call)

Both calls insert 0.00070000 into the table.

The “workaround” is to register the input parameter as system.db.FLOAT, like this:

call = system.db.createSProcCall(procedureName="[dbo].[decimal_test_insert]")
call.registerInParam("val", system.db.FLOAT, 0.00069606)
system.db.execSProcCall(call)

This correctly inserts 0.00069606.

I also created a MySQL table and stored procedure, but the value in this case is not being rounded up when using system.db.DECIMAL; it is inserted properly for both system.db.DECIMAL and system.db.FLOAT.

That made me consider that the MSSQL JDBC driver was at fault, so I created a Java project based on this project because my Java skills are not what they used to be.

So I hacked my way and added this method:

public void insertDecimal() {
    CallableStatement cstmt = null;
    try {
        cstmt = connection.getConnection().prepareCall("{call dbo.decimal_test_insert(?)}");
        cstmt.setBigDecimal("val", new BigDecimal("0.00069606"));
        cstmt.execute();
    } catch (Exception ex) {
        Logger.getLogger(RunExamples.class.getName()).
                log(Level.SEVERE, null, ex);
    } finally {
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.class.getName()).
                        log(Level.WARNING, null, ex);
            }
        }
    }
}

And this correctly inserts 0.00069606 into the database.

Which leads me to the one possible conclusion: there is something wrong when using system.db.DECIMAL for more than four numbers after the decimal point.

While I could definitely just use system.db.FLOAT instead of system.db.DECIMAL I do believe that I should be registering parameters with the same data type as used in the DB.

I am only hoping someone from Inductive Automation confirms this is as a bug or just points me to the error I am making by using system.db.DECIMAL when calling Microsoft SQL Server stored procedures.

Thanks