call to stored procedure from dataadapter.fill goes on forever

[Shyam Arjarapu]

Hello Everyone,

I have a weird issue with ASP.NET calling stored procedure on MySQL. call to this procedure on MySQL Server is fine. However, the same call from dataadapter .fill method hangs and runs forever. Any and all further request to open a connection fails and I need to restart the MySQL server.

MySQL Procedure:

create procedure usp_temp() 
begin 
  select quote_id, symbol from tb_quotes limit 0,3; 
end

call to above procedure from MySQL Server returns some results

mysql> call usp_temp();
+----------+------------+
| quote_id | symbol     |
+----------+------------+
|        1 | 3MIN       |
|        2 | AARTIDRUGS |
|        3 | AARTIIND   |
+----------+------------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

however, my .net could [edit:code] hangs at .Fill() method call

        Dim sConn As String
        Dim ods As New DataSet
        Dim oConn As MySqlConnection = Nothing
        Dim oCmd As MySqlCommand = Nothing
        Dim oDA As MySqlDataAdapter = Nothing

        sConn = ConfigurationManager.ConnectionStrings.Item(0).ConnectionString
        Try
            oConn = New MySqlConnection(sConn)
            oCmd = oConn.CreateCommand()
            oCmd.CommandType = CommandType.StoredProcedure
            oCmd.CommandText = "usp_temp" 

'NOTE: If I have select quote_id, symbol from tb_quotes limit 0,3; 
'as the command text and commadtype as text then everything goes smooth
            
            oDA = New MySqlDataAdapter()
            oDA.SelectCommand = oCmd
            oConn.Open()

            oDA.Fill(ods, "tb_indices") '<-- Hangs here forever.

        Catch ex As Exception
            Throw
        Finally
            If Not oCmd Is Nothing Then
                oCmd.Dispose()
            End If
            If Not oConn Is Nothing Then
                oConn.Close()
                oConn.Dispose()
            End If
        End Try

        GridView1.DataSource = ods
        GridView1.DataBind()

I am using

MySQL Server: 5.0.24a-community-nt-log
OS: Windows 2003 Server
VS IDE: Visual Studio .NET 2005
Framework Version: .NET 2.0 Framework
Driver: MySQL Connector Net 1.0.8 , added as a reference from C:\Program Files\MySQL\MySQL Connector Net 1.0.8\Binaries\.NET 2.0\MySql.Data.dll

Does anyone of you have come across such problems before or am I missing something?



Edited 1 time(s). Last edit at 10/25/2006 09:01PM by Shyam Arjarapu.