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

[ebjm]

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 problem is that when I try to pass a parameter to the stored procedure like this;
SET @p0 = 'xyz';

CALL my_sp(@p0);

Power BI returns a message saying I should use an unencrypted connection. When I do it then gives the following error: "MySQL: Fatal error encountered during command execution."

So my question is - how can I pass a parameter to a MySQL stored procedure from powerBI?

I'd be grateful for any pointers / advice. Thank you.

[Anonymous]

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]

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]

How are you passing it?

[ebjm]

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

[Anonymous]

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

[nirvana_moksh]

So you can use the R code will pass the date parameter in your Stored Procedure and this will work with more than one parameters too?

[Anonymous]

Yep, you can pass as many fields into the R visual as you need. You can then reference all of them in your SQL code embedded in your R code

[nirvana_moksh]

I am going to try this tonight, does your video detail all the necessary information? Also for the RODBC connector, do we need to download that on SSMS?

[Anonymous]

The only difference between my video and your situation is that you will be passing in a Power BI parameter. To do this, you will need to create the parameter and enable load so that you can then pass it into the R Visual. As for RODBC, that's an R package that you will need to install. Other than that, you should be good to go. Feel free to reach out with questions.

Edit: Reading back through your initial post, not sure if you are passing a Power BI parameter. You will be able to pass any column or Power BI parameter using this method.