Call a function: a value, rows, an effect

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

The sample, as it is in the repository.

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 function, in three scenarios: one value (<c>Value&lt;T&gt;()</c>); rows, which on MySQL come from a
/// procedure (<c>ResultSets[0]</c>); and an effect, also a procedure's job. A function is the same
/// <see cref="ArxProcedureExpression"/> with <see cref="ArxProcedureType.Function"/>: ARX sends <c>SELECT fn(…)</c>
/// instead of <c>CALL</c>. MySQL functions return one value, never rows (RetrieveMultiple's RealCase6 and Advanced8 show what to
/// read instead).
/// </summary>
public sealed class Basic3_CallAFunction : ISample
{
    public string Name => "execute-basics-3-call-a-function";

    public string Title => "Call a function: a value, rows, an effect";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Function Fabrics";
        account["account_number"] = "ACC-E301";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var (first, title) in new[] { ("Fran", "Buyer"), ("Finn", "Engineer"), ("Faye", "Director") })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Fn-E301";
            contact["job_title"] = title;
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_OneValueAsync(dbContext, accountId);

        await Scenario2_RowsAsync(dbContext);

        await Scenario3_AnEffectAsync(dbContext, accountId);
    }

    /// <summary>1. <c>fn_contact_count(p_account_id)</c> returns a bigint.</summary>
    private static async Task Scenario1_OneValueAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. One value");

        var function = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter(accountId.AsInt64());

        var count = (await dbContext.Service.ExecuteAsync(function)).Value<long>();
        Show($"{count} contacts");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT fn_contact_count(a.id)
         * FROM account a
         * WHERE a.account_number = 'ACC-E301';
         * -- Expect: 3.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. Rows: MySQL functions return one value, so rows come from a procedure. <c>sp_contacts_named(p_last_name)</c>
    /// returns a table: its rows are the first result set.
    /// </summary>
    private static async Task Scenario2_RowsAsync(SampleContext dbContext)
    {
        Step("2. Rows: a procedure, since MySQL functions return one value");

        var procedure = new ArxProcedureExpression("sp_contacts_named");
        procedure.AddParameter("p_last_name", "Fn-E301");

        var rows = (await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0];
        Show(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["job_title"]})")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * CALL sp_contacts_named('Fn-E301');
         * -- Expect: 3 rows: Fran Buyer, Finn Engineer, Faye Director.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. An effect is a procedure's job. <c>sp_rename_account</c> renames the account and sets modified_on; it returns
    /// no rows and has no OUT values (MySQL procedures have no RETURN value).
    /// </summary>
    private static async Task Scenario3_AnEffectAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. An effect: a procedure, not a function");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "Fabrikam Functions");
        procedure.AddParameter("p_reason", "a new brand");

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS touched
         * FROM account
         * WHERE account_number = 'ACC-E301';
         * -- Expect: Fabrikam Functions, 1.
         * ===========================================================
         */
    }
}

  > 1. One value
      3 contacts
  > 2. Rows: a procedure, since MySQL functions return one value
      Fran (Buyer), Finn (Engineer), Faye (Director)
  > 3. An effect: a procedure, not a function
      result sets: 0, outputs: 0