MySQL Bugs: #34359: When altering an stored procedure in visual studio parameters dissapear

Bug #34359	When altering an stored procedure in visual studio parameters dissapear
Submitted:	6 Feb 2008 19:57	Modified:	1 Mar 2008 12:00
Reporter:	Mike Hat
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.1.4	OS:	Microsoft Windows (XP)
Assigned to:		Target Version:
Tags:	stored procedures parameters visual studio 2005 net c#

Description:
When I try to modify any stored procedure that has a parameter thru the server explorer
in visual studio 2005 the parameters of the stored procedure dissapear.

Example:

I save this:

DELIMITER $$
PROCEDURE `StoredProcedure1`(payment_amount DECIMAL(6,2),payment_seller_id INT)
BEGIN
......................
END $$
DELIMITER ;

and i get this afterwards

DELIMITER $$
PROCEDURE `StoredProcedure1`()
BEGIN
......................
END $$
DELIMITER ;

I can save them when creating or modifying them, and if I check thru the query browser
they are ok, but when I display them thru the visual studio server explorer the
parameters dissapear from the declaration, not from body of the procedure

How to repeat:
1) Create a new procedure

CREATE PROCEDURE StoredProcedure2 (sarasa DECIMAL(6,2)) 
BEGIN 
SELECT '?sarasa';
END

2) save

3) create a table adapter and the method generated automatically (get for example or
fill) will ask for the correct type.

4)Open the stored procedure for tweaking it and you will find this

PROCEDURE StoredProcedure2 ()
BEGIN 
SELECT '?sarasa';
END

I've been working on this a little more, i tracked the problem to a dataset

StoredProcDescriptor.cs

Line 206
   Protected override DataTable ReadTable(DataConnectionWrapper connection, object[]
restrictions, string sort)
   {
	if (connection == null)
	    throw new ArgumentNullException("connection");
...................
	DataTable dt = conn.GetSchema("Procedures", rest);
	connection.Connection.UnlockProviderObject();
	return dt;
   }

The dataset returns 20 columns when they should be 21!

The query (line 41 same file)

        protected new const string EnumerateSqlTemplate =
            "SELECT " +
            "r.ROUTINE_CATALOG, " +
            "r.ROUTINE_SCHEMA, " +
            "r.ROUTINE_TYPE, " +
            "r.ROUTINE_NAME, " +
            "r.SPECIFIC_NAME, " +
            "r.DTD_IDENTIFIER, " +
            "r.ROUTINE_BODY, " +
            "r.ROUTINE_DEFINITION, " +
            "r.EXTERNAL_NAME, " +
            "r.EXTERNAL_LANGUAGE, " +
            "r.PARAMETER_STYLE, " +
            "r.IS_DETERMINISTIC, " +
            "r.SQL_DATA_ACCESS, " +
            "r.SQL_PATH, " +
            "r.SECURITY_TYPE, " +
            "r.CREATED, " +
            "r.LAST_ALTERED, " +
            "r.SQL_MODE, " +
            "r.ROUTINE_COMMENT, " +
            "r.`DEFINER`, " +
            "CONVERT (p.PARAM_LIST, CHAR) AS PARAM_LIST " +
            "FROM information_schema.ROUTINES r " +
            "JOIN mysql.PROC p ON r.SPECIFIC_NAME = p.SPECIFIC_NAME " +
            "AND r.ROUTINE_SCHEMA = p.DB " +
            "AND r.ROUTINE_TYPE = p.`TYPE` " +
            "WHERE r.ROUTINE_SCHEMA = {1} AND r.ROUTINE_TYPE = {2} AND r.ROUTINE_NAME =
{3}";

* Parameter 21 "CONVERT (p.PARAM_LIST, CHAR) AS PARAM_LIST " is missing, I have some
trouble with subqueries in the sql editor, so this could be related

"Select a.column,(SELECT 'a') as sarasa from a" only returns column.

I'll post updates as soon as i got some news

It seems that I needed a weekend to figure it out.

LINE #206 - File: StoredProcDescriptor.cs
-------------------------------------------------------------------
Protected override DataTable ReadTable(DataConnectionWrapper connection, object[]
restrictions, string sort)
...
...
...
DataTable dt = conn.GetSchema("Procedures", rest);
-------------------------------------------------------------------

GetSchema("Procedures", rest) returns 20 fields instead of 21, I thought that it was
using the SQL defined in the line 41 of the same file, but it wasnt. When you call it the
function returns all columns but PARAM_LIST, It must be a GetSchema error, I've seen a few
open issues related with GetSchema.

I used the sql defined in line 41 and worked fine, this is what i did:

SUGESTED FIX ( UGLY )
-------------------------

LINE #217 - File: StoredProcDescriptor.cs
-------------------------------------------------------------------
string[] rest = restrictions == null ? null : new string[restrictions.Length];
if (rest != null)
	for (int x = 0; x < rest.Length; x++)
		if (restrictions[x] != null)
			rest[x] = restrictions[x].ToString();

//DataTable dt = conn.GetSchema("Procedures", rest);
DbCommand _command = conn.CreateCommand();
_command.CommandText = string.Format(EnumerateSqlTemplate, "", "'" + rest[1].ToString() +
"'", "'PROCEDURE'", "'" + rest[2].ToString() + "'");
DbDataReader _dr = _command.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(_dr);

connection.Connection.UnlockProviderObject();
return dt;
}
-------------------------------------------------------------------

I know its ugly code, sorry, I'm sure it can be improved, I just wanted to post this fix
fast to help out :P

I'll check out GetSchema to see if i can fix it from there later on.

Best Regards
Mike Hat

Hi Mike and thanks for your efforts. I'm drowning in all possible connectors reports right
now and don't have time for proper analysis right away so I asked c/NET team leader to
take a peek into this problem.

Tonci Grgin:

No problem, just trying to help :P

I took a look at the .Net connector (mysql.data.dll) and found out that when you call
GetSchema after a few turns you get to the "Query" function, that actually does the
query.
The query only affects the "INFORMATION_SCHEMA.ROUTINES" table and never joins with the
"mysql.PROC" table. So there is no way you can get the procedure param_list.

One way could it to use the code I put in my previous message, or a better one :P. The
other way could it be to modify the Query function, but i see it a little complicated
since its used for many things. Perhaps a subselect there?

---> StringBuilder query = new StringBuilder("SELECT *, (SELECT param_list from
mysql.PROC where bla bla bla) from FROM INFORMATION_SCHEMA.");

Here's the code of the query function that returns de dataset without the param_list

Hope this helps

LINE #250 - ISSchemaProvider.cs (mysql.data proyect)
------------------------------------------------------------------------------
private DataTable Query(string table_name, string initial_where,
            string[] keys, string[] values)
        {
            StringBuilder where = new StringBuilder(initial_where);
            StringBuilder query = new StringBuilder("SELECT * FROM
INFORMATION_SCHEMA.");
            query.Append(table_name);

            if (values != null)
                for (int i = 0; i < keys.Length; i++)
                {
                    if (i >= values.Length) break;
                    if (values[i] == null || values[i] == String.Empty) continue;
                    if (where.Length > 0)
                        where.Append(" AND ");
                    where.AppendFormat(CultureInfo.InvariantCulture, "{0}='{1}'",
keys[i], values[i]);
                }

            if (where.Length > 0)
                query.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", where);

            return GetTable(query.ToString());
        }

Nicer fix :P

LINE #215 - File: StoredProcDescriptor.cs
-------------------------------------------------------------------
//DbConnection conn = //DbConnection)connection.Connection.GetLockedProviderObject();

string[] rest = restrictions == null ? null : new string[restrictions.Length];
if (rest != null)
	for (int x = 0; x < rest.Length; x++)
		if (restrictions[x] != null)
			rest[x] = restrictions[x].ToString();

//DataTable dt = conn.GetSchema("Procedures", rest);
DataTable dt = connection.ExecuteSelectTable(string.Format(EnumerateSqlTemplate, "", "'"
+ rest[1].ToString() + "'", "'PROCEDURE'", "'" + rest[2].ToString() + "'"));

//connection.Connection.UnlockProviderObject();
return dt;
}

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/42294

Fixed in 5.1.6 and 5.2.1

A note has been added to the 5.1.6, and 5.2.1 changelog: 

When altering a stored procedure within Visual Studio, the parameters to the procedure
could be lost.