How to retrieve SQL data using a stored procedure

DerekJones

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

yyhuang

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.

 

 

BalazsBarany

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

bhupendra_patil

 

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

chakravarthy_ra

How do we make that 200 a parameter that can be keyed by user ? Will it prompt user for value .

BalazsBarany

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