#42 BaseSelectQuery#getFromTables() and INNER JOINs

[Gord Thompson]

After learning of BaseSelectQuery#getFromTables() in this comment I did some experimenting and discovered an anomaly.

Repro code:

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.)");
    }
}

If a query named [InvoiceLineItems] uses an "old fashioned" WHERE-based join

SELECT Invoice.InvoiceNumber, Invoice.InvoiceDate, InvoiceDetail.LineItemNumber, InvoiceDetail.Description
FROM Invoice, InvoiceDetail
WHERE (((Invoice.InvoiceNumber)=[InvoiceDetail].[InvoiceNumber]));

then the repro code produces the following, as expected:

Query [InvoiceLineItems]
  Tables used:
    Invoice
    InvoiceDetail

However, if the saved query uses an explicit INNER JOIN

SELECT Invoice.InvoiceNumber, Invoice.InvoiceDate, InvoiceDetail.LineItemNumber, InvoiceDetail.Description
FROM Invoice INNER JOIN InvoiceDetail ON Invoice.InvoiceNumber = InvoiceDetail.InvoiceNumber;

then the repro code produces

Query [InvoiceLineItems]
  Tables used:
    Invoice INNER JOIN InvoiceDetail ON Invoice.InvoiceNumber = InvoiceDetail.InvoiceNumber

It would be nice if .getFromTables() could return just the table names in such cases.

[James Ahlborn]

right, so you've stumbled upon somewhat of a disconnect as to what jackcess provides and what would be "useful" in other scenarios. jackcess is ultimately just providing a pretty "accessor" for the query data stored in the access db. as you may have noticed in your testing, things like union queries are stored almost entirely as a single string. in other words, jackcess does pretty much zero string "parsing". (i think there may be a small amount of work done to properly quote certain values). i.e. jackcess doesn't really have any understanding of access sql syntax (outside of quoting identifiers). in order to fulfill this feature request, jackcess would need to incorporate a sql parser.

[Gord Thompson]

Understood. Just posted this as a "wish list" item. I've also been investigating passing the results of Query#toSQLString() to JSqlParser with some success, although (as I mentioned in another thread) there could be some Access SQL "quirks" that a generic SQL parser may trip over.