ReportServer Forum

Johann

Member

Registered: 2022-05-30

Groovy Scripts Reports - How to import SQL queries from .sql file

Would like to achieve the following:
1. Consolidate all SQL queries in one place so debugging and changes could be easier
2. Enable syntax highlighting for SQL code as currently there is no highlighting in Groovy files for SQL queries (using IntelliJ IDE)

Am wondering if it is possible to factor out my SQL queries from my groovy script reports into a .sql file, and then import the relevant queries into the script reports. How should I go about doing this?

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Groovy Scripts Reports - How to import SQL queries from .sql file

Hi Johann,

your groovy script should be able to parse the .sql file and look for the relevant query. You would have to write a parser/lookup for that, but it should be doable.

Regards,
Eduardo

Johann

Member

Registered: 2022-05-30

Re: Groovy Scripts Reports - How to import SQL queries from .sql file

Hi Eduardo,

I am trying to read my SQL query from .sql using Groovy's File library. I have the following line of code:

def query1 = new File('/fileserver/bin/queries/data1.sql').text

where my SQL file data1.sql is under the ReportServer path '/fileserver/bin/queries'. However, I am getting the following error when I try to execute my Groovy script:

The report could not be executed: net.datenwerke.rs.scripting.service.scripting.exceptions.ScriptEngineException: javax.script.ScriptException: java.io.FileNotFoundException: /fileserver/bin/queries/data1.sql (No such file or directory)
------- SCRIPT ERROR INFO -------
Script execution failed.
error message: javax.script.ScriptException: java.io.FileNotFoundException: /fileserver/bin/queries/data1.sql (No such file or directory) (java.io.FileNotFoundException)
script arguments:
file: ...
line: def query1 = new File('/fileserver/bin/queries/data1.sql').text

May I ask how I could fix this? Thank you!

Last edited by Johann (2022-06-17 06:59:17)

Johann

Member

Registered: 2022-05-30

Re: Groovy Scripts Reports - How to import SQL queries from .sql file

Resolved: Apparently we cannot use the Java/Groovy File class.

According to the documentation (https://reportserver.net/en/guides/scri … -to-Files/), use:

 def query = GLOBALS.services['fileService'].read('/fileserver/bin/queries/data1.sql') 

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Groovy Scripts Reports - How to import SQL queries from .sql file

Hi Johann,

yes, in order to use the internal reportserver file system you have to work with the services to read the file contents.

Thanks for posting your solution.

Regards,
Eduardo

Johann

Member

Registered: 2022-05-30

Re: Groovy Scripts Reports - How to import SQL queries from .sql file

Got it, thank you Eduardo!