You are not logged in.
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?
Offline
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
Offline
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').textwhere 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').textMay I ask how I could fix this? Thank you!
Last edited by Johann (2022-06-17 06:59:17)
Offline
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') Offline
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
Offline
Got it, thank you Eduardo!
Offline