Jasper reports SQL query error upgraded to v6.1

[noobtiger]

I upgraded the Jasper server from v6.0.1 to v6.1, all my reports which have SQL queries associcated with them are now crashing with the following error:

org.teiid.api.exception.query.QueryParserException: TEIID30378 Direct usage of XQuery is no longer supported, use XMLQUERY instead.

org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]declare[*] @InputTable LegalApps_dbo.udtGlobalEntityId" at line 1, column 1. Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

 

My SQL query is as follows:

declare  @InputTable utd

insert into @InputTable

select PersonID from dbo.vPersonTable where ($X{IN,PersonID,ic_SelectUser})

select *  from dbo.foobar

where someID in (select * from someFunction(@InputTable,$P{ic_IncludeTeam}))

 

How do I change the SQL query to run the report successfully. Can anyone please give me any pointers.

Thanks.

[joel.carlson]

try adding Declare to validation.properties file, /{yourinstallpath}/jasperreports-server-cp-6.1.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi

Look for Validator.ValidSQL=       then add declare where you see select and call and see if that helps.