Returning result sets from stored procedures using MySQLDirect .NET 3.00

Returning result sets from stored procedures using MySQLDirect .NET 3.00

Postby 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
wade0t
 
Posts: 1
Joined: Fri 29 Jul 2005 17:43

Postby 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();
      }
    }
Serious
 

Returning result sets from stored procedures using MySQLDirect .NET 3.00

Postby 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
jk106
 

Postby 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.
Serious
 

Postby 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.
khallgren
 
Posts: 2
Joined: Tue 17 Jan 2006 18:23

Postby 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.
Serious
 

Postby khallgren » Wed 18 Jan 2006 15:07

I was running version 2.8.

I updated to 3.2 and the problem was corrected.

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


Return to dotConnect for MySQL