0 Replies Latest reply on Nov 13, 2012 2:41 PM by mtolman_1

    FileMaker ODBC error with text fields greater than 255 characters

    mtolman_1

      Summary

      FileMaker ODBC error with text fields greater than 255 characters

      Product

      FileMaker Server

      Version

      12v2

      Operating system version

      Windows

      Description of the issue

      Using FileMaker Server's built in ODBC driver if you do a SQL query to grab a field in a database that is more than 255 characters you get the first 255 characters of the field, and then after that you get garbled data for the remaining length of the field.   If there are more than 10,000 characters in the field that you are grabbing you get a ODBC error and the whole query stops processing.

      Important Note: this only occurs when the checkbox for ODBC system driver has the convert text fields to long VarChar option unchecked.

      Steps to reproduce the problem

      make a filemaker database with one table and one field.  Put 1000 chars in that field.  For example set the field to 11111111111...  (1000 of these).  Also repeat this with 100,000 chars in the field.  Then use a SQL query to grab the contents of the field using FileMaker's ODBC driver.

      Expected result

      should get 255 chars of all 1s in the field.

      Actual result

      You get 255 1s followed by a lot of extraneous info that looks like data from the buffer or a memory overflow.  With the second test with the larger field contents you get a ODBC error (500).

      Workaround

      If you check the box in the ODBC driver for converting text to long VarChar then it handles things beautifully.  It truncates the data to 4096 chars, but even if you give it far more chars than that it nicely handles it without returning garbled data or causing things to crash.  The bug only exists when that checkbox is unchecked in FileMaker's ODBC driver and you are doing a query against a field that has more than 255 characters.