Get tables with table pattern

[Pramodh]

Hi James,

I have a query regarding getTableNames() method. I can load all the table names with this method. But, what if I want to load only tables with some patterns (dynamic tables) e.g: Test_2_2019, Test_1_2019.

[Gord Thompson]

Related to this UCanAccess thread.

[James Ahlborn]

Is the issue that the entire collection of table names is too much memory?

So there's no super-simple builtin way to do this. that said, table names are just entries in the system catalog. You can grab the system catalog table from the database instance (DatabaseImpl.getSystemCatalog()). table names are entries with the type of 1. you could use any of the Cursor iterable filter mechanisms to step through the table names.

it would probably be pretty simple to expose an iterator for table names on the Database interface.

[Pramodh]

Thank you James..
Is there any way to find out dependency tables of the MS Access saved query?

[James Ahlborn]

can you explain what you are trying to accomplish?

[Gord Thompson]

You might be able to use something like this, although such a tool may not be familiar with some of the nuances of Access SQL syntax.

[Pramodh]

I am working with large mdb database to fetch records to my Java code to process them. Due to large size of the mdb, it is taking more time for connection from UCanaccess driver. As per Gord suggestion we are creating linked database from the code he shared.
We are providing option to select table to be load, so that table can be copied to linked database.

But, if there is a view/Query to fetch records, we have to move all dependent tables to linked database.

I tried SysObjJoin on "MSysQueries" and "MSysObjects" which is throwing error for foreignkey reference.

I'm using below code as of now which feels heavy. Is there any simple way for this.

Table sysObj = db.getSystemTable("MSysObjects");
Table sysQue = db.getSystemTable("MSysQueries");
for(Row sysObjRow : sysObj){
Object sysObjvalue = sysObjRow.get("Id");
Object sysObjNameValue = sysObjRow.get("Name");
for(Row sysQueRow : sysQue) {
Object sysQuevalue = sysQueRow.get("ObjectId");
Object sysAttrValue = sysQueRow.get("Attribute");
Object sysNameValue = sysQueRow.get("Name1");
if(sysObjvalue.equals(sysQuevalue) && sysAttrValue.toString().equals("5") && sysObjNameValue.equals("Query1")){
tableList.add(sysNameValue.toString());
}
}
}

[James Ahlborn]

i'm not sure if you realize, but you can get Query instances from the Database. I'm not sure what type of queries you need to deal with, but if they are some form of "select" query, you can use BaseSelectQuery.getFromTables() to get a list of the tables used in the query.

[Pramodh]

I am using UNION query like below.

Select order.name, order.id, order.purchaseDate from tblorderdetails order
UNION
Select sale.name, sale.id, sale.saleDate from tblorderdetails sale

Also, BaseSelectQuery.getFromTables() method cannot be referenced directly as it is non-static method.

[Gord Thompson]

Access treats UNION queries differently from simple SELECT queries, as can be seen from inspecting the values saved in [MSysQueries]. Apparently a UNION query is not compatible with BaseSelectQuery; this code will throw the ClassCastException instead of displaying the table names:

for (Query qry : db.getQueries()) {
    System.out.printf("Query [%s]%n", qry.getName());
    System.out.println("  Tables used:");
    try {
        for (String tableName : ((BaseSelectQuery) qry).getFromTables()) {
            System.out.printf("    %s%n", tableName);
        }
    } catch (ClassCastException cce) {
        System.out.println("    (BaseSelectQuery#getFromTables is not available for this query.)");
    }
}

You also have the extra challenge that order is a reserved word in SQL (as in ORDER BY), so many SQL parsers like JSqlParser and even SQL Server (T-SQL) will choke on your query unless you quote the order alias:

Select [order].name, [order].id, [order].purchaseDate from tblorderdetails [order]
UNION
...