4 Replies Latest reply on Nov 9, 2015 1:22 PM by MrBond

    Accessing binary field via ESS MySQL

    MrBond

      Hi,

       

      I have a FileMaker Pro 14 database hosted on a FileMaker Server 14 accessing a MySQL Database on the same machine. It works perfect for nearly all fields. Except the binary fields, those are not supported. Nevertheless, what workaround should I do to read these fields? For information: in the fields are stored only PDF and JPG files. They should be displayed in a container field in FileMaker.

       

      My first idea is to store the binary data directly as Base64 Text in a MEDIUMTEXT field in MySQL and then read this field via the Base64Decode function of Filemaker.

       

      Another idea is to store the data in a MEDIUMBLOB field and trigger a SQL action on pressing a button in FileMaker that has a TO_BASE64 in the query and stores the data as Base64 Text in a separate field to display the content.

       

      Any other ideas? What are the pros and cons of the mentioned solutions (if they work)?

       

      Best greetings from Austria,

      Bond

        • 1. Re: Accessing binary field via ESS MySQL
          beverly

          you may use a new DSN (data source name) to the MySQL in your ODBC set up.

           

          The IMPORT script step can access tables and use the SELECT statement (SQL).

           

          This note from p. 15-16 of the fm14_sql_reference.pdf may help:

          =======

          Retrieving the contents of a container field: CAST() function and GetAs() function

           

          You can retrieve binary data, file reference information, or data of a specific file type from a container field.

           

          If file data or JPEG binary data exists, the SELECT statement with GetAS(field name, 'JPEG') retrieves the data in binary form; otherwise, the SELECT statement with field name returns NULL.

           

          To retrieve file reference information from a container field, such as the file path to a file, picture, or QuickTime movie, use the CAST() function with a SELECT statement. For example:

           

                      SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data

           

          In this example, if you:

          1 Inserted a file into the container field using FileMaker Pro but stored only a reference to the file,

           

          the SELECT statement retrieves the file reference information as type SQL_VARCHAR. 1 Inserted the contents of a file into the container field using FileMaker Pro, the SELECT

           

          statement retrieves the name of the file.

          1 Imported a file into the container field from another application, the SELECT statement displays

           

          '?' (the file displays as Untitled.dat in FileMaker Pro).

           

          To retrieve data from a container field, use the GetAs() function. You may use the DEFAULT option or specify the file type. The DEFAULT option retrieves the master stream for the container without the need to explicitly define the stream type:

           

          SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data

          To retrieve an individual stream type from a container, use the GetAs() function with the file’s type

           

          based on how the data was inserted into the container field in FileMaker Pro. For example:

           

          1 If the data was inserted using the Insert > File command, specify 'FILE' in the GetAs() function. For example:

           

                      SELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data

           

          1 If the data was inserted using the Insert > Sound command (Standard sound — MAC OS X raw format), specify 'snd' in the GetAs() function. For example:

           

                      SELECT GetAs(Company_Meeting, 'snd ') FROM Company_Newsletter

           

          ========

           

          Perhaps you can import these along with a primary key that allows linking to the correct records?

           

          beverly

          • 2. Re: Accessing binary field via ESS MySQL
            actualjon

            It is true that binary (blob) fields are not supported in ESS.  As Beverly said, you can use "Import Records" (either as a script step or from the File menu) with an ODBC data source to import BLOB fields into FM container fields.  However, the documentation and syntax that Beverly references do not apply here (they are referencing the "FileMaker ODBC" driver, appropriate when you use FM as a data source for a different ODBC-aware app).

             

            With "Import Records", you should be able to just reference your BLOB field in the SELECT:

             

            SELECT my_blob FROM my_table

             

            Once the BLOB is imported into the container field, you may need to use a script to export the container to a temporary file and then insert the field into a different container field, in order for FM to recognize that the data is a JPEG (or PDF).

             

            Jonathan Monroe

            Actual Technologies - ODBC for Mac OS X

            1 of 1 people found this helpful
            • 3. Re: Accessing binary field via ESS MySQL
              beverly

              yep! Jonathan's right. I was quoting the FMP as xDBC source.

               

              beverly

              • 4. Re: Accessing binary field via ESS MySQL
                MrBond

                Thanks for your answers!

                 

                As this is a live system, the "Import Records" solution seems not the perfect one.

                I found a better solution: Saving the binary data directly as Base64 in a MEDIUMTEXT field (for up to 16MB files). Then I only have to add a calculation field in FileMaker with "Base64Decode ( Fieldname ; "Filename.pdf" )" and I have direct access to that field. In my opinion this is also the more secure solution.

                 

                The only thing that is missing is the original filename. Any ideas? Or is it better not to extract that for security reasons?

                 

                Bond