AnsweredAssumed Answered

Cannot insert file into External Storage Container Field using ODBC SQL

Question asked by cz223 on Mar 2, 2016

I am developing an Outlook VBA program to insert Outlook attachment files into a FileMaker 14 Document Container field with External Storage option. I'm using ODBC driver v14.01.


All the other code works. But it seems the FM Container Field (external storage option) only likes TEXT (VARCHAR) data type. It does not allow binary stream, which means I cannot use ODBC SQL to add a file to the Container Field:


Referring to the following code, if I change the SQL parameter type for the file stream to adLongVarChar or adVarChar, the insert sql command completes successfully. And all the to-be-uploaded PDF is saved directly as some blahblah code inside the Container Field. Not as an external file as it should be. And in FM, you cannot download the PDF from the Container Field.


If I change the SQL parameter type to adBinary or any other types, the FM ODBC driver will come back with "Invalid Type" error during execution.


Has anyone successfully insert a file into FM Container field (External Storage option) using ODBC SQL? What's the trick to get it work?


I've searched through Internet, FM SQL Reference, FM ODBC/JDBC Guide etc. and find no successful case or example, although FM SQL Reference indicates that it can be done. Your help is very much appreciated.

The following is a section of the VBA code:


  # attName is the attachment file name

  # connDB is ADODB.Connection


    Dim prepStmt As New ADODB.Command

    Dim param As New ADODB.Parameter

    Dim attFileStream As New ADODB.Stream


    Set prepStmt.ActiveConnection = connDB

    prepStmt.CommandText = "INSERT INTO ForeclosureDocument (""_fk_ForeclosureID"", fcDocumentName, fcDocumentContainer) VALUES (" & foreclosureID & ", 'Test Doc', ? AS '" & attName & "')"

    prepStmt.CommandType = adCmdText

    prepStmt.Prepared = True


    attFileStream.Type = adTypeBinary


    attFileStream.LoadFromFile attName


    Set param = prepStmt.CreateParameter("fileContent", adLongVarChar, adParamInput, attFileStream.Size, attFileStream.Read)

    prepStmt.Parameters.Append param



I've also tried the putAs option. It works but with the same problem as described above. The SQL Insert go as


    prepStmt.CommandText = "INSERT INTO ForeclosureDocument (""_fk_ForeclosureID"", fcDocumentName, fcDocumentContainer) VALUES (" & foreclosureID & ", 'Test Doc', putAs(?, 'PDF '))"