I have been using pyodbc to connect with a local Access database (.accdb file), but have been unable to update a Long Text field, if the string I am trying to input has more than 255 characters. The data type of the field (Description) is Long Text, and the Text Format is Rich Text.
The code:
import pyodbc
pyodbc.pooling = False
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:\...\...\...\...\database.accdb;'
)
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
temp_desc = complex_descricao(comp_idx, "T_Compound", crsr, cnxn) #Function produces large string (> 255 characters)
#temp_desc = temp_desc[:255] # Filter I have used to test the maximum number of characters allowed
crsr.execute("UPDATE T_Compound SET Description = ? WHERE ID_Comp = ?", temp_desc, comp_idx)
cnxn.commit()
This produces the following error:
Error: ('HY104', '[HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid precision value (98) (SQLBindParameter)')
I have tried to change the field properties directly in the database. I have also tried to insert a string with more than 255 characters directly in Acces, and was able to do so.
I have searched for ways of fixing the problems, and I think it has something to do with binding parameters, although I do not have much knowledge on the topic. I leave here a link I believe it may have something to do with the issue: https://github.com/mkleehammer/pyodbc/wiki/Binding-Parameters
Any help in making it possible to update the field with large strings would be appreciated.
You have encountered a regression in pyodbc 4.0.22. It is under investigation (multiple GitHub issues here) and should be fixed in a future release of pyodbc. In the meantime, simply revert to pyodbc 4.0.21:
pip install pyodbc==4.0.21