User Defined Type (UDT) Out Parameters

[Michael-A-McMahon]

JDBC has registerOutParameter methods on CallableStatement which accept a type name:
https://docs.oracle.com/javase/8/docs/api/java/sql/CallableStatement.html#registerOutParameter-int-java.sql.SQLType-java.lang.String-

Is there any interest in the R2DBC SPI also having a way to do this?

This problem can already be solved by driver-specific extensions to the SPI. For instance, I've drafted a solution for Oracle R2DBC here:
oracle/oracle-r2dbc#83 (comment)

Just wanted to check in with the broader R2DBC community before I go off and implement this. Should we have a standard way to do this? Or, is it fine for each driver to have its own unique API?

[mp911de]

We introduced with version 0.9 a Parameter abstraction that accepts a type reference along with in/inOut/out characteristics. I think https://r2dbc.io/spec/1.0.0.RELEASE/api/io/r2dbc/spi/Parameters.html#out-io.r2dbc.spi.Type- should lead you to the desired functionality.

Parameter objects are designed to be used with Statement.bind(...) so we do not need additional API.

The spec contains also a bit of documentation regarding out parameters and type references.

The Postgres driver uses this feature to reference UDTs. The type lookup is however vendor-specific and we never really considered a mapping API/registry for types. That could be an inspiration for the next major revision of R2DBC.

Let me know whether that helps.

[Michael-A-McMahon]

I like the idea of using io.r2dbc.spi.Type to represent UDTs. As an Oracle specific extension, I'm considering a factory method like this:

  /**
   * Returns a {@code Type} representing a user defined type.
   * @param type The base type
   * @param name The type name
  public Type userDefined(Type type, String name) {
    ...
  }

This lets the application code express the same information as JDBC's registerOutParameter method: A base type, and a user defined name. It might be used like this:

connection.createStatement("{? = call CREATE_A_PERSON (?)}")
  .bind(0, Parameters.out(
    OracleR2dbcTypes.userDefined(OracleR2dbcTypes.STRUCT, "UDO_PERSON")))
  .bind(1, "YOURE NAME")
  .execute();

I'm confused about what is meant by "mapping API/registry". In this case, I'm just considering how application code would express the name of a user defined type. JDBC choose to codify that as a part of its standard, with registerOutParameter accepting a name parameter. If multiple R2DBC drivers need to support named types, then a standard way to do that could be beneficial, as it would allow for code that is portable across drivers.

A SPI extension that is specific to Oracle R2DBC is fine for now. But if we end up with multiple drivers exposing different ways to express named types, then a common interface might be a better solution.

[mp911de]

I'm confused about what is meant by "mapping API/registry".

Sorry, I wasn't clear. Something along the lines of Type ConnectionFactory.lookupType(String typeName) or Type Connection.getUserType(String typeName).

[Michael-A-McMahon]

Thanks, I understand what you mean now, and I like the idea of a type lookup method.
Assuming the result is not already cached, I think this would require a remote database call. So perhaps Publisher<Type> would be the right return type?

[mp911de]

Good catch, I was too quick capturing my idea. Indeed, Publisher<Type> would be the way to go.

[lukaseder]

Assuming the result is not already cached, I think this would require a remote database call.

Are you folks sure this is what should be done here? In JDBC, the Connection::getTypeMap is purely about client side type mappings, I think?

[Michael-A-McMahon]

Perhaps we should use this thread to explore the larger idea of supporting UDTs?

I originally just wanted support for the highly specific case of a UDT out parameter bind. But really there's more to this: What Java type is that out parameter mapped to? I think JDBC would answer that question with "java.sql.Struct or the connection type map". And yes, set/getTypeMap should be a purely client side operation; No publisher needed for that.

But for this proposed lookupType method, that would be a remote call to the database. At the least, the driver would need to check if the named type actually exists, and emit an error if not. The driver might also obtain metadata about the UDT, such as the fields in a STRUCT, or the element type of an ARRAY.

I have to admit that my knowledge on JDBC's support for structured types is weak. I'm planning to dig in to it more in the coming weeks. At the moment, I'm super busy with other projects. I'd like to contribute more once things settle down.

[lukaseder]

Perhaps we should use this thread to explore the larger idea of supporting UDTs?

I definitely think so. This shouldn't be addressed merely in the context of procedure parameters.

JDBC has 2 ways of interacting with UDTs:

• java.sql.Struct just creates structurally typed UDT references for quick ad-hoc usage. It's usually good enough
• java.sql.SQLData is a way to allow for implementing custom client side representations of UDTs as Java objects. When registering something in the Connection::getTypeMap (or when providing the type map explicitly to e.g. CallableStatement::getObject, etc.), then the expectation is for all the Class references in the type map to be references to classes implementing SQLData

I personally think that the second approach should not have been added to JDBC. It feels rather weird, has a few important flaws (e.g. SQLData::readSQL and SQLData::writeSQL cannot access the JDBC Connection, so it's hard to get this binding right when nesting data structures and arrays), and the java.sql.Struct approach was really sufficient already.

I've never had the need for any round-trip inducing lookupType method in JDBC, neither back when binding to JDBC directly at a previous employer, nor when implementing jOOQ. Usually, there has always been an assumption that the required type exists, and that it has a certain specification. You don't need a lookupTable method either, because you make the same assumptions about tables, no?

A bit of additional background on UDTs: While ojdbc implemented JDBC completely in this area and probably also designed these ORDBMS features both in JDBC and in the SQL standard, PostgreSQL has a more elegant view on the topic. In PostgreSQL, table row types are also "UDTs", so for example, you can write things like:

SELECT actor FROM actor;
SELECT ROW(id, first_name, last_name)::actor FROM actor;

In both cases, you'll get a nested record / UDT style data structure of type actor, which isn't a formally declared TYPE but an implicit one, derived from the table. Note that I'm using standard SQL ROW constructors here to create a nested record. You could also just omit the cast and write this:

SELECT ROW(id, first_name, last_name) FROM actor;

Now, the UDT is only structurally typed, not nominally typed. Informix also works more or less this way, and I think it would make sense for Oracle Database to also add these features to consolidate and follow the SQL standard more closely (though I don't think it will be a priority any time soon).

So, a UDT is just a nominally typed ROW, very similar to io.r2dbc.spi.Row. I think the SPI must be able to model at least Oracle's and PostgreSQL's interpretation of the ORDBMS feature, as they're the most popular implementations. I don't think anyone is already working on an Informix driver?

[Michael-A-McMahon]

I've decided to add support for UDTs in the Oracle driver. If anyone is curious about that, they can see the changes here:
oracle/oracle-r2dbc#104

Thank you @mp911de and @lukaseder for the discussion on this thread. I found it to be very helpful when making some decisions for the Oracle driver.

[lukaseder]

Thanks for the ping. I have a TODO to look into these integrations sometime soon, as a customer is interested.

[Michael-A-McMahon]

Good to hear. Feedback from library authors such as your selves is truly invaluable. I did my best to get the API right for Oracle, but please let me know if you think there's anything we do better.