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

    Cannot insert file into External Storage Container Field using ODBC SQL

    cz223

      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.Open

          attFileStream.LoadFromFile attName

       

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

          prepStmt.Parameters.Append param

          prepStmt.Execute

       

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