7 Replies Latest reply on Feb 1, 2011 9:18 AM by TSGal

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

    dsimpson

      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

        • 1. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
          MikhailEdoshin

          Have you tried to use the GetAs function, i.e.:

          SELECT GetAs(picture, 'JPEG') FROM `test`.`asset_management2`

          • 2. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
            dsimpson

            Mikhail,

            Thanks for your suggestion.

            Actually, yes I did test the GetAs() function and it does retrieve data for a single field, however the PDF says that this is not necessary. The PDF says: 

            =====

            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 <null>.

            =====

            Using the GetAs() function actually presents a few problems:

            1) The new ODBC implementation is intended to be SQL standards compliant, and this function doesn't conform to the SQL standard. Basically, with all other SQL database implementations, you get the contents of the field even without using a custom function. And the documentation does say that this should work too. The note above from the PDF manual seems to be saying that the JPEG preview info should be retrieved even without specifying the GetAs() function - and this is exactly what I need from the field. And this type of SELECT statement has worked well for all previous versions of FileMaker.

            2) There are issues with too many columns in many FM tables I end up having to process, which can potentially exceed the maximum SQL statement size. 

            3) And there are also issues with non-SQL compliant column names in FM database tables.

            Due to issues with items #2, #3 that is why I always use a SELECT * FROM <tablename> SQL statement when retrieving data fromFM db tables. I have to write a general purpose tool, which can handle even database tables having over 1000 columns, and many thousands of records - so I need to handle even extremely large use cases in terms of table sizes, and column counts.

            • 3. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
              MikhailEdoshin

              I agree, this indeed doesn't work as documented. I wonder how it works with the new SQL functions in the plug-in API, which now also return data in native format. Cannot check right now but when I'm on it I will certainly check this particular case.

              • 4. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
                TSGal

                dsimpson:

                Thank you for your detailed posts.

                We did switch to a new ODBC driver with FileMaker Pro/Server 11, and as you discovered, there is an inconsistency.

                I have forwarded your posts to our Development and Software Quality Assurance (Testing) departments for review and confirmation.  When I receive any information, I will let you know.

                TSGal
                FileMaker, Inc.

                • 5. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
                  TSGal

                  dsimpson:

                  Our Testing department said that this was a design change.  You can still use:

                  SELECT <Container field name> FROM <FileMaker Table>

                  ... to retrieve the text data in the container field.  However, to retrieve the binary data, use GetAs ().  Unbeknownst to me, this is mentioned on page 40 of the ODBC and JDBC Guide at:

                  http://www.filemaker.com/support/product/docs/fms/fm11_odbc_jdbc_guide_en.pdf

                  Start reading the bottom of page 40, "Retrieving the contents of a container field: CAST() function and GetAs() function", and continue through page 41.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
                    dsimpson

                    Thank you for your updated notes.

                    The problem with this "design change" is that I don't know what type of data is actually contained within the container field prior to making the SELECT call. 

                    The previous behavior was preferred, because I could always count on getting the JPEG preview image, without worrying about the data type for images (gif, PNG, JPEG). And in most of my data transfer projects, this was perfectly acceptable. Making this requirement also causes issues with large numbers of columns in the database as well as column names which are not SQL compliant. Since I write a 3rd party utility which reads this data from customer databases, I don't have complete control over the column naming.

                    • 7. Re: FileMaker 11 ODBC Connection does not retrieve Container Field JPEG Preview Data
                      TSGal

                      dsimpson:

                      I have forwarded your last post to Development and Testing for discussion.  I'll keep you posted as information becomes available to me.

                      TSGal
                      FileMaker, Inc.