CODEWITHSUNDEEP
c#mysqlmariadb
clemensoe
clemensoe

Reputation: 398

MySqlCommand call function return type

I have a question in relation to this one:

MySqlCommand call function

Using MariaDB

10.5.11-MariaDB-1:10.5.11+maria~focal

and the given function (simplified)

CREATE FUNCTION `activity_plugin_active`() RETURNS binary(1)
BEGIN
        DECLARE result INT DEFAULT 0;
        RETURN result;
END

To get the result in .NET of the function I use a helper method

bool activitiesPluginActive = 1 == ExecuteIntScalarAsync(
  "activity_plugin_active", 
  null, 
  CancellationToken.None)
.GetAwaiter().GetResult();

But this method is a bit bulky

private async Task<int> ExecuteIntScalarAsync(
  string functionName, 
  IEnumerable<DbParameter> parameters, 
  CancellationToken cancellationToken = default)
{
    using (MySqlConnection conn = new MySqlConnection(databaseConfiguration.GetConnectionString()))
    {
        await conn.OpenAsync();
        MySqlCommand cmd = new MySqlCommand(functionName) 
        { 
            CommandType = CommandType.StoredProcedure, 
            Connection = conn 
        };

        if (parameters != null)
            foreach (var parameter in parameters)
                cmd.Parameters.Add(parameter);

        var returnValue = new MySqlParameter("returnvalue", MySqlDbType.Int32)
        {
            Direction = ParameterDirection.ReturnValue
        };
        cmd.Parameters.Add(returnValue);
        
        logger.LogInformation($"Executing { functionName }");
        await cmd.ExecuteScalarAsync(cancellationToken);
        
        // return value is byte[1], value 48
        byte[] bytes = returnValue.Value as byte[];

        // need to cast to string, value "0"
        var sValue = System.Text.Encoding.Default.GetString(bytes);
        
        // need to cast to int
        return Convert.ToInt32(sValue);
    }
}

As you see in the code comments above the return value is a byte[1] - with the given function it has the value 48 - ASCII CODE for number zero. Although the return type is defined as MySqlDbType.Int32.

How do I get directly returned an integer without the need to cast twice?

Upvotes: 0

Views: 206

Answers (1)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

It is because in your function you are specifying that return the result of function as binary(1) (i.e. RETURNS binary(1)) . Can you try changing it to INT like:

CREATE FUNCTION `activity_plugin_active`() RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    RETURN result;
END

Upvotes: 1

Related Questions