ranja

FileMaker ODBC driver returns corrupted text data when the length of the text exceeds 2046

Discussion created by ranja on Apr 15, 2016
Latest reply on May 9, 2017 by TSGal

Product and version: FileMaker Server 14, FileMaker ODBC driver 14.0.9

OS and version: Windows Server 2012R2, Windows 7(client)

Description:
The FileMaker ODBC driver returns corrupted text data when the length of the text exceeds 2046.

How to replicate:
I tested with the following C# code:

    using(var conn = new System.Data.Odbc.OdbcConnection("DRIVER={FileMaker ODBC};Database=MyDB;Server=fmserver14.mycompany.local;uid=WebUser;pwd=***"))
    {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Field1 FROM Table1";
                    using (var reader = cmd.ExecuteReader())
                    {
                        if(reader!= null && reader.Read())
                        {
                            var result = reader.GetString(0);   
                        }
                    }
                }
      }

Here, the table 'Table1' has a text field 'Field1'.  As long as the field contains only '1-byte characters', it works fine.
However, when the text contains Japanese characters, such as, "いろはにほへと...", the corrupted text is returned when the Length(Field1) exceeds 2046.
For example, when Length(Field1)=2047, the length of the returned text becomes 2729 and its content shows up as

result[0] to result[1363] : OK
reulst[1364] to result[2045]: null
result[2046] to result[2728]: Has data

That is, the returned text has been bloated with 682 null characters inserted between the orgiginal 1363th and 1364th characters.

When the text length is much larger and contains both 1-byte alphanumeric letters and Japanese characters, the way of corruption becomes unpredicatably complicated.

 

Workaround (if any):
You can overcome this isssure by using the following code instead of calling GetString() method:

int ordinal = 0;
string result = null;
try
{
int arraySize = 256;
long dataIndex = 0;

var outChar = new byte[arraySize];

var retval = (int)reader.GetBytes(ordinal, dataIndex, outChar, 0, arraySize);

if (retval == arraySize)//read further
{
  int bufferSize = 2048;//2K
  var buffer = new byte[bufferSize];

  do
  {
   dataIndex += retval;
   retval = (int)reader.GetBytes(ordinal, dataIndex, buffer, 0, bufferSize);
   if (retval > 0)
   {
    arraySize += retval;
    Array.Resize(ref outChar, arraySize);
    Array.Copy(buffer, 0, outChar, dataIndex, retval);
   }
  } while (retval > 0);
}
else
{
  arraySize = retval;
}

result = arraySize == 0 ? null : System.Text.Encoding.UTF8.GetString(outChar, 0, arraySize);
}
catch (Exception ex)
{}

 

(You cannot use this workaround when you are using the SqlDataSource web control.)

Outcomes