Use variable in SQL query

[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...

[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,

[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 ?

[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.

[Joanne]

Hi Vladimir,
Please find attached a simple test template using a variable in the SQL query.
Thanks for your help.

[HighAley]

Hello.

Sorry, but now we support string variable only.
Please, change its type to string.

Thank you.

[Joanne]

Ok, thanks. Will the "int" variable type be supported in a future release of Reports.JS ?

[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.

[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

[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.