Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

SQL.GetFieldAsContainer

Queries field value as container.

Component Version macOS Windows Linux Server iOS SDK
SQL 6.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "SQL.GetFieldAsContainer"; Command; Field; FileName )   More

Parameters

Parameter Description Example
Command The command reference number gained with SQL.NewCommand. $Command
Field The name or one based index of the field. $Field
FileName The filename for the container.
This will determinate the type of data to FileMaker.		 "test.jpg"

Result

Returns container value or error.

Description

Queries field value as container.
If you can provide a file name, we can use it to auto detect the type of data and import it in the right format, e.g. add a preview for PDF or the image size for JPEG.

Examples

Query image field value:

MBS( "SQL.GetFieldAsContainer"; $Command; "MyBlob"; $FileName )

Extract BLOBs from a Oracle database and store in files:

Extract BLOBs from a Oracle database and store in files:

Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption";$Connection;"OCI8.LIBS";"/Users/test/Downloads/instantclient_19_8/libclntsh.dylib") ]

Set Variable [ $result ; Value: MBS("SQL.Connect";$Connection;"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.128.12.150)(PORT=1521))(CONNECT_DATA=(SID=STRMTL)))";"User”;"Password";"Oracle") ]
If [ MBS("ISError") ]
    Show Custom Dialog [ "Failed to connect"; $result ]
Else
    Set Variable [ $sql ; Value: "SELECT FILE_NAME, FILE_DATA, FILE_DESCRIPTION FROM UPLOADED_FILE OFFSET 1452000 ROWS FETCH NEXT 10 ROWS ONLY" ]
    Set Variable [ $cmd ; Value: MBS("SQL.NewCommand"; $Connection; $sql) ]
    If [ MBS("ISError") ]
        Show Custom Dialog [ "Failed to parse SQL"; $cmd ]
    Else
        Set Variable [ $r ; Value: MBS("SQL.Execute"; $cmd) ]
        If [ MBS("ISError") ]
            Show Custom Dialog [ "Failed to run query"; $r ]
        Else
            Set Variable [ $folderPath ; Value: "/Users/test/Desktop/PDF/folder" ]
            #
            Set Variable [ $r ; Value: MBS("SQL.FetchNext"; $cmd) ]
            If [ MBS("ISError") ]
                # Failed to get record?
                Show Custom Dialog [ $r ]
            Else If [ $r = 0 ]
                # no records found
            Else
                Loop
                    # get file name
                    Set Variable [ $filename ; Value: MBS("SQL.GetFieldAsText"; $cmd; "FILE_NAME") ]
                    # get as BLOB with PDF
                    Set Variable [ $compressed ; Value: MBS("SQL.GetFieldAsContainer"; $cmd; "FILE_DATA"; $fileName) ]
                    #
                    # store in field
                    // New Record/Request
                    // Set Field [ SQL_API::PDF ; $PDF ]
                    // Commit Records/Requests [ With dialog: On ]
                    #
                    # write to file
                    Set Variable [ $filePath ; Value: MBS("Path.AddPathComponent"; $folderPath; $filename) ]
                    Set Variable [ $r ; Value: MBS("Container.WriteFile"; $PDF; $filePath) ]
                    #
                    # loop ends after last field
                    Set Variable [ $r ; Value: MBS("SQL.FetchNext"; $cmd) ]
                    Exit Loop If [ $r ≠ 1 ]
                End Loop
            End If
        End If
        Set Variable [ $r ; Value: MBS("SQL.FreeCommand"; $cmd) ]
    End If
End If
Set Variable [ $r ; Value: MBS("SQL.FreeConnection"; $Connection) ]

See also

Blog Entries

This function is free to use.

Created 17th May 2016, last changed 9th May 2022


SQL.GetFieldAsBoolean - SQL.GetFieldAsDate