ODBC Import Strange Occurrence

[Davesmind]

I am building a simple ODBC Import to report mechanism. In building the connection and pulling the data everything works. I ran the scheduled script on the server and for some unknown reason a specific field is not being pulled. I even ran the script via perform on server and I get the same missing field. I can't imaging how the server and the client can both pull data, but the result is different. Any one have an idea as to what is happening? 

Below is an example of both the client and server side scripts. The import script is exactly the same script run under both instances except, for the server side, I added a "Perform on Server" to trigger the script. As you can see the doc_no does not populate when importing on the server. Yet it still pulls data without issue as no errors are reported on the server.

Client Side

Server Side

Edited June 12, 2015 by BlueSpud
Updated

[Wim Decorte]

Any validation on that field?  Any errors logged or reported?

[Davesmind]

There are no validations within this table. This is a simple table created just to pull this data. I matched the sql database (Informix) exactly. I even checked the fields on the Informix side just to make sure that the doc_no field type matches filemaker. It is an integer field so their should be no issues. 

No Errors, that is the crazy part of this issue. If i run the import on the client, there are no issues and the doc_no is populated. If I run the import on the server only that field is not populated. I even changed doc_no to a text field and again was able to import via the client and not via the server.

 

The only direction I can think to look is that the FM Server is 64bt (FMS13) while the client is 32bit. I am using two different ODBC drivers and getting two different results. It just makes no sense that both drivers pull the same data except for that field.  

[Davesmind]

So, I tracked down the issue to the versions of Filemaker that are 64Bit Applications. I am now using Filemaker 14 Pro Advanced 64Bit and am getting the same issue as the FM 13 Server. It appears that Filemaker is not able to pull an integer from an Informix database. This is not an issue with the ODBC driver itself as I have a 64 Bit SQL query tool using the same ODBC driver and I can run exactly the same query. I am also able to pull any other data from Informix via the ODBC connector into Filemaker without issue. This issue also does not affect Filemaker applications running as 32 Bit as all 32 Bit ODBC connectors return the correct information. This is becoming a real drag on my development. If I can't get this to work I may have to abandon Filemaker altogether or find an alternative method to pull data from our Informix system. I am very frustrated at the moment as this prevents me from running anything on the server via FM 13 or 14. I cannot continue developing everything on the client side as this hinders me in many other ways. Has anyone ever come across an issue like this?

Here is my table data on Informix:

Here is the FM data after import: 

Here is the same data via an SQL Query tool:

[Davesmind]

UPDATE AND POSSIBLE SOLUTION/WORKAROUND

It is obvious that there is something wrong with the 64 Bit version of Filemaker and pulling in data via a 64 Bit ODBC connector from a Informix database system. I've attempted different ways to pull in the data and concluded that a Integer field appears to be the issue. Again i have had no issue with any version of Filemaker that is 32 bit. I also have no issue using other applications that pull data through the same 64bit Odbc connector. I also have not noticed the same issue with other field types such as SMALLINT.

SOLUTION/WORKAROUND

Since i cannot wait for Filemaker to fix the issue or doubt they will fix an issue for one particular database there is a workaround.

SELECT CAST ( fieldname as CHAR(10)) as fieldname FROM mydatabase

If you run into this issue try using the CAST statement. I'm sure you can use other data types. In my case i had no issue importing the Integer cast as a char into a Filemaker number field. I'm sure that the main interest is to maintain the datatype as much as possible. At least now I have the data in the Filemaker side and now can work with it.