B4J Question [jRDC] SELECT column name with special characters from MySQL

aeric · Jan 25, 2021

I created a view with column name in german word and I tried to select the data.
Eg.

SQL:

SELECT
    `Color`,
    `Januar` AS `January`,
    `Februar` AS `February`,
    `März` AS `March`,
    `April` AS `April`,
    `Mai` AS `May`,
    `Juni` AS `June`,
    `Juli` AS `July`,
    `August` AS `August`,
    `September` AS `September`,
    `Oktober` AS `October`,
    `November` AS `November`,
    `Dezember` AS `December`,
    `Total`
FROM view_Color

I get the following error in log:

Waiting for debugger to connect...
Program started.
closing old db.
ResponseError. Reason: java.sql.SQLSyntaxErrorException: Unknown column 'März' in 'field list', Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.sql.SQLSyntaxErrorException: Unknown column 'MÃ¤rz' in 'field list'</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> java.sql.SQLSyntaxErrorException: Unknown column 'MÃ¤rz' in 'field list'</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

May I know how I can solve this error?

Edit: For the workaround, I am avoiding special characters in the view. Just want to learn if there is a way to insist using the special characters.

Sandman · Jan 25, 2021

I had an issue like this many years ago, in another language, with a table brandnames. After trying everything I could think of from the code, and always failing, I went to the database: In it I created a view, called brandnames_sane_names, which I used in my code instead.

The view did exactly what you showed in your example, and it worked like a charm. Of course, this is only possible if you have access to the database so you can create views in it.

aeric · Jan 25, 2021

I have another way which is to create a routine (stored procedure or prepared statement) and call it using "Call" keyword in config.properties.

B4X:

# Query 2
sql.select_FabreByFehler = CALL sp_FabreByFehler

Philip Chatzigeorgiadis · Jan 26, 2021

I am not sure if this helps but when I access fields with greek characters, I have to bracket them - works for sure with SQL Server.
e.g. if the field is ΕΠΙΘΕΤΟ, I access it with SELECT [ΕΠΙΘΕΤΟ] FROM ...

B4J Question [jRDC] SELECT column name with special characters from MySQL

aeric

Expert

Sandman

Expert

aeric

Expert

Philip Chatzigeorgiadis

Active Member