2 Replies Latest reply on Nov 16, 2013 10:20 PM by ranja

    ODBC query results capped at 4090 characters... please help!

    HilaryOsborne

      Title

      ODBC query results capped at 4090 characters... please help!

      Post

           I am a web developer for a company which uses filemaker for pretty much everything. Due to the lack of reliability of the php api engine within filemaker server we had to move storing web data from filemaker to a mysql bridge server.. this resulted in a complicated spider web of filemaker scripts and relationships to try and keep data up to date. Since then I have been working on a symfony2 bundle which works with filemaker's ODBC functionality. I have found ODBC to be a far better performer and easier to integrate with due to the usage of SQL.

           I have hit a brick wall however when it comes to retrieving data from fields over a certain size. Originally I could not extract data larger than 255 characters however once updating the filemaker 11 driver I was able to increase this to 4090 characters. All characters over 4090 characters are cut off when trying to return them from a ODBC query. If I can get a solution to this problem it will open the doors to me using filemaker for so much here however if I can't.. it pretty much leaves all of my efforts for dead.

           To recap on what I have done so far

            

             
      1.           I have tried both filemaker 11 and 12 with the latest ODBC drivers for each
      2.      
      3.           I have ticked the treat text as long text option when setting up the ODBC driver
      4.      
      5.           Text results still return capped at 4090 characters

            

           What I need to work out is if I can return all of the characters within a field

            

      UPDATE: Possible ugly solution

           I have thought about it and in the end if no solution can be found I can create a portal connecting to a table where HTML code can be stored in 4090 character chunks. I could query this and build the final page by joining afterwards. This is a very ugly solution.. I hope there is a better way of retriving this data

        • 1. Re: ODBC query results capped at 4090 characters... please help!
          philmodjunk

               Are you importing/exporting data from ODBC or using ESS with a shadow table?

               I can find this reference in my Known Bugs List, but the original poster indicates a limit of 8192 characters instead of 4090.

               For More Information see:     External Data Source Text Field Truncated to 8192 chars when viewed in layout field.

               This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

               It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

          • 2. Re: ODBC query results capped at 4090 characters... please help!
            ranja

                 I have encountered the same issue with FileMaker ODBC driver 12.3.103a (32bit).
                 In my case, however, the text is truncated to around 1365(= 4096/3) characters
                 I think it is because the raw data is UTF-8 and the UTF-8 text is then converted to Unicode.

                 I am using C# and here is a sample code:

                 using (var conn = new System.Data.Odbc.OdbcConnection("DSN=TestDSN;UID=Admin;PWD=password"))
                 {
                  conn.Open();
                  using (var cmd = conn.CreateCommand())
                  {
                   cmd.CommandText = "SELECT Field1 FROM Table1";
                   using (var reader = cmd.ExecuteReader())
                   {
                    while (reader.Read())
                    {

                     var txt= reader.GetString(0);
                     var tl = txt.Length;//truncated to about 1365
                    }
                   }
                  }
                 }

                 What is worse in my environment, depending on the text data, the system can hang!
                 (Of course, I have set the Use Long Varchar option in the ODBC panel to YES.)

                 I am sure this is a bug of the FileMaker ODBC driver, and I hope they will fix this bug as soon as possible.