Alpha

These pages are built from turborel-cs’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Prepared Statements

A statement prepared once runs as often as wanted, with new values each time. Its dynamic parameters are written ?, and the server says the type each takes and, of a query, its columns. A prepared statement is valid across transactions until it is deallocated or the session reset.

Prepare, execute, read a cursor

PrepareAsync gives a PreparedStatement; ExecuteAsync runs it with one value for each parameter. A query's rows are read all at once by ExecuteAsync, or through its cursor: OpenCursorAsync gives its first rows and says whether it has more, FetchAsync the next, and the cursor closes with its last row or by CloseCursorAsync. DeallocateAsync destroys the statement.

using Turborel.Data.Authentication;
using Turborel.Data.Session;
using Turborel.Data.Transport;
using Turborel.Data.Types;

await using NativeConnection connection =
    await NativeConnection.ConnectAsync(new TcpTarget("localhost", 17435), "ann", new TrustMechanism());
PreparedStatement raise = await connection.PrepareAsync("UPDATE STAFF SET SALARY = SALARY + ? WHERE NAME = ?");
Console.WriteLine(string.Join(", ", raise.Parameters.Select(p => p.Type)));
foreach ((string name, decimal amount) in new[] { ("Ann", 100m), ("Bob", 250.25m) })
{
    var outcome = (DoneOutcome)await connection.ExecuteAsync(raise, [TurborelDecimal.FromDecimal(amount), name]);
    Console.WriteLine($"{name}: {outcome.Completion.RowCount} row");
}

PreparedStatement list = await connection.PrepareAsync("SELECT NAME, SALARY FROM STAFF ORDER BY NAME");
Batch first = await connection.OpenCursorAsync(list, [], rows: 1);
Console.WriteLine($"{string.Join(", ", first.Rows[0])}; more: {first.More}");
Batch rest = await connection.FetchAsync(list, rows: 0);
Console.WriteLine($"{string.Join(", ", rest.Rows[0])}; more: {rest.More}");
await connection.DeallocateAsync(list);
await connection.QueryAsync("COMMIT");
ExactType { Precision = 9, Scale = 2 }, CharacterType { Length = 20, Units = Characters, Varying = True }
Ann: 1 row
Bob: 1 row
Ann, 5300.00; more: True
Bob, 4350.75; more: False

A decimal becomes an exact number by TurborelDecimal.FromDecimal.

Several cursors, and describing again

Once the server grants cursors, a statement has several cursors open at once, each numbered by the program: cursor names one in OpenCursorAsync, FetchAsync and CloseCursorAsync, 0 by default. Once it grants describe, DescribeAsync describes a statement again, as it is now, and DescribeCursorAsync an open cursor's columns.

A CALL's output parameters

A CALL's parameters are its inputs alone. Its columns are one for each ? of an OUT or INOUT parameter, in the procedure's order, and executing it gives one row of their values:

using Turborel.Data.Authentication;
using Turborel.Data.Session;
using Turborel.Data.Transport;
using Turborel.Data.Types;

await using NativeConnection connection =
    await NativeConnection.ConnectAsync(new TcpTarget("localhost", 17435), "ann", new TrustMechanism());
await connection.QueryAsync("CREATE PROCEDURE PAY (IN WHO VARCHAR(20), OUT SALARY DECIMAL(9,2)) READS SQL DATA SELECT SALARY INTO PAY.SALARY FROM STAFF WHERE NAME = WHO");
PreparedStatement call = await connection.PrepareAsync("CALL PAY(?, ?)");
Console.WriteLine($"{call.Parameters.Count} parameter, {call.Columns.Count} column: {call.Columns[0].Name}");
var output = (RowsOutcome)await connection.ExecuteAsync(call, ["Bob"]);
Console.WriteLine(output.Rows[0][0]);
await connection.QueryAsync("COMMIT");
1 parameter, 1 column: SALARY
4350.75

See also

Statements and Cursors in the API Reference.