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
Set param = prepStmt.CreateParameter("fileContent", adLongVarChar, adParamInput, attFileStream.Size, attFileStream.Read)
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 '))"