AnsweredAssumed Answered

SQL Functions Causing 1000 character limit in selected fields

Question asked by squivo_1 on Sep 11, 2012

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.

Outcomes