How to use a JOB variable in an SQL query? - VisualCron - Forum

Community forum

hansvl
2021-09-10T11:46:28Z
After setting a value to the JOB variable CUSTOMER_ID, I try use this JOB variable in a SQL QUERY.

select *
from business.customers
where customer_id = (JOB(Active,Variable.CUSTOMER_ID)}

The run job result in the next error message:
Exception in Task: 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 'TranslateVariables->Unhandled error: Index was outside the bounds of the array. ' at line 1

How can I pass a (JOB) variable to an SQL query?
thomas
2021-09-10T14:36:18Z
Your first bracket is wrong:

(JOB(Active,Variable.CUSTOMER_ID)}

should be:

{JOB(Active,Variable.CUSTOMER_ID)}

Generally speaking I find it easier to declare variables, and use those variables in the query. An additional advantage is that you avoid implicit type conversion. In your case the MySql engine has to convert a string to int, and this can cause performance issues in some cases. This is how it would look like in SQL Server (I don't know the syntax for MySql)

DECLARE @customerId AS INT = CAST({JOB(Active,Variable.CUSTOMER_ID)} AS INT);

select *
from business.customers
where customer_id = @customerId;
Support
2021-10-12T18:31:58Z
I hope thomas reply solved your issue hans, if not, let us know.
Michael
Support
http://www.visualcron.com 

Please like  VisualCron on facebook!
Scroll to Top