8 Replies Latest reply on Feb 18, 2016 7:05 PM by user19752

    Binary type FQLx.  What for?

    ranja

      I'm writing a program that access the FileMaker Server 14 Windows version via ODBC connection.

      Suppose 'FieldBlob' is a container field.  When I execute an SQL statement

        INSERT INTO MyTalbe (FieldBlob) VALUES (?),

      the binary data is stored in the container field as a binary data of type 'FILE'.  Thus, I can recover the data with

        SELECT GetAs( FieldBlob, 'FILE') FROM MyTable WHERE ...

       

      So far, it's fine.

       

      BUT, after having made an update like

        UPDATE MyTalbe SET FieldBlob = ? WHERE ...

      the type of the binary data stored in the container field becomes 'FQLx' now!!! So I have to use the SELECT statement like

        SELECT GetAs( FieldBlob, 'FQLx') FROM MyTable WHERE ...

       

      Actually, you won't be able to tell, by looking at the record, that the data has been updated or not after the first INSERT.

      So you have to write the SELECT statement like

        SELECT GetAs( FieldBlob, 'FILE'), GetAs( FieldBlob, 'FQLx') FROM MyTable WHERE ...

      and use the second data when the first one turns out to be NULL.

       

      Is this by design?

      Why is the type 'FQLx' necessary?   Can't you go solely with the 'FILE' type?

        • 1. Re: Binary type FQLx.  What for?
          user19752

          There is a keyword for any type. From the document https://fmhelp.filemaker.com/docs/14/en/fm14_sql_reference.pdf

          SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data

           

          'FQLx' may be saying how the content is inserted, if you use FMP there are many of "Insert File", "Insert PDF" etc.

          • 2. Re: Binary type FQLx.  What for?
            ranja

            Hello, user19752.

             

            I didn't know there was the DEFAULT keyword that can be used with the GetAs function.

            Thanks a lot.

            • 3. Re: Binary type FQLx.  What for?
              user19752

              I couldn't find but there should be some way to set the type on INSERT/UPDATE.

              For example, can you view a PDF in interactive contents it is INSERTed by ODBC ?

              • 4. Re: Binary type FQLx.  What for?
                beverly

                in the fm 14 sql reference PDF?

                 

                https://community.filemaker.com/docs/DOC-5773

                 

                p. 16-17:

                 

                === blatent copy ===

                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:

                • 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

                • 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

                • If the data was inserted using the Insert > Picture command, drag and drop, or paste from the

                clipboard, specify one of the file types listed in the following table. For example:

                SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons

                === end copy ===

                 

                then there is a list of File types after the above.

                 

                beverly

                • 5. Re: Binary type FQLx.  What for?
                  user19752

                  These are only showing use of SELECT.

                   

                  I meant that, if use "Insert File" to insert a PDF file, it is shown as file icon in interactive contents, can't view pages like as "Insert PDF". So, if the file is inserted using ODBC resulting like as "Insert FQLx", what happens?

                  • 6. Re: Binary type FQLx.  What for?
                    beverly

                    Good question! I think the examples only show SELECT, but wouldn't they also be useful for UPDATE and INSERT?

                     

                    Maybe we can get someone from TS to respond on the FQL.x?

                    beverly

                    • 7. Re: Binary type FQLx.  What for?
                      ranja

                      I found that we can make use of the PutAs function.
                      I should have read the latest manual more carefully.

                        INSERT INTO MyTalbe (FieldBlob) VALUES ( PutAs(?, 'FILE'))

                        UPDATE MyTalbe SET FieldBlob =  PutAs(?, 'FILE') WHERE ...

                      To view it as a PDF, you can use 'PDF ' as the data type.

                       

                      By the way, you can convert the type of the binary data in a container field by doing the substitution with the following FileMaker calculation formula:

                      Base64Decode ( Base64Encode ( FieldBlob ); "Untitled.pdf" )

                      • 8. Re: Binary type FQLx.  What for?
                        user19752

                        Thanks ranja.

                        Now I can't find where I tried to find it... may be SetAs()?