Page 1 of 2
Use variable in SQL query
Posted: Thu Dec 03, 2015 1:31 pm
by Joanne
How to use variable in SQL query ? on the variables panel for Reports.JS designer, the option to "Allow using as SQL parameter" is not present, and when trying to use the variable in a query, the report is not rendered...
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 6:50 am
by HighAley
Hello.
You could use variables in your query like in other components.
Code: Select all
select * from table where id = {IntVariable}
If the type of column is char you should use quotation marks:
Code: Select all
select * from table where id = '{StringVariable}'
Also you could add all where statement from variable:
Code: Select all
select * from table {WhereVariable}
the variable has next value "where id = 3"
Thank you,
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 9:04 am
by Joanne
Hi, thanks for the reply. But I get the error "ER_PARSE_ERROR: 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 '}' at line 1 Error: Report cannot be rendered!" when using variables in a query.
I am using the latest build of Report.JS (2015.3), in the windows designer, created a variable of type int (name empId) with value 1 and my data source query is like "select * from tb_employee where id = {empId}".
Am i missing something ?
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 11:51 am
by Vladimir
Hello Joanne,
We tested the variables in the SQL query on the release 2015.3, works correctly.
Please send us a report template for testing, maybe we missed some nuances.
Thank you.
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 12:09 pm
by Joanne
Hi Vladimir,
Please find attached a simple test template using a variable in the SQL query.
Thanks for your help.
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 1:36 pm
by HighAley
Hello.
Sorry, but now we support string variable only.
Please, change its type to string.
Thank you.
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 1:39 pm
by Joanne
Ok, thanks. Will the "int" variable type be supported in a future release of Reports.JS ?
Re: Use variable in SQL query
Posted: Fri Dec 04, 2015 1:52 pm
by HighAley
Hello.
We are working on it now and do the best to add it in our next prerelease build on December 11.
Thank you.
Re: Use variable in SQL query
Posted: Wed Jan 13, 2016 7:58 am
by Joanne
Hi, any update regarding this topic (I downloaded the latest pre-release build [JS_2016.01.08_Trial.zip]), but it seems that variables of type 'int' are still not supported when used in SQL queries...
Thanks
Joanne
Re: Use variable in SQL query
Posted: Wed Jan 13, 2016 2:53 pm
by HighAley
Hello, Joanne.
We had made an improveemnt. The variables should work right.
How do you use the variable? Could you send us a sample?
Thank you.