Escaping single quotes in a workflow variable when using it in SQL Execute

Escaping single quotes in a workflow variable when using it in SQL Execute

book

Article ID: KB0082396

calendar_today

Updated On:

Products Versions
Spotfire Data Science 6.x

Description

Escaping single quotes in a workflow variable when using it in SQL Execute

Assume there is a workflow variable defined as:
@var_def=var_def in ('1234','5678')

so that you can use it in a Row Filter operator as a boolean expression - when its value gets replaced by "var_def in ('1234','5678')").

If you need to insert the value of this variable into another table using a SQL Execute operator (let's say for logging purposes), it will be evaluated as a boolean expression instead of being taken as a string.

Issue/Introduction

Escaping single quotes in a workflow variable when using it in SQL Execute

Resolution

In order to take the variable value as a text, enclose it into double dollar signs in the SQL Execute this way:

insert into my_table values ($$@var_def$$)

which inserted (var_def in ('1234','5678')) into my_table instead of evaluating this expression into a boolean value.
(Replace my_table with whatever table name you need.)