Using macro inside SQL queries

achint_kumar

Hi All,

I am trying to use Macros very dynamically in the SQL queries used to retrieve data from DW. I am assigning macro values from Excel as input.  
I am facing some issues in doing so. It shows me error stating "eval is not a recognized built in function name"

Please find below screenshot of process:


Kindly help resolve this, is it that we cannot use these macros inside SQL functions?

MartinLiebig

Hi,

eval() can only be used in the expression parser. It should just work without.

Best,

Martin

BalazsBarany

Hi,

a better way to do this is using the prepared statements option and specifying the parameters in the dialog. You can use the macros there and specify the data type. 
This is a much safer way of putting macros (=strings) into SQL statements, you can avoid SQL injections and SQL syntax errors with the prepareds statements.

Sometimes it's not possible to avoid using macros in SQL statements, but you should prefer the prepared statement method.
An example when you can't use that is if structural aspects of the SQL query (table and column names) are variable and specified with a macro. E. g.:

SELECT %{varColumn}
FROM %{varTable}
ORDER BY %{varSortOrder}

Of course, you need to be extra careful when doing this, e. g. validate the format and contents of your macros before executing this.

Best regards,
Balázs

MartinLiebig

Hi,

eval() can only be used in the expression parser. It should just work without.

Best,

Martin

BalazsBarany

Hi,

a better way to do this is using the prepared statements option and specifying the parameters in the dialog. You can use the macros there and specify the data type. 
This is a much safer way of putting macros (=strings) into SQL statements, you can avoid SQL injections and SQL syntax errors with the prepareds statements.

Sometimes it's not possible to avoid using macros in SQL statements, but you should prefer the prepared statement method.
An example when you can't use that is if structural aspects of the SQL query (table and column names) are variable and specified with a macro. E. g.:

SELECT %{varColumn}
FROM %{varTable}
ORDER BY %{varSortOrder}

Of course, you need to be extra careful when doing this, e. g. validate the format and contents of your macros before executing this.

Best regards,
Balázs