Forum Discussion

ebjm's avatar
ebjm
Regular Visitor
8 years ago

How to pass a parameter to a MySQL stored procedure from powerBI

Hi - I am using powerBI to connect to a MySQL server. We can connect fine and we can receive data fine - I am calling a stored procedure from powerBI using the following command; CALL my_sp(); My...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hey ebjm

 

Hopefully this can help you out. I wrote this reply on another thread:

 

"It turns out that this is actually possible in Power BI. I've created a walkthrough video on how to do this here.

 

The basic gist is to add an R visual that takes in your slicer selection as its value. There is an R package called RODBC that can run SQL code against your database. You can then pass your slicer value into the SQl query using some string interpolation or the "paste" function. Let me know how this works for you."

 

 

 You will need to change the connection type from SQL Server to MySQL.

 

Hope this helps,

Parker

 

ebjm's avatar
ebjm
Regular Visitor
8 years ago

Thanks but I am puzzled. Why can I pass the following from Power BI to my MySQL server?
CALL my_sp();
but I am not allowed to pass
SET @p0 = 'xyz';
CALL my_sp(@p0);

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    How are you passing it?

    • ebjm's avatar
      ebjm
      Regular Visitor
      8 years ago

      I pass it through the 'SQL statement' text field after 'Get Data'.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I'm not too familiar with MySQL but when connecting to SQL Server, Power BI will wrap the entire command in a SELECT. 

         

        That leads me to believe that 

        SELECT
           *
        FROM (CALL my_sp();
             )

         will work but the following won't:

        SELECT
   *
FROM
   (SET @p0 = 'xyz';
   CALL my_sp(@p0);
   )

        Maybe that's the reason for your troubles. But like I said in an earlier post, take a look at my video and you can get around your issue if your situation fits. It involves using an R visual to run your SP instead of the Get Data function of Power BI.

         

        Hope this helps,

        Parker