Use variable in SQL query
Use variable in SQL query
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
Hello.
You could use variables in your query like in other components.
If the type of column is char you should use quotation marks:
Also you could add all where statement from variable:
the variable has next value "where id = 3"
Thank you,
You could use variables in your query like in other components.
Code: Select all
select * from table where id = {IntVariable}
Code: Select all
select * from table where id = '{StringVariable}'
Code: Select all
select * from table {WhereVariable}
Thank you,
Re: Use variable in SQL query
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 ?
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
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.
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
Hi Vladimir,
Please find attached a simple test template using a variable in the SQL query.
Thanks for your help.
Please find attached a simple test template using a variable in the SQL query.
Thanks for your help.
- Attachments
-
- TestVariables.mrt
- (4.31 KiB) Downloaded 278 times
Re: Use variable in SQL query
Hello.
Sorry, but now we support string variable only.
Please, change its type to string.
Thank you.
Sorry, but now we support string variable only.
Please, change its type to string.
Thank you.
Re: Use variable in SQL query
Ok, thanks. Will the "int" variable type be supported in a future release of Reports.JS ?
Re: Use variable in SQL query
Hello.
We are working on it now and do the best to add it in our next prerelease build on December 11.
Thank you.
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
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
Thanks
Joanne
Re: Use variable in SQL query
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.
We had made an improveemnt. The variables should work right.
How do you use the variable? Could you send us a sample?
Thank you.