Returning result sets from stored procedures using MySQLDirect .NET 3.00

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
wade0t
Posts: 1
Joined: Fri 29 Jul 2005 17:43

Returning result sets from stored procedures using MySQLDirect .NET 3.00

Post by wade0t » Fri 29 Jul 2005 18:07

11-Jul-05 New features in MySQLDirect .NET 3.00
Returning result sets from stored procedures supported

Please provide an example of returning a result set from a stored procedure in both sql and c# .net

thank you

Serious

Post by Serious » Mon 01 Aug 2005 06:50

Here is an example:

Code: Select all

    private static void OutReader(IDataReader reader) {

      try {
        do {
          while (reader.Read()) {
            for (int i = 0; i < reader.FieldCount; ++i)
              Console.Write(reader[i].ToString() + ",");
            Console.WriteLine();
          }
          Console.WriteLine("\n");
        }
        while (reader.NextResult());
      }
      finally {
        reader.Close();
      }
    }

    [STAThread]
    static void Main(string[] args) {
      
      MySqlConnection connection = new MySqlConnection();
      connection.ConnectionString = "host=server;port=3308;database=test;user id=root;password=root;";
      MySqlCommand command = new MySqlCommand();
      command.Connection = connection;

      connection.Open();
      try {
        command.CommandText = "DROP PROCEDURE IF EXISTS test.MR";
        command.ExecuteNonQuery();

        command.CommandText = @"CREATE PROCEDURE test.MR()
BEGIN
  insert into emp(hiredate)values('');
  select * from emp;
  select * from dept;
END";
        command.ExecuteNonQuery();

        command.CommandText = "MR";
        command.CommandType = CommandType.StoredProcedure;
        OutReader(command.ExecuteReader());
      }
      catch (Exception ex) {
        Console.WriteLine(ex.GetType().Name);
        Console.WriteLine(ex.Message);
      }
      finally {
        connection.Close();
      }
    }

jk106

Returning result sets from stored procedures using MySQLDirect .NET 3.00

Post by jk106 » Tue 02 Aug 2005 11:10

I am using MySQLDirect.Net 3.0 in a web application using vb.net. I tried the code mentioned (converted to vb.net) for getting the result set, i got the following error:

MySqlExceptionPROCEDURE tsg.test can't return a result set in the given context

stored procedure tst.test contains a siple query "select * from tbl"

Please help, how to retrive result set through stored procedure in my web application

Serious

Post by Serious » Tue 02 Aug 2005 12:17

Please send us small test project if possible to reproduce the problem; it is desirable to use 'test' schema objects, otherwise include definition of your own database objects. Do not use third party components.

If it is impossible for you to create test project please send us a piece of your code where the error occurs.

khallgren
Posts: 2
Joined: Tue 17 Jan 2006 18:23

Post by khallgren » Tue 17 Jan 2006 18:25

Any follow up on this? I'm getting the same error in vb.net 8.0.

My code:

Dim objConn As MySqlConnection
Dim objCommand As MySqlCommand

Dim daTest As MySqlDataAdapter
Dim dsTest As DataSet

Dim strConn As String
Dim strSQL As String

strConn = "User Id=x;Password=x;Database=x;Host=x;Port=x;"

objConn = New MySqlConnection

objConn.ConnectionString = strConn

objConn.Open()

objCommand = New MySqlCommand

strSQL = "spGetRecords"

objCommand.Connection = objConn

objCommand.CommandText = strSQL
objCommand.CommandType = CommandType.StoredProcedure

daTest = New MySqlDataAdapter(objCommand)

dsTest = New DataSet

daTest.Fill(dsTest)

Error thrown on last line.

Serious

Post by Serious » Wed 18 Jan 2006 09:21

We tested your code on MySQLDirect .NET 3.20.7, MySQL 5.0.15, Visual Basic 2005 and found no problems with your code. We used following stored procedure

Code: Select all

PROCEDURE `test`.`spGetRecords`()
BEGIN
  select * from test.dept;
END
Please make sure you're using latest build of MySQLDirect .NET, check also if you have correct stored procedure definition.

Error you've specified can appear when using MySqlConnection.Direct=false mode, so you shouldn't use client mode when working with stored procedures. For more information refer to this property documentation.

If you cannot solve the problem please specify MySQLDirect .NET version, MySQL version, send us your stored procedure definition.

khallgren
Posts: 2
Joined: Tue 17 Jan 2006 18:23

Post by khallgren » Wed 18 Jan 2006 15:07

I was running version 2.8.

I updated to 3.2 and the problem was corrected.

Thanks.

Post Reply