Alpha

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

Large Objects

A CHARACTER LARGE OBJECT or BINARY LARGE OBJECT value may be far longer than a program wants to hold at once. The server sends a short one whole in its row, and a longer one by its locator, which the program reads a range at a time. A program writes a long value a piece at a time and passes it as a parameter. This chapter uses the low-level client, turborel.client.

Whole, or by its locator

A large object of at most the server's SERVER.LOB_INLINE_BYTES, 64 KiB by default, comes whole: a CLOB as a str, a BLOB as bytes. A longer one comes as a turborel.LobLocator: whether it is binary, its locator, and its lengths in octets and, of a CLOB, in characters.

from turborel import LargeObject
from turborel.client import Trust, connect

conn = connect(("127.0.0.1", 7422), "ANN", Trust())
conn.query("CREATE TABLE DOCS (ID INTEGER, BODY CLOB(1M))")
insert = conn.prepare("INSERT INTO DOCS VALUES (?, ?)")
conn.execute(insert, (1, LargeObject("short")))
conn.execute(insert, (2, LargeObject("é" * 40_000)))
conn.query("COMMIT")
for doc, body in conn.query("SELECT ID, BODY FROM DOCS ORDER BY ID").rows:
    print(doc, body if isinstance(body, str) else (body.octets, body.characters))
1 short
2 (80000, 40000)

A parameter wrapped in LargeObject is sent whole, as a large object; a str or bytes alone would be sent as a character or binary string.

Reading by ranges

read_lob(lob, start, count) reads at most count octets from octet start, from 0; with characters=True, a CLOB's characters, whole ones, as UTF-8. A read gives at most 256 KiB. lob_reader(lob) gives the whole value as a binary stream, read a chunk at a time. Both take a value held whole too, reading it from the value itself.

[(body,)] = conn.query("SELECT BODY FROM DOCS WHERE ID = 2").rows
print(conn.read_lob(body, 0, 3, characters=True).decode())
print(len(conn.lob_reader(body).read()))
ééé
80000

How long a locator lasts

A locator is valid until its transaction ends; under autocommit a result that sent one keeps its transaction active until the program ends it. free_lob ends a locator sooner. hold_lob keeps it valid past its transaction's commit, until it is freed or a rollback ends it. A locator no longer valid fails with SQLSTATE 0F001.

conn.hold_lob(body)
conn.query("COMMIT")
print(conn.read_lob(body, 0, 1, characters=True).decode())
conn.free_lob(body)
try:
    conn.read_lob(body, 0, 1)
except Exception as e:
    print(e.sqlstate)
conn.query("ROLLBACK")
é
0F001

Writing a piece at a time

create_lob(binary=...) makes an empty large object and gives a binary stream to write it with; finish gives its locator, to pass as a parameter within the same transaction. A CLOB's octets are UTF-8, and a character may be split between writes.

writer = conn.create_lob(binary=False)
for part in ("chapter one, ", "chapter two"):
    writer.write(part.encode("utf-8"))
book = writer.finish()
conn.execute(insert, (3, book))
print(conn.query("SELECT BODY FROM DOCS WHERE ID = 3").rows)
conn.query("COMMIT")
[('chapter one, chapter two',)]

See also

Large Objects, the reference page; Transactions.