Alpha

These pages are built from turborel-ruby’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 of at most SERVER.LOB_INLINE_BYTES, 64 KiB unless the operator sets otherwise, comes in its row whole: a Turborel::Lob whose data holds its text or octets. A longer one comes as a Turborel::Lob that holds its locator and its lengths, octets and characters, not its octets. This chapter tells how a program reads one, writes one, and how long a locator lasts.

Reading

read_lob(lob, from, count) reads at most count octets from octet from, the first 0, at most a chunk of 256 KiB at a time; characters: true counts a CLOB's characters instead. Of a value held whole, it reads with no round trip. lob_reader(lob) gives a stream of the whole value, read a chunk at a time: its read and readpartial are an IO's, so IO.copy_stream copies it.

Writing

create_lob(kind), of :character or :binary, gives a writer: each write or << takes a piece, sent in Appends of at most a chunk, and finish gives the value to pass as a parameter of a CLOB or BLOB. A CLOB's octets must be UTF-8, and a character may be split between writes.

conn = Turborel::Client.connect("127.0.0.1", 7422, user: "ann", mechanism: Turborel::Client::Trust.new)
conn.query("CREATE TABLE SCANS (ID INTEGER, IMAGE BLOB(10M))")
writer = conn.create_lob(:binary)
1024.times { writer.write("\x07".b * 4096) }
insert = conn.prepare("INSERT INTO SCANS VALUES (1, ?)")
conn.execute(insert, [writer.finish])
conn.query("COMMIT")
image = conn.query("SELECT IMAGE FROM SCANS").rows.first.first
p [image.data, image.octets]
p conn.lob_reader(image).read.bytesize
p conn.read_lob(image, 0, 4)
conn.query("COMMIT")
conn.terminate
[nil, 4194304]
4194304
"\a\a\a\a"

How long a locator lasts

A locator is valid until the transaction that read it ends. A statement whose reply sent one keeps its transaction active, under autocommit too, so commit once the value is read. free_lob(lob) ends the locator sooner; hold_lob(lob) keeps it past the commit, until it is freed or a rollback ends it. A locator no longer valid fails with SQLSTATE 0F001; free_lob and hold_lob of a value held whole, which has none, fail with 22000.

conn = Turborel::Client.connect("127.0.0.1", 7422, user: "ann", mechanism: Turborel::Client::Trust.new)
image = conn.query("SELECT IMAGE FROM SCANS").rows.first.first
p conn.in_transaction?
conn.hold_lob(image)
conn.query("COMMIT")
p conn.read_lob(image, 0, 2)
conn.free_lob(image)
begin
  conn.read_lob(image, 0, 2)
rescue Turborel::Error => e
  p [e.sqlcode, e.sqlstate]
end
conn.query("ROLLBACK")
conn.terminate
true
"\a\a"
[-301, "0F001"]

An import sends a field longer than 64 KiB for a CLOB or BLOB column by create_lob, so no message carries more, as Copying, Importing and Exporting tells.