Using macro inside SQL queries

achint_kumarachint_kumar Member Posts: 16 Contributor II
edited April 2020 in Help
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?
Tagged:

Best Answers

  • mschmitzmschmitz Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,233 RM Data Scientist
    Solution Accepted
    Hi,
    eval() can only be used in the expression parser. It should just work without.

    Best,
    Martin
    - Head of Data Science Services at RapidMiner -
    Dortmund, Germany
    achint_kumar
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 788 Unicorn
    Solution Accepted
    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
    mschmitzMarcoBarradas
Sign In or Register to comment.