How do you pass parameters to a stored proc in MySQL?
-
I've been struggling with this issue for weeks and am only about an inch closer to having a solution. I've done dozens of searches trying to find a way of passing parameters to a stored procedure in a MySQL database. At this point this is what I've got for the call in the dataset properties for the Query:
call imb_closure.getCases(?, ?, ?, ?, ?, ?);
This is, I think, the correct syntax for an ODBC connection. (I'm using ODBC 5.1, because we've got an old MySQL db.)
It's the parameters that really have me stumped. In the Dataset properties | Parameters for the 6 parameters to the stored proc I've tried all of the following syntaxes, none of which have worked:
- @BeginDate (the name of the first parameter in the SP is BeginDate)
- BeginDate
- param_1
- Parameter1
- ?
At the moment, I have the parameters all specified as "?". Doing so gives me the following error message:
===================================
Object reference not set to an instance of an object. (Microsoft SQL Server Report Designer)
------------------------------
Program Location:
at Microsoft.DataWarehouse.Controls.QueryBuilderCtl.InitQueryBuilder()
at Microsoft.DataWarehouse.Controls.QueryBuilderCtlWithToolBar.InitQueryBuilder()
at Microsoft.ReportingServices.QueryDesigners.VDTQueryDesigner.InitializeVDTQueryDesigner()
at Microsoft.ReportingServices.QueryDesigners.VDTQueryDesigner.InitializeQueryDesigner()
at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWrapper.InitializeDesigner()
at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWithGenericModeWrapper.OnEditAsTextClicked(Object sender, EventArgs e)I really hate that message because it gives me no idea at all what's wrong. What in heck is the object that its trying to use which is null. I've tried placing my head on the keyboard, in an effort to osmose it into my brain, to no avail. I really need help, please, with this because at this rate I won't get it for months to a year.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Build the call as an expression - instead of trying to pass parameters. Look here: https://stackoverflow.com/questions/16351363/ssrs-mysql-stored-procedure-with-parameters-through-odbc
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you for responding! I really appreciate it.
I've already changed it from a command type of stored procedure to text, as I've seen elsewhere that's what you're supposed to do. It is probably best if I provide screen shots. Here's what I've got when I define the Query in the Dataset properties:
Initially I had Stored Procedure selected, but as the link advised I changed it to Text. (I don't see a Command option in that Query, so I thought Text would do.)
But the problem is still how do I get the values to the stored procedure assigned dynamically in a SSRS report? This next picture is what I've got for the Dataset properties - Parameters tab:
Kindest Regards, Rod Connect with me on LinkedIn.
-
Click on the Fx button and build your expression - something like this:
="call imb_closure.getCases('" & Parameters!Parameter1.Value & "','" & Parameters!Parameter2.Value & "')"
Add all of the expected parameters in the same fashion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I am still struggling with this issue. Yesterday I was told to go to this link How do i call mysql stored procedure from ssrs Report ? This is a somewhat different approach to the problem I'm having. What I've got now for the Dataset's Properties' Query is:
- Name: GetCasesDataSet
- Data source: IMBDataSource (which is the ODBC DSN)
- Query type: Text
- Query:
EXEC ('CALL imb_closure.getCases(?, ?, ?, ?, ?, ?, ?)', @BeginDate, @EndDate, @CaseStatus, @jackson, @Backlog, @EAR, @Late) at OURMYSQLSERVER
Then for the Dataset Properties | Parameters I've got this (in part):
Clicking on the Fx button on any of the parameters does nothing. They appear to all be disabled.
If I click on the Query Designer... button on the Query tab, that takes me to the Query Designer window. If I click on the execute ("!") button, it pops up a window for me to provide values to the parameters. Entering valid values for the 7 parameters yields the following error:
0===================================
An error occurred while executing the query.
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.41-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXEC ('CALL imb_closure.getCases(?, ?, ?, ?, ?, ?, ?)', @BeginDate, @EndDate, @C' at line 1 (Microsoft SQL Server Report Designer)
===================================
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.41-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXEC ('CALL imb_closure.getCases(?, ?, ?, ?, ?, ?, ?)', @BeginDate, @EndDate, @C' at line 1 (myodbc5.dll)
------------------------------
Program Location:
Server stack trace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.<>c__DisplayClass8_0.<ExecuteActiveQueryCallback>b__0()
at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)
Kindest Regards, Rod Connect with me on LinkedIn.
-
You are making this much more difficult than it really is...you create the dataset from an expression, you do not include any parameters on the dataset because they are included in the expression:
You click the function/expression button here - which opens the following:
Now - you concatenate each parameter into the call as needed, building the call so it has the values from each parameter *hard-coded* into the result from the expression.
You do not pass the parameters to the call - so you don't need to setup parameters on the dataset. Remove all of the parameters since they are not needed and build up the expression to use the parameters as above.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Ah, OK. I believe I understand what you're saying now. Before I implement that, though, I've got to mention that one of my coworkers was with me, discussing this issue. He recommended that I try running the query from within a SSMS query window. I tried it and got an error there as well, but this time an entirely different error. The error said:
Server 'OURMYSQLSERVER' is not configured for RPC.
If this is indeed the case, then this might be at the root of my problem. I don't know enough about MySQL to be able to configure it, so I'm not asking others who have the experience, to determine if OURMYSQLSERVER is configured to allow remote procedure calls.
Kindest Regards, Rod Connect with me on LinkedIn.
- Rod at work wrote:
Ah, OK. I believe I understand what you're saying now. Before I implement that, though, I've got to mention that one of my coworkers was with me, discussing this issue. He recommended that I try running the query from within a SSMS query window. I tried it and got an error there as well, but this time an entirely different error. The error said:
Server 'OURMYSQLSERVER' is not configured for RPC.
If this is indeed the case, then this might be at the root of my problem. I don't know enough about MySQL to be able to configure it, so I'm not asking others who have the experience, to determine if OURMYSQLSERVER is configured to allow remote procedure calls.
This is trying to run the procedure through a linked server between SQL Server and MySQL. I would not do that...
From SSRS you want to connect directly to MySQL and execute the code on that system. This reduces any performance issues that would be introduced by running the procedure across a linked server.
If - however - you decide that you must use a linked server, then you need to create a stored procedure in SQL Server that then calls the code across the linked server. To do that you would most likely need to build the code dynamically - that is, pass in the parameters from SSRS to build a string that is used to execute the MySQL stored procedure.
The other option then would be to move the stored procedure in MySQL over to SQL Server and reference the tables directly (using synonyms for the linked server objects). For example: select ... from my.synonym - where my.synonym is created as: create synonym my.synonym for mysql.db.schema.object;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffery, I tried to go directly against MySQL for over a week. I was never able to do so. Finally, I found solutions on Stack Overflow saying that the way to get to that data is through Linked Server. And honestly its the only thing I've been able to do which at least lets me do a SELECT against a table. I agree that it would probably be better if I could go directly against MySQL, but running into a brick wall trying to do so, no matter how many ways I tried to do so, I just gave up.
However, you do have a really interesting idea, of developing a stored procedure in SQL Server, which calls the SP in MySQL, passing the parameters. I've been able to successfully call the SP in the MySQL linked server, from SSMS. That might be a great way for me to approach this problem.
Kindest Regards, Rod Connect with me on LinkedIn.
-
I have not had to access MySQL from SSRS - but I have accessed Intersystems Cache, Vertica and other database products. The requirements are all related to how that products ODBC/OLEDB drivers are setup and configured.
This is the same for MySQL - you must have the driver installed and configured on the SSRS server and working (validated through ODBC data sources applet). Once you have that working - you then setup/configure a shared data source in SSRS and test/validate that it is working here.
That takes care of the server - so any deployed reports can use that shared data source.
The next thing you need to do is install the driver on your local workstation and get it working and validated. Once you have that - you can then use it in VS.
Before Intersystems Cache provided a .NET managed provider - we would setup the connection using ODBC. This required an ODBC data source that was setup and configured in ODBC data sources that was then referenced within the reports dataset. Same thing on the server...
If you can access MySQL through a linked server - you can access it directly using the same connection information in SSRS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply