Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.
How to retrieve SQL data using a stored procedure
DerekJones
Member Posts: 1 Learner III
Hi,
I have data in a SQL server table that is only able to be extracted using a stored procedure. The data in the underlying table is stored as a binary blob.
How do I do this?
Regards,
Derek
Tagged:
0
Answers
You can run SQL scripts in rapidminer studio thru 'Execute SQL' operator.
Put your SQL codes that run a stored procedure in the query window.
The stored procedure may create a data table in your database and you can retrieve &load db table with 'Read database' operator.
Hi!
Microsoft SQL Server and most modern SQL databases support the Common Table Expression syntax. You can wrap your procedure call in a CTE and then access the result as a virtual table.
If your procedure call works like this:
CALL yourprocedure()
You should be able to use the following expression in a Read Database operator:
WITH dataset AS (
CALL yourprocedure()
)
SELECT * FROM dataset
your simplest option may be using the "Read Database" operator and use use "call <procedurename(params)>"
For example if my procedure looks like below
To use from Rapidminer "Read database" you can modify the sql to look something like this
How do we make that 200 a parameter that can be keyed by user ? Will it prompt user for value .
Hi!
The preferred solution for this is using "prepare statement" and changing the "200" in the query to a question mark ("?").
Now you have different options. The user can enter 200 directly in the Parameters window, or you can use a macro. Macros have the syntax %{macroName} and can be set in the process context, or using Set Macro or Extract Macro.
Regards,
Balázs