Call a stored procedure

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Execute.Basics;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL connection string.");

await new Basic1_CallAProcedure().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.MySQL.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Execute.Basics;

/// <summary>
/// Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, which
/// MySQL refuses (its procedures have no default values). The call is described with
/// <see cref="ArxProcedureExpression"/>, the way a query is described with <see cref="ArxQueryExpression"/>; ARX sends
/// <c>CALL</c> with every value as a parameter, and checks named arguments against the procedure's own names.
/// </summary>
public sealed class Basic1_CallAProcedure : ISample
{
    public string Name => "execute-basics-1-call-a-procedure";

    public string Title => "Call a stored procedure";

    public async Task RunAsync(SampleContext dbContext)
    {
        var service = new ArxMySqlDbService(dbContext.ConnectionString);

        var account = new ArxEntity("account");
        account["name"] = "Old Name Ltd";
        account["account_number"] = "ACC-E101";
        var accountId = await dbContext.Service.CreateAsync(account);

        await Scenario1_NamedArgumentsAsync(service, accountId);

        await Scenario2_PositionalArgumentsAsync(service, accountId);

        await Scenario3_ADefaultAsync(service, accountId);
    }

    /// <summary>1. <c>sp_rename_account(p_account_id, p_name, p_reason)</c> with every argument named. Nothing comes back.</summary>
    private static async Task Scenario1_NamedArgumentsAsync(ArxMySqlDbService service, ArxRecordId accountId)
    {
        Step("1. Named arguments");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "New Name Ltd");
        procedure.AddParameter("p_reason", "rebranding");

        var result = await service.ExecuteAsync(procedure);
        Show($"renamed; outputs: {result.Outputs.Attributes.Count()}, result sets: {result.ResultSets.Count}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: New Name Ltd (until scenario 2 renames it again).
         * ===========================================================
         */
    }

    /// <summary>2. The same procedure with positional arguments, in the procedure's order.</summary>
    private static async Task Scenario2_PositionalArgumentsAsync(ArxMySqlDbService service, ArxRecordId accountId)
    {
        Step("2. Positional arguments");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter(accountId.AsInt64());
        procedure.AddParameter("Positional Ltd");
        procedure.AddParameter("a positional call");

        await service.ExecuteAsync(procedure);
        Show("renamed to Positional Ltd");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: Positional Ltd (until scenario 3).
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. <c>p_reason</c> left out: MySQL procedures have no default values, so the call is refused (ARX4004) and nothing
    /// changes. Pass every parameter; give <c>null</c> for "no value".
    /// </summary>
    private static async Task Scenario3_ADefaultAsync(ArxMySqlDbService service, ArxRecordId accountId)
    {
        Step("3. A parameter left out: refused, MySQL has no defaults");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "Final Name Ltd");

        try
        {
            await service.ExecuteAsync(procedure);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.DataTypeMismatch)
        {
            ShowError(error);
        }

        procedure.AddParameter("p_reason", null);
        await service.ExecuteAsync(procedure);
        Show("renamed to Final Name Ltd, with p_reason null");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, modified_on IS NOT NULL AS modified
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: Final Name Ltd, 1.
         * ===========================================================
         */
    }
}

  > 1. Named arguments
      renamed; outputs: 0, result sets: 0
  > 2. Positional arguments
      renamed to Positional Ltd
  > 3. A parameter left out: refused, MySQL has no defaults
      caught ArxDatabaseException: ARX4004 DataTypeMismatch: Execute on 'sp_rename_account' doesn't match the procedure's parameters: no value for 'p_reason' (MySQL procedures have no default values). Give every parameter, by the procedure's own names.
      renamed to Final Name Ltd, with p_reason null