DB password in a Macro

jeanphiv

Hi

I use rapidminer 5.3 and I would like to use macro to define my url connection. For each connection I use %{dbUrl} as database url, %{dbUser} as database user and finaly %{dbPassword} as database password.
As the password is crypted, I can't see it in the gui and the xml is  <parameter key="password"value="wmozxlSaf/NIcEbFJ1ZcnUdjsnoxdWvWq7MeuIaWnCs="/>

This method works well as long as i stay in the same computer. The issue is that this method doesn't work as soon as i try to copy the process or run it on another computer or server. After some search, i think this is due to the crypted password value which seems to belong to the computer.

Is there a way to prevent the password being crypted?

Thanks for your help

Jean-Philippe

     

JEdward

Hi Jean-Philippe,

I think it's fair as it stops naughty people copying your process to their computer and then using it with access however they wish, but I do think that the ability to load a database connection file would be a nice feature to see in a later version of the ReadDatabase operator especially for cases where processes and connections need to be shared in this way. 

In the short term I'd say you can quickly make a database connection using the Execute Script operator and return the data as an example set, but this is not ideal. 
Unfortunately I don't have MySQL on my laptop at the moment or time to debug properly, but this might work (or is also just as likely to give errors). 

It's based on this example of using the Execute Script operator to generate an example set by Haddock: https://rapid-i.com/rapidforum/index.php/topic,2214.msg8755.html#msg8755
And the first example here on making an SQL connection using Groovy: http://groovy.codehaus.org/Database+features

Good luck!

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.3.015">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.3.015" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="execute_script" compatibility="5.3.015" expanded="true" height="76" name="Execute Script" width="90" x="246" y="30">
        <parameter key="script" value="import com.rapidminer.tools.Ontology;&#10;import groovy.sql.Sql&#10;&#10;def foo = 'cheese'&#10;def sql = Sql.newInstance(&quot;jdbc:mysql://localhost:3306/mydb&quot;, &quot;user&quot;,&#10;                      &quot;pswd&quot;, &quot;com.mysql.jdbc.Driver&quot;)&#10;&#10;// construct attribute set&#10;Attribute[] attributes= new Attribute[0];&#10;attributes[0] = AttributeFactory.createAttribute(&quot;Name&quot;, Ontology.STRING);&#10;&#10;MemoryExampleTable table = new MemoryExampleTable(attributes);&#10;DataRowFactory ROW_FACTORY = new DataRowFactory(0);&#10;&#10;String[] strings= new String[0];&#10;&#10;sql.eachRow(&quot;select * from FOOD where type=${foo}&quot;) {&#10; &#9;&#9;// construct example data&#10;&#9;     strings[0]=it.name.toString();&#10;&#10;      &#9;// make and add row&#10;        &#9;DataRow row = ROW_FACTORY.create(strings, attributes); &#10;        &#9;table.addDataRow(row);&#9;&#10;&#9;&#9;}&#10;&#10;ExampleSet exampleSet = table.createExampleSet();&#10;return exampleSet;"/>
      </operator>
      <connect from_op="Execute Script" from_port="output 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>