7 Replies Latest reply on Jul 16, 2015 2:55 PM by TSGal

    ODBC Import Strange Occurrence

    Davesmind

      Title

      ODBC Import Strange Occurrence

      Your post

      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 the client using the 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? I have compared both the server ODBC connection and the Client and they match and both use the same account to connect.

      Attached 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.

      This is very strange behavior and since the server reports no errors and I am actually getting most of the data except for one field, I really have no clue what else can be the issue.

      Thanks for your help,

       

      odbc_script.jpg

        • 1. Re: ODBC Import Strange Occurrence
          Davesmind

          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:

           

          • 2. Re: ODBC Import Strange Occurrence
            Davesmind

            FM and SQL Query

             

            • 3. Re: ODBC Import Strange Occurrence
              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.

              • 4. Re: ODBC Import Strange Occurrence
                TSGal

                Davesmind:

                Thank you for your posts and screen shots.

                I have sent everything to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

                TSGal
                FileMaker, Inc.

                • 5. Re: ODBC Import Strange Occurrence
                  TSGal

                  Davesmind:

                  Our Development and Testing departments are aware of this issue with Informix, and that is why it is not supported.  For a complete list of supported ODBC drivers, see Knowledge Base Article #6420:

                  http://help.filemaker.com/app/answers/detail/a_id/6420

                  Continue to keep us updated with any other Informix ODBC related issues.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: ODBC Import Strange Occurrence
                    Davesmind

                    TSGal,

                    I have been using Filemaker with Informix (Filemaker 12-14) for almost 2 years. I have never had an issue, so while you don't support it, I'd say you are 99% there. The only issue i have ever come across is with the 64bit versions of Filemaker. I would say that it is totally acceptable to not support all of the specific SQL syntax that an individual database has and thus not support it. On the other hand it is not really a fair answer, by saying we don't support it, when you can't import an Integer (one of the most common data types). SQL is universal and so are Integers. I would expect Filemaker to support basic SQL for any and all ODBC supported SQL Databases as they did all of the hard work of creating the connector. The fact that it works without any issues on the 32bit end, but not the 64bit end leads me to believe that it is a simple issue for Filemaker to fix. Even Microsoft Excel can perform basic SQL queries and pull integers from Informix and I do not believe it supports it in any way.  

                    As a further update to my post - I am now using FM14Pro Adv 64bit as my main programming interface. I setup a simple report that pulls data from an Informix ODBC connector. My query contains the CAST commands within the query. I also setup 2 odbc connectors for Informix one 32bit and one 64bit with exactly the same names. After successfully performing my imports and getting the report I wanted i then changed over to FM 13 (32bit). I ran the exact same report and changed nothing. The report imported without any issue. This result can allow you to slowly transition your clients on FM13 Pro to 14Pro if you do in fact move from 32bit to 64bit.

                    • 7. Re: ODBC Import Strange Occurrence
                      TSGal

                      Davesmind:

                      Thank you for your comments.  I have forwarded them to Development and Testing.

                      See your Inbox at the top of this page for additional information.

                      TSGal
                      FileMaker, Inc.