Java 8 and JDBC:ODBC Drivers in Access

Question

Since version 8 Java not longer support JDBC:ODBC drivers. I try to change the source code of my app in order to use UcanAccess Ver.2.0.9.3 but this throw many exceptions like :

net.ucanaccess.jdbc.UcanaccessSQLException: unknown token:
---
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: AS required: )

This is an example of bugged code :

    String selectString = "SELECT [Maintenance input Check Due List].InputRepairStation, [Maintenance input Check Due List].[Interval (Mos)], [Maintenance input Check Due List].InputAircraftHours, [Maintenance input Check Due List].InputAircraftLandings,  [Maintenance input Check Due List].Check, [Maintenance input Check Due List].Title, [Maintenance input Check Due List].InputNumber,[Maintenance input Check Due List].InputDescription, [Maintenance input Check Due List].AircraftCurrentTSN, [Maintenance input Check Due List].AircraftCurrentCSN, [Maintenance input Check Due List].Aircraft, [Maintenance input Check Due List].InputDateOUT, [Maintenance input Check Due List].[Interval (Hrs)], [Maintenance input Check Due List].[Interval(Ldgs)], [Maintenance input Check Due List].NextDueCheckMonth, [Maintenance input Check Due List].NextDueCheckHours, [Maintenance input Check Due List].NextDueCheckLdgs, [Maintenance input Check Due List].RemainCheckMonth, [Maintenance input Check Due List].RemainCheckHours,[Maintenance input Check Due List].RemainCheckLdgs, [Maintenance Schedule Check].MSC_Input, [Maintenance input Check Due List].Check "
            + "FROM [Maintenance input table] RIGHT JOIN ([Maintenance Schedule Check] RIGHT JOIN ("
            + "SELECT  [Maintenance input table].InputRepairStation, [Maintenance input table].InputAircraftHours, [Maintenance input table].InputAircraftLandings, [Check Table].Check, [Check Table].Title, [Maintenance input table].InputNumber, [Maintenance input table].InputDescription,[Aircraft].AircraftCurrentTSN,[Aircraft].AircraftCurrentCSN, [Check Table].Aircraft, [Maintenance input table].InputDateOUT,  [Check Table].[Interval (Mos)], [Check Table].[Interval (Hrs)], [Check Table].[Interval(Ldgs)], IIf([Interval (Mos)] Is Null,Null,DateAdd('m',[Interval (Mos)],[InputDateOUT])) AS NextDueCheckMonth, [Interval (Hrs)]+[InputAircraftHours] AS NextDueCheckHours, [Interval(Ldgs)]+[InputAircraftLandings] AS NextDueCheckLdgs, IIf([NextDueCheckMonth] Is Null,Null,DateDiff('d',Now(), [NextDueCheckMonth],1,1)) AS RemainCheckMonth, IIf([NextDueCheckHours] Is Null,Null,[NextDueCheckHours]-[AircraftCurrentTSN]) AS RemainCheckHours, [NextDueCheckLdgs]-[AircraftCurrentCSN] AS RemainCheckLdgs "
            + "FROM (([Aircraft] INNER JOIN [Check Table] ON [Aircraft].AicraftVar = [Check Table].Aircraft) INNER JOIN (((SELECT [Check Aircraft Status].CAS_Check, Max([Maintenance input table].InputDateOUT) AS MaxDeInputDateOUT "
            + "FROM ([Maintenance input table] INNER JOIN [Work Order table] ON [Maintenance input table].InputNumber = [Work Order table].WOInput) "
            + "INNER JOIN [Check Aircraft Status] ON [Work Order table].WONumber = [Check Aircraft Status].CAS_WO "
            + "GROUP BY [Check Aircraft Status].CAS_Check) AS [Maintenance input check wo select] "
            + "INNER JOIN [Maintenance input table] ON [Maintenance input check wo select].MaxDeInputDateOUT = [Maintenance input table].InputDateOUT)  INNER JOIN [Check Aircraft Status] ON [Maintenance input check wo select].CAS_Check = [Check Aircraft Status].CAS_Check) ON [Check Table].Check = [Check Aircraft Status].CAS_Check) INNER JOIN [Work Order table] ON ([Work Order table].WONumber = [Check Aircraft Status].CAS_WO) AND ([Maintenance input table].InputNumber = [Work Order table].WOInput) "
            + "WHERE ((([Check Table].Aircraft)='"
            + ac.getAicraftVAR()
            + "') AND (([Check Table].[Interval (Mos)]) Is Not Null)) "
            + "OR ((([Check Table].Aircraft)='"
            + ac.getAicraftVAR()
            + "') AND (([Check Table].[Interval (Hrs)]) Is Not Null)) "
            + "OR ((([Check Table].Aircraft)='"
            + ac.getAicraftVAR()
            + "') AND (([Check Table].[Interval(Ldgs)]) Is Not Null)) "
            + "ORDER BY [Check Table].[Interval (Mos)]) AS [Maintenance input Check Due List] "
            + "ON [Maintenance Schedule Check].MSC_Check = [Maintenance input Check Due List].Check) "
            + "ON [Maintenance input table].InputNumber = [Maintenance Schedule Check].MSC_Input "
            + "WHERE ((([Maintenance input Check Due List].RemainCheckMonth)<"
            + AircraftList.getMonth()
            + "*30) AND (([Maintenance input table].InputAircraft)='"
            + ac.getAicraftVAR()
            + "' Or ([Maintenance input table].InputAircraft) Is Null)) OR  "
            + "((([Maintenance input Check Due List].RemainCheckHours)<"
            + ac.hoursEstimation * AircraftList.getMonth()
            + ") AND (([Maintenance input table].InputAircraft)='"
            + ac.getAicraftVAR()
            + "' Or ([Maintenance input table].InputAircraft) Is Null)) OR "
            + "((([Maintenance input Check Due List].RemainCheckLdgs)<"
            + ac.ldgsEstimation
            * AircraftList.getMonth()
            + ") AND (([Maintenance input table].InputAircraft)='"
            + ac.getAicraftVAR()
            + "' Or ([Maintenance input table].InputAircraft) Is Null)) "
            + "ORDER BY IIf([RemainCheckMonth] IS Null, IIf([RemainCheckHours] IS Null, IIf([RemainCheckLdgs] IS Null, null, [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30), IIf([RemainCheckLdgs] IS Null, [RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30, IIF([RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30< [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30, [RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30, [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30))), IIf([RemainCheckHours] IS Null, IIf([RemainCheckLdgs] IS Null, RemainCheckMonth, IIF(RemainCheckMonth < [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30, RemainCheckMonth, [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30)), IIf([RemainCheckLdgs] IS Null, IIF(RemainCheckMonth <[RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30, RemainCheckMonth, [RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30), IIf([RemainCheckMonth] < [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30, IIf([RemainCheckMonth] < [RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30, [RemainCheckMonth], [RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30), IIf([RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30<[RemainCheckHours]/"
            + ac.hoursEstimation
            + "*30, [RemainCheckLdgs]/"
            + ac.ldgsEstimation
            + "*30, [RemainCheckHours]/"
            + ac.hoursEstimation + "*30)))))";

This throw the folowing exception :

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: AS required: )

My questions are the following :

Is there any solution to find the old and bad, but functional, JDBC:ODBC Library thus i can import it in my code ?
Is there a solution to bypass these exceptions without changing the queries (there is a lot of query in there) ?

You can use Java 8 to talk to a service running Java 6 or 7 which is using your driver. — Peter Lawrey, Jan 23 '15 at 08:24
Have you read Robert Petermeier answer to this question ? http://stackoverflow.com/questions/14229072/removal-of-jdbc-odbc-bridge-in-java-8 — Gerard Rozsavolgyi, Jan 23 '15 at 08:32
I never find this thread. In fact the java app is a kind of trigger to generate some PDF's by reading a by access database where a very big app exist : i can't change the database technology, it's to munch work (i think 6 full-time month at least). Currently I using JAVA 7 but i will solve this issue before hearing of "big security" problem in java 7 or a update made by mistake. — Kromen, Jan 23 '15 at 09:02
Are you using the 2.0.9.3. ? Please post along the sql code that raises those errors, they may be easy to solve. — jamadei, Jan 23 '15 at 11:39
Nice, that's challenging. It would be great if you attach in some way an empty db, only tables and query, no data. And a print of the resulting selectString. Is it possible? — jamadei, Jan 23 '15 at 16:12
@jamadei I was curious so I pasted the Java statement into Eclipse, had it substitute some dummy values for `ac.getAicraftVAR()`, etc., and then dump the resulting SQL statement. Then I reformatted it and pasted it [here](http://pastebin.com/dX5DTUSs). — Gord Thompson, Jan 23 '15 at 17:34

jamadei · Answer 1 · 2015-01-23T22:03:47.633

1

Please, just try to replace the hsqldb.jar in the ucanaccess distribution with the current 2.3.2.(download it from the hsqldb web site). Notice that the porting to hsqldb 2.3.2 isn't yet completed, but in your specific case it will likely work. Anyway, thanks to Gord who was able to reproduce the issue on the ucanaccess help forum.

edited Jan 23 '15 at 22:03

answered Jan 23 '15 at 21:52

jamadei

1,700
9
8

Java 8 and JDBC:ODBC Drivers in Access

1 Answers1