Skip to content

Instantly share code, notes, and snippets.

@dpwrussell
Last active November 30, 2020 20:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dpwrussell/2570a2858c600253e08b2c827bd71857 to your computer and use it in GitHub Desktop.
Save dpwrussell/2570a2858c600253e08b2c827bd71857 to your computer and use it in GitHub Desktop.
FileMaker JDBC From Python
*.class
*.pyc
fmjdbc.jar

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.

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);
}
}
}
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]
Cython (0.24.1)
jnius (1.1.dev0)
pip (8.1.2)
setuptools (15.0)
six (1.10.0)
@csmu-cenr
Copy link

Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment