0 Replies Latest reply on Mar 4, 2016 10:01 AM by cz223

    Cannot insert file into FM Container Field using ODBC SQL

    cz223

      Product and version: FileMaker Pro 14.0.4 Adv database hosted on FileMaker Server 14.04.412. FM ODBC Driver v14.01

       

      OS and version: Client machine Windows 7 Home Premium. FileMaker Server hosted on Windows Server 2008 R2 SP1

       

      According to the FileMaker SQL Reference, Chapter 2, as shown below:

       

      In container fields, you can INSERT text only, unless you prepare a parameterized statement and stream the data from your application. To use binary data, you must specify the type in a PutAs() function: PutAs(col, ‘type’), where the type value is a type as described in “Retrieving the contents of a container field: CAST() function and GetAs() function” on page 40.

       

      We should be able to insert a file as binary stream into FM Container Field using ODBC SQL.


      However, when we use binary stream to insert a file into FM container field using SQL INSERT or UPDATE through FM ODBC driver (prepare parameterized sql statement and pass in binary stream), the ODBC driver always comes back with "Invalid Parameter Type" error.


      If we set the sql pararmeter type to VARCHAR, the SQL INSERT and UPDATE completes successfully. But of course the file content is screwed up in the Container Field.

       

      We suspect that the FM ODBC driver enforces any FM Container Field to be of VARCHAR/TEXT/STRING type, which prevents Insert or Update of binary stream into the FM container field. However, this is a very much needed feature to upload file into FM system by other third party applications (FM does not provide other API interfaces)

       

      Through discussion on Internet, it seems JDBC driver is without this problem. But throughout the whole FM community and internet, we've found no one has successfully done it in ODBC.

       

      The following two links are our posts in the FM forum concerning the issue:

       

      Cannot insert file into External Storage Container Field using ODBC SQL

      Any successful case of Inserting a File into FM Container Field using ODBC SQL?

       

      Thanks in advance in looking into this urgent issue.

       

      Stephen