Vert.x JDBCPool x Agroal x MS-SQL - Issue with stored procedure that does "nothing" (The statement must be executed before any results can be obtained.)

[wilnie]

Version

Vert.x 4.2.5
MS-SQL 8.2.2.jre8
Agroal 1.16

Context

Not sure if it's a Vert.x or MS-SQL or Agroal issue

When I call a stored procedure (sqlConnection.preparedQuery(storedProcedure).execute(params)) that may do nothing (cf. below) I got an exception.

com.microsoft.sqlserver.jdbc.SQLServerException: L'instruction doit être exécutée avant de pouvoir obtenir des résultats.
(in english The statement must be executed before any results can be obtained.)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(SQLServerStatement.java:2216)
at io.agroal.pool.wrapper.StatementWrapper.getGeneratedKeys(StatementWrapper.java:380)
at io.vertx.jdbcclient.impl.actions.JDBCQueryAction.decodeReturnedKeys(JDBCQueryAction.java:193)
at io.vertx.jdbcclient.impl.actions.JDBCQueryAction.decode(JDBCQueryAction.java:61)
at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:73)
at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:39)
at io.vertx.ext.jdbc.impl.JDBCConnectionImpl.lambda$schedule$3(JDBCConnectionImpl.java:219)
at io.vertx.core.impl.ContextImpl.lambda$null$0(ContextImpl.java:159)
at io.vertx.core.impl.AbstractContext.dispatch(AbstractContext.java:100)
at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:157)
at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:750)

Even with options

• setAutoGeneratedKeys = true/false
• set nocount on/off
  The error occurs

Maybe I didn't use the API correctly, but should not the API take in consideration the case when a stored procedure makes no select nor update nor insert nor delete ?

Look close to issue #199

Do you have a reproducer?

Not yet on GITHUB, be here is a simple Java code
`public class TestJdbc {
private static final Logger loggerClass = LogManager.getLogger(TestJdbc.class);

	public static void main(String[] args) {
		Vertx vertx = Vertx.vertx();

		JDBCPool pool = createPool(vertx, createDatabaseConfig());

		pool.getConnection()
				.onFailure(connectionFailure -> {
					connectionFailure.printStackTrace();
				})
				.onSuccess(sqlConnection -> {
					try {
						callStoredProcedure(sqlConnection, 1);
					}
					catch(Exception e) {
						e.printStackTrace();
					}
				});
	}

	private static void callStoredProcedure(SqlConnection connection, int param) {
		String storedProcedure = "{call myps(?)}";

		List paramsAsList = Stream.of(1).collect(Collectors.toList());
		Tuple params = Tuple.from(paramsAsList);
		connection
				.preparedQuery(storedProcedure)
				.execute(params)
				.onFailure(queryException -> {
					queryException.printStackTrace();
					connection.close();
				})
				.onSuccess(res-> {
					loggerClass.info("Executed");
					connection.close();
				});
	}

	private static JsonObject createDatabaseConfig() {
		JsonObject config = new JsonObject()
				.put("url", "jdbc:sqlserver://myserver:1234;database=mybase")
				.put("user", "myuser")
				.put("password", "mypassword")
				.put("max_pool_size", 100);

		return config;
	}

	private static JDBCPool createPool(Vertx vertx, JsonObject config) {
		JDBCConnectOptions connectOptions = new JDBCConnectOptions()
				.setJdbcUrl(config.getString("url"))
				.setUser(config.getString("user"))
				.setPassword(config.getString("password"))
				.setAutoGeneratedKeys(false);

		PoolOptions poolOptions = new PoolOptions()
				.setMaxSize(config.getInteger("max_pool_size"))
				.setConnectionTimeout(1)
				.setEventLoopSize(1);

		DataSourceProvider provider = new AgroalCPDataSourceProvider(connectOptions, poolOptions);
		return JDBCPool.pool(vertx, provider);
	}

}`

Stored procedure

`CREATE PROCEDURE [dbo].[myps]
-- Add the parameters for the stored procedure here
@Number int = NULL
AS
BEGIN
-- Insert statements for procedure here
IF(@Number = 0)
SELECT 'Zero'

--SELECT 1
--WHERE @number = 0

END`

Extra

If, in the stored procedure, I add a dummy SELECT 1 WHERE 1 = 0, no more exception.
Meaning that we may find a workaround on SQL side (but seems dirty)

[tsegismont]

Thanks for the report