Bind and Into Variables

A bind expression supplies a value to a database command. An into expression identifies where PPJ writes a value retrieved from a result row. Both are application expressions embedded in SQL text, but their direction and timing differ.

Before execution:
application value → bind expression → provider parameter → database

During a successful fetch:
database result column → PPJ conversion → into destination

PPJ parses these expressions and uses its scripting/binding infrastructure to resolve application members. The ADO.NET provider handles the resulting database parameters and result values. The database does not evaluate a C# field access such as locals.sName.

Why Generated Locals Are Objects

Ordinary C# locals are not exposed as object members for runtime lookup. Ice Porter can extract function locals and parameters into a generated inner class, commonly named SqlLocals, and replace their uses with fields on a locals instance. This lets a string such as :sName refer to an actual runtime member.

The extraction option applies broadly because an expression can be constructed dynamically; it is not always possible to determine all referenced locals by scanning literal SQL strings. Disabling extraction requires checking scripts and computed expressions as well as direct SQL calls.

A Query with One Input and One Output

For a company lookup, the important values are:

• nId: a bind value used to select the company.
• sName: an into destination receiving the returned name.
• nFetch: the fetch status argument used by the generated code.

A representative SQL expression is:

select name from companies where id = :nId into :sName

This is PPJ/SAL embedded SQL notation. Do not paste the application-side into clause into a database console and expect the database to know sName.

The following method excerpt assumes the generated SqlLocals.Lookup class has public nId, sName, and nFetch fields of the corresponding PPJ types. The caller supplies an already connected handle and owns its connection/transaction lifecycle.

public SalBoolean TryReadName(SalSqlHandle hSql, SalNumber companyId,
    ref SalString name)
{
    name = SalString.Null;
    var locals = new SqlLocals.Lookup();
    locals.nId = companyId;
    locals.sName = SalString.Null;

    using (new SqlContext(locals, this))
    {
        if (!hSql.PrepareAndExecute(
            "select name from companies where id = :nId into :sName"))
            return false;

        if (!hSql.FetchNext(ref locals.nFetch))
            return false;

        name = locals.sName;
        return true;
    }
}

This example returns a value only after a successful fetch. It leaves the output null on the false-return paths, rather than displaying an old local value. A successful fetch can still return a database null name. A false fetch result and a SQL exception also need to be distinguished under the application's configured error policy; this excerpt does not suppress exceptions or implement that policy.

Use a unique key for this lookup. If several rows can match, define whether to fetch all rows, reject the ambiguity, or select one with an explicit ordering. “The first row” without such a rule is not a stable business choice.

Context and Timing

The correct two-argument constructor order is new SqlContext(locals, this). Keep that scope active through preparation, execution, and fetching wherever expressions are resolved. See SqlContext.

When reusing a prepared command, establish how the installed PPJ/provider refreshes parameter values and metadata. Do not assume changing a C# variable, reusing a command parameter, and re-executing SQL are the same operation. Test consecutive executions with different values, null transitions, and changed string lengths.

Values Versus SQL Text

Bind values rather than concatenate user input into SQL. A bound value cannot stand for a table name, column name, sort direction, or arbitrary SQL clause; choose those from an explicit set of permitted identifiers or operations.

For each provider, check named versus positional parameters, repeated parameter names, nulls, dates, decimal precision, Unicode, and output/receive values. Keep business nulls distinct from “no row” and “operation failed.” The binding engine preserves access to application values; it does not make every provider's SQL dialect or type behavior identical.