FileMaker JDBC From Python

.gitignore

*.class
*.pyc
fmjdbc.jar

README.md

Instructions

Download the FileMaker Pro 14 xDBC package for Mac and copy the file fmjdbc.jar to the project directory.

Java Example

Edit FilemakerJDBCTest.java and enter credentials.

javac FilemakerJDBCTest.java
CLASSPATH=fmjdbc.jar:. java FilemakerJDBCTest

Python Example

Edit fmjdbc.py and enter credentials.

pip install -r requirements.txt
CLASSPATH=fmjdbc.jar:. python fmjdbc.py

In the Python example I get the column name and column type for each column from the metadata and this is stored in an array of tuples.

FilemakerJDBCTest.java

import java.sql.*;
import java.util.ArrayList;

class FilemakerJDBCTest {
	public static void main(String[ ] args) {

		String fmsHostname = "fmprod13.med.harvard.edu";
		String fmFilename = "HITS_Reagent_Tracker";
		String fmUser = "";
		String fmPassword = "";

		// register the JDBC client driver
		try {
			Driver d = (Driver)Class.forName("com.filemaker.jdbc.Driver").newInstance();
		} catch(Exception e) {
			System.out.println(e);
		}

		// establish a connection to FileMaker
		Connection con = null;

		try {
			con = DriverManager.getConnection("jdbc:filemaker://" + fmsHostname + "/" + fmFilename, fmUser, fmPassword);
		} catch(Exception e) {
			System.out.println(e);
		}

    Statement stmt = null;
    String query = "SELECT * FROM Inventory";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);

      // Get all the column names
      ResultSetMetaData rsm = rs.getMetaData();
      ArrayList<String> columnNames = new ArrayList<String>();
      for(int i=1; i<rsm.getColumnCount(); i++){
          columnNames.add(rsm.getColumnName(i));
      }

      while (rs.next()) {
        String output = "";
        for (String columnName : columnNames) {
          output += rs.getString(columnName) + ", ";
        }
        System.out.println(output + "\n");
      }

    } catch (Exception e ) {
      System.out.println(e);
    }

	}
}

fmjdbc.py

from jnius import autoclass

DriverManager = autoclass('java.sql.DriverManager')
JDBCDriver = autoclass('com.filemaker.jdbc.Driver')
JDBCType = autoclass('java.sql.JDBCType')

fmsHostname = 'fmprod13.med.harvard.edu'
fmFilename = 'HITS_Reagent_Tracker'
fmUser = ''
fmPassword = ''

d = JDBCDriver()

con = DriverManager.getConnection(
    'jdbc:filemaker://' + fmsHostname + '/' + fmFilename, fmUser, fmPassword
)

query = 'SELECT * FROM Inventory'

stmt = con.createStatement()
rs = stmt.executeQuery(query)

# Get all the column names
rsm = rs.getMetaData()

columnDetails = [
    (rsm.getColumnName(i), JDBCType.valueOf(rsm.getColumnType(i)).getName())
    for i
    in xrange(1, rsm.getColumnCount())
]

print columnDetails

while rs.next():
    print [rs.getString(columnDetail[0]) for columnDetail in columnDetails]

requirements.txt

Cython (0.24.1)
jnius (1.1.dev0)
pip (8.1.2)
setuptools (15.0)
six (1.10.0)

Thank you.