Call Stored Procedure, Read Workflow Credentials in Java Snippet

Hi,

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.

 

Any help is greatly appreciated!

 

Nathan

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.

Thanks,

Namitha

Hello,

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.

Bye,

Tobias

Hello,

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.

Bye

Tobias

1 Like

Hi Tobias,

Is there a way, that I assign knime varible the return value from store procedure?

thanks in advance for your reply.

Regards
Arun

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

1 Like