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?
1 of 1 people found this helpful
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).
Actual Technologies - ODBC for Mac OS X
yep! Jonathan's right. I was quoting the FMP as xDBC source.
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?