Progress messages from a procedure

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Execute.RealCases;

// 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 RealCase6_ProgressMessages().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.MySQL.Operations.Execute.RealCases;

/// <summary>
/// The messages a procedure sends, in two scenarios. A MySQL procedure reports with a warning: <c>SIGNAL SQLSTATE
/// '01000'</c> (class 01 is a warning, not an error, and the procedure goes on). MySQL keeps only the warnings of the
/// procedure's last statement, so a procedure reports one note when it ends rather than progress while it runs. It comes
/// back in <see cref="ArxExecuteResult.Messages"/>, in order, for the application to show or log as it chooses (ARX never
/// writes them to a log); <c>CollectMessages = false</c> leaves them out.
/// </summary>
public sealed class RealCase6_ProgressMessages : ISample
{
    public string Name => "execute-real-6-progress-messages";

    public string Title => "Progress messages from a procedure";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_TheMessagesInOrderAsync(dbContext);

        await Scenario2_NotCollectedAsync(dbContext);
    }

    private static ArxProcedureExpression Import(int rows)
    {
        var procedure = new ArxProcedureExpression("sp_import_progress");
        procedure.AddParameter("p_rows", rows);
        return procedure;
    }

    /// <summary>1. An import of 1,000 rows: the note the procedure ends with, read with SHOW WARNINGS in the same round trip.</summary>
    private static async Task Scenario1_TheMessagesInOrderAsync(SampleContext dbContext)
    {
        Step("1. The note the procedure ends with");

        var result = await dbContext.Service.ExecuteAsync(Import(1_000));

        foreach (var message in result.Messages)
        {
            Show($"{message.Severity,-7} {message.Text}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT routine_name, routine_type
         * FROM information_schema.routines
         * WHERE routine_schema = DATABASE() AND routine_name = 'sp_import_progress';
         * -- Expect: sp_import_progress, PROCEDURE (the messages themselves are only seen by the session that ran the call).
         * ===========================================================
         */
    }

    /// <summary>2. The same call without collecting them.</summary>
    private static async Task Scenario2_NotCollectedAsync(SampleContext dbContext)
    {
        Step("2. Not collected");

        var result = await dbContext.Service.ExecuteAsync(Import(1_000), new ArxExecuteOptions { CollectMessages = false });
        Show($"{result.Messages.Count} messages");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT COUNT(*)
         * FROM information_schema.routines
         * WHERE routine_schema = DATABASE() AND routine_type = 'PROCEDURE' AND routine_name = 'sp_import_progress';
         * -- Expect: 1.
         * ===========================================================
         */
    }
}

  > 1. The note the procedure ends with
      Warning imported 1000 rows in 4 steps of 250: a large import
  > 2. Not collected
      0 messages