variable expansion in SQL query endpoint

[fjavanderspek]

Goodday,

is it possible to use variables (instance-level, project-level, otherwise) in SQL query endpoint SQL statements, in an API service? And if so, can they be used as database object identifiers? (schema name, table name, etc)

Kind regards

[SarinaS]

Hi @fjavanderspek,

It is not possible to reference variables from the API node. Usually you would instead retrieve parameters and pass them to your endpoint. 

However, if you really were interested in using variables you could:
(1) Switch to creating a Python endpoint
(2) Your Python API service would need to create a remote API client to interact with your design/automation nodes remotely
(3) You could then use the regular Python functions like get_variables() to retrieve your variables in your code
(4) Finally, you could build your SQL query using your retrieved variable(s) and then create a SQL query using client.sql_query() 

Thank you,
Sarina

[fjavanderspek]

Just to make sure I understand correctly: we can't use parameters passed to APINodeClient.sql_query as database object identifiers, right? (e.g. pass a table name 'customers' and have a SQL query endpoint with a statement defined as 'SELECT * FROM ?', where we pass the table name as parameter). If we want to use dynamic database object identifiers, we should look for a way other than using SQL Query endpoints, such as the way you've suggested (using the client.sql_query); is my understanding correct?

[SarinaS]

Hi @fjavanderspek,

Indeed, it's not possible to pass the tablename/schema as parameters in a SQL Query endpoint. So indeed, your understanding is correct. 

Thanks,
Sarina