Unable to insert 5 decimal places into decimal(15,5)

[trimline-gaiustemple]

When trying to insert a php float with 5 decimal places into a decimal(15,5) column I get the following error:

PHP version
PHP 8.1.2 (cli) (built: Jan 19 2022 10:18:23) (ZTS Visual C++ 2019 x64) PHP SQLSRV or PDO_SQLSRV version
PDO_SQLSRV 5.10.0+15327 Microsoft ODBC Driver version
Both 17 and 18 SQL Server version
Azure SQL Server Client operating system
Windows Table schema
affected column is decimal(15,5) Problem description
When trying to insert a 5dp value, an error is thrown Expected behavior and actual behavior
Expected behavior is to be able to insert 5dp values into the 5dp column. Instead this error is thrown PDOException::("SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Error converting data type nvarchar to numeric.")

When rounding values to 4dp, it successfully inserts but that is causing inaccuracies in the data.

[absci]

Hi, is it possible for you to get an ODBC trace?

[trimline-gaiustemple]

SQL.LOG Does this help?

[absci]

The trace that you provided doesn't look like it contains the error you described. Could you try to start trace on the ODBC driver that you use to connect your database? Then trigger this error before stop trace.

Also, I can't reproduce it with this simple PHP script. Are you trying to do something similar to it? If so, could you run this and post the result?

$conn = new PDO("sqlsrv:server = $azureServer; $connectionInfo", $azureUser, $azPwd);

$tsql = "
        SET NOCOUNT ON
        DROP TABLE IF EXISTS #test_decimal

        CREATE TABLE #test_decimal
        (  
         MyDecimalColumn DECIMAL(15,5)  
        );

        INSERT INTO #test_decimal
                    ([MyDecimalColumn])
                VALUES
                    (1234512345.12345);

        SELECT * FROM #test_decimal;
        ";
$stmt = $conn->query($tsql);
$result = $stmt->fetchall(PDO::FETCH_ASSOC);
print_r($result);
unset($stmt);
unset($conn);

[absci]

Close due to inactivity, feel free to reopen