0 Replies Latest reply on Mar 2, 2016 1:52 PM by cz223

    Cannot insert file into External Storage Container Field using ODBC SQL


      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 '))"