Execution of select query returns wrong values (floats become very large integers)

[SimoneD89]

Environment

• OS: fedora 37, kernel 6.0.11-300.fc37.x86_64
• Python: 3.11.1
• pyodbc: 4.0.35
• Database: Microsoft SQL Server
• Driver: ODBC Driver 17 for SQL Server
• (Libreoffice 7.4.3.2)

Issue/Describe the bug

I hit the following bug by trying to use the module scriptforge (to use python functions in libreoffice Calc).

I've tried to reduce the example as much as possible. The script imports a float number from a sql query. When executed from shell it returns -0.65, when executed from libreoffice calc it returns -650000000. I suspect it is related to local configurations. It could be a duplicate of https://github.com/mkleehammer/pyodbc/issues/753.

To Reproduce

 ├── script.py
 ├── module.py

script.py
---------
from scriptforge import CreateScriptService
from module import import_float

def test(args=None) -> None:
    float_number = float(import_float())

    doc = CreateScriptService("Calc")
    doc.SetValue("A12", float_number)
    return None

module.py
---------
import os
import pyodbc

def import_float(args=None) -> float:
    # Connection to the server
    driver = "{ODBC Driver 17 for SQL Server}"
    url, database, uid, pwd = ...
    cnxn = pyodbc.connect(
        "Driver={" + driver + "};"
        "Server=" + url + ";"
        "Database=" + database + ";"
        "uid=" + uid + ";"
        "pwd=" + pwd
    )
    cursor = cnxn.cursor()

    # Query to select a single float number [decimal(38, 9)]
    query = (
        "SELECT TOP 1 column FROM Table"
    )

    float_number = list(cursor.execute(query))[0][0]
    cnxn.close()

    return float_number

Error

When the test (script.py) function is executed from libreoffice Calc, it returns -650000000 instead of -0,65. If I put the import_float function inside script.py it correctly returns -0.65. In the past I already hit similar bugs without using scriptforge/libreoffice).

[gordthompson]

In your import_float function, what is repr(float_number) immediately before you return it?

[SimoneD89]

When executed directly (e.g. from shell python script.py) it returns Decimal('-0.650000000'). When executed from libreoffice calc (running a macro python ), it returns Decimal('-650000000'). I used the following code to intercept repr(float_number) in both cases.

def import_float(args=None) -> float:
    [...]
    float_number = list(cursor.execute(query))[0][0]
    cnxn.close()

    with open("/home/user/float_number.txt", "w") as file:
        file.write(repr(a))
    return float_number

[gordthompson]

Can you confirm that the correct value is entered into Calc if you use this instead?

SELECT TOP 1 CAST(column AS float) FROM Table

[SimoneD89]

It works, whit your suggestion I get the correct value.

Thank you very much for your help. What was the problem?

[gordthompson]

As you suspected, it's almost certainly the same issue as #753 . Have you tried doing

pyodbc.setDecimalSeparator(".")

before executing your (original) query? It might save you having to remember to CAST(), and would also help if you actually wanted to keep the return value as decimal() instead of float.

[SimoneD89]

It also works with pyodbc.setDecimalSeparator(".").

Thank you very much.

[gordthompson]

Duplicate of #753