Problem with stored procedures and OUT parameters

Problem with stored procedures and OUT parameters

Postby hcro12 » Tue 10 Mar 2015 09:27

Given stored procedure:

Code: Select all
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetNetworkElementData`(
                                     IN Id INT,
                                     OUT ParentLink INT,
                                     OUT Number INT  ,
                                     OUT Name TEXT  ,
                                     OUT TimeStamp nvarchar(50)  ,
                                     OUT AddressType_Descr nvarchar(50)   
                                     )
BEGIN   
                                     select
                                     NetworkElement_Number as Number,
                                     Lnk_Omse_Networkelements as ParentLink,
                                     NetworkElement_Name as Name,
                                     NetworkElement_TimeStamp as TimeStamp,
                                     NetworkElement_AddressType_Descr as AddressType_Descr
                                     from Omse_Networkelements, Omse_Networkelement_Types, Omse_Networkelement_Addresstypes 
                                     where   Omse_Networkelements.NetworkElement_Id = Id  AND
                                     Omse_Networkelement_Types.NetworkElement_Type_Id = Omse_Networkelements.Lnk_Omse_Networkelement_Types
                                     AND
                                     Omse_Networkelement_Addresstypes.NetworkElement_AddressType_Id = Omse_Networkelement_Types.Lnk_Omse_Networkelement_Addresstypes;
                                     END ;;
DELIMITER ;


This stored procedure works and returns expected results.

Dotconnect generated code:

Code: Select all
   [Function(Name=@"sp_GetNetworkElementData")]
        public Devart.Data.Linq.ISingleResult<Sp_GetNetworkElementDataResult> Sp_GetNetworkElementData([Parameter(Name="Id", DbType="INT(10)")] System.Nullable<int> Id, [Parameter(Name="ParentLink", DbType="INT(10)")] out System.Nullable<int> ParentLink, [Parameter(Name="Number", DbType="INT(10)")] out System.Nullable<int> Number, [Parameter(Name="Name", DbType="TEXT")] out string Name, [Parameter(Name="TimeStamp", DbType="VARCHAR(50)")] out string TimeStamp, [Parameter(Name="AddressType_Descr", DbType="VARCHAR(50)")] out string AddressType_Descr)
        {
            IExecuteResult _Sp_GetNetworkElementDataResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), Id, (System.Nullable<int>)null, (System.Nullable<int>)null, (string)null, (string)null, (string)null);
            ParentLink = ((System.Nullable<int>)(_Sp_GetNetworkElementDataResult.GetParameterValue(1)));
            Number = ((System.Nullable<int>)(_Sp_GetNetworkElementDataResult.GetParameterValue(2)));
            Name = ((string)(_Sp_GetNetworkElementDataResult.GetParameterValue(3)));
            TimeStamp = ((string)(_Sp_GetNetworkElementDataResult.GetParameterValue(4)));
            AddressType_Descr = ((string)(_Sp_GetNetworkElementDataResult.GetParameterValue(5)));
            return ((Devart.Data.Linq.ISingleResult<Sp_GetNetworkElementDataResult>)(_Sp_GetNetworkElementDataResult.ReturnValue));
        }


When executed and debugged, the Results View of the _Sp_GetNetworkElementDataResult call contains the correct and expected data but the GetParameterValue calls ALWAYS return NULL.
:(
Please help asap, I'm using the latest version of the trial, we are considering bying a Developer Edition but this one has to work!
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: Problem with stored procedures and OUT parameters

Postby MariiaI » Wed 11 Mar 2015 10:53

Most likely, this issue is not related to LinqConnect/dotConnect for MySQL, but it is related to common principles of working with stored procedures in MySQL.
The same results (i.e. nulls) are obtained when executing such stored procedure in management studio for MySQL, e.g. dbForge Studio for MySQL. Also, a similar issue has been discussed here: http://stackoverflow.com/questions/23902085/mysql-gets-all-of-procedure-out-parameter-null

Please make sure your SP works in any management studio for MySQL as expected, correct it if necessary, and, if it still fails with LinqConnect, please send us the additional information, e.g. the DDL/DML scripts for all necessary database objects that are used in this SP, specify the version of MySQL server, etc.

Please also take a look at these articles:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
http://www.devart.com/linqconnect/docs/Stored-Routines.html
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problem with stored procedures and OUT parameters

Postby hcro12 » Wed 11 Mar 2015 11:25

It works now!
It turned out that indeed the syntax of the stored procedure was wrong.
Thanks for the advice!
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: Problem with stored procedures and OUT parameters

Postby MariiaI » Wed 11 Mar 2015 11:34

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL