Calling a Stored Procedure returns empty strings on a column

[Yasir323]

Environment

• Python: 3.8.10
• pyodbc: 4.0.35
• OS: Ubuntu 20.04
• DB: SQL Server 12.0.6024.0
• driver: ODBC Driver 18 for SQL Server

  Issue

I am calling a stored procedure from one of my cronjobs. The SP returns data from 2 tables. In the second table there is a column 'remark' which is None when the respective data in the NULL, but when there is some data present, the value I get in my script is an empty string. I tried calling the same SP in SQL Server Management Studio and the results are just fine.

Also, I tried querying the data from that table in another script just to test using a select query, and the results are fine there too.

Here is the detail on that particular column:

[gordthompson]

What happens if you create a stored procedure like

CREATE PROCEDURE test_sp
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP 10 remark FROM table_name WHERE remark IS NOT NULL;
END

and call that?

[v-chojas]

Also, can you post example Python code that you're using, as well as an ODBC trace?

[Yasir323]

Also, can you post example Python code that you're using, as well as an ODBC trace?

tables = []
with conn.cursor() as cur:
cur.execute(query)
while cur.nextset():
columns = [column[0] for column in cur.description]
table = []
for row in cur.fetchall():
table.append(dict(zip(columns, row)))
tables.append(table)
for row in tables[1]:
print(row['remark'])

SQL.LOG

[v-chojas]

That's a Windows ODBC trace, but you said you're using Ubuntu...?

Also would help if you told us what column number is having this problem.

[gordthompson]

Your result set has two columns named "remark". Column 19

tripsTemp       b34-2e78    EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       19 
        UCHAR *             0x000000455CBEF3E0 [       6] "remark"
        SWORD                      200 
        SWORD *             0x000000455CBEF3CC (6)
        SWORD *             0x000000455CBEF3C0 (12)
        SQLULEN *           0x000000455CBEF3D0 (500)
        SWORD *             0x000000455CBEF3C8 (0)
        SWORD *             0x000000455CBEF3C4 (1)

and column 85

tripsTemp       b34-2e78    EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       85 
        UCHAR *             0x000000455CBEF3E0 [       6] "remark"
        SWORD                      200 
        SWORD *             0x000000455CBEF3CC (6)
        SWORD *             0x000000455CBEF3C0 (12)
        SQLULEN *           0x000000455CBEF3D0 (100)
        SWORD *             0x000000455CBEF3C8 (0)
        SWORD *             0x000000455CBEF3C4 (1)

The values returned are

tripsTemp       b34-2e78    EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       19 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x000001E9F63F0BD0 [      16] "SHOLAPUR"
        SQLLEN                  4096
        SQLLEN *            0x000000455CBEF408 (16)

and

tripsTemp       b34-2e78    EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       85 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x000001E9F63F0BD0 <zero length>
        SQLLEN                  4096
        SQLLEN *            0x000000455CBEF408 (0)

respectively. When you print(row['remark']) you apparently get the latter one, because of your packing the rows into a dict.

[Yasir323]

Your result set has two columns named "remark". Column 19

tripsTemp       b34-2e78  EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       19 
        UCHAR *             0x000000455CBEF3E0 [       6] "remark"
        SWORD                      200 
        SWORD *             0x000000455CBEF3CC (6)
        SWORD *             0x000000455CBEF3C0 (12)
        SQLULEN *           0x000000455CBEF3D0 (500)
        SWORD *             0x000000455CBEF3C8 (0)
        SWORD *             0x000000455CBEF3C4 (1)

and column 85

tripsTemp       b34-2e78  EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       85 
        UCHAR *             0x000000455CBEF3E0 [       6] "remark"
        SWORD                      200 
        SWORD *             0x000000455CBEF3CC (6)
        SWORD *             0x000000455CBEF3C0 (12)
        SQLULEN *           0x000000455CBEF3D0 (100)
        SWORD *             0x000000455CBEF3C8 (0)
        SWORD *             0x000000455CBEF3C4 (1)

The values returned are

tripsTemp       b34-2e78  EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       19 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x000001E9F63F0BD0 [      16] "SHOLAPUR"
        SQLLEN                  4096
        SQLLEN *            0x000000455CBEF408 (16)

and

tripsTemp       b34-2e78  EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001E9F6A00780
        UWORD                       85 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x000001E9F63F0BD0 <zero length>
        SQLLEN                  4096
        SQLLEN *            0x000000455CBEF408 (0)

respectively. When you print(row['remark']) you apparently get the latter one, because of your packing the rows into a dict.

Yes that is right, it was I guess a bug in the SP. Thank you.