Alpha

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

Copying, Importing and Exporting

Rows move in bulk three ways: a copy sends them into a table with no round trip but its end; an import reads them from a source, such as CSV text; an export writes a query's rows as they come. This chapter tells how, with the low-level client and the table of Large Objects.

Copying rows into a table

A connection's bulk().copyIn begins a copy into a table, of the columns named, or all of them; its send sends rows, many to a message, with no reply each; its finish gives the rows inserted. copyRows copies the rows an iterable gives. The server assigns each value to its column as INSERT does, so a string too long is refused, not cut. A copy is all or nothing: a row refused undoes every row of it, its failure's detail naming the row. Under autocommit the copy is its own transaction; otherwise it runs within the transaction under a savepoint of its own, so a copy that fails keeps what the transaction did before. A copy closed before it finishes is abandoned. While one is under way the connection sends nothing but its rows, its end and a ping; any other request is the program's misuse (SQLSTATE TB910).

conn.statements().query("CREATE TABLE READINGS (ID INTEGER PRIMARY KEY, SENSOR VARCHAR(10))");
conn.statements().query("COMMIT");
List<List<Value>> rows = new ArrayList<>();
for (int i = 1; i <= 50_000; i++) {
    rows.add(List.of(new Value.Exact(BigInteger.valueOf(i), 0), new Value.Text("s" + (i % 7))));
}
conn.setAutocommit(true);
System.out.println("copied " + conn.bulk().copyRows("READINGS", List.of("ID", "SENSOR"), rows));
List<List<Value>> more = new ArrayList<>();
for (int i = 50_001; i <= 50_010; i++) {
    more.add(List.of(new Value.Exact(BigInteger.valueOf(i), 0), new Value.Text("s0")));
}
more.set(3, more.get(0));
try {
    conn.bulk().copyRows("READINGS", List.of(), more);
} catch (TurborelException e) {
    System.out.println(e.getSQLState() + ", " + e.diagnostics().get("DETAIL"));
    System.out.println("rows kept of the second copy: " + ((com.turborel.jdbc.internal.session.Outcome.Rows)
            conn.statements().query("SELECT COUNT(*) FROM READINGS WHERE ID > 50000")).rows());
}
copied 50000
23000, row 4 of the copy
rows kept of the second copy: [[Exact[units=0, scale=0]]]

Importing

bulk().importRows inserts a Source's records into a table. A source gives its headings, the columns it fills, if it knows them, and then a record at a time, so memory holds one record whatever the source's size. Delimited reads CSV and tab-separated text: a header, a delimiter and a text for null as its Dialect says, a field in quotes holding commas, quotes written twice and line breaks.

An Import names the table, how many rows are committed at a time, 1000 by default or 0 for all at once, and whether a row that fails is skipped, and given to the program, rather than ending the import. The session must have no transaction active, as an import commits its batches. Where the server grants copy, no row is to be skipped, and no column is a large object, the import goes by copy, with no round trip a row; otherwise by a prepared INSERT. A string too long for its column is refused, not cut, and a field longer than 64 KiB for a large object column is sent by creating a large object. An import that stops throws Import.Stopped, saying where its record began, why, and how many rows were committed and rolled back.

String csv = "ID,TITLE\n10,Emma\n11,\"Persuasion, a novel\"\n12,\"a title far longer than forty characters, it is\"\n13,Sanditon\n";
try (Connection conn = Connect.tcp("127.0.0.1", 17434, "ann", new Trust())) {
    Delimited source = new Delimited(new ByteArrayInputStream(csv.getBytes(StandardCharsets.UTF_8)),
            Delimited.Dialect.csv().withHeader());
    Import.Imported imported = conn.bulk().importRows(source, new Import("BOOKS", 1000, true),
            rejected -> System.out.println(rejected));
    System.out.println(imported);
record 3, line 4: [ISO/IEC 9075:1992 §13.8 GR4b iii, §9.2 GR3e ii] a value of 47 characters, not spaces beyond 40, does not fit CHARACTER VARYING(40); skipped
Imported[rows=3, rejected=1]

Exporting

bulk().export runs a query and writes its rows to an OutputStream as they come, a batch at a time, so memory holds a batch whatever the result's size. As CSV, it writes a header of the columns' names, then a record for each row, ended by CR LF, a field quoted where it holds a comma, a quote, a line break or white space at either end, a null an empty field, an empty string ""; each value as Turborel writes it. As native rows, it writes the protocol's own frames, which Bulk.readNative reads back with every value as it was, of whatever type.

ByteArrayOutputStream csv = new ByteArrayOutputStream();
long rows = conn.bulk().export("SELECT ID, TITLE FROM BOOKS ORDER BY ID", csv, Bulk.Format.CSV);
System.out.print(csv.toString(StandardCharsets.UTF_8));
System.out.println(rows + " rows");
ByteArrayOutputStream frames = new ByteArrayOutputStream();
conn.bulk().export("SELECT ID, TITLE FROM BOOKS ORDER BY ID", frames, Bulk.Format.NATIVE);
Outcome.Rows back = Bulk.readNative(new ByteArrayInputStream(frames.toByteArray()));
System.out.println(back.rows());
ID,TITLE
1,Long
2,Short
2 rows
[[Exact[units=1, scale=0], Text[text=Long]], [Exact[units=2, scale=0], Text[text=Short]]]

The classes are told in full in the API Reference, Copy, Import and Export.