Insert JPEG into Filemaker Container field with ODBC

[Jkhourigan]

I am trying to insert a JPEG into a container field in a Filemaker Pro Advanced 12 data base with LabVIEW 2011 with the Database Connectivity tool kit.  I have been able to retrieve and display data from the container field using SELECT GetAs (Column_Name, 'JPEG') FROM Table_Name.....This is outlined in the ODBC guide for filemaker.  So database connection and selection of binary files from containers is functional.  

The Code below works just fine using this query

SELECT GetAs (Pic, 'JPEG') FROM PhotoTest WHERE Name ='Jeremy'

However I am having trouble attemping to update or insert JPEGs into the container. I am getting a Syntax error from the SQL Query.VI when I try to UPDATE a container field with a JPEG.   Using the code shown below, the SQL Query that is generating the syntax error is:

UPDATE PhotoTest SET PutAs (Pic, 'JPEG')=?  WHERE Name ='Jeremy'

Error -2147217911 occurred at NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Update Data.vi->Untitled 4

Possible reason(s):
ADO Error: 0x80040E09
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [FileMaker][FileMaker] FQL0001/(1:22): There is an error in the syntax of the query. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Update Data.vi->Untitled 4

The error is first generated deep inside the DB Tools Execute Query VI. In some of the ADODB functions.

My first hunch is that the UPDATE Query syntax is but perhaps the data of type "string" generated by the JPEG file string VI is at fault.  The ODBC guide for filemaker says that PutAs is for uploading a binary stream into a container.  In the parameterized SQL query the value is coming through as "string" rather than "binary", but changing the data type to binary before the Parameterized query was generated did not help.

 Should I typecast the string before using PutAs()?

So I am a bit stuck.   I realize this is as much a Filemaker issue as a LabVIEW issue, but if any LabVIEW folks have any insight it would be greatly appreciated. 

[PhillipBrooks]

DELETED

[PhillipBrooks]

I poked around for a while and the ADO error 0x80040E09 seems to be most commonly caused by security settings on the DB. That might explain why you can query but not insert/update.

I also noticed that you seem to have a space between the GetAs and PutAs and your field name/type. The examples I saw for FileMaker would make your SQL statements:

SELECT GetAs(Pic, 'JPEG') FROM PhotoTest WHERE Name ='Jeremy'

UPDATE PhotoTest SET PutAs(Pic, 'JPEG')=?  WHERE Name ='Jeremy'

Maybe the space is significant in the UPDATE use case...

[Jkhourigan]

Thanks for looking into this Phillip.

The Admin account on the database has full access privileges and I can read/write to non-container fields.  I added a password to that account just in case (it's just a test database at this point) But I will enquire with FM to see if there are any other settings that might prohibit binary streaming to a container.  

The space after PutAs didn't affect the error.  

http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf

[Jkhourigan]

I also tried PutAs  '.SGI' (bitmap) and 'TIFF' (totally inefficient file format) to no avail.

[PhillipBrooks]

I would pursue this on the FileMaker forum and be sure to share all of the error information.

They won't understand the LabVIEW portion of the error, but the ADO 0x80040E09 and FQL0001/(1:22) codes should be of help.