ReportServer Forum

h_Geri

Member

Registered: 2018-07-11

MAria DB syntax error

I tried to execute a simple riport and I got this error:

Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from AS `xx__rs_col_3`, from_uid AS `xx__rs_col_4`, to AS `xx__rs_col_5`, to_uid' at line 1 Query is: SELECT `xx__rs_col_0`, `xx__rs_col_1`, `xx__rs_col_2`, `xx__rs_col_3`, `xx__rs_col_4`, `xx__rs_col_5`, `xx__rs_col_6`, `xx__rs_col_7`, `xx__rs_col_8`, `xx__rs_col_9`, `xx__rs_col_10`, `xx__rs_col_11`, `xx__rs_col_12`, `xx__rs_col_13`, `xx__rs_col_14` FROM ( SELECT order_id AS `xx__rs_col_0`, event_id AS `xx__rs_col_1`, parent_transaction_id AS `xx__rs_col_2`, from AS `xx__rs_col_3`, from_uid AS `xx__rs_col_4`, to AS `xx__rs_col_5`, to_uid AS `xx__rs_col_6`, currency AS `xx__rs_col_7`, amount AS `xx__rs_col_8`, status AS `xx__rs_col_9`, executor_class AS `xx__rs_col_10`, modul AS `xx__rs_col_11`, created_date AS `xx__rs_col_12`, modified_date AS `xx__rs_col_13`, site_code AS `xx__rs_col_14` FROM ( select * from transaction ) colQry) aliasQry /* user: 6 */ /* report: 4760 */ /* token: 4760-6-1531323854486-1132992215 */ /* currentuser: 6 */

MAria db version: 10.2.9-MariaDB

config:
hibernate.dialect=net.datenwerke.rs.utils.hibernate.MariaDbDialect
hibernate.connection.driver_class=org.mariadb.jdbc.Driver

But at the first run, the dialect was MysqlDialect. Is this matter?

Any idea?

Last edited by h_Geri (2018-07-11 15:52:47)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: MAria DB syntax error

Hi h_Geri,

some questions:
1. what version exactly of reporterver have you installed? (rsversion.properties)
2. did you perform a manual or a bitnami installation?
3. I see in your query many columns. Could you please try to delete the most columns as possible where we can see the problem? I.e. a minimal example
4. Could you post screenshots to reproduce the problem? Use some online image db like http://www.imgbb.com
5. You installed mariaDB externally and created a datasource pointing to it, right? Could you post your JDBC URL ?

Regards,
Eduardo

h_Geri

Member

Registered: 2018-07-11

Re: MAria DB syntax error

Hi Eduardo,

Thank you for your answer.

My answers here:
1: version=RS3.0.3-6003
schemaversion=RS3.0-9
buildDate=1530020651312-2018-06-26-15-44-00
2: manual, OS: MAC HighSierra
5: jdbc:mysql://127.0.0.1:3306/HGeriDatabaseName?nullNamePatternMatchesAll=true&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=CET
3: with one collumn, it works. When the collumn type not mixed works. When I mixed type of collumn list (char, num) the result is:
Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from AS `xx__rs_col_1` FROM ( select * from transaction
) colQry) aliasQry) limi' at line 1
Query is:
SELECT * FROM (SELECT `xx__rs_col_0`, `xx__rs_col_1` FROM ( SELECT order_id AS `xx__rs_col_0`, from AS `xx__rs_col_1` FROM ( select * from transaction
) colQry) aliasQry) limitQry LIMIT 50 OFFSET 0 /* user: 6 */ /* report: 4760 */ /* token: 4760-6-1531381640502--122098312:0.3673322595865518 */ /* currentuser: 6 */
4:https://ibb.co/kJKw98

Thanks,
Geri

h_Geri

Member

Registered: 2018-07-11

Re: MAria DB syntax error

I see what is the problem. one collumn name is 'from'...
How can I solve this issue.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: MAria DB syntax error

Hi Geri,

you are right, this is the problem. I checked and if you quote the from: `from` it works. I raised ticket RS-2972 for investigating this.
In the meanwhile, please just rename your column to another value.

Regards,
Eduardo

h_Geri

Member

Registered: 2018-07-11

Re: MAria DB syntax error

Thanks,

The rename is not a good way, because the business application uses that collumn.

G.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: MAria DB syntax error

Hi Geri,

you can create a view in your MariaDB that uses another name as a workaround and use this view instead of the table.

Regards,
Eduardo

h_Geri

Member

Registered: 2018-07-11

Re: MAria DB syntax error

Eduardo,

Thanks! What a trivial solution! I'll do that

G.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: MAria DB syntax error

Hi Geri,

I checked again and you don't need a view for this.
Check this configuration file in your internal reportserver file system: etc/datasources/sql.cf  (https://reportserver.net/en/guides/conf … ces-sqlcf/)
Change the property "quoteIdentifiers" from "smart" to "always". Now reportserver will always quote identifiers in all your queries. Please note that this applies for all datasources in your reportserver installation.

Your configuration file should look similar to:

<?xml version="1.0" encoding="UTF-8"?>
<!--
 ReportServer Configuration File
 filename: datasources/datasources.cf
 
 Configures the internal SQL-query builder. 
-->
<configuration>
   <sql>
   	  <!-- define a maximum number of records that can go into an IN clause --> 
      <incondition>
         <maxsize>1000</maxsize>
      </incondition>
      <quoteIdentifiers>always</quoteIdentifiers>
   </sql>
</configuration>

Don't forget to reload your configuration (terminal -> config reload) or restart reportserver after saving your changes.
We will check if we should change the default to "always" instead of "smart". Anyway, you can now delete your view and use reportserver directly with your table.

Regards,
Eduardo