MySQL Bugs: #39252: Error viewing stored procedure info in server explorer

Bug #39252	Error viewing stored procedure info in server explorer
Submitted:	4 Sep 2008 18:35	Modified:	10 Oct 2008 10:40
Reporter:	Chris Robison	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.3	OS:	Windows (Vista Enterprise x86)
Assigned to:		CPU Architecture:	Any
Tags:	Parameters, Server Explorer, stored procedures

Description:
When I add a data connection to the server explorer (to the latest MySQL 5.0 community edition server) in Visual Studio 2008 Team, I would like to view the parameters available in each stored procedure. The moment I try to expand the node of a stored procedure that has parameters (this error doesn't appear on stored procedures that don't have parameters), I get the following error:

Column 'ROUTINE_CATALOG' does not belong to table Procedure Parameters.

How to repeat:
1. Create a data connection in the server explorer.
2. Expand that node so you see Tables, Views, Stored Procedures, etc.
3. Expand the Stored Procedures nodes
4. Try and expand one of the stored procedures that has parameters the error will then come up.

Suggested fix:
There is no work around that I have found for this. This might be the cause of some of the problems people are running into when trying to use stored procedures in Visual Studio datasets.

Error screen shot

Attachment: routine_catalog error.jpg (image/jpeg, text), 36.85 KiB.

Hi Chris and thanks for your report.

If I'm not mistaken, problem here is that 5.x servers are matched against tables that exist only in MySQL 6.x server:

24.18. Other INFORMATION_SCHEMA Tables
We intend to implement additional INFORMATION_SCHEMA tables. In particular, we acknowledge the need for the PARAMETERS and REFERENTIAL_CONSTRAINTS tables. (REFERENTIAL_CONSTRAINTS is implemented in MySQL 5.1, and PARAMETERS is implemented in MySQL 6.0.)

Thus, verified as described and has nothing to do with particular version of VS or OS.

Looking at general server log I don't see the call to "non-existent" table so my guess is bug is in parsing code.

I fixed this in my rewritten DDEX provider for 5.3 but will backport the fix to 5.2.4

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53373

fixed in 5.2.4

So is there a way to get read only access to the source tree so that I can get those changes and build them myself?

Sure. Check out svn.mysql.com

An entry was added to the 5.2.4 changelog:

When a data connection was created in the server explorer of Visual Studio 2008 Team, an error was generated when trying to expand stored procedures that had parameters.

This did fix the server explorer bug and the ability to look at parameters in it. What still doesn't work at this point is when you right-click on a TableAdapter and say Add -> Query -> Use Existing Stored Procedures. When you select a stored procedure, that window bombs out with no error message.

The initial setup of a table adapter that uses stored procedures works fine now. The window where you setup the Select, update, delete, and insert procedures works great, the parameters now show up. So, I guess I'm not seeing the difference in what that window does as compared to the Add Query window. They should be resolving stored procedure parameters the same shouldn't they?

(BTW, I not sure weather I was supposed to reopen this bug or not)

Okay, so I have an error message for you, I compiled the connector into debug and then attached to the process, so here is the error that appears when I try to select a stored procedure from the list in the Add Query. 

{"Column 'IS_RESULT' does not belong to table Procedure Parameters."}

IS_RESULT Exception

Attachment: IS_ResultError.jpg (image/jpeg, text), 217.05 KiB.

Hi Chris.

Normally I would have told you to open new report instead of reopening this one but, judging from error you get, it seems that "parsing for 6.x" tables is still in effect.

Reggie, can you please recheck.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53643

fixed in 5.2.4

I just downloaded the revision 1410 built the MySql.VisualStudio.dll installed it and gave it a try. 

'''I found the following:'''

 * When going to the Server explorer and trying to expand a Procedure I still get the : Column ROUTINE_TYPE does not belong to table procedure parameters

 * When adding a table adapter, using existing stored procedures I can select the stored procedure with parameters that i have, doesn't crash like it used to, BUT when getting to the last page of the wizard i get an error saying : Column ROUTINE_TYPE does not belong to table procedure parameters

So for me its far from over, anyone else tried this?

Im running VS 2008
Mysql 5.1 and 5.0
OS WinXP

Best regards!!

Error window I get in the wizard when creating a tableadapter on VS2008

Attachment: errorwindow.jpg (image/jpeg, text), 53.64 KiB.

Did you also build the mysql.data assembly?  I added the routine_type column to the parameters schema that is returned by GetSchema so you would need to also have the latest build of mysql.data installed in the gac and registered in your machine.config

When I add a new dataset, I still get the following problem in the picture. The select statement now works, which is good, but for some reason the Update and Delete statements don't generate.

Here are the final results of configuring a brand new data set using existing stored procedures.

Attachment: NewDataSet.jpg (image/jpeg, text), 45.87 KiB.

I've had lots of trouble getting those to work reliably.  I'll take a look today

No, I didn't built mysql.data, i'll do it nowm, give it a try and tell you what happened

I'm having trouble for testing this.

I cannot sign the dll correctly, i cannot remember where to put the 79A115C9-B133- 4891-9E7B-242509DAD272 in the whole sn - gactutil process, can anyone point me to a how-to or something pls?

Thanks

Continued in Bug#39409

I am experiencing the same problem (although using MS Visual Studio 2005) and I keep seeing it is fixed in 5.2.4.

May I know where I cna get a copy of 5.2.4?  Thanks.

The 5.2.4 changelog entry was updated:

When a data connection was created in the server explorer of Visual Studio 2008 Team, an error was generated when trying to expand stored procedures that had parameters. 

Also, if TableAdapter was right-clicked and then Add, Query, Use Existing Stored Procedures selected, if you then attempted to select a stored procedure, the window would close and no error message would be displayed.