[CONJ-967]  clearParameters() breaks validity when using output parameters in stored procedures Created: 2022-04-29  Updated: 2022-05-23  Resolved: 2022-05-13

Status: Closed
Project: MariaDB Connector/J
Component/s: stored procedure
Affects Version/s: 3.0.4
Fix Version/s: 3.0.5

Type: Bug Priority: Major
Reporter: Holger Flörke Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None


 Description   

If you would like to use output parameters in stored procedures, you have to register these parameters with "registerOutParameter". You do not have to initialize the parameter with a value, because it is a output parameter.

If you would like to reuse a stored procedure, a common pattern is to call "clearParameters" to get rid of any input parameter value. After the "clearParameters" you have to set an explicit value for the output parrameter. Otherwise you will get an expception eg "Caused by: java.sql.SQLTransientConnectionException: (conn=5) Parameter at position 2 is not set". That's wrong.

 Comments   
Comment by Holger Flörke [ 2022-04-29 ]

RegisterOutParameter initialize the Out-Parameter:

  public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException {
 
    checkIndex(parameterIndex);
 
    outputParameters.add(parameterIndex);
 
    parameters.set(parameterIndex - 1, Parameter.NULL_PARAMETER);
 
  }

ClearParameters will throw away any parameters regardless of in/out:

  public void clearParameters() throws SQLException {
 
    checkNotClosed();
 
    parameters = new ParameterList();
 
  }

ValidParameters will check of any parameter regardless of in/out has a value.

In my opinion, out-parameters should not be checked in ValidParameters or should be Nulled in ClearParameters.

Questions are welcome. I am able to provide a test case...

Comment by Diego Dupin [ 2022-05-13 ]

Thanks for reporting this issue.
OUTPUT parameters are now taken in account when clearing parameters (https://github.com/mariadb-corporation/mariadb-connector-j/commit/a6972bad7e3742823f24e6e0ea16e8e1ec5ef94d)
This will be corrected in 3.0.5 release

Generated at Thu Feb 08 03:19:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.