Alpha

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

Running Queries

A connection runs a statement with query, and gives what it did as an Outcome. This chapter tells how; Statements and Cursors lists every method.

Each example runs on $connection, a connection made as Connecting shows, as a user with a schema of its own.

A statement and its outcome

query runs one statement. A query's Outcome holds its columns, each a Turborel\Type\Column of a name and a type, and its rows, each a list of one value for each column. Another statement's outcome has no columns, and its completed gives its completion: its SQLCODE and SQLSTATE, 0 and 00000 of a statement that did what it was asked. isRows() tells the one from the other.

$connection->query('CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(20))');
$connection->query("INSERT INTO STAFF VALUES (1, 'Ada'), (2, 'Grace')");
$staff = $connection->query('SELECT ID, NAME FROM STAFF ORDER BY ID');
foreach ($staff->rows as [$id, $name]) {
    echo $id->units, ' ', $name, PHP_EOL;
}
$none = $connection->query('SELECT NAME FROM STAFF WHERE ID > 9');
echo count($none->rows), ' rows, SQLCODE ', $none->completed->sqlcode(), ', SQLSTATE ', $none->completed->sqlstate(), PHP_EOL;
1 Ada
2 Grace
0 rows, SQLCODE 100, SQLSTATE 02000

A query that returns no row completes with SQLCODE 100 and SQLSTATE 02000. A query's completion comes once its last row has, so completed is that of a query too.

Values

A character string comes as a PHP string, an approximate number as a float, a boolean as a bool, and null as null. An exact number comes as a Turborel\Value\Exact, of its digits as units and its scale, so none of its digits is lost; a datetime, an interval, a binary string, a UUID, an XML value, a large object and each composite come as a class of Turborel\Value. A statement's parameters are written the same way, and a PHP int or float is an exact or approximate number. Appendix A maps each type.

Rows as they come

query holds every row before it returns. queryInto gives a query's columns, then its rows a batch at a time as the server sends them, to a Turborel\Statements\Sink, so no more than a batch is held; it returns a Streamed of how many rows there were, or of another statement's completion.

use Turborel\Statements\Sink;

$connection->query('CREATE TABLE TEN (N INTEGER)');
$connection->query('INSERT INTO TEN VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)');
$sum = new class implements Sink {
    public int $total = 0;
    public function columns(array $columns): void {}
    public function rows(array $rows): void
    {
        foreach ($rows as [$n]) {
            $this->total += (int) $n->units;
        }
    }
};
$streamed = $connection->queryInto('SELECT A.N FROM TEN A, TEN B, TEN C', $sum);
echo $streamed->rows, ' rows, their sum ', $sum->total, PHP_EOL;
1000 rows, their sum 4500

What a completion tells

completed() gives the last statement's completion, each part as the server granted it: rows(), the rows an INSERT, UPDATE, DELETE or MERGE changed, or a query returned; generated(), the identity values an INSERT generated, in the order of its rows, as SQL writes them; and time(), the server's time for the statement in microseconds, total, queued waiting for a worker before it, and locks waiting for locks within it. commitPosition() gives the log position of the last commit.

$connection->setAutocommit(true);
$connection->query('CREATE TABLE PEOPLE (ID INTEGER GENERATED ALWAYS AS IDENTITY, NAME VARCHAR(20))');
$connection->query("INSERT INTO PEOPLE (NAME) VALUES ('Edsger'), ('Barbara')");
$done = $connection->completed();
echo $done->rows(), ' rows, generated ', implode(', ', $done->generated()), PHP_EOL;
2 rows, generated 1, 2

Several statements in one request

queryMany runs a text of several statements, split at each semicolon outside a string, a comment or a compound statement, as one request: each in order, until one fails. Its Ran gives each outcome in outcomes, and in failure the failure that stopped the rest, if one did.

$ran = $connection->queryMany("CREATE TABLE NOTES (T VARCHAR(10)); INSERT INTO NOTES VALUES ('a'); SELECT * FROM NOPE; INSERT INTO NOTES VALUES ('b')");
echo count($ran->outcomes), ' ran; then ', $ran->failure->getSqlState(), ': ', $ran->failure->getMessage(), PHP_EOL;
2 ran; then 2A000: table ANN.NOPE is not defined

Query attributes

setQueryAttributes sets attributes, by name, that every later statement carries until they are set again; SQL reads one by QUERY_ATTRIBUTE(name). One named traceparent is a request's trace context, which the server writes in its record of a slow statement.

$connection->setQueryAttributes(['team' => 'tea']);
echo $connection->query("VALUES QUERY_ATTRIBUTE('team')")->rows[0][0], PHP_EOL;
tea