Does your text field in FileMaker contain just ASCII text, or are formatting characters present?
The Substitute and TextFormatRemove functions might help you.
The fields contain alpha letters, numbers and dashes. Can you give me an example of what you mean by a "formatting character"?
There are no Filemaker text formating functions involved, the fields are just populated by users during data entry.
What do you mean by "blob"? In many SQL databases, "blob" means a binary file which is analagous to a container field in FileMaker. Blobs are not supported by FileMaker unfortunately, which is making document management via SQL tables inside FileMaker hard to do. Anyway, when you say "blob", are you just saying a bunch of unrecognized text or is it an ASCII representation of hex data or what is happening? If you are quering against a FM 10 database, then are you asking for container fields in FileMaker? What is the ODBC driver you are using. What program are you using ODBC to access FileMaker? ODBC drivers have been updated a lot since version 10. Is there any reason you are not wanting to use FileMaker 12?
I will find out more about what exactly is being returned, but I know that the fields being queried are text fields, not containers. We are not having an issue returning data that are in FM date or number fields.
We are using Cognos to querry FM via the ODBC driver that comes with FMSA 10. I have a rather large system and have a lot of testing to perform before upgrading to FM 12. Even on FMSA10, I would think returning text data should not be a difficult tasks for the ODBC driver.
In FIleMaker text fields, a user may select some of the text and apply a font, a font size, a font color, italics, bold, etc. These characters may cause your query to interpret the data as a Binary Large OBject, or BLOB - the TextFormatRemove function in FileMaker can remove these formatting characters embedded within the text field.
Thank you, I was not aware that user formatting could interfere. I will make new calculated fields removing any possible formatting.
No worries Evan - it is an issue I have had to deal with before - Good Luck!
OK let's recap, Evan:
1. FMS is ?? and driver is ??
You said, "ODBC drivers for Windows that come with FM Server 10, and querrying against a FMSA 10 on OSX".
a. the database(s) on FMSA10 on OSX (what verison of OSX and what v rev of FMSA10?), is that correct?
b. the ODBC driver for Windows (from FMS10), I'm guessing is on the "Cognos" machine to set up the ODBC connection to FMS. can you confirm?
2. you've set up "maximum length needed" on the database fields, so that VARCHAR data can be returned.
a. when you make the query from Cognos, is there a way to specify the length? and is that needed?
b. is there another way to make the connection from Cognos (some way to set up the ODBC within that applicaiton) that might make a difference?
3. can you supply an example or two of the actual QUERY(IES) from Cognos to FMS? Obsure anything that might be sensitive, but that might help answer your question.
I am having problems with reading a CALCULATED TEXT field via ODBC. I found this link:
that explaines the problem for TEXT fields but the solution it suggests does not work with CALCULATED TEXT fields!