AnsweredAssumed Answered

FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data

Question asked by dsimpson on Aug 21, 2010
Latest reply on Feb 1, 2011 by TSGal

Summary

FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data

Product

FileMaker Pro

Version

FileMaker Pro Advanced 11.0v2, FileMaker ODBC Driver 11.0.58

Operating system version

MacOS X 10.6.4, Windows XP Pro SP2

Description of the issue

With previous versions of FileMaker Pro (versions 5 - 10) it was possible to retrieve data from container fields via ODBC by making a simple SELECT * FROM SQL statement.

The fm11_odbc_jdbc_guide_en.pdf includes the following info on p. 40
You can retrieve binary data, file reference information, or data of a specific file type from a container field. To retrieve binary data, use a standard SELECT statement. For example:
SELECT Company_Brochures FROM Sales_Data
If file or JPEG data exists, the SELECT statement retrieves the data in binary form; otherwise, the SELECT statement returns .

Even though the ODBC driver documentation PDF states that binary data will be retrieved from the container field, the JPEG preview info is not retrieved when using the SELECT * SQL statement. With FileMaker 11, 0 bytes of data are retrieved via ODBC from container fields, and with FileMaker 10 the JPEG preview info is properly retrieved (as expected).

The FileMaker 11 ODBC implementation documents:
Specifying the column name of the container field, using GetAs(fieldname, ‘type’).
Requiring the column name to be specified can cause problems due to having large numbers of columns which could exceed the maximum SQL statement size. And many column names within FileMaker database files are not SQL compliant, thus requiring quoting.
Specifying the type of data stored within the container field.
Specifying type info for the stored data is not always possible because the type of data can change from one record to the next. So one record might contain a JPEG, another record might contain a PNG, GIF etc.

So I am submitting this issue because it doesn’t work as documented, and also because  the current implementation represents a change in behavior from previous versions of the ODBC implementation. Using the same test database file opened within FileMaker 10, works just fine and the JPEG preview image data is retrieved as expected.

Steps to reproduce the problem

Open database file having container fields with FileMaker Pro/Advanced 11.
Install FileMaker ODBC Driver 11.0.58
Create ODBC DSN to FileMaker database.
Use ODBC compatible application to retrieve data from FileMaker 11 database, using SELECT * FROM .
For this testing, I am using my own application (FmPro Migrator) to transfer the data to MySQL for testing. Other apps could potentially be used as well.

Use Navicat for MySQL to view the transferred data. (see attached screenshots).

Expected result

Use Navicat for MySQL to view the image data transferred into the MySQL Database fields. I have also transferred data into SQLite database files and observed the same results with the SQLite Manager utility.

Actual result

0 bytes retrieved from container fields

Exact text of any error message(s) that appear

None

Configuration information

listed above

Workaround

Downgrade to FileMaker Pro/Advanced 10 when retrieving container field image data. When using FileMaker Pro/Advanced 10, the JPEG preview data is retrieved properly from the container fields and the images are visible within the BLOB viewer of Navicat. Each image contains the HEX bytes JFIF near the start of the image, indicating that it consists of JPEG data.

[There is another image showing the actual data transferred and visible in Navicat, but it appears that only 1 image can be uploaded for a report.]

FM11_0_bytes_container_field_data.png

Outcomes