In my workflow, I will need to execute a DB stored procedure from Knime. Unfortunately Knime does not directly support it, so I have to develop Java Snippet code to make DB connections and make the procedure call. Then I have the problem of storing DB password: I certainly don't want to store it in the Java code as plain text. I can see that NodeModel.getCredentialsProvider() provides accessor to credentials but NodeModel is inaccessible from Java Snippet.
A thought for a kinky workaround: put a trigger on a special table that executes your stored procedure on insert. This same table can keep the parameters to the SP and be updated with the result.
Kinky: yes. But it might alleviate you of having to do weird stuff from a snippet.
Thanks for your help. This would work if I have control over the database but I am only able to call the procedure. I am relying on our internal support to develop customerized node and I hope I will have a solution soon, but in general I think it will be useful if Knime can execute stored procedures and also provide a way to retrieve workflow credentials in Java Snippet in future releases.
@hellolakisha , I am having the same problem calling a Stored Procedure within KNIME. I was wondering if you ever solved your problem and were willing to share your customized Java Snippet code to call the procedure.
you can call stored procedures with the Database Reader node as described here. However this only works if do not want to return the result of the procedure.
attached you can find an example of a workflow that creates and calls a stored procedure within MySQL which can be adapted to work also with Oracle. The workflow uses the Database Reader node with the option "Run SQL query only during execute, skips configure" enabled (which is the default) to call stored procedures and the Database Executor node to create new ones.
Hello Arun,
have a look at the workflow that I have attached to my previous answer. It contains an example of a stored procedure that returns the procedure result as variable.
Bye
Tobias
Hi @Nuke_Attokurov , For the purposes of demonstration, I used using the postgres tutorial here:
which contains a stored procedure named “transfer” that subtracts an amount from one row in a table to another.
The simplified KNIME workflow to call the stored procedure was the same as for many other databases and simply connected, then utilises the DB SQL Executor node to call the stored proc followed by a DB Query Reader to read the result of a table:
I didn’t have to do anything special to make this work, so if you are having specific problems with calling a postgres stored procedure, please post more info about what it is you are wanting to do.
