SELECT TOP @size fails with R2dbcException

[stdreyer]

Parameter binding is not working at all on the latest snapshot or 1.0.0.M7.

Depending on what you do it says

Binding parameters is not supported for the statement [SELECT TOP $1 * FROM metadata
Binding parameters is not supported for the statement [SELECT TOP :size * FROM metadata
executeMany; uncategorized R2dbcException for SQL [SELECT TOP @size * FROM metadata]; Incorrect syntax near '@size'.; nested exception is R2dbcException{errorCode=102, sqlState='S0001'} io.r2dbc.mssql.MssqlException: Incorrect syntax near '@size'.

A prepared statement is recognized when it contains an @ but parameters which such a character cannot be bound. The documentation says named parameters start with : and indexed with $.

[mp911de]

SQL server uses at-prefixed parameters, see
https://github.com/r2dbc/r2dbc-mssql/blob/master/src/test/java/io/r2dbc/mssql/ColumnMetadataIntegrationTests.java#L134

Care to describe your entire scenario? Looks like you’re integrating with a Spring Data R2DBC and there we can pass thru parameters and we can use colon-prefixed named parameters in the current snapshot version.

[stdreyer]

I'm totally aware of that but still it does not work.

Currently I'm trying to invoke the database client inside of a rest controller call directly:

  @GetMapping
  Flux<Metadata> findAll(@RequestParam(defaultValue = "20") final int size) {
    return databaseClient
      .execute()
      .sql("SELECT TOP @P0 * FROM metadata")
      .bind("P0", size)
      .as(Metadata.class)
      .fetch()
      .all();
  }

Results in:

executeMany;` uncategorized R2dbcException for SQL [SELECT TOP @P0 * FROM metadata]; Incorrect syntax near '@P0'.; nested exception is R2dbcException{errorCode=102, sqlState='S0001'} io.r2dbc.mssql.MssqlException: Incorrect syntax near '@P0'.

Prior to that I tried it with a @Query method in my ReactiveCrudRepository.

I have a Spring Boot application with WebFlux + Netty.
R2DBC dependencies look like that:

      <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-r2dbc</artifactId>
        <version>1.0.0.M1</version>
      </dependency>

      <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-releasetrain</artifactId>
        <version>Lovelace-RELEASE</version>
        <scope>import</scope>
        <type>pom</type>
      </dependency>
      <dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-spi</artifactId>
        <version>1.0.0.M6</version>
      </dependency>
      <dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-mssql</artifactId>
        <version>1.0.0.M7</version>
      </dependency>

[mp911de]

Parametrization of TOP does not seem to be possible as per SQL Server. Using the same statement with JDBC gives me the same failure:

        SQLServerDriver driver = new SQLServerDriver();
        Properties properties = new Properties();


        Connection connection = driver.connect("jdbc:sqlserver://localhost:1433;databaseName=master", properties);

        PreparedStatement ps = connection.prepareStatement("SELECT TOP ? * FROM mytable");
        ps.setInt(1, 10);
        ps.executeQuery();

Driver Logging:

Mär 15, 2019 3:04:26 PM com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement buildExecSQLParams
FEIN: SQLServerPreparedStatement:1: calling sp_executesql: SQL:SELECT TOP @P0 * FROM mytable    

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)

Closing this one as invalid.

On a related note: Please file a feature request in https://github.com/spring-projects/spring-data-r2dbc to add paging capabilities to DatabaseClient.select().

[stdreyer]

Oh there is paging in the DatabaseClient.select():

   return databaseClient.select()
      .from(Metadata.class)
      .page(PageRequest.of(page, size))
      .fetch()
      .all();

Guess what happens:

executeMany; uncategorized R2dbcException for SQL [SELECT id, title, cost_center_number, property_number, building_number, story_code, unit_code, code, building_section, street_address, drawing_type, drawing_status, date_created FROM metadata OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY]; Incorrect syntax near '1'.; nested exception is R2dbcException{errorCode=102, sqlState='S0001'} io.r2dbc.mssql.MssqlException: Incorrect syntax near '1'.

[mp911de]

That one does not yet consider dialect specifics. SQL server requires ORDER BY fields for pagination. That’s going to be considered in Spring Data‘s Dialect support.

[stdreyer]

I still don't get the vibe how this is supposed to work.

[mp911de]

You need to pass in a valid SQL statement into the statement creation method via "SELECT TOP " + count + " * FROM metadata".

The exception on OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY is a bug in Spring Data R2DBC that will be adressed with spring-projects/spring-data-r2dbc#55 and spring-projects/spring-data-relational#125.