dotConnect problem with OUT parameter

dotConnect problem with OUT parameter

Postby Burnone » Wed 25 Nov 2009 22:34

I am calling a stored procedure from C# using the dotConnect sqlConnection object.

This same query works properly using the dbForge debugger or running a SQL query directly against mySQL ('ok' is returned in the output parameter).

It only happens when using the dotConnect object. Using dotConnect the OUT parameters returns 'NOT FOUND2' but all the table operations have executed as expected so it should have returned 'ok'

I can only determine that dotConnect is calling the SP 2x (under the hood), even though my C# code is only getting the one callback.

Thanks for your help

C# Code
----------
Devart.Data.MySql.Web.MySqlDataSource mySqlDataSource = new Devart.Data.MySql.Web.MySqlDataSource(Constants.Config.ConnectionString, "foo");
mySqlDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
mySqlDataSource.Selected += new SqlDataSourceStatusEventHandler(Call_OnSelected);

mySqlDataSource.SelectParameters.Add("id1", System.Data.DbType.Int64, "1");
mySqlDataSource.SelectParameters.Add("id2", System.Data.DbType.Int64, "2");
Parameter result = new Parameter("result", System.Data.DbType.String);
result.Direction = System.Data.ParameterDirection.Output;
mySqlDataSource.SelectParameters.Add(result);

mySqlDataSource.DataBind();
mySqlDataSource.Select(DataSourceSelectArguments.Empty);


private void Call_OnSelected(object sender, SqlDataSourceStatusEventArgs e)
{
// NOTE: I have put a breakpoint here to ensure this was not being double called.

e.Command.Connection.Open();
e.Command.ExecuteNonQuery();

// RESULT IS INCORRECT HOWEVER ALL THE OPERATIONS EXCECUTED PROPERLY SO THE RESULT SHOULD HAVE BEEN OK
string result = System.Convert.ToString(e.Command.Parameters["result"].Value);

...
}



MySQL Stored Procedure (isolated to delete that causes problem)
--------------
CREATE DEFINER = 'root'@'localhost'
PROCEDURE ff.foo(IN id1 BIGINT, IN id2 BIGINT, OUT result VARCHAR(128))
body:
BEGIN
DECLARE le_id BIGINT;
DECLARE p_id BIGINT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT
'sqlexception'
INTO
result;
ROLLBACK;
END;

START TRANSACTION;

SELECT
pm.pa_id
INTO
le_id
FROM
pms pm,
par p
WHERE
pm.m_id = id1
AND pm.pr_id = 1
AND p.pa_id = pm.pa_id
AND CURRENT_TIMESTAMP () < p.expiration
AND id1 <> id2;

IF (le_id IS NULL) THEN
SELECT
'NOT FOUND 1'
INTO
result;

ROLLBACK;
LEAVE body;
END IF;

SELECT
pm.ph_id
INTO
p_id
FROM
pms pm
WHERE
pm.m_id = id2 AND pm.pa_id = le_id AND pm.pr_id = 2;

IF (p_id IS NULL) THEN
SELECT
'NOT FOUND 2'
INTO
result;

ROLLBACK;
LEAVE body;
END IF;

INSERT IGNORE INTO pmb (ph_id, pa_id)
SELECT
p_id, le_id;

DELETE
FROM
pml
WHERE
m_id = id2;

-- THIS DELETE CAUSES THE PROBLEM ... WHICH MAKES ME THINK IT IS RUNNING 2x
DELETE
FROM
pms
WHERE
m_id = id2;

-- I EXPECT THIS TO BE RETURNED BUT ITS NOT HOWEVER ALL THE TABLES EXECUTED AS
-- IF IT MADE IT THIS FAR!!! THAT IS WHY I THINK ITS RUNNING 2X FROM DOTCONNECT
SELECT
'ok'
INTO
result;

COMMIT;
END[code][/code]
Burnone
 
Posts: 5
Joined: Fri 20 Nov 2009 19:25

Postby Shalex » Thu 26 Nov 2009 10:44

Unfortunately, MySQL does not support OUT parameters in protocol yet. However, dotConnect for MySQL allows you to handle OUT parameters using server-side variables. Please refer to our documentation at http://www.devart.com/dotconnect/mysql/docs/Parameters.html, the Using OUT and INOUT parameters section. Try using the MySqlCommand object and processing the result with reader.
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL