Question

3
Replies
109
Views
chamilaf Member since 2015 6 posts
Virtusa
Posted: 3 years ago
Last activity: 3 years 3 months ago
Closed
Solved

Pega 7.2.2 Report-definition support to MYSQL Error code: 1064

Hi,

I am trying to run a report def to load students. It is a pretty basic table with 3 columns.

However when the rep-def is run it throws below error.

Error:
** An error occured on executing the query for the report definition - There was a problem getting a list: code: 1064 SQLState: 42000 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`school_id` AS "school_id" , "PC0".`school_name` AS "school_name" , "PC0".`scho' at line 1 DatabaseException caused by prior exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`school_id` AS "school_id" , "PC0".`school_name` AS "school_name" , "PC0".`scho' at line 1 | SQL Code: 1064 | SQL State: 42000

Query is as below

SELECT "PC0".`school_id` AS "school_id" , "PC0".`school_name` AS "school_name" , "PC0".`school_desc` AS "school_desc" FROM school "PC0" WHERE ( "PC0".`school_id` = ? OR "PC0".`school_name` = ? )

I tried to run the query in MYSQL and I get the same error. it looks like MYSQL does not agree with the way PEGA generates the query.

I found out that MYSQL does not like prefix to be in double quotes such as "PC0". I manually removed them and MYSQL runs it perfectly. (see below query)

SELECT PC0.`school_id` AS "school_id" , PC0.`school_name` AS "school_name" , PC0.`school_desc` AS "school_desc" FROM school PC0 WHERE ( PC0.`school_id` = 1 OR PC0.`school_name` = 'name1' )

Is this a knows issue or behavior ? Please let me know possible solution.

Thanks

Chamila

Low-Code App Development Reporting
Moderation Team has archived post
Share this page LinkedIn