Options

How to retrieve SQL data using a stored procedure

DerekJonesDerekJones Member Posts: 1 Contributor I
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

  • Options
    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
  • Options
    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

  • Options
    bhupendra_patilbhupendra_patil Administrator, 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

  • Options
    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 .

  • Options
    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.