I am trying to open an MS Access database I have using the latest UCanAccess driver. When I call DriverManager.getConnection() I get this error:
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: IS required: (
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:210)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:233)
at msaccess.TestConnection.<init>(TestConnection.java:23)
at msaccess.TestConnection.main(TestConnection.java:34)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: IS required: (
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.converters.LoadJet.execCreate(LoadJet.java:1176)
at net.ucanaccess.converters.LoadJet.access$0(LoadJet.java:1171)
at net.ucanaccess.converters.LoadJet$TablesLoader.defaultValues(LoadJet.java:479)
at net.ucanaccess.converters.LoadJet$TablesLoader.createSyncrTriggers(LoadJet.java:856)
at net.ucanaccess.converters.LoadJet$TablesLoader.createTriggers(LoadJet.java:814)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:846)
at net.ucanaccess.converters.LoadJet$TablesLoader.access$3(LoadJet.java:839)
at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1228)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:199)
... 4 more
Caused by: org.hsqldb.HsqlException: unexpected token: IS required: (
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
at org.hsqldb.ParserBase.readThis(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserRoutine.compileIf(Unknown Source)
at org.hsqldb.ParserRoutine.compileSQLProcedureStatementOrNull(Unknown Source)
at org.hsqldb.ParserDDL.compileTriggerRoutine(Unknown Source)
at org.hsqldb.ParserDDL.compileCreateTrigger(Unknown Source)
at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 15 more</init>
Anyone who can help?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If it is of any help, I debugged this and found that this is the statement which it tries to execute:
CREATE TRIGGER DEFAULT_TRIGGER23 BEFORE INSERT ON INFORMATIONVIEW REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW.CARDINALITY IS NULL THEN SET NEW.CARDINALITY= 0 ; END IF
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Knut.
Firstly, thank you for your effort.
I thought It was due to the "cardinality" hsqldb keyword, yet I can create a table with a numeric column named cardinality and with a zero default value and then open the database without problems.
Also, the statement you mentioned shouldn't cause any sql error(in fact, debugging, the execution of the same sql statement you mentioned works fine ).
-Are you sure you didn't have an old hsqldb version in your classpath?
-Can you open your database using the UCanAccess console?
-Do you mind copying the table(without data) in a new database and attaching it?
Thank you.
Marco
Last edit: Marco Amadei 2014-10-15
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for your input. Yes, there is a numeric column called "Cardinality" in the table "InformationView" in the database. Since that is a keyword it indeed seems like that could be the problem (as HSQLDB is also complaining about the encountered "IS" instead of the expected "("). Would it perhaps make sense to quote table and column names in the statements? E.g.
CREATE TRIGGER DEFAULT_TRIGGER23 BEFORE INSERT ON "INFORMATIONVIEW" REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW."CARDINALITY" IS NULL THEN SET NEW."CARDINALITY"= 0 ; END IF
I was in fact only using the jars part of the UCanAccess download and I am sure I didn't have any other versions on the classpath. I didn't try with the UCanAccess console, but I would be really surprised if that works, because all I do in the Java code is call DriverManager.getConnection().
I am on OSX and don't really have any tool to open the database so it will be somewhat difficult for me to create a copy of it...
When you say you were able to add a column named cardinality with a zero default value, did you test this in an MS Access MDB file? If yes, then I am a bit surprised this works and didn't cause any problems for you.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Knut, it seems to be a comunication failure.
I have OBVIOUSLY tested what I said with a MS Access database, and, I repeat, it works with the 2.0.9.2. So I wasn't able to reproduce the issue. In attachment the database I tested.
Yes if I had gotten an error, I would have fixed it exactly by quoting the column name.
But because I can't reproduce the issue, I can't fix it.
As first step the console tries to connect to the database you pass in input.
Therefore, if you'll be able to connect with the console, it means there is something wrong in your environment.
Otherwise(if you won't be able), it means that the problem I identified was misleading, and so it could be very useful to have a copy of the INFORMATIONVIEW table.
Thank you again.
Thanks for confirming that you tested this with MS Access. I am sorry if my comment came across as condescending in any way. That was certainly not my intent! I just wanted to make sure before I go through the trouble of getting access to some Windows box so that I can copy the database table for you.
I will try what you said and make sure again that I use the same version as you did.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
Absolutley no problems, thank you!.
This bug is something strange(and our work is strange as well).
Just to remove all my doubt, could you execute this before trying to get the connection?
System.out.println("hsqldb version "+org.hsqldb.jdbc.JDBCDriver.class.getPackage().getImplementationVersion());
Please, let me know your findings!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Turns out that I during my debugging somehow picked up the wrong statement (although it positively raises an exception for the one I mentioned). Anyway, if I try to load the database in the console I get this error message:
Cannot execute:CREATE TRIGGER DEFAULT_TRIGGER84 BEFORE INSERT ON SCREENITEM REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW.ROW IS NULL THEN SET NEW.ROW= 0 ; END IF unexpected token: IS required: (
So apparently there is a column called "ROW" in the database which is causing problems.
Thank you very much for your prompt help!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Okey, bug reproduced.
The fix will be in the 2.0.9.3. and it will handle of all keywords that could cause problems in that context.
I'm going to release the 2.0.9.3 in the next few days.
Knut, thank you so much!
Last edit: Marco Amadei 2014-10-18
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Fixed on trunk.
In your specific case the issue should be solved but a more extensive analysis showed few other cases of potential issues with keywords.
I'm going to fix them asap.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am trying to open an MS Access database I have using the latest UCanAccess driver. When I call DriverManager.getConnection() I get this error:
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: IS required: (
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:210)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:233)
at msaccess.TestConnection.<init>(TestConnection.java:23)
at msaccess.TestConnection.main(TestConnection.java:34)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: IS required: (
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.converters.LoadJet.execCreate(LoadJet.java:1176)
at net.ucanaccess.converters.LoadJet.access$0(LoadJet.java:1171)
at net.ucanaccess.converters.LoadJet$TablesLoader.defaultValues(LoadJet.java:479)
at net.ucanaccess.converters.LoadJet$TablesLoader.createSyncrTriggers(LoadJet.java:856)
at net.ucanaccess.converters.LoadJet$TablesLoader.createTriggers(LoadJet.java:814)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:846)
at net.ucanaccess.converters.LoadJet$TablesLoader.access$3(LoadJet.java:839)
at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1228)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:199)
... 4 more
Caused by: org.hsqldb.HsqlException: unexpected token: IS required: (
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
at org.hsqldb.ParserBase.readThis(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserRoutine.compileIf(Unknown Source)
at org.hsqldb.ParserRoutine.compileSQLProcedureStatementOrNull(Unknown Source)
at org.hsqldb.ParserDDL.compileTriggerRoutine(Unknown Source)
at org.hsqldb.ParserDDL.compileCreateTrigger(Unknown Source)
at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 15 more</init>
Anyone who can help?
If it is of any help, I debugged this and found that this is the statement which it tries to execute:
CREATE TRIGGER DEFAULT_TRIGGER23 BEFORE INSERT ON INFORMATIONVIEW REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW.CARDINALITY IS NULL THEN SET NEW.CARDINALITY= 0 ; END IF
Hi Knut.
Firstly, thank you for your effort.
I thought It was due to the "cardinality" hsqldb keyword, yet I can create a table with a numeric column named cardinality and with a zero default value and then open the database without problems.
Also, the statement you mentioned shouldn't cause any sql error(in fact, debugging, the execution of the same sql statement you mentioned works fine ).
-Are you sure you didn't have an old hsqldb version in your classpath?
-Can you open your database using the UCanAccess console?
-Do you mind copying the table(without data) in a new database and attaching it?
Thank you.
Marco
Last edit: Marco Amadei 2014-10-15
Hi Marco,
Thanks for your input. Yes, there is a numeric column called "Cardinality" in the table "InformationView" in the database. Since that is a keyword it indeed seems like that could be the problem (as HSQLDB is also complaining about the encountered "IS" instead of the expected "("). Would it perhaps make sense to quote table and column names in the statements? E.g.
CREATE TRIGGER DEFAULT_TRIGGER23 BEFORE INSERT ON "INFORMATIONVIEW" REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW."CARDINALITY" IS NULL THEN SET NEW."CARDINALITY"= 0 ; END IF
I was in fact only using the jars part of the UCanAccess download and I am sure I didn't have any other versions on the classpath. I didn't try with the UCanAccess console, but I would be really surprised if that works, because all I do in the Java code is call DriverManager.getConnection().
I am on OSX and don't really have any tool to open the database so it will be somewhat difficult for me to create a copy of it...
When you say you were able to add a column named cardinality with a zero default value, did you test this in an MS Access MDB file? If yes, then I am a bit surprised this works and didn't cause any problems for you.
Knut, it seems to be a comunication failure.
I have OBVIOUSLY tested what I said with a MS Access database, and, I repeat, it works with the 2.0.9.2. So I wasn't able to reproduce the issue. In attachment the database I tested.
Yes if I had gotten an error, I would have fixed it exactly by quoting the column name.
But because I can't reproduce the issue, I can't fix it.
As first step the console tries to connect to the database you pass in input.
Therefore, if you'll be able to connect with the console, it means there is something wrong in your environment.
Otherwise(if you won't be able), it means that the problem I identified was misleading, and so it could be very useful to have a copy of the INFORMATIONVIEW table.
Thank you again.
Hi Marco,
Thanks for confirming that you tested this with MS Access. I am sorry if my comment came across as condescending in any way. That was certainly not my intent! I just wanted to make sure before I go through the trouble of getting access to some Windows box so that I can copy the database table for you.
I will try what you said and make sure again that I use the same version as you did.
Hi,
Absolutley no problems, thank you!.
This bug is something strange(and our work is strange as well).
Just to remove all my doubt, could you execute this before trying to get the connection?
System.out.println("hsqldb version "+org.hsqldb.jdbc.JDBCDriver.class.getPackage().getImplementationVersion());
Please, let me know your findings!
Turns out that I during my debugging somehow picked up the wrong statement (although it positively raises an exception for the one I mentioned). Anyway, if I try to load the database in the console I get this error message:
Cannot execute:CREATE TRIGGER DEFAULT_TRIGGER84 BEFORE INSERT ON SCREENITEM REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW.ROW IS NULL THEN SET NEW.ROW= 0 ; END IF unexpected token: IS required: (
So apparently there is a column called "ROW" in the database which is causing problems.
Thank you very much for your prompt help!
Just in case it should matter, the table also has another column named "COLUMN" :-(
Okey, bug reproduced.
The fix will be in the 2.0.9.3. and it will handle of all keywords that could cause problems in that context.
I'm going to release the 2.0.9.3 in the next few days.
Knut, thank you so much!
Last edit: Marco Amadei 2014-10-18
Thank you, Marco!
Fixed on trunk.
In your specific case the issue should be solved but a more extensive analysis showed few other cases of potential issues with keywords.
I'm going to fix them asap.