Question

3
Replies
130
Views
Close popover
chamila fernando (chamilaf)
Virtusa

Virtusa
LK
chamilaf Member since 2015 7 posts
Virtusa
Posted: May 28, 2017
Last activity: May 29, 2017
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,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.