MySQL Bugs: #47672: Unable to add stored procedure queries to Typed DataSet

Bug #47672	Unable to add stored procedure queries to Typed DataSet
Submitted:	28 Sep 2009 10:30	Modified:	9 Jun 2011 18:12
Reporter:	The Assimilator	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.1.2	OS:	Windows (XP Pro SP2 32-bit)
Assigned to:	Julio Casal	CPU Architecture:	Any

Description:
When attempting to add a stored procedure query to a TableAdapter on a Typed DataSet, the Visual Studio wizard simply disappears after selecting the procedure to use (presumably this is being caused because the provider is throwing an unhandled exception).

Additionally, it appears that the stored procedure selected is actually called, which means that whatever the procedure does (insert records, delete entire production database, etc.) will be performed on the database. This is not a good idea!

How to repeat:
1. Create a Typed DataSet and add a TableAdapter to it.
2. Right-click the TableAdapter and select "Add Query". In the wizard that appears choose "Use existing stored procedure" and click "Next >".
3. On the next screen, select a stored procedure from the drop-down list. As soon as a selection is made, the wizard disappears with no error dialog.

Suggested fix:
Add error logging to the MySQL Visual Studio integration component so that when an unhandled exception is thrown, it's written somewhere instead of being lost. Even better, improve the provider's error handling so that it catches and throws the appropriate exceptions instead of relying on Visual Studio to do this.

MSSQL has a "set fmtonly" option that allows the server to only return metadata (i.e. columns) when a procedure is called, which is used by Visual Studio to get the column list without modifying the database. If MySQL has an equivalent construct this should be used - if not, a simple workaround would be to use

start transaction;
call procedure_name ( );
rollback;

instead of just invoking the procedure.

Also, maybe you should consider adding a new bug category: "Connector/NET Visual Studio integration".

Workaround:

Instead of "Use existing stored procedure", choose "Use SQL statements".
On the next page of the wizard, select the most appropriate statement type.
Finally, under "What data should the table load?" type in the name of your stored procedure in this format:

`databasename`.`procedurename`

Once your query has been added, you simply need to select it in Visual Studio and change the "CommandType" to "StoredProcedure".

Dropping to S3 as there is a workaround.

Hi Assimilator and thanks for your report.

I am unable to repeat the problem... Dragged table from server explorer to xsd, added SP via Add/Query ... everything works as expected.

Now, it could be something on privileges or something in your connection string, I do not know as you have not posted it.

As for other remarks, let me study them more and reply later.

Env: c/NET 6.1.2, VS2008Pro on W2K8SEx64, MySQL server 5.1.31x64 on remote OpenSolaris host. Connection string: server=**;user id=**;persist security info=True;database=**;allow user variables=True;convert zero datetime=True;functions return string=True;use affected rows=True;logging=True;pooling=False

"Use Procedure Bodies" has default value of TRUE.

> MSSQL has a "set fmtonly" option that allows the server to only return metadata (i.e. columns) when a procedure is called, which is used by Visual Studio to get the column list without modifying the database. If MySQL has an equivalent construct this should be used - if not, a simple workaround would be to use

start transaction;
call procedure_name ( );
rollback;

instead of just invoking the procedure.

MySQL has this functionality but it depends on various parameters (as do in MSSQL I suppose) like:
  Can user actually connect to "mysql" database on server?
  Is MySQL server I__S capable
and so on... See documentation on how this works.

> and let me know how you go about debugging the Connector/NET integration with Visual Studio? Thanks!

Me? I don't do that... I'd too like to see a manual entry on how to build VS integration libs and replace ones that are already installed. I guess I'd just have to play around with it more.

> Also, maybe you should consider adding a new bug category: "Connector/NET Visual Studio integration".

No, we abandoned this approach as VS integration is now integral part of connector.

As for contact, Reggie is the one to decide and he's on vacation. Will try to remember to ping him regarding your request when he comes back.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Assigning Wlad for further checking.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.