6 Replies Latest reply on Oct 29, 2013 1:58 PM by JonJ

    fetch data via python and ODBC

    ccban

      Title

      fetch data via python and ODBC

      Your post

           we have filemaker server 11 advanced running on a windows 7 pro box. i'm writing a program that needs to access databases VIA python and ODBC. i cant seem to get it to connect, i think i may have the name of the driver wrong.

            

           pyodbc.connect('DRIVER={FileMaker ODBC};SERVER=192.168.1.100;DATABASE=database.fp7;UID=username;PWD=password')

           this hangs for a while and then gives me the following error:
           Error: ('08S01', '[08S01] [FileMaker][FileMaker] Failed to connect to listener (2) (65535) (SQLDriverConnect)')

            

           i have double/triple checked the login info, and even tried using a different account. ive also completely turned off the firewall on the hosting computer to no avail.

           the pyodbc library basically just passes SQL commands to whatever database. im not incredibly familiar with SQL so maybe the problem is in my syntax there?

           Any help would be greatly appreciated.

        • 1. Re: fetch data via python and ODBC
          JonJ

               Hi,

               There could be several things here, as ODBC connections have several layers of 'gotchas'.

               First things first, have you enabled ODBC connections in the FM server console? Also, is the account you are connecting with use a privilege set that allows connection via ODBC (in the filemaker file, under manage security)

               If that's all good, are you using a 32bit version of Python?  FMS11 is 32 bit, and the ODBC driver is 32 bit (you may have noticed that you have to use the legacy 32 bit version of windows' ODBC data source administrator to set up the DSN properly.  I've not tried with Python, but I know that the normal 64bit version PowerShell can't connect with FM11 via ODBC, and you have to use the 32bit version instead. 

               By the way... does everything connect when you test the DSN connection in data source administrator?

               Hope this helps,

               J.

                

          • 2. Re: fetch data via python and ODBC
            JonJ

                 Also, if Python is running on a different machine than filemaker, make sure than Windows Firewall on the filemaker server is allowing port 2399 for TCP traffic!

            • 3. Re: fetch data via python and ODBC
              ccban
                   

                        Also, if Python is running on a different machine than filemaker, make sure than Windows Firewall on the filemaker server is allowing port 2399 for TCP traffic!

                    i realized in my syntax i did not enter the correct port after the IP, i was using 5003 which i think connects filemaker pro to the server. . but now i get a different error (see below). i have multiple clients both remote and local connecting to the database. 

                   if anyone else is trying to figure out the syntax for connecting to a FM database via python using pyodbc, here it is:
                    

                   pyodbc.connect('DRIVER={FileMaker ODBC};SERVER=192.168.1.100:2399;DATABASE=your-data-base-name;UID=yourusername;PWD=yourpass')

                    

                   i found that it will not connect with our a username and a password. one of our databases only had a password. i also found that if the name of your database has spaces, it will throw back an error at you.

              • 4. Re: fetch data via python and ODBC
                JonJ

                     I'm not sure I understand... is this solved? 

                • 5. Re: fetch data via python and ODBC
                  ccban

                       yes it is resolved. when filemaker pro connects to a database on FMS, it uses port '5003' because thats what the 'filemaker network' uses.

                       when you connect via ODBC, it uses port 2399 to connect to the server.

                       i was forwarding port 5003 on my router and i had just assumed that ODBC would connect via the same port. the documentation for filemaker and ODBC quite lacking.

                       if my understanding of the ports is wrong, please correct me so future readers dont go through the same mess.

                        


                       if anyone would like, here is the basic connection script in python. im using the pyodbc library and py ver 2.7.5

                       #full documentation for pyodbc at http://code.google.com/p/pyodbc/w/list
                       #this is how you connect to filemaker server 11 via ODBC.
                       #to find the table name, open the database in filemaker VIA openremote.
                       #then go: file->manage->database. in the "Table" tab you can change the table name
                       #filemakers "fields" are SQLs "columns"
                       #tables cannot have spaces in their names due to pyodbc limitations

                       import pyodbc
                       cnxn = pyodbc.connect('DRIVER={FileMaker ODBC};SERVER=192.168.1.1:2399;DATABASE=TBS3;UID=USER;PWD=PASSWORD')
                       pyodbc.connect
                       cursor = cnxn.cursor()
                       cursor.execute("select some-column from some-table")
                       row = cursor.fetchall()
                       if row:
                           print row[3]
                        

                  • 6. Re: fetch data via python and ODBC
                    JonJ

                         Great! That sounds correct about the ports--that was another 'gotcha'!