FileMaker ODBC error with text fields greater than 255 characters
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.
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.
should get 255 chars of all 1s in the field.
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).
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.