Page 1 of 1
Returning result sets from stored procedures using MySQLDirect .NET 3.00
Posted: Fri 29 Jul 2005 18:07
by wade0t
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
Posted: Mon 01 Aug 2005 06:50
by Serious
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();
}
}
Returning result sets from stored procedures using MySQLDirect .NET 3.00
Posted: Tue 02 Aug 2005 11:10
by jk106
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
Posted: Tue 02 Aug 2005 12:17
by Serious
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.
Posted: Tue 17 Jan 2006 18:25
by khallgren
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.
Posted: Wed 18 Jan 2006 09:21
by Serious
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.
Posted: Wed 18 Jan 2006 15:07
by khallgren
I was running version 2.8.
I updated to 3.2 and the problem was corrected.
Thanks.