Using JPype - How can I access JDBC Meta Data Functions

Question

I'm using JayDeBeAPI which uses JPype to load FileMaker's JDBC driver and pull data.

But I also want to be able to get a listing of all tables in the database.

In the JDBC documentation (page 55) it lists the following functions:

The JDBC client driver supports the following Meta Data functions:

getColumns

getColumnPrivileges

getMetaData

getTypeInfo

getTables

getTableTypes

Any ideas how I might call them from JPype or JayDeBeAPI?

If it helps, here's my current code:

import jaydebeapi
import jpype

jar = r'/opt/drivers/fmjdbc.jar'
args='-Djava.class.path=%s' % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)

conn = jaydebeapi.connect('com.filemaker.jdbc.Driver',
        SETTINGS['SOURCE_URL'], SETTINGS['SOURCE_UID'], SETTINGS['SOURCE_PW'])
curs = conn.cursor()

#Sample Query:
curs.execute("select * from table")
result_rows = curs.fetchall()

Update:

Here's some progress and it seems like it should work, but I'm getting the error below. Any ideas?

> conn.jconn.metadata.getTables()
*** RuntimeError: No matching overloads found. at src/native/common/jp_method.cpp:121

I can't test right now, but I think that the call should match the Java getTables method signature, so what about: `conn.jconn.getMetadata().getTables(None, None, "%", None)` — Juan Mellado, Jan 15 '14 at 08:20

score 5 · Accepted Answer · answered Jan 15 '14 at 14:44

5

Ok, thanks to eltabo and Juan Mellado I figured it out!

I just had to pass in the correct parameters to match the method signature.

Here's the working code:

import jaydebeapi
import jpype

jar = r'/opt/drivers/fmjdbc.jar'
args='-Djava.class.path=%s' % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)

conn = jaydebeapi.connect('com.filemaker.jdbc.Driver',
        SETTINGS['SOURCE_URL'], SETTINGS['SOURCE_UID'], SETTINGS['SOURCE_PW'])
results = source_conn.jconn.getMetaData().getTables(None, None, "%", None)

#I'm not sure if this is how to read the result set, but jaydebeapi's cursor object
# has a lot of logic for getting information out of a result set, so let's harness
# that.
table_reader_cursor = source_conn.cursor()
table_reader_cursor._rs = results
read_results = table_reader_cursor.fetchall()
#get just the table names
[row[2] for row in read_results if row[3]=='TABLE']

answered Jan 15 '14 at 14:44

Greg

45,306
89
231
297

1

No, don't delete it. This was really useful. Small correction: you set conn as your connection and later you refer all the time to souce_conn. I'm assuming you'd like to set source_conn instead of conn (line 9).I had one other issue (maybe I'm using a different version of jpype or jaydebeapi); I needed to set the cursors _meta attribute as follows: `table_reader_cursor._meta = results.getMetaData()`. If I didn't do this, I'd get an AttributeError as it tried to call getColumncount on _meta. Other than that, you really helped me out! – HSquirrel Nov 05 '14 at 08:57
you can access metadata from a particular request in the self.cursor._meta attribute as well. – sakurashinken Jan 15 '15 at 21:27
@Greg how can i get list of views, store procudure, triggers and functions of a database using jaydebeapi in python – Sony Khan Mar 02 '22 at 19:04

score 3 · Answer 2 · answered Jan 15 '14 at 11:44

From ResultSet Javadoc:

public ResultSet getTables(String catalog,
                       String schemaPattern,
                       String tableNamePattern,
                       String[] types)
                throws SQLException

You need pass the four parameter to the method. I'm not a python developer, but in Java I use :

ResultSet rs = metadata.getTables(null, "public", "%" ,new String[] {"TABLE"} );

to get all the tables (and only the tables) in a schema.

Regards.

Using JPype - How can I access JDBC Meta Data Functions

2 Answers2

Linked