Execute Store Procedure

KNIME Analytics Platform
1

Hi ,

 I would like to execute stored procedure which is within the Oracle Package.

 The things I would like to do is :

1) Create corresponding stored procedure parameters in knime (for example : parameter 1 is 'start date', parameter 2 is 'end date',  which will be pass to stored procedure

2) dynamically construct the SQL for calling stored procedure

3) execute stored procedure in knime by corresponding node (e.g. Database SQL Executor Node)

 

I manage to do step 1 and 2, while I cannot execute the stored procedue in knime.

Note : the SQL I finally constructed as follows :

exec pkgparm.setval(P_CLASTYPID=>4, P_SDT=>to_date('20170224','yyyymmdd'), P_EDT=>to_date('20170410','yyyymmdd'))

		<p>&nbsp;</p>

		<p><strong>Above is the SQL to execute stored procedure, which takes 3 parameters.</strong></p>

		<p>It works fine if I execute in my TOAD for Oracle while<strong> it doesn&#39;t work if I place it under &quot;Database SQL Executor Node&quot; of knime.</strong></p>

		<p>Attached is my knime workflow for your reference.</p>

		<p>Appreciate if you can advice what&#39;s wrong and how to resolve. Thanks a lot !</p>

		<p>&nbsp;</p>
		</td>
	</tr>
</tbody>

 

2

Hello,

Anyone can assist regarding the above ?

Thanks very much !

3

Hi,

please have a look at this post that describes how to call stored procedures from within KNIME. This post might be also useful for you as it describes how to execute several statements within a single database node by using ;<SPACE> to separate the different statements.

Bye

Tobias

4

It’s annoying, but I’m able to call SQL stored procedures by using an R snippet.

  1. I pass the parameters I’ll use to the R snippet
  2. Build the “exec sp_someSP” statement in R and add on the passed in parameters
  3. Populate knime.out with the dataset you get back from SQL

Executing this returns a table from the R snippet.

image

Here’s my snippet:

library(RODBC)

connStr = paste("Driver=ODBC Driver 17 for SQL Server;Server=some_servername;","Database=SomeDB;uid=some_user;pwd=some_pw", sep = "")
channel = odbcDriverConnect(connection = connStr, case = "nochange", colQuote = c("[", "]"))
sql = paste("exec sp_someSP '", knime.in$"AllCombinedPositions", "', '", knime.in$"StartDateString", "', '", knime.in$"EndDateString", "'", sep = '')

df = sqlQuery(channel, sql, as.is = TRUE)
# close the connection
odbcClose(channel)

knime.out <- df
1 Like
5

The DB Query Reader doesn’t work: parsed statement is SELECT TOP 10 * FROM (EXECUTE [dwhsys].[UPDATE_REPORTING_DB]; select 1 as a
) AS "tempTable_5815650831818703960

error is Incorrect syntax near the keyword ‘EXECUTE’.

MS SQL Server Knime 4.5.2

6

As I know this is not possible in SQL Server to use SP in select directly. It is possible for functions.

7

Thanks, I know, this is why I don’t understand how the proposed solution on reader could work :slight_smile:

8

Example is done for Oracle. Anyway, DB Reader has to work for you.