R2DBC 0.9.0.M2 available (Borca-M2)

Dear R2DBC Community,

We’re excited to announce that the majority of R2DBC implementations has upgraded to R2DBC 0.9 M2. As of today, the following libraries comply with the specification:

  • oracle-r2dbc 0.3.0
  • r2dbc-h2 0.9.0.M1
  • r2dbc-mariadb 1.1.0-beta
  • r2dbc-mssql 0.9.0.M2
  • r2dbc-pool 0.9.0.M2
  • r2dbc-postgres 0.9.0.M2
  • r2dbc-proxy 0.9.0.M2

You can consume drivers and the specification artifacts either individually or through a version-managed bill of materials:

If you use Maven, you can add the following lines to your pom.xml:

<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>io.r2dbc</groupId>
      <artifactId>r2dbc-bom</artifactId>
      <version>Borca-M2</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>

<dependencies>
  <dependency>
    <groupId>com.oracle.database.r2dbc</groupId>
    <artifactId>oracle-r2dbc</artifactId>
  </dependency>
</dependencies>

This release is a major leap forward as the specification version 0.9 defines fine-grained interaction for parameter specifications, out-parameter consumption (typically used for stored procedures), and consumption of individual result segments using a single operator.

Let’s have a detailed look at the most significant changes.

Consumption of OUT Parameters

The initial draft of R2DBC assumed that stored procedures are out of scope because they were rarely used. While the specification evolved, we learned that stored procedures are still heavily leveraged for workloads that are heavily tied to the database. We decided to provide means to call stored procedures properly by introducing the Parameters abstraction and consumption of results of stored procedures by introducing OutParameters.

The Parameters abstraction was introduced with the previous milestone. OutParameters is specified in this milestone. It essentially provides access by name and index to out parameters returned from a stored procedure invocation. To consume out parameters, callers need to:

  1. Register OUT parameter bindings
  2. Consume out parameters through the map(…) or flatMap(…) operator.

Consider the following stored procedure (using Microsoft SQL Server):

CREATE PROCEDURE say_hello
    @TheName nvarchar(50),
    @Greeting nvarchar(255) OUTPUT
AS

    SET NOCOUNT ON;  
    SET @Greeting = CONCAT('Hello ', @TheName)

This procedure concatenates Hello with the input parameter @TheName and returns @Greeting as out parameter.

The invocation is straight forward by calling say_hello through SQL and two parameters. Note that @Greeting is bound as out parameter with a specific data type of VARCHAR.

connection.createStatement("EXEC say_hello @P0, @Greeting OUTPUT")
    .bind("@P0", "Walter")
    .bind("@Greeting", Parameters.out(R2dbcType.VARCHAR))
    .execute()
    .flatMap(it -> it.map((readable) -> {
        return readable.get(0);
    }));

You might be surprised by seeing the map(…) operator accepting a Function<Readable, T> instead of BiFunction<Row, RowMetadata, T>. With the introduction of OutParameters, we introduced Readable as superinterface for OutParameters and Row that defines get(…) methods for both subtypes. Both Row and OutParameters follow the same semantics of by-name and by-index access to statement results so we introduced map(Function<Readable, T>) for easier consumption of tabular and stored procedure results.

OutParameters and Row define getMetadata methods in case you’re interested in out parameters/column metadata.

Consuming individual segments

Imagine a client that passes-thru SQL without being aware of what kind of result it is going to produce. Result.map(…) and Result.getRowsUpdated() have been the only two methods to consume results and both are mutually exclusive. Consuming rows doesn’t allow for access to update counts and vice versa.

This release removes this limitation by introducing filter and flatMap methods that conceptually turn Result into a Publisher<Segment>. Segment is a type hierarchy to represent the individual result types that a statement can produce: Update counts, rows, out parameters, messages, and any vendor-specific types.

Consumers that are interested in all types of segments can leverage the flatMap(Function<Segment, Publisher<T>>) operator to process a single segment into zero, one, or many resulting items, depending on how the result should be processed.

Consider the following example. The function only processes row segments by copying values into a Map:

result.flatMap(segment -> {

    if (!(data instanceof Result.RowSegment)) {
        return Mono.empty();
    }

    Result.RowSegment data = (Result.RowSegment) segment;

    Map<String, Object> rowData = new HashMap<>();
    for (ColumnMetadata column : data.row().getMetadata().getColumnMetadatas()) {
        rowData.put(column.getName(), data.row().get(column.getName()));
    }

    return Mono.just(rowData);
})

To avoid repetative patterns of instanceof checks, results can be filtered regarding their segments that a Result should hold. filter(Predicate<Segment> filter) returns a filtered Result. That is useful when interested only in particular segments. The code above could be rewritten to:

result.filter(Result.RowSegment.class::isInstance).flatMap(segment -> {

    Result.RowSegment data = (Result.RowSegment) segment;

    Map<String, Object> rowData = new HashMap<>();
    for (ColumnMetadata column : data.row().getMetadata().getColumnMetadatas()) {
        rowData.put(column.getName(), data.row().get(column.getName()));
    }

    return Mono.just(rowData);
});

A segment type that was introduced with this release is the message segment. Messages are of informational, warning or error nature and can lead to error signals by translating these into R2dbcException.

The segment API now gives access to SQL warnings that weren’t accessible in the previous R2DBC release.

result.filter(Result.Message.class::isInstance).flatMap(data -> {

    Result.Message message = (Result.Message) data;
    assertThat(message.errorCode()).isZero();
    assertThat(message.sqlState()).isEqualTo("S0000");
    assertThat(message.message()).isEqualTo("error message desc");
    assertThat(message.exception()).isInstanceOf(R2dbcNonTransientResourceException.class);

    return Mono.just(data);
}))

The filter(…) operator can also be used to filter out message segments so you can create a Result that doesn’t contain any segments leading to an error:

result.filter(it -> !(it instanceof Result.Message)).map(…)

Subsequent operators (map, getRowsUpdated, flatMap) process the result in its filtered form. To illustrate this a bit more, the following code would filter out all update counts and consuming the update count through getRowsUpdated would never return the actual result:

result.filter(it -> !(it instanceof Result.UpdateCount)).getRowsUpdated()

The segments API is an advanced mechanism to consume results and segments are only valid within the map/flatMap operator function. Segments must never leave these functions as they are invalidated once the function call terminates.

You can find the full list of API changes in the R2DBC 0.9.0.M2 announcement blog post.

This release ships a first batch of drivers that implement the specification changes. Watch out for future evolution or reach out to the maintainers of the driver you’re interested in if the driver doesn’t fully support the specification yet.