German umlauts in column names

[Marvin Emil Brach]

I have a legacy DB with german umlauts (ä, ü, ö, ß).
When accessing the columns with names I get the error "column not found"

Yes; I know that it's possible to access with index, too.
But it's a bug and i have to report it :)

Nice work by the way, thanks!

[Gord Thompson]

I have been able to recreate this with 2.0.9.3. I created a table in an Access 2000 .mdb file with columns

ID - AutoNumber, Primary Key
Straße - Text(255)

and when I run the code

try (ResultSet rs = s.executeQuery("SELECT * FROM Table1")) {
    ResultSetMetaData rsmd = rs.getMetaData();
    for (int i=1; i<=rsmd.getColumnCount(); i++) {
        System.out.println(rsmd.getColumnName(i));
    }
}

the columns are listed as

ID
STRASSE

If I try

System.out.println(rs.getString("Straße"));

I get the UcanaccessSQLException: Column not found: Straße

[Marco Amadei]

Marvin & Gord,
Yes, it's a bug about no-roman characters. It will be fixed in the 2.0.9.4.
Cheers Marco

[penguinized]

Hey Marco,
I need this feature of non-roman characters in column names, too.
When do you think 2.0.9.4 will be released?

thanks for your work :)

[Marco Amadei]

I'm very close to the release, and I should have the time to spend in the 2.0.9.4 release this week.
Cheers Marco

[Marco Amadei]

Ops, I've done further tests and found that it already worked.
Gord comment is correct because STRASSE is the internal column name assigned.
Nevertheless System.out.println(rs.getString("Straße")) should work.
My junit test net.ucanaccess.test.NoRomanCharacterTest(see the svn trunk) has been updated and it works fine.
So, are you using the correct java file text encoding (UTF-8)?
If not, the Straße string in the source file is saved as something like Stra?e.
Please, I'm very close to the release, may someone confirm that(after a check to the text file encoding)?

[Marco Amadei]

Found!
I believed to have treated ß as a no roman character, but I don't and it isn't.
If you print System.out.println("Straße".toUpperCase());
the result is STRASSE (I must confess my total ignorance about the german ß )
Also the issue is reproducible using hsqldb only, so it's unrelated to UCanAccess
(maybe someone else didn't understand the ß).
If the name were Straße1¼ it would work, because UCanAccess would have transformed the name in uppercase, converting the original name to match it with the internal name.
At the end, in the 2.0.9.4 I can get it work.
Thanks to Gord for his suggestion that helped me to understand.
Cheers Marco

[penguinized]

Hey Marco,
thanks for the update!
After the update to 2.0.9.4, I can use Umlauts in table names and in column names for SELECT:
SELECT DISTINCT Prüfung FROM Prüfung
works as it should.

But when I add a WHERE statement it crashes:
SELECT DISTINCT Prüfung FROM Prüfung WHERE Prüfstelle LIKE 'PR'
gives the following exception and stack trace:

net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: PRÜ
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:202)
    at UCant.main(UCant.java:38)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: PRÜ
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:199)
    ... 1 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: PRÜ
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(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)
    ... 4 more

[Marco Amadei]

Still something strange, and likely new interesting findings about ü.
This runs: SELECT DISTINCT Prüfstelle FROM Prüfung WHERE Prüfstelle = 'PR'
If you use a like condition after the german name, something fails in one specific Regular Expression (that treats SQL criteria so, in turn, the regular expressions in sql).
BUT
if you escape the german name, all works nicely:
SELECT DISTINCT Prüfung FROM Prüfung WHERE [Prüfstelle] LIKE 'PR'.
Hope this may be acceptable, waiting for I fix it definitively.