1 2 Previous Next 15 Replies Latest reply on Apr 18, 2016 5:08 AM by disabled_morkus

    How to query data with .NET and ODBC drivers?

    SuperJMN

      I have this question that nobody anwered:

       

      http://stackoverflow.com/questions/36497236/how-to-extract-data-from-columns-using-odbc-and-filemaker

       

      This is the original message.

       

      I'm using FileMaker ODBC in a .NET application.

      I have an IDbReader (System.Data.Odbc.OdbcDataReader) with some results. FieldCount is 8 but when I invoke this

      var isNull = reader.IsDBNull(0);

      It throws an exception.

      However, if I invoke this code:

      var str = reader.GetString(0); var isNull = reader.IsDBNull(0);

      It works! So it seems that I cannot check if a value is null before retrieving the actual value. That doesn't make sense.

      How is it supposed to work? I'm really out of ideas.

      It seems that I have to extract fields blindly before checking for null! After I have called reader.GetString() on every field (every field is string), reader.IsDBNull() works perfecly.

      The exception that is thrown is this:

      '((System.Data.Odbc.OdbcDataReader)reader).IsDBNull(0)' threw an exception of type 'System.Data.Odbc.OdbcException' Data: {System.Collections.ListDictionaryInternal} ErrorCode: -2146232009 Errors: {System.Data.Odbc.OdbcErrorCollection} HResult: -2146232009 HelpLink: null InnerException: null Message: "" Source: "" StackTrace: " en System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)\r\n en System.Data.Odbc.OdbcDataReader.GetColAttribute(Int32 iColumn, SQL_DESC v3FieldId, SQL_COLUMN v2FieldId, HANDLER handler)\r\n en System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)\r\n en System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)\r\n en System.Data.Odbc.OdbcDataReader.IsDBNull(Int32 i)" TargetSite: {Void HandleError(System.Data.Odbc.OdbcHandle, RetCode)}

      This question is also related with this issue: IDbReader throwing exceptions for existing columns

        • 1. Re: How to query data with .NET and ODBC drivers?
          taylorsharpe

          I don't know .net, but I know before moving forward, it would help to provide versions (FileMaker Server, ODBC driver, OS, .net, etc.).  From a FileMaker perspective, I'm more interested if you're using a current version of FileMaker server with matching ODBC driver and I would be testing it with some other programs that make ODBC calls even if it is just something like Excel.  At least that would give more of an indication of if the problem is at the FileMaker side or the .net side of things. 

          • 2. Re: How to query data with .NET and ODBC drivers?
            SuperJMN

            Thanks for answering, Taylor. This is all I know:

             

            The version of the ODBC driver is "13.02.14.00" that correspond to the latest drivers available on the web. I'm using the x64 version.

            Sorry, but I don't know the version of the server.

            • 3. Re: How to query data with .NET and ODBC drivers?
              taylorsharpe

              I'm assuming this is a Windows server?  Windows 2008?  2012?  Also, I checked my driver version and it is FileMaker ODBC driver version 14.0.9.  What version of FileMaker Server are you using?  The ODBC drivers to read FileMaker database data come with the server install software only.  They are in the install's extras folder. 

              • 4. Re: How to query data with .NET and ODBC drivers?
                SuperJMN

                I can confirm the server is using Windows Server 2008. I will have to check the exact FileMaker versions there because the server is owned by our customer. Excuse me, but do you mean that there may be some incompatibilites between versions?

                • 5. Re: How to query data with .NET and ODBC drivers?
                  SuperJMN

                  Sorry, but the info above is not correct:

                   

                  The server in which FileMaker Server is installed is a Windows Server 2012 R2.

                  FileMaker versions are:

                  • ODBC driver 64-bit version 13.0.2
                  • Filemaker Server is 13.0.9.905.
                  • 6. Re: How to query data with .NET and ODBC drivers?
                    taylorsharpe

                    As a test, can you try Microsoft Excel ODBC connection to your FileMaker Server and try a WHERE statement that searches for nulls?  If it fails then it looks like a problem with the FileMaker SQL or ODBC driver.  If it succeeds, then there is an issue at your .Net application. 

                     

                    FYI, FileMaker Server 14 is the current version.  However, I've not noticed really any changes from the performance and how the 14 ODBC driver works as compared to the 13.  But you can always download a trial version of 14 and test it.  I don't think you'll see a difference, but you never know. 

                    • 7. Re: How to query data with .NET and ODBC drivers?
                      SuperJMN

                      Thanks for you answer, Taylor.

                       

                      Can you please, explain how to perform the test? I have Excel 2013 installed, but I don't know how to do that.

                       

                      I really want to know how to solve this issue, since it's forcing us to wrap every reader.GetString(n) call inside a try-catch block, something that is inadmisible in any kind of application.

                      • 8. Re: How to query data with .NET and ODBC drivers?
                        taylorsharpe

                        You have to have the FileMaker Server set up to allow ODBC access and use the FileMaker ODBC driver to set up a DSN (Data Set Name) on the computer with Excel using the ODBC Data Source Administrator.  You can get the FileMaker ODBC drive in the FileMaker Server setup in the Extras folder.  Set up the DSN via the IP and FileMaker driver to point to the database you want to use ODBC to connect to.  Then any program on your computer, including Excel, that can make an ODBC connection can use that DSN to connect to the FileMaker server and read data out of the tables with SQL calls. Obviously you will need your FileMaker credentials, but it works pretty well.  And you can use ODBC (or JDBC) to connect to all kinds of sources.  Regardless, the purpose of your connecting is to see if your SQL call in Excel works or fails like happened in your .NET app. 

                        • 9. Re: How to query data with .NET and ODBC drivers?
                          ranja

                          The FileMaker ODBC driver v13 for Windows has some serious bugs.

                          Use v14.0.9, which is the latest version.

                          Though it still has a serious bug, it will work in your case.

                           

                          I tested with v14, but "reader.IsDBNull(0)" did not throw an exception.

                          • 10. Re: How to query data with .NET and ODBC drivers?

                            If you can't get the ODBC driver working right away, why not try a quick Java program (extremely similar to C#) as a test? The problem you're describing should not be happening, obviously.

                             

                            JDBC is super nice since it doesn't require much setup, just a user name and password. Very simple setup.

                             

                            From my experience the JDBC driver is quite solid. There was a serious bug I discovered in version 13, but FM fixed it in the current version.

                             

                            ---

                             

                            Alternatively: Report your issue to FileMaker since this could be, as ranja said, a bug.

                             

                            ---

                             

                            Hope this helps.

                             

                            -m

                            • 11. Re: How to query data with .NET and ODBC drivers?
                              SuperJMN

                              I'm already asking in a support chat, but they seem to be even more lost than me :S I updated to the ODBC drivers v14 (x64) and I noticed that the exception that is thrown when I call reader.IsDBNull(...) has changed. Now it says this:

                               

                              'reader.IsDBNull(0)' threw an exception of type 'System.OverflowException'

                                  Data: {System.Collections.ListDictionaryInternal}

                                  HResult: -2146233066

                                  HelpLink: null

                                  InnerException: null

                                  Message: "Arithmetic operation resulted in an overflow."

                                  Source: "System.Data"

                                  StackTrace: "   in System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)\r\n   in System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)\r\n   in System.Data.Odbc.OdbcDataReader.IsDBNull(Int32 i)"

                                  TargetSite: {System.Data.Odbc.TypeMap GetSqlType(Int32)}

                              • 12. Re: How to query data with .NET and ODBC drivers?

                                Did you try JDBC?  I've never gotten messages like these.

                                 

                                ----

                                 

                                If you don't want to do that, perhaps you can post a problem report with FM to see if they can reproduce the problem.

                                 

                                - m

                                • 13. Re: How to query data with .NET and ODBC drivers?
                                  SuperJMN

                                  No, I haven't. Sorry. How could I test with them? I could install JDK 8, and execute some JAVA code if you tell me what to execute, but I haven't used JAVA in years...!

                                  • 14. Re: How to query data with .NET and ODBC drivers?

                                    Hey,

                                     

                                    I would recommend downloading an IDE like Eclipse. It's free and perfect for this kind of testing.

                                     

                                    Eclipse Downloads

                                     

                                    Then, get the JDBC driver that comes free with FileMaker (with the distribution you get when you buy it) so you can connect to SQL Server or whatever your destination database is. Put the JDBC driver (just the jar file) into the "External Jar" section of Eclipse (aka "The classpath") so the java code can find it. The "build path" is where this generally is. Let me know if you have trouble with this and I'll download a fresh version of Eclipse and help you (I use Intellij IDEA - which you could use too free for 30 days or use the community edition).

                                     

                                    For JDBC connections, you only need to configure a user name, password, and a JDBC URL to connect. The JDBC URL is simple but depends on the database you're connecting to.

                                     

                                    The URL I posted below has an example JDBC connection for a 'coffeebreak" mysql database. Here's the representative JDBC URL:

                                     

                                    String url = "jdbc:mysql://localhost/coffeebreak";

                                     

                                    (Very simple, but you could substitute a URL for "localhost" if remote.)

                                     

                                    I saw this link doing a quick Duck search: A Simple JDBC Example

                                     

                                    ----

                                     

                                    I would put a break point at the "conn = DriverManager.getConnection(url, "username", "password");" line of code and make sure you can connect. If that works, I'm betting you can do some testing. You might not want to do anything but a SELECT test, but the example above shows the other SQL operations just in case (more of the CRUD pie).

                                     

                                    Give this a try and let me know how it goes. I'll be glad to help as much as I can without having direct access to anything.

                                     

                                    - m

                                    1 2 Previous Next