# system.db.execSProcCall system.db.DECIMAL bug

**URL:** <https://forum.inductiveautomation.com/t/system-db-execsproccall-system-db-decimal-bug/36921>\
**Category:** Ignition\
**Tags:** database\
**Created:** [July 21, 2020, 9:45pm UTC](https://forum.inductiveautomation.com/t/system-db-execsproccall-system-db-decimal-bug/36921 "2020-07-21T21:45:48Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![cesrom](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@cesrom](https://forum.inductiveautomation.com/u/cesrom)\
**Post date:** [July 21, 2020, 9:45pm UTC](https://forum.inductiveautomation.com/t/system-db-execsproccall-system-db-decimal-bug/36921/1 "2020-07-21T21:45:49Z")

</div>

**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:

```auto
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:

```auto
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](https://github.com/garystafford/mssql-sp-java-jdbc) because my Java skills are not what they used to be.

So I hacked my way and added this method:

```java
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