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 20:35	Modified:	10 Oct 2008 12:40
Reporter:	Chris Robison
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	5.2.3	OS:	Microsoft Windows (Vista Enterprise x86)
Assigned to:		Target Version:
Tags:	stored procedures, Server Explorer, Parameters
Triage:	D1 (Critical)

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.