MySQL Forums
Forum List  »  Connector/ODBC

unhandled error from mysql_next_result() when calling stored procedure from ODBC
Posted by: Eduardo Schnadower
Date: June 23, 2016 08:12AM

Hello everyone, I am developing a .NET website using Framework 4.0 and the ODBC connector to MySQL. (For some reason I couldn't find how to use the Connector\net with datasource objects, but that is for another post).

I am calling a stored procedure with one input parameter and two output parameters. When calling it from MySQL workbench it works as expected, but when calling it from my .net project it gives the error below.

So here is the .net code:
OdbcConnection o = new OdbcConnection(ConfigurationManager.ConnectionStrings["CervezaPro"].ToString());
o.Open(); //Se abre la conexión a base de datos
OdbcCommand oc = o.CreateCommand();
oc.CommandType = CommandType.StoredProcedure;
oc.CommandText = "{ CALL CongelarProceso(?,?,?) }";
oc.Parameters.AddWithValue("p_idRecetas", DDLReceta.SelectedValue);
OdbcParameter mensaje = new OdbcParameter("p_mensaje", OdbcType.VarChar,2000);
mensaje.Direction = ParameterDirection.Output;
OdbcParameter resultado = new OdbcParameter("p_resultado", OdbcType.Int);
resultado.Direction = ParameterDirection.Output;
oc.Parameters.Add(mensaje);
oc.Parameters.Add(resultado);
oc.ExecuteNonQuery();

And this is the stored procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `CongelarProceso`(in p_idRecetas int, out p_Mensaje varchar(2000), out p_resultado int)
BEGIN
DECLARE done INT DEFAULT FALSE;
dECLARE vStatus int;
declare vDescripcion varchar(100);
declare vEtapa, vIniciocicLo, vFinciclo, vInsumo1, vCantidadProceso, vInsumo2, vCantidadReceta int;
Declare cProceso cursor for select etapaproceso, iniciociclo, finciclo
from procesoelaboracion where recetas_encabezado_idRecetas = p_idRecetas
order by etapaproceso;
Declare cDiscrepancias cursor for SELECT
*
FROM
(SELECT
insumomateriaprima insumos_idinsumos,
SUM(cantidadprocesoanadir) * REPETICIONES_PROCESO(pe.recetas_encabezado_idrecetas, pe.etapaproceso) cantidadproceso
FROM
MateriaPrimaProceso mp, procesoelaboracion pe, insumos i
WHERE
mp.procesoelaboracion_idprocesoelaboracion = pe.idprocesoelaboracion
AND mp.insumomateriaprima = i.idinsumos
AND i.descripcion <> 'Instrucción Especial'
AND pe.recetas_encabezado_idrecetas = p_idRecetas
GROUP BY insumomateriaprima) proceso
LEFT JOIN
(SELECT
insumos_idinsumos, cantidad
FROM
recetas_detalle
WHERE
recetas_encabezado_idrecetas = p_idRecetas) recetas ON proceso.insumos_idinsumos = recetas.insumos_idinsumos
WHERE
recetas.insumos_idinsumos IS NULL
OR recetas.cantidad <> proceso.cantidadproceso
UNION SELECT
*
FROM
(SELECT
insumomateriaprima insumos_idinsumos,
SUM(cantidadprocesoanadir) * REPETICIONES_PROCESO(pe.recetas_encabezado_idrecetas, pe.etapaproceso) cantidadproceso
FROM
MateriaPrimaProceso mp, procesoelaboracion pe, insumos i
WHERE
mp.procesoelaboracion_idprocesoelaboracion = pe.idprocesoelaboracion
AND mp.insumomateriaprima = i.idinsumos
AND i.descripcion <> 'Instrucción Especial'
AND pe.recetas_encabezado_idrecetas = p_idRecetas
GROUP BY insumomateriaprima) proceso
RIGHT JOIN
(SELECT
insumos_idinsumos, cantidad
FROM
recetas_detalle
WHERE
recetas_encabezado_idrecetas = p_idRecetas) recetas ON proceso.insumos_idinsumos = recetas.insumos_idinsumos
WHERE
proceso.insumos_idinsumos IS NULL
OR recetas.cantidad <> proceso.cantidadproceso;
set vStatus = 0;
set p_Mensaje = '';
set p_resultado = 0;
open cProceso;
begin
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
lectura: loop
fetch cProceso into vEtapa, vIniciociclo, vFinciclo;
if done then
LEAVE lectura;
end if;
if vInicioCiclo = 1 then
if vStatus = 1 then
set p_mensaje = concat(p_Mensaje,'\nSe detectó un inicio de ciclo sin que se haya cerrado el ciclo anterior en la etapa: ', vEtapa);
set p_resultado = -1;
else
set vStatus = 1;
end if;
end if;
if vFinCiclo = 1 then
if vStatus = 0 then
set p_Mensaje = concat(p_Mensaje,'\nSe detectó un fin de ciclo sin un inicio de ciclo correspondiente en la etapa: ', vEtapa);
set p_Resultado = -1;
leave lectura;
else
set vStatus = 0;
end if;
end if;
END LOOP;
end;
if vStatus = 1 then
set p_Mensaje = concat(p_Mensaje,'\nSe detectó un inicio de ciclo sin cerrar en el proceso.');
set p_Resultado = -1;
end if;
open cDiscrepancias;
begin
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
set done = false;
lecturad:Loop
fetch cDiscrepancias into vInsumo1, vCantidadProceso, vInsumo2, vCantidadReceta;
if done then
LEAVE lecturad;
end if;
SELECT
descripcion
INTO vDescripcion FROM
insumos
WHERE
idinsumos = vInsumo1;
set p_Mensaje = concat(p_Mensaje, '\nInsumo: ',vDescripcion, ' Cantidad en proceso: ', vCantidadProceso, ' Cantidad en receta: ', vCantidadReceta);
set p_resultado = -1;
end loop;
end;
if p_resultado = 0 then
UPDATE procesoelaboracion
SET
congelarproceso = 1
WHERE
recetas_encabezado_idrecetas = p_idRecetas;
end if;
END

And finally, this is the error that appears when executing from .NET:
Server Error in '/' Application.

ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
(The line repeats an uncountable amount of times)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
(this line again repeats too many times)

Source Error:



Line 184: oc.Parameters.Add(mensaje);
Line 185: oc.Parameters.Add(resultado);
Line 186: oc.ExecuteNonQuery();


Stack Trace:



[OdbcException (0x80131937): ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.30]unhandled error from mysql_next_result()

(line again repeats)

System.Data.Odbc.OdbcDataReader.NextResult(Boolean disposing, Boolean allresults) +1056179
System.Data.Odbc.OdbcDataReader.Close(Boolean disposing) +52
System.Data.Odbc.OdbcDataReader.Close() +11
System.Data.Odbc.OdbcCommand.ExecuteScalar() +140
ProcesoElaboracion.ButCongelar_Click(Object sender, EventArgs e) in c:\Users\shinaco\Documents\Visual Studio 2015\WebSites\HelloWorld\Cerveza\ProcesoElaboracion.aspx.cs:186
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9692746
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3562

Any help regarding this would be very much appreciated

Options: ReplyQuote


Subject
Written By
Posted
unhandled error from mysql_next_result() when calling stored procedure from ODBC
June 23, 2016 08:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.