0 Replies Latest reply on Sep 11, 2012 8:42 AM by squivo_1

    SQL Functions Causing 1000 character limit in selected fields

    squivo_1

      Summary

      SQL Functions Causing 1000 character limit in selected fields

      Product

      FileMaker Pro

      Version

      11 Advanced

      Operating system version

      OSX Mountain Lion

      Description of the issue

      It appears to me that if do a JOIN, or use any of the SQL Functions inside a select statement,  there seems to be a 1000 character limit for the fields being returned. Fields containing more than 1000 chars come back as obscure text characters.

      Steps to reproduce the problem

      CASE 1:

      ExecuteSQL(
      "select "
      & "A." & FieldNameFor( TableA::Field1 ) &
      ", A." & FieldNameFor( TableA::Field2 ) &
      " from "
      & TableNameFor( TableA ) & " A "
      & " join " & TableNameFor(  TableB ) & " B "
      & "on B." & FieldNameFor( TableB::Field1 ) & " = A." & FieldNameFor( TableA::Field3 ) &
      " where A." & FieldNameFor( TableA::Field1 ) & " = '" & $condition & "' and B." & FieldNameFor( TableB::Field4 ) & " <> '" & $condition2 & "' "
      )

      where TableA::Field1 or TableA::Field2 contain 1000+ plus chars


      CASE 2:

              ExecuteSQL(
              "select distinct "
              & " Coalesce( Cast(" & FieldNameFor ( TableA::Field1 ) & " as varchar(1) ), 'false' ) ,"
              & FieldNameFor ( TableA::Field2 ) & ","
              & FieldNameFor ( TableA::Field3 ) & ","
              " from "
              & TableNameFor ( TableA::FieldDummy ) &
              " where "
              & FieldNameFor ( TableA::Field4 ) & " in ( " & $condition & " )"
              )

      where Field1 is Number (1 or 0 ) or null, Field2 is text, Field3 has 1000+ chars

      Expected result

      CASE 1:

      TableA::Field1 or TableA::Field2 contain 1000+ plus chars == I expect to see the actual contents of the field.

      CASE 2:

      true/false,text,text

      Actual result

      CASE 1:
      Wacky Obscure Text

      CASE 2:
      true/false, text, Wacky Obscure Text

      Exact text of any error message(s) that appear

      The Wacky characters may or may not change everytime the query is run.

      Configuration information

      FieldNameFor(  )
      returns the Second Word after the '::' FieldNameFor( TableA::Field1 ) returns = Field1

      TableNameFor(  )
      returns the First Word before the '::' TableNameFor( TableA::Field1 ) returns = TableA


      Let([
      a = GetFieldName( field );
      b = Substitute( a ; "::" ; ¶ )
      ];
           GetValue (b ; 1 )
      )

      Workaround

      CASE 1: remove Join - Use sub query

      ExecuteSQL(
      "select "
      & "A." & FieldNameFor( TableA::Field1 ) &
      ", A." & FieldNameFor( TableA::Field2 ) &
      " from "
      & TableNameFor( TableA ) & " A "
      & " where A." & FieldNameFor( TableA::Field1 ) & " = '" & $condition & "' "
      & " and A." & FieldNameFor( TableA::Field3 ) & " in ( '" &

      Substitute( ExecuteSQL("select B." & FieldNameFor( TableB::Field1 ) & " from " & TableNameFor( TableB ) & " B "
      & " where B." & FieldNameFor( TableB::Field4 ) & " <> '" & $condition2 & "' " ) ; ¶ ; "','" ) & "')"
      )



      CASE 2: remove CASE and COALESCE functions

      ExecuteSQL(
              "select distinct "
              & FieldNameFor ( TableA::Field2 ) & ","
              & FieldNameFor ( TableA::Field3 ) & ","
              " from "
              & TableNameFor ( TableA::FieldDummy ) &
              " where "
              & FieldNameFor ( TableA::Field4 ) & " in ( " & $condition & " )"
              )

      CASE 1 + 2:  For all Text fields containing 1000+ chars, reduce them down to 999 chars.  Then the queries work fine.