Unable to upload large BLOBs with "SQL Server" driver on Windows

[harshparekhau]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

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.12.0
• pyodbc: '5.0.1'
• OS: Windows 10 64-bit:
• DB: SQL Server 2016
• driver: 2017.177.02.01

Issue

I have done some trouble shooting and found that there is a length limitation in cursor I tried the csv and it worked but couldnt upload a PDF.

Code

import pyodbc,os
import pandas as pd

# Replace the following variables with your actual database connection details
server_name = 'DC3-SQLDEV-01\\VPNR'
database_name = 'Viewpoint'
def pdf_to_byte_array(pdf_file_path):
    with open(pdf_file_path, 'rb') as file:
        byte_array = bytearray(file.read())
    return byte_array

def insert_attachment(byte_data, file_type):#vendor,original_invoice_no,inv_date,description,inv_total):
    # Connect to the SQL Server database using Trusted Connection (Windows Authentication)
    conn_str = f'DRIVER={{SQL Server}};SERVER={server_name};DATABASE={database_name};Trusted_Connection=yes;'
    db_connection = pyodbc.connect(conn_str)
    cursor = db_connection.cursor()

    try:
        # Stored procedure parameters


        # Execute the stored procedure
        cursor.execute("EXEC dbo.uspInsertViewpointAttachment1 @AttachmentData=?, @FileType=? ",(byte_data,file_type))
        #Commit the transaction (if needed)
        cursor.commit()
        print("Stored procedure executed successfully.")
        # cursor.execute("SELECT * from bHQAF WHERE APCo=? AND APRef=?", (pyodbc.Binary(byte_data),file_type))
        # Commit the transaction (if needed)
        
        # result=cursor.fetchval()
        # cursor.commit()
        # keyID=result
        # cursor.commit()
        # print(f"Stored query executed successfully.{keyID}")

        # cursor.close()
        # db_connection.close()
        # return result
    except pyodbc.Error as ex:
        print("Error executing the stored procedure:", ex)
        
    finally:
        # Close the cursor and database connection
        cursor.close()
        db_connection.close()
        return True #keyID
def pdf_to_byte_array(pdf_file_path):
    with open(pdf_file_path, 'rb') as file:
        byte_array = file.read()
    return byte_array
    
os.chdir('B:/ReprocessF/test')
pdf_file_path=r'INV-0111.csv'
byte_array = pdf_to_byte_array(pdf_file_path)
file_type='.csv'
Attachment_ID=insert_attachment(byte_array, file_type)
print(type(byte_array))

print(pdf_file_path)
#print(Attachment_ID)

[harshparekhau]

using a larger file causes the issue small file works fine

[v-chojas]

SQL Server is the old driver that comes with Windows and only supports a subset of current SQL features. According to the driver version you mentioned, try using Driver=ODBC Driver 17 for SQL Server instead.

[gordthompson]

How large does the file have to be before you start to have problems?

[harshparekhau]

The version that you mentioned is the only one that I am using already, It works with a dot net program so I see its the limitation of PYODBC library that it is clipping off even the command part. [image: image.png] *Regards,* *Harsh Parekh*

…

On Fri, 8 Dec 2023 at 01:43, v-chojas ***@***.***> wrote: SQL Server is the old driver that comes with Windows and only supports a subset of current SQL features. According to the driver version you mentioned, try using Driver=ODBC Driver 17 for SQL Server instead. — Reply to this email directly, view it on GitHub <#1310 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJUXQJR2BWTDWP4QEOJP25TYIHPZFAVCNFSM6AAAAABAKQACPCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBVGU3TQNZWG4> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[harshparekhau]

How large does the file have to be before you start to have problems?

Not sure exactly. however it worked with a 2kB file and didnt with a 150kB file

[gordthompson]

This proof-of-concept code shows that there is no such "length limitation in [a pyodbc] cursor", or at least not nearly as low as you report (150kb).

import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "Database=test;",
    autocommit=True,
)
crsr = cnxn.cursor()

crsr.execute("DROP TABLE IF EXISTS my_table")
crsr.execute("CREATE TABLE my_table (id int primary key, att varbinary(max))")

with open("test.pdf", "rb") as f:
    attachment_data = f.read()
print(f"attachment_data parameter is {len(attachment_data):,} bytes")
# attachment_data parameter is 8,407,659 bytes

crsr.execute(
    "INSERT INTO my_table (id, att) VALUES (?, ?)",
    (1, attachment_data),
)
db_len = crsr.execute(
    "SELECT LEN(att) FROM my_table WHERE id = 1"
).fetchval()
print(f"number of bytes stored in database: {db_len:,}")
# number of bytes stored in database: 8,407,659

Your problem is either a driver issue or something wrong with your code.

[v-chojas]

I wonder if this might be related to not consuming all the resultsets: #262

[gordthompson]

@v-chojas - I do get an error when I try to use the ancient Driver=SQL Server:

pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (0) (SQLPutData); [HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified. (7125)')

A smaller PDF file (16 KiB) works, but the larger (8 MiB) file fails.

[v-chojas]

That confirms wrong driver was being used.

[gordthompson]

For the record, the largest BLOB that the ancient "SQL Server" driver can insert is 409_600 bytes (400 KiB). 409_601 bytes fails with the error message above.