Unable to use macros in Read Database operator

christos_karras

I'm trying to use macros in the Read Database operator but they are not interpreted and it results in an error. I tried two different cases:
1. Dynamic SQL, with macros directly in the SQL query
2. Prepared statements, with macros used in the parameters

I need both options to work for different queries, but none of them work.

I found a few old discussions that seem to say what I'm trying to do should work, but I was not able to make it work:
* https://community.rapidminer.com/discussion/19000/query-inputs-using-set-macros
* https://community.rapidminer.com/discussion/6901/rm5-use-macros-in-sql-statements-iterate-over-tables
* https://community.rapidminer.com/discussion/17994/ms-sql-and-query-input

I tested with RapidMiner 9.5.001 and 9.6 beta

Example error log, where we see it tried to execute the query without replacing the macro by its value:

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.5.001" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="set_macro" compatibility="9.5.001" expanded="true" height="68" name="Set Macro (2)" width="90" x="112" y="85">
        <parameter key="macro" value="query_start_date_time"/>
        <parameter key="value" value="20140701 00:00:00.000"/>
      </operator>
      <operator activated="false" class="jdbc_connectors:read_database" compatibility="9.5.001" expanded="true" height="82" name="Read Database" width="90" x="246" y="34">
        <parameter key="define_connection" value="repository"/>
        <parameter key="connection_entry" value="/Connections/TestConnection"/>
        <parameter key="database_system" value="MySQL"/>
        <parameter key="define_query" value="query"/>
        <parameter key="query" value="SET NOCOUNT ON&#10;DECLARE @StartDate DateTime&#10;DECLARE @EndDate DateTime&#10;SET @StartDate = ?&#10;SET @EndDate = ?&#10;SELECT @StartDate AS StartDate, @EndDate AS EndDate&#10;"/>
        <parameter key="use_default_schema" value="true"/>
        <parameter key="prepare_statement" value="true"/>
        <enumeration key="parameters">
          <parameter key="parameter" value="VARCHAR.${query_start_date_time}"/>
          <parameter key="parameter" value="VARCHAR.20200205 23:59:59\.000"/>
        </enumeration>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <operator activated="true" class="jdbc_connectors:read_database" compatibility="9.5.001" expanded="true" height="82" name="Read Database (2)" width="90" x="246" y="187">
        <parameter key="define_connection" value="repository"/>
        <parameter key="connection_entry" value="/Connections/TestConnection"/>
        <parameter key="database_system" value="MySQL"/>
        <parameter key="define_query" value="query"/>
        <parameter key="query" value="SET NOCOUNT ON&#10;DECLARE @StartDate DateTime&#10;DECLARE @EndDate DateTime&#10;SET @StartDate = '${query_start_date_time}'&#10;SET @EndDate = '20200205 00:00:00.000'&#10;SELECT @StartDate AS StartDate, @EndDate AS EndDate&#10;"/>
        <parameter key="use_default_schema" value="true"/>
        <parameter key="prepare_statement" value="false"/>
        <enumeration key="parameters"/>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <connect from_op="Read Database (2)" from_port="output" to_port="result 2"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
      <portSpacing port="sink_result 3" spacing="0"/>
    </process>
  </operator>
</process>
</code>Feb 6, 2020 1:10:52 PM INFO: Executing query: 'SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '${query_start_date_time}'
SET @EndDate = '20200205 00:00:00.000'
SELECT @StartDate AS StartDate, @EndDate AS EndDate'

Feb 6, 2020 1:10:52 PM SEVERE: Process failed: Database error occurred: Conversion failed when converting date and/or time from character string.
Feb 6, 2020 1:10:52 PM SEVERE: Here: 
Feb 6, 2020 1:10:52 PM SEVERE:           Process[1] (Process)
Feb 6, 2020 1:10:52 PM SEVERE:            subprocess 'Main Process'
Feb 6, 2020 1:10:52 PM SEVERE:              +- Set Macro[1] (Set Macro)
Feb 6, 2020 1:10:52 PM SEVERE:        ==>   +- Read Database - Dynamic SQL[1] (Read Database)
Feb 6, 2020 1:10:52 PM SEVERE:              +- Read Database - Prepared Statement[0] (Read Database)
</pre><div>I have confirmed that Set Macro is executed before the queries:<br><img alt="" src="https://us.v-cdn.net/6030995/uploads/editor/0r/ndruff7cp1fd.png" title="Image: https://us.v-cdn.net/6030995/uploads/editor/0r/ndruff7cp1fd.png"><br><br><br>Set Macro operator:<br><img alt="" src="https://us.v-cdn.net/6030995/uploads/editor/rs/wq194rvu8syc.png"><br></div><br>Case 1 example query (dynamic SQL):<br><img alt="" src="https://us.v-cdn.net/6030995/uploads/editor/sd/16z4qvs1mnpp.png" title="Image: https://us.v-cdn.net/6030995/uploads/editor/sd/16z4qvs1mnpp.png"><br><br>Case 2 example (prepared statement)<br><img alt="" src="https://us.v-cdn.net/6030995/uploads/editor/p7/japcohx10jxb.png"><br><img alt="" src="https://us.v-cdn.net/6030995/uploads/editor/tf/7jyv41ejubh5.png" title="Image: https://us.v-cdn.net/6030995/uploads/editor/tf/7jyv41ejubh5.png"><br></div><br><br>Process XML:<br><pre class="CodeBlock"><code>

Other notes:
- The queries work fine if I replace the reference to the macro by hard-coded values
- I confirmed that the macro was set by looking at the Context tab and setting a breakpoint before the Read Database operator
- The queries I used might seem strange and unnecessary but these are just simplified queries to obtain the simplest example reproducing the issue while giving some general idea about the actual queries.

Should macros work for this operator?

sgenzer

hi @christos_karras yes macros work just fine in SQL like you're trying to do. But I think you have a small syntax error: macro notation in RapidMiner is with a %, not a $. Hence the line should be:

SET @StartDate = '%{query_start_date_time}'

Scott

christos_karras

Thanks @sgenzer I didn't notice the mistake at first, but changing $ by % fixed the problem and I got it working for both cases.