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
Returning result sets from stored procedures using MySQLDirect .NET 3.00
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
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
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
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.
If it is impossible for you to create test project please send us a piece of your code where the error occurs.
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.
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.
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 procedurePlease 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.
Code: Select all
PROCEDURE `test`.`spGetRecords`()
BEGIN
select * from test.dept;
END
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.