| Bug #35938 | Problem using MySqlCommand and Procedures | ||
|---|---|---|---|
| Submitted: | 9 Apr 2008 16:38 | Modified: | 23 Apr 2008 15:23 |
| Reporter: | Dayvison Pellegrina | ||
| Status: | Unsupported | ||
| Category: | Connector/Net | Severity: | S3 (Non-critical) |
| Version: | 5.0.8.1 | OS: | Microsoft Windows |
| Assigned to: | Target Version: | ||
[10 Apr 2008 8:38]
Tonci Grgin
Hi Dayvison and thanks for your report. Can you make absolutely sure the user name you connect with is allowed to execute that SP? Can you please try with root account too and inform me of result.
[15 Apr 2008 19:32]
Dayvison Pellegrina
Thanks for your feedback,
I am sure that the permissions are appropriate. In some tests I've defined the
following administrator permissions in my user:
Select_priv Insert_priv Update_priv Delete_priv
Create_priv Drop_priv Reload_priv Shutdown_priv
Process_priv File_priv Grant_priv
References_priv Index_priv Alter_priv Show_db_priv
Super_priv Create_tmp_table_priv Lock_tables_priv
Execute_priv Repl_slave_priv Repl_client_priv
Create_view_priv Show_view_priv Create_routine_priv
Alter_routine_priv Create_user_priv
Then I've reloaded the application. With the "procedure bodies = false" directive the
application did not return anything. Without it, the error bellow ocurred.
**********************************************************************
Server Error in '/ASPNET' Application.
Parameter '?login' not found in the collection.
Description: An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and where it
originated in the code.
Exception Details: System.ArgumentException: Parameter '?login' not found in the
collection.
Source Error:
Line 52:
Line 53:
Line 54: using (MySqlDataReader myDR = myCmd.ExecuteReader())
Line 55: {
Line 56: GridView1.DataSource = myDR;
Source File: e:\home\dayvison\Web\ASPNET\mysql_procedure.aspx Line: 54
Stack Trace:
[ArgumentException: Parameter '?login' not found in the collection.]
MySql.Data.MySqlClient.MySqlParameterCollection.GetParameter(String parameterName)
+275
MySql.Data.MySqlClient.MySqlParameterCollection.get_Item(String name) +8
MySql.Data.MySqlClient.StoredProcedure.Resolve() +530
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +510
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +6
ASP.mysql_procedure_aspx.Button1_Click(Object sender, EventArgs e) in
e:\home\dayvison\Web\ASPNET\mysql_procedure.aspx:54
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String
eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean
includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET
Version:2.0.50727.1433
**********************************************************************
What is the "procedure bodies = false" directive finality?
Has any further hint that can help me?
Thanks.
[23 Apr 2008 13:56]
Tonci Grgin
Hi Dayvison. I do owe you an answer to "What is the "procedure bodies = false" directive finality?". The only place where connector can fetch SP info is mysql.proc table. Above option tells c/NET your account has privilege to read from mysql.proc table. You should check this from command line client like this: mysql -uUser_ID -p -hYour_host mysql mysql> select * from proc; This problem of yours sure seems privilege related but I need to test it properly before I'm sure.
[23 Apr 2008 15:23]
Tonci Grgin
Dayvison, according to changelog this should be fixed in c/NET 5.1.3 and later. Please test and inform me of result. This is a duplicate of Bug#29098.
Description: Hello everybody! I am having a problem when using procedures that access views using MySqlCommand. When I use this process there's no error messages, but the procedure does not return values to my GridView. I believe that problem can be in version 5.0.8.1, because the problem does not occur in version 5.2.1 . Important note: When I don't use the directive “use procedure bodies = false” the error below occurs : *************************************************************** Server Error in '/exemplos' Application. -------------------------------------------------------------------------------- Data is Null. This method or property cannot be called on Null values. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. Source Error: Line 33: Line 34: Line 35: using (MySqlDataReader myDR = myCmd.ExecuteReader()) Line 36: { Line 37: GridView1.DataSource = myDR; Source File: c:\Dayvison\projetos\exemplos\procedure_mysql.aspx Line: 35 Stack Trace: [SqlNullValueException: Data is Null. This method or property cannot be called on Null values.] MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull) +386 MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 column) +38 MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +354 [InvalidOperationException: Unable to retrieve stored procedure metadata. Either grant SELECTprivilege to mysql.proc for this user or use "use procedure bodies=false" with your connection string.] MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +413 MySql.Data.MySqlClient.ISSchemaProvider.GetProcedureParameters(String[] restrictions, DataTable routines) +502 MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName) +227 MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName) +35 MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName) +102 MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName) +98 MySql.Data.MySqlClient.StoredProcedure.Resolve() +148 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +518 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +6 ASP.procedure_mysql_aspx.Button1_Click(Object sender, EventArgs e) in c:\Dayvison\projetos\exemplos\procedure_mysql.aspx:35 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 *************************************************************** What is the purpose of “use procedure bodies” diretive? Thank you for your attention . Best regards, How to repeat: Web: Windows 2003 Server IIS6 2.0/3.5 .Net Framework DataBase: Linux RedHat 5 - MySQL 5.0.41 Example Table: ********************************************* CREATE TABLE `tbProcedure` ( `id` int(11) NOT NULL auto_increment, `texto1` varchar(100) collate latin1_general_ci NOT NULL, `texto2` text collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ; ********************************************* Example View: ********************************************* CREATE VIEW `vw_tbProcedure` AS select `tbProcedure`.`id` AS `id`,`tbProcedure`.`texto1` AS `texto1`,`tbProcedure`.`texto2` AS `texto2` from `tbProcedure` where (1 = 1); ********************************************* Example Proc: ********************************************* CREATE PROCEDURE `SP_Login`(IN login varchar(100), IN senha text) BEGIN select * from vw_tbProcedure where texto1 = login and texto2 = senha; END ********************************************* Example ASPX file : ********************************************* <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="MySql.Data.MySqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> protected void Button1_Click(object sender, EventArgs e) { using (MySqlConnection myConn = new MySqlConnection()) { //myConn.ConnectionString = "Data Source=****;User Id=****;Password=****;Database=*****;pooling=false;"; myConn.ConnectionString = "Data Source=****;User Id=****;Password=****;Database=*****;pooling=false;use procedure bodies=false"; myConn.Open(); using(MySqlCommand myCmd = new MySqlCommand()) { myCmd.Connection = myConn; myCmd.CommandType = CommandType.StoredProcedure; myCmd.CommandText = procTextBox.Text; myCmd.Parameters.AddWithValue(parTextBox1.Text, valTextBox1.Text); myCmd.Parameters.AddWithValue(parTextBox2.Text, valTextBox2.Text); using (MySqlDataReader myDR = myCmd.ExecuteReader()) { GridView1.DataSource = myDR; GridView1.DataBind(); } } } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <span style="font-size: 10pt; font-family: Verdana"><strong>Insira os dados abaixo:</strong><br /> <br /> Procedure: </span> <asp:TextBox ID="procTextBox" runat="server">SP_Login</asp:TextBox><br /> <span style="font-size: 10pt; font-family: Verdana">Parâmetro 01: <asp:TextBox ID="parTextBox1" runat="server">@Usuario</asp:TextBox> Valor: <asp:TextBox ID="valTextBox1" runat="server">teste</asp:TextBox><br /> Parâmetro 02: <asp:TextBox ID="parTextBox2" runat="server">@Senha</asp:TextBox> Valor: <asp:TextBox ID="valTextBox2" runat="server">teste</asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Enviar" /><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </span> </div> </form> </body> </html> ********************************************* thanks again !