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.

Large Objects

A CLOB or BLOB value is a TurborelLargeObject. One of at most SERVER.LOB_INLINE_BYTES, 64 KiB unless the server's operator says, comes whole in its row, its octets in Data. A longer one comes by its locator, IsLocator true, with its lengths, Octets and, of a CLOB, Characters; the program reads it over the connection, by ranges or as a stream. A locator is valid until the transaction it was given in ends, so a result that sent one keeps its transaction open, under autocommit too.

using System.Text;
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 TABLE MANUALS (ID INTEGER, BODY CLOB(10M))");

LobWriteStream writer = await connection.CreateLobAsync(LargeObjectKind.Character);
for (int chapter = 1; chapter <= 3000; chapter++)
{
    await writer.WriteAsync(Encoding.UTF8.GetBytes($"Chapter {chapter}: the café's menu. "));
}

TurborelLargeObject written = await writer.FinishAsync();
PreparedStatement insert = await connection.PrepareAsync("INSERT INTO MANUALS VALUES (?, ?)");
await connection.ExecuteAsync(insert, [new TurborelDecimal(1, 0), written]);
await connection.ExecuteAsync(insert, [new TurborelDecimal(2, 0), "a short one"]);

var rows = (RowsOutcome)await connection.QueryAsync("SELECT ID, BODY FROM MANUALS ORDER BY ID");
foreach (IReadOnlyList<object?> row in rows.Rows)
{
    var body = (TurborelLargeObject)row[1]!;
    Console.WriteLine(body.IsLocator
        ? $"{row[0]}: by its locator, {body.Characters} characters in {body.Octets} octets"
        : $"{row[0]}: whole, {SqlLength(body)} octets");
}

var manual = (TurborelLargeObject)rows.Rows[0][1]!;
Console.WriteLine(Encoding.UTF8.GetString(await connection.ReadLobAsync(manual, 11, 21, characters: true)));
using var reader = new StreamReader(connection.OpenLobReader(manual));
string all = await reader.ReadToEndAsync();
Console.WriteLine($"read whole: {all.Length} characters, ending '{all[^20..]}'");
await connection.QueryAsync("COMMIT");

static int SqlLength(TurborelLargeObject lob) => lob.Data!.Value.Length;
1: by its locator, 91893 characters in 94893 octets
2: whole, 11 octets
the café's menu. Chap
read whole: 91893 characters, ending '0: the café's menu. '

CreateLobAsync makes a large object written a piece at a time through a LobWriteStream, sent to the server a chunk at a time; a character split between writes is sent whole. FinishAsync gives the value, to pass as a parameter by its locator. A CLOB's octets are UTF-8.

ReadLobAsync reads a range, by octets or, of a CLOB, by characters, at most 256 KiB a request; OpenLobReader gives the whole value as a stream, read a chunk at a time. FreeLobAsync frees a locator, and HoldLobAsync keeps one valid past its transaction's commit, until it is freed or a rollback ends it. A large object that came whole has no locator to free or hold: SQLSTATE 22000.

See also

Large Objects in the API Reference.