Calling SQL Server stored procedure from Java with R2DBC

Question

I have a stored procedure which has 2 parameters. An input NVARCHAR and an output parameter int. That stored procedure is just writing a string into a table. It's a procedure for testing out some tech. I try to call it from Java with R2DBC. I used the Connection.createStatement and the DatabaseClient approaches as well. None of them really worked out.

Here is an example of my java code using the DatabaseClient. It's just a sample which is good for a proof of concept that I can call a stored procedure.

      public int callProcedureWithDatabaseClient() {
        databaseClient.sql("CALL MY_SCHEMA.MY_PROCEDURE(Prm1, @Prm2)")
            .bind("Prm1", "Some text to save in database")
            .fetch()
            .all()
            .doOnNext(stringObjectMap -> log.info("{}" + stringObjectMap))
            .subscribe();
    
        return 0;
      }

After calling this method I get this error:

reactor.core.Exceptions$ErrorCallbackNotImplemented: java.util.NoSuchElementException: Parameter [Prm1] does not exist in query [CALL MY_SCHEMA.MY_PROCEDURE(Prm1, @Prm2)]
Caused by: java.util.NoSuchElementException: Parameter [Prm1] does not exist in query [CALL MY_SCHEMA.MY_PROCEDURE(Prm1, @Prm2)]

I think the syntax is not really correct. I tried more ways, more syntaxes, but there was always an issue. Also tried without the schema, did not worked.

• Do you have any idea how can I make it work?
• The second question is if I can make it work, how can I get the value of the output variable Prm2?

Alternatively, here is a sample code for the connection based call. If the one above can't be fixed, but this one can, than it is also OK:

 public int callProcedureWithConnection() {
    ConnectionFactoryUtils.getConnection(connectionFactory)
        .flatMapMany(connection -> connection.createStatement("CALL MY_SCHEMA.MY_PROCEDURE(:Prm1, @Prm2)")
              .bind(":Prm1", Parameters.in(R2dbcType.NVARCHAR, "bacabacabacabacabacn"))
              .bind("@Prm2", Parameters.out(R2dbcType.INTEGER))
            .execute())
        .flatMap(result -> {
          return result.map((row, rowMetadata) -> {
            log.info("{}", row);
            return row.get(0);
          });
        })
        .subscribe();
    return 0;
  }

Any help would be appreciated! Thanks you!