Data from Sybase image column truncated at 32 KiB when retrieved via pyodbc

Mohamed Amine Mejri

I have PDF files stored as image datatype (large binary data as mentioned in the doc) in a sybase database table. I am trying to read one of those files from the db and write it to a file in a local folder using python pyodbc package like this example :

 driver = "FreeTDS"
 prt = 'port'
 db = 'db'
 passwd = 'passwd'
 usr = 'usr'
 serv = 'serv'
 conn = pyodbc.connect(driver=driver, server=serv, port=prt, uid=usr, pwd=passwd)
 sql_query = (
    "SELECT ARCH_DOC_DOC as file_content FROM table_name WHERE ARCH_DOC_ID = id"
 )
 cursor = conn.cursor()
 cursor.execute(sql_query)
 pdf_data = cursor.fetchone()[0]
 with open('my_test_file.pdf', 'wb') as f:
     f.write(pdf_data)

I am using TDS driver and running this code on Debian GNU/Linux 11 machine

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.2.3
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: yes

The problem is that I am getting corrupt file in the end and after testing a couple of files I noticed that I am always getting a file size 33ko. For example, the original file size that I am using to test is 90ko in the db and the file I am getting is only 33ko. So I am wondering if the issue is in the database/driver config or if there is a limit in the size of data that I can read with pyodbc ? And how can I fix that ?

Gord Thompson

This is a reproducible issue, discussed here

https://github.com/mkleehammer/pyodbc/issues/1226

As a workaround, we can use JayDeBeApi and jTDS, like so:

import jaydebeapi

cnxn = jaydebeapi.connect(
    "net.sourceforge.jtds.jdbc.Driver",
    "jdbc:jtds:sybase://192.168.0.199:5000/mydb;useLOBs=false",
    ["sa", "myPassword"],
    "/home/gord/Downloads/jtds-1.3.1.jar"
    )
crsr = cnxn.cursor()
crsr.execute("SELECT ARCH_DOC_DOC FROM so76408133 WHERE ARCH_DOC_ID = 1")
pdf_data = crsr.fetchone()[0]
with open("test_pdf", "wb") as f:
    f.write(pdf_data)

Note that this requires a Java Runtime Environment (JRE). On Ubuntu/Debian, it can be installed via

sudo apt install default-jre

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Database column data having linebreaks are ignored when retrieved from Hibernate?

Data truncated for column when multiplying numbers

Recover data from truncated partition image

Image gets rotated when retrieved from file

Short error when data is retrieved from Firebase

Flutter web: Values retrieved from Firestore map are truncated when added to List

Data truncated for column?

Data truncated for column

How to decode binary image data retrieved from mongodb in nodejs

Data not retrieved from database

Why data is truncated when importing from XML to an Access database

When is data really retrieved from database using EF Core?

Data on Firestore console differs from the retrieved when consulting the database

MYSQL: 1265 Data truncated for column?

Exact match of column data to be retrieved

Image not being retrieved from Dropbox

Why is my data being truncated? Warning | 1265 | Data truncated for column

Add custom data attributes to Semantic UI Dropdown when items are retrieved via Ajax

"Unwrapping" data retrieved from Firebase

Order of data retrieved from parse

Retrieved data from Localstorage not fresh

Enum failing == when value retrieved via reflection

SQLite: How to retrieve data from column in one table using SELECT to insert retrieved data in another table

Using data retrieved via polling in Rails view

js check if an image truncated/corrupted data

SequelizeDatabaseError Data truncated for column position at row 4

Error Code: 1265. Data truncated for column

How to solve Data truncated for column issue

SQLSTATE[01000]: Warning: 1265 Data truncated for column