AnsweredAssumed Answered

Updating container (binary blob) to FileMaker via pyodbc

Question asked by macfreek on Apr 30, 2018
Latest reply on Apr 30, 2018 by macfreek

I want to UPDATE an image in a local FileMaker database using the ODBC connection.

I expected that the following Python code would work, but it fails:

import pyodbc
with open('myimage.jpg', 'rb') as f:
  img = f.read()
# ensure we have a valid image
assert isinstance(img, bytes)
assert len(img) == 10506

dsn
= 'DRIVER=/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so;Server=127.0.0.1;Port=2399;Database=MyDatabase;UID=Admin;PWD='
cnxn = pyodbc.connect(dsn)
cnxn.setencoding(encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='macroman')

cursor
= cnxn.cursor()
q = "UPDATE MyTable SET PutAs(Image, 'JPEG')=? WHERE Id=?"
cursor.execute(q, img, 37370)

It raises an exception: ProgrammingError: '42000', '[42000] [FileMaker][FileMaker] FQL0001/(1:18): There is an error in the syntax of the query. (8310) (SQLPrepare)'.

Reading an images with SELECT GetAs(Image, 'JPEG') FROM MyTable WHERE Id=37370) works fine. I receive a bytes objects which is the image.

I received syntax errors before, which were caused by not setting the encoding correct (github.com/mkleehammer/pyodbc/issues/89). Could something similar be the case?

Does FileMaker support updating containers via ODBC? If not, are there programmatic alternatives from outside FileMaker?

Outcomes