fetchval() does not return all bytes of result?

[xiongyu0523]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.9
• pyodbc: 4.0.32
• OS: Windows 11
• DB: Azure SQL DB
• driver: ODBC Driver 17 for SQL Server

Issue

I have a formatted JSON query like this "SELECT TOP 100 FROM table FOR JSON AUTO". I'm currently using fetchval() to retrieve the JSON array string from pyodbc. It seems like the string was tailed if number of characters go over 2025, so cause a error when I call json.loads() for a invalid string.

The return string can be very long if I query 1000 or more records, is there a limiation on how many bytes fetchval() can return? or I need to use other method to read the result? (fetchone, fetchall return row object but I simply just want JSON string)

    crsr.execute("SELECT TOP 100  FROM mytable FOR JSON AUTO")
    mystr = crsr.fetchval()
    json.loads(mystr)

[xiongyu0523]

Oh, it seems it only fetch the first column of first row, what is the default size to split JSON string result into rows?

I was able to itera list returned by fetchall() use below code, each element in the list is a single entry truple so I need use [0] to get string.

rows = crsr.fetchall()
for row in rows:
    str += row[0]

[v-chojas]

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server#output-of-the-for-json-clause "A large result set splits the long JSON string across multiple rows."

The limit for splitting seems to be ~2KB. This is by design.

[xiongyu0523]

Thanks for the clarity