For the upcoming
jOOQ 3.3, we’re now integrating support for the
MS Access database through the JDBC-ODBC bridge, which is included in the JDK up until Java SE 7.
Note that it will be removed in Java 8! Alternative access to access databases (pun intended) can be obtained through a hack involving
ucanaccess, which is basically combining the
HSQLDB parser with Jackcess, a
low-level I/O library for MS Access database files.
MS Access is still an immensely popular relational database, ranking in the top 10 at
db-engines.com’s DBMS ranking. Yet it has quirks. Many of them. One is the fact that there is no formal CROSS JOIN operation. Remember, most databases support explicit CROSS JOINing as such:
SELECT p1.name player1,
p2.name player2
FROM player p1
CROSS JOIN player p2
The above query will generate all the pairings between two players.
The same can be written in pre-ANSI SQL-92 with comma-separated table lists:
SELECT p1.name player1,
p2.name player2
FROM player p1,
player p2
The first syntax, however, is more powerful and more expressive as it can be used in nested JOIN expressions and it shows your intent more clearly.
A nice example was given here in a previous blog post.
How to work around a missing CROSS JOIN
Usually, missing support for
CROSS JOIN can be emulated trivially using an
INNER JOIN with a
TRUE predicate as such:
SELECT p1.name player1,
p2.name player2
FROM player p1
JOIN player p2
ON 1 = 1
This is what
jOOQ does for the Sybase Adaptive Server Enterprise database. But this doesn’t work for MS Access, because the
JOIN operation there explicitly requires column references from either table on either side.
The documentation reads:
Syntax:
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field2
This is quite a bummer from many points of view, not only for
CROSS JOIN emulation. Given that any ANSI-92
JOIN syntax can be transformed into an ANSI-86 join expression (table list in the
FROM clause and all predicates in the
WHERE clause), it is also a bit surprising.
A simple workaround that seems to work for
some use-cases is to take any numeric column from either table, and multiply it by zero:
SELECT p1.name player1,
p2.name player2
FROM player p1
JOIN player p2
ON p1.id * 0 = p2.id * 0
But if the database itself is already this quirky, I suspect that it might not be able to optimise the above SQL.
In short…
MS Access does not support
CROSS JOIN. For the time being, try to work around it using comma-separated table lists, while we work out more sophisticated SQL transformation in jOOQ.
Like this:
Like Loading...
Maybe ucanaccess is a hack but it’s currently the one open-source solution because it runs pretty fine on non-windows os Operative Systems and because the JDBC-ODBC bridge will be removed in Java 8(and, you know, I like to win easy).
It supports both access and standard SQL(e.g., both CROSS JOIN and the terrible square brackets).
It’s a very young project but it’s quickly improving in the latest versions (e.g., with the 2.x.x you can access to the access system tables… ops another pun intended). So, keep it in mind for the next JOOQ versions!;)
Hi Marco,
Thanks for chiming in! Yes, we actually have quite a bit of respect for ucanaccess. Even if it’s a hack, it’s a quite cunning one, and I believe it has a good future!
In the short run, we didn’t want to base jOOQ’s Access support on an external dependency as we’re estimating that MS Access users might not jump on the JDK 8 in 2014 or 2015 yet, and even if they do, there are also commercial JDBC-ODBC bridges to replace the Sun one.
But we’ll certainly keep this option in mind and we’ll happily contribute to your project, should we integrate in the future.
Actually, can I access (haha, running-pun) relationship information through ucanaccess? That doesn’t seem to be reliable through MS Access / JDBC-ODBC
Hi,
Yes, of course, you can do it in two different ways:
This is the most reliable way to get metadata about relationships through ucanaccess.
(e.g.: jdbc:ucanaccess://;sysschema=true;)
and executing queries like this:
resultset example:
Thanks, hope to hear from you soon, Marco
Great! I guess I’d have to resort to getCrossReference, as jOOQ does not control connection parameters. It would be more user-friendly, as it would work out of the box…