MS SQL AND QUERY INPUT

rloser

I am new to Dataminer and programming.  We have a MSSQL 2008 database which I need to query.
Is there some way to create an input interface which would allow me change the data-time variable which is the only thing
that changes in the query?  We use a "start-time" and "stop-time" variable.

That is:  WHERE start-time  > '2011-11-01 03:00:00' and start-time < '2011-11-01 04:45:00'

I would like to have two variables StartTime1  and StartTime2 such that I could use that as a variable instead of the having to
input the times.  We have anywhere from two to nine areas where the time is referenced.  Having a variable instead of needing to
change each entry would greatly speed up the process as I need to run queries on over six months worth of data with various start-stop times and durations.

Thanks in advance.

MariusHelf

Hi, the process below shows an example on how to use so called Macros in RapidMiner in combination with Read Database.

Set Macro defines a macro, i.e. a variable, called birthdate with a certain value. To use the macro later on, just enter the macro name into any parameter field of any operator like this: %{birthdate}.

Read Database executes a query:

SELECT *
FROM `dimlead` WHERE birthdate < ?

Please not the questionmark: it indicates, that this statement is a prepared statement. That means, that any questionmarks will be replaced by the operator during execution with certain values. The values can be specified if you enable the "prepare statement" parameter and then add an entry to the "parameters". There you specify VARCHAR as type and as value "%{birthdate}".

When you execute this operator, two things will happen:
1. %{birthdate} is replaced by '1954-01-01'
2. The questionmark in the SQL statement is replaced by '1954-01-01'

Even though prepared statements may seem a bit complicated, it's quite easy in reality, and they prevent nasty things like SQL injection etc.

Hope this helps! If you have any question left, please ask.

Best, Marius

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.2.006">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.2.006" expanded="true" name="Process">
    <process expanded="true" height="670" width="727">
      <operator activated="true" class="set_macro" compatibility="5.2.006" expanded="true" height="60" name="Set Macro" width="90" x="45" y="30">
        <parameter key="macro" value="birthdate"/>
        <parameter key="value" value="1954-01-01"/>
      </operator>
      <operator activated="true" class="read_database" compatibility="5.2.006" expanded="true" height="60" name="Read Database" width="90" x="179" y="30">
        <parameter key="connection" value="cecile_test"/>
        <parameter key="query" value="SELECT *&#10;FROM `dimlead` WHERE birthdate &lt; ?"/>
        <parameter key="table_name" value="dimlead"/>
        <parameter key="prepare_statement" value="true"/>
        <enumeration key="parameters">
          <parameter key="parameter" value="VARCHAR.%{birthdate}"/>
        </enumeration>
      </operator>
      <connect from_op="Read Database" from_port="output" 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>