How get ColumnMetadata in Result ?

zhou-hao commented 1 year ago

select * from my_table where 1=2;

I want get the column information that sql will return, Whether there is a result or not.

In JDBC: ResultSet.getMetaData().

Michael-A-McMahon commented 1 year ago

In our current release, there's no way to get metadata when a query returns zero rows. This limitation exists because the R2DBC SPI requires metadata to be emitted along with a row.

I do not know if all databases support this, but Oracle Database will respond with metadata even if a query returns zero rows. This metadata could be exposed by an Oracle R2DBC extension, but it would be nicer to have a standard, portable, interface in the R2DBC SPI.

My first thought is to define a subclass of Result.Segment:

package io.r2dbc.spi;

/**
 * A segment of metadata for rows that have been queried. This segment is
 * emitted before any {@link RowSegment} of a {@code Result}. Instances of this
 * segment allow metadata to be consumed when a query returns zero rows.
 */
interface RowMetadataSegment extends Result.Segment {

  /** @return The metadata of rows that have been queried. Not null. */
  RowMetadata metadata();
}

A RowMetadataSegment could be consumed using the flatMap method of Result.

Publisher<String> toCsv(Result result) {
  return result.flatMap(segment -> {
    if (segment instanceof RowMetadataSegment) {
      String headerRow =
        ((RowMetadataSegment)segment).metadata()
          .getColumnMetadatas()
          .stream()
          .map(ColumnMetadata::getName)
          .colllect(Collectors.joining(","));
      return Flux.just(headerRow);
    }
    else if (segment instanceof RowSegment) {
      Row row = ((RowSegment)segment).row();
      String dataRow = IntStream.range(0, row.metadata().getColumnMetadatas().size())
        .mapToObj(index -> row.get(index, String.class))
        .collect(Collectors.joining(","));
      return Flux.just(dataRow);
    }
    // ignoring other segments
  });

This is all a rough draft of an idea to get us started. (I'm sure there's at least one compiler error above).

Would RowMetadataSegment offer the solution you need? Maybe we need something else, or maybe there's a better way to do it?

Michael-A-McMahon commented 1 year ago

Whoops. I thought this issue was opened against oracle-r2dbc. Sorry for any confusion.

I'll leave my post up as it might provide some ideas for an SPI enhancement.

mp911de commented 1 year ago

What Michael said. If there are no results, you won't get hold of any metadata. The specification describes how to get metadata if there is a result with at least one row in it.

r2dbc / r2dbc-spi

How get ColumnMetadata in Result ? #279