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

DerekJonesDerekJones Member Posts: 1 Learner III
edited November 2018 in Help

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:

Answers

  • yyhuangyyhuang Administrator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 364 RM Data Scientist

    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.

     

     

    SQL.PNG 28.7K
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn

    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_patilbhupendra_patil Employee, Member Posts: 168 RM Data Scientist

     

    your simplest option may be using the "Read Database" operator and use use "call <procedurename(params)>"

     

    For example if my procedure looks like below

    procedure.png

    To use from Rapidminer "Read database"  you can modify the sql to look something like this

    call procedure.png

  • chakravarthy_rachakravarthy_ra Member Posts: 6 Contributor II

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

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn

    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

Sign In or Register to comment.