python-3.xms-accessucanaccessjpypejaydebeapi

UcanAccess retrieve stored query sql


I'm trying to retrieve the SQL that makes up a stored query inside an Access database.

I'm using a combination of UcanAccess 4.0.2, and jaydebeapi and the ucanaccess console. The ultimate goal is to be able to do the following from a python script with no user intervention.

When UCanAccess loads, it successfully loads the query:

Please, enter the full path to the access file (.mdb or .accdb): /Users/.../SnohomishRiverEstuaryHydrology_RAW.accdb
Loaded Tables:
Sensor Data, Sensor Details, Site Details
Loaded Queries:
Jeff_Test
Loaded Procedures:

Loaded Indexes:
Primary Key  on Sensor Data Columns: (ID) 
, Primary Key  on Sensor Details Columns: (ID) 
, Primary Key  on Site Details Columns: (ID) 
, Index on Sensor Details Columns: (SiteID) 
, Index on Site Details Columns: (SiteID) 

UCanAccess>

When I run, from the UCanAccess console a query like

SELECT * FROM JEFF_TEST;

I get the expected results of the query.

I tried things including this monstrous query from inside a python script even using the sysSchema=True option (from here: http://www.sqlquery.com/Microsoft_Access_useful_queries.html):

SELECT DISTINCT MSysObjects.Name,
   IIf([Flags]=0,"Select",IIf([Flags]=16,"Crosstab",IIf([Flags]=32,"Delete",IIf
   ([Flags]=48,"Update",IIf([flags]=64,"Append",IIf([flags]=128,"Union",
   [Flags])))))) AS Type
   FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
   MSysQueries.ObjectId;

But get an object not found or insufficient privileges error.

At this point, I've tried mdbtools and can successfully retrieve metadata, and data from access. I just need to get the queries out too.

If anyone can point me in the right direction, I'd appreciate it. Windows is not a viable option.

Cheers, Seth

***********************************
* SOLUTION
***********************************

from jpype import *
startJVM(getDefaultJVMPath(), "-ea", "-Djava.class.path=/Users/seth.urion/local/access/UCanAccess-4.0.2-bin/ucanaccess-4.0.2.jar:/Users/seth.urion/local/access/UCanAccess-4.0.2-bin/lib/commons-lang-2.6.jar:/Users/seth.urion/local/access/UCanAccess-4.0.2-bin/lib/commons-logging-1.1.1.jar:/Users/seth.urion/local/access/UCanAccess-4.0.2-bin/lib/hsqldb.jar:/Users/seth.urion/local/access/UCanAccess-4.0.2-bin/lib/jackcess-2.1.6.jar")

conn = java.sql.DriverManager.getConnection("jdbc:ucanaccess:///Users/seth.urion/PycharmProjects/pyAccess/FE_Hall_2010_2016_SnohomishRiverEstuaryHydrology_RAW.accdb")

for query in conn.getDbIO().getQueries():
    print(query.getName())
    print(query.toSQLString())

Solution

  • If you can find a satisfactory way to call Java methods from within Python then you could use the Jackcess Query#toSQLString() method to extract the SQL for a saved query. For example, I just got this to work under Jython:

    from java.sql import DriverManager
    
    
    def get_query_sql(conn, query_name):
        sql = ''
        for query in conn.getDbIO().getQueries():
            if query.getName() == query_name:
                sql = query.toSQLString()
                break
        return sql
    
    
    # usage example
    if __name__ == '__main__':
        conn = DriverManager.getConnection("jdbc:ucanaccess:///home/gord/UCanAccessTest.accdb")
        query_name = 'Jeff_Test'
        query_sql = get_query_sql(conn, query_name)
        if query_sql == '':
            print '(Query not found.)'
        else:
            print 'SQL for query [%s]:' % (query_name)
            print
            print query_sql
        conn.close()
    

    producing

    SQL for query [Jeff_Test]:
    
    SELECT Invoice.InvoiceNumber, Invoice.InvoiceDate
    FROM Invoice
    WHERE (((Invoice.InvoiceNumber)>1));