11 Replies Latest reply on Dec 4, 2016 2:41 PM by m_aoto

    Any successful case of Inserting a File into FM Container Field using ODBC SQL?

    cz223

      According to the FileMaker SQL Reference, as shown below:

       

      In container fields, you can INSERT text only, unless you prepare a parameterized statement and stream the data from your application. To use binary data, you must specify the type in a PutAs() function: PutAs(col, ‘type’), where the type value is a type as described in “Retrieving the contents of a container field: CAST() function and GetAs() function” on page 40.

       

      We can insert a file as binary stream into FM Container Field using ODBC sql.

       

      Anyone who has successfully done so? It seems the whole internet, the community and even FM Priority Tech Support does not have an answer for that. And my experiment fails with ODBC driver requiring the SQL parameter for the Container Field must be of VARCHAR, TEXT type. It does not allow Binary Stream data type.

       

      Any help will be very much appreciated.

       

      Stephen

        • 1. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?

          Hey Stephen,

           

          I wrote a MySQL <---> FileMaker Sync program in Java that updates container fields with JPGs just fine. That was in FileMaker 13. As an aside, I had discovered a refresh bug with the JDBC driver that FIleMaker corrected in FMP 14.

           

          Why don't you try this with JDBC/Java to see if that works. If the JDBC doesn't work, I could help you better, but I don't personally use ODBC.

           

          Please post back.

           

          - m

          • 2. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
            cz223

            Hi Morkus,

             

            Thanks a lot for your reply. Unfortunately, I'm using VBA to develop an Outlook plugin to send Outlook email attachments into an FM Document Container. So it leaves me with only ODBC option.

             

            With your JDBC approach, do you have to set the SQL parameter type for the to-be-transferred-file to some binary or other data types, during your PrepareSqlStatement code?

             

            I'm just stuck at the SET-SQL-Parameter-Type step during PrepareSqlStatement stage. Basically, the FM ODBC driver only accepts VARCHAR or TEXT data type for the FM Container Field. If I set it to Binary Stream, it comes back with "Invalid Type". If I set it to VARCHAR, it goes successfully. But of course the file content is screwed up in the Container Field.

             

            How did you report the bug for FM JDBC driver previously? Do you have any connection to some FM engineer with deep knowledge?

             

            Thanks again for your help.

             

            Stephen

            • 3. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?

              The insert statement itself just has the expected "?" placeholders.


              The actual "insert" code references the data types, as in ...

               

              someconnection.setString(5, otherConnection.getString("Name"));

              someconnection.setString(6, otherConnection.getString("Address1"));

              someconnection.setString(7, otherConnection.getString("Address2"));

               

              .

              .

              .

              // test for image code:

              // isImage is a boolean I set by trying to get the bytes from the image. Otherwise, I set that field to null.

               

              if (isImage)

              {

                someconnection.setBlob(14, inStream, inStream.available());

              }

              else
              {

                someconnection.setBlob(14, (Blob) null);

              }

               

              Followed by an execute() command on the connection.

               

              (all surrounded by try...catch catching SQL Exception, of course).

               

              ---

               

              When I reported the bug to FileMaker, TSGal sent it off to the development team. There was no transparency. I never contacted anyone and nobody from the dev team ever contacted me. I was later informed (weeks or months, forget) that the bug was fixed in FileMaker 14. When you report bugs, they seem to go into a black hole. Not even the FM support people on this forum seem to know what is happening once they hand them off to the devs. (I had suggested they setup a FileMaker database to track this, but that's just me.)

               

              In my first reply to you, I was only suggesting a JDBC approach as a test to see if you could connect at all, not as a development approach change to consider.

               

              Hope my code above helped a little.

               

              Let me know if you have additional questions and I'll try to help.

               

              - m

              • 4. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                cz223

                Hi Morkus,

                 

                I really appreciate your effort and time in helping me. It seems JDBC accepts BLOB with no problem. But the FM ODBC driver does not seem to like non-text input for the container field. I'll try the black hole to report the bug

                 

                Thanks again

                 

                Stephen

                • 5. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?

                  Sounds good. Glad I could help you narrow things down at least a little.


                  Try to post a code sample in the report-a-problem area. TSGal or someone will let you know the status.

                   

                  Please post back with any updates.

                   

                  Good luck.

                   

                  - m

                  • 6. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                    cz223

                    Thanks a lot Morkus .  I will give it a try.

                     

                    Stephen

                    • 7. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                      wimdecorte

                      cz223 wrote:

                       

                      Hi Morkus,

                       

                      Thanks a lot for your reply. Unfortunately, I'm using VBA to develop an Outlook plugin to send Outlook email attachments into an FM Document Container. So it leaves me with only ODBC option.

                       

                       

                      There is another option: base64 encode the document and send it as text, let FM do the decoding.

                       

                      You don't even need ODBC for this.  I've written Outlook add-ins that do this and I use fmDotNet (that uses the FMS underlying XML API), that saves me from having to install ODBC drivers AND allows me to run scripts on FMS...

                      • 8. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                        cz223

                        Hi Wimdecorte,

                         

                        Wooh...base64 encode is a brilliant idea! And I actually get it work!!! Thanks a bunch for the hint.

                         

                        The only issue is that now I have to use Internal Storage Container field (store files inside FM database). Do you have any suggestions as to how to use this approach with the External Storage field as well?

                         

                        Again, thanks

                         

                        Stephen

                        • 9. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                          wimdecorte

                          cz223 wrote:

                           

                          The only issue is that now I have to use Internal Storage Container field (store files inside FM database). Do you have any suggestions as to how to use this approach with the External Storage field as well?

                           

                           

                          Shouldn't make a difference.  A container is a container is a container.  The external/remote storage is a feature of how the data is managed by the host, not a different kind of container.

                          Are you finding it does not work?

                           

                          The main difference between your approach and mine is that I don't use use ODBC but the XML API so it is easy for me to run a script and decode the base64 and insert it into a container by calling a script.  The fact that the container is stored externally does not make a difference.

                           

                          Best regards,

                          Wim

                          • 10. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                            cz223

                            Hi Wimdecorte,


                            Thanks a lot for your suggestion. As you've suggested, I have used base64 encode to successfully solve my problem. In order to help anyone who has similar issue, here is the approach:


                            I use VBA for Outlook to base64 encode the the email file attachment as string. Then it has no problem using ODBC SQL to update or insert into the FM container field.


                            For VBA, this is the base64 encode function I've used


                            Public Function encodeBase64(bytes)

                              Dim DM, EL

                              Set DM = CreateObject("Microsoft.XMLDOM")

                              ' Create temporary node with Base64 data type

                              Set EL = DM.createElement("tmp")

                              EL.DataType = "bin.base64"

                              ' Set bytes, get encoded String

                              EL.NodeTypedValue = bytes

                              encodeBase64 = EL.Text

                            End Function

                             

                            On the FM side, create a Calculation Field, which basically

                             

                            Base64Decode(YourFileContainerField, YourFileName)

                             

                            You can use the Calculation Field as normal Contain Field to download files now.

                             

                            I hope this will help anyone with similar question. And again, thanks Morkus and Wimdecorte for your Help.

                             

                            Stephen

                            • 11. Re: Any successful case of Inserting a File into FM Container Field using ODBC SQL?
                              m_aoto

                              I haven't fully confirmed yet, but It seems like binary object is treat differently in Visual Basic (each byte treat as unicode character) so can't specify a file stream as parameter. Instead of specifying file stream, I converted the file contents in Hex encoded data.

                               

                              Demo: Dropbox - ODBC_Test.zip  (Demo includes database, jpeg file, and VBscript for Hex and Base64)

                               

                              Dim conn, rs, sql

                               

                              Set conn = CreateObject("ADODB.Connection")

                              conn.ConnectionString = "Driver=FileMaker ODBC;Server=localhost;Database=ODBC_TEST;UID=Admin"

                              conn.Open

                               

                              set fso = CreateObject("Scripting.FileSystemObject")

                              CurrentDirectory = fso.GetAbsolutePathName("."): filename = "test.jpg"

                              filepath = fso.BuildPath(CurrentDirectory, filename)

                               

                              Buffer = ReadBinaryAsHex(filepath)

                               

                              Set sql = CreateObject("ADODB.Command")

                              sql.CommandText = "INSERT INTO ODBC_TEST (A,B,C,CONT2) VALUES ('C','1','2',X'"& Buffer &"' As '" & filename &  "')"

                              sql.ActiveConnection=conn

                               

                              set rs = sql.Execute

                              conn.Close

                               

                              Function ReadBinaryAsHex(fileName)

                                Dim buf(), i

                                With CreateObject("ADODB.Stream")

                                  .Mode = 3: .Type = 1: .Open: .LoadFromFile fileName

                                  ReDim buf(.Size - 1)

                                  For i = 0 to .Size - 1: buf(i) = Hex(AscB(.Read(1)))

                                      If Len(buf(i)) = 1 then buf(i) = "0" & buf(i)

                                  Next

                                  .Close

                                End With

                                ReadBinaryAsHex = Join(buf,"")

                              End Function