SQL Query and date_now()

Mike

Hi folks,

Following problem: There is a mysql table with a Timestamp column. I want to retrieve all entries of the current day. I was quite optimistic that the following operator would do the trick:

      <operator activated="true" class="read_database" compatibility="5.3.015" expanded="true" height="60" name="DB Import" width="90" x="45" y="30">
        <parameter key="connection" value="myDBname"/>
        <parameter key="query" value="SELECT `ID`,`Timestamp`&#10;FROM `myTablename` Where`Timestamp` &gt;= ?"/>
        <parameter key="prepare_statement" value="true"/>
        <enumeration key="parameters">
          <parameter key="parameter" value="VARCHAR.%{today}"/>
        </enumeration>
      </operator>

Actually it works fine if I use a static value in the macro definition of %{today}:

    <macros>
      <macro>
        <key>today</key>
        <value>2015-01-13</value>
      </macro>
    </macros>

But it fails when I try to dynamically calculate the current date e.g. as follows:

    <macros>
      <macro>
        <key>today</key>
        <value>date_str_custom(date_now(),"yyyy-MM-dd")</value>
      </macro>
    </macros>

As a result I get all entries of the table instead.

Anyone got an idea?

Cheers, Mike.

Marco_Boeck

Hi,

you cannot use expressions in macros directly. Instead you have to add a "Generate Macro" operator. Example process which logs the current date macro to the console:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="6.3.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.3.000" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="retrieve" compatibility="6.3.000" expanded="true" height="60" name="Retrieve Iris" width="90" x="45" y="30">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="generate_macro" compatibility="6.3.000" expanded="true" height="76" name="Generate Macro" width="90" x="179" y="30">
        <list key="function_descriptions">
          <parameter key="today" value="date_str_custom(date_now(),&quot;yyyy-MM-dd&quot;)"/>
        </list>
      </operator>
      <operator activated="true" class="print_to_console" compatibility="6.3.000" expanded="true" height="76" name="Print to Console" width="90" x="313" y="30">
        <parameter key="log_value" value="%{today}"/>
      </operator>
      <connect from_op="Retrieve Iris" from_port="output" to_op="Generate Macro" to_port="through 1"/>
      <connect from_op="Generate Macro" from_port="through 1" to_op="Print to Console" to_port="through 1"/>
      <connect from_op="Print to Console" from_port="through 1" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

Regards,
Marco

Mike

Ah, ok, thanks for the quick reply.
So if there is a datatable, with very many timestamped entries, and I want to query the data for a specific day, which can be chosen dynamically, what would be the best way to do so? I'm a bit reluctant to load the whole table.
Cheers, Mike.

Marco_Boeck

Hi,

just as you did in your original post, use the macro you define somewhere before executing the "Read Database". You can set the macro with any of the macro operators. As long as it is set before the actual Database operator, you should be fine.


Regards,
Marco