Exception when using keywords in column- and table-names

Help
AlBundy33
2014-01-13
2014-01-24
  • AlBundy33
    AlBundy33
    2014-01-13

    Is there anything I can set to load accdb-files with "invalid" column names?
    In my case a column is name "End" - the seems to be a problem for hsqldb. :-)


    net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: END
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:182)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:190)
    at net.ucanaccess.console.Main.main(Main.java:150)
    Caused by: java.sql.SQLSyntaxErrorException: unexpected token: END
    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:749)
    at net.ucanaccess.converters.LoadJet.access$0(LoadJet.java:744)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createSyncrTable(LoadJet.java:238)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTable(LoadJet.java:412)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTable(LoadJet.java:400)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:485)
    at net.ucanaccess.converters.LoadJet$TablesLoader.access$3(LoadJet.java:475)
    at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:801)
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:171)
    ... 3 more
    Caused by: org.hsqldb.HsqlException: unexpected token: END
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserBase.checkIsNonCoreReservedIdentifier(Unknown Source)
    at org.hsqldb.ParserDQL.checkIsSchemaObjectName(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreateTableBody(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreateTable(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)
    ... 14 more
    unexpected token: END

     
  • AlBundy33
    AlBundy33
    2014-01-13

    Found a solution - but needs a patch of net.ucanaccess.converters.SQLConverter
    1. add "END" to KEYWORDLIST
    2. in basicEscapingIdentifier add at least these lines
    escaped = escaped.replaceAll("€", "EUR");
    escaped = escaped.replaceAll("²", "2");
    escaped = escaped.replaceAll("³", "3");
    with this patch I was able to load my file in console.

     
  • Marco Amadei
    Marco Amadei
    2014-01-13

    Hi AlBundy33,
    You're right. UcanAccess handles hsqldb keywords and so generally there aren't problems, but "END" wasn't listed in the hsqldb KEYWORDLIST (class net.ucanaccess.converters.SQLConverter).
    I've already patched this bug in the trunk. I will release the patch asap.
    Thank you.

    Marco

     
    Last edit: Marco Amadei 2014-01-13
  • AlBundy33
    AlBundy33
    2014-01-13

    Great - please don't forget to replace the mentioned characters too.

    Thanks

     
  • Marco Amadei
    Marco Amadei
    2014-01-13

    Okay,
    It will be done ;-)
    Cheers Marco

     
  • AlBundy33
    AlBundy33
    2014-01-14

    Maybe this characters should be replaced in basicEscapingIdentifier():
    escaped = escaped.replaceAll("€", "EUR");
    escaped = escaped.replaceAll("²", "2");
    escaped = escaped.replaceAll("³", "3");
    escaped = escaped.replaceAll("ß", "ss");

    An additional validation parameter would be nice that throws an exception if such an error occurs: "user lacks privilege or object not found: XXX"
    In my case I've tried to load an accdb-file that references another accdb that was missing.

     
  • Marco Amadei
    Marco Amadei
    2014-01-14

    In the next Ucanaccess version I'm going to handle all "special characters" like those you mentioned.
    About point 2: the references to other database are handled by jackcess, nevertheless
    I'll look for a solution to throw a more meaningful exception.
    Cheers Marco

     
  • AlBundy33
    AlBundy33
    2014-01-14

    Maybe you can also add a parameter to remap that path to external files. ;-)

     
  • Marco Amadei
    Marco Amadei
    2014-01-14

    If I've understood correctly, you suggest to let configure external databases by
    specifing their path as jdbc connection parameter so that you could execute cross database queries...
    So you would load a main database and one or more optional databases.
    This implementation is actually in my extension plan, but it requires a bit of effort, thus it may take some more time.
    Cheers Marco

     
  • AlBundy33
    AlBundy33
    2014-01-14

    Right - in my case the accdb referenced H:\some\where\foobar.mdb

    But loading the accdb (in my project or dbeaver) does not show any error.
    Therefore I've requested the exception on such case.

    The second parameter is to solve the issue e.g remap=H:\some\where\foobar.mdb;C:\my_local_foobar.mdb)

    Not sure if this is possible but would be nice.

     
  • Marco Amadei
    Marco Amadei
    2014-01-14

    Okay, that's simple and I have already found a solution.
    This extension will be implemented in UCanaccess 2.0.2.
    Cheers Marco

     
  • AlBundy33
    AlBundy33
    2014-01-14

    But it should be possible to define multiple mappings in case there are multiple files referenced. ;-)

     
  • Marco Amadei
    Marco Amadei
    2014-01-14

    Yes, I agree. No problems.
    Cheers Marco

     
  • AlBundy33
    AlBundy33
    2014-01-15

    Great.

    Thanks

     
  • AlBundy33
    AlBundy33
    2014-01-19

    It seems that net.ucanaccess.converters.SQLConverter has a wrong encoding because the keys for noRomanCharacters were not displayed correctly.

     
  • Marco Amadei
    Marco Amadei
    2014-01-19

    It depends on your source encoding setting.
    You have to set it to it to ISO-8859-1 or Cp1252 (if your are working on a windows os).
    You won't have problems with the compiled code.
    Cheers Marco

     
  • Marco Amadei
    Marco Amadei
    2014-01-19

    Also I'll translate these characters in unicode for avoiding compilation problems.
    Marco

     
  • Marco Amadei
    Marco Amadei
    2014-01-24

    I've implemented the "remap" feature and the conversion of non-roman characters (see documentation on the UCanAccess website).
    If one or more of external files don't exist, I still write a warning log: it isn't a blocking error and throwing an exception would be excessive.
    Cheers Marco