Annotation @Procedure generating one too many argument

[MarcTerrasson]

Hi,

I used @Procedure annotation to call a stored procedure in a MS SQL-Server and it create one argument too many. I tried with @Query and it's ok.

Here is the stored procedure declaration

DELIMITER $$
CREATE PROCEDURE `import.SP_CreateImport`(IN `categoryCode` VARCHAR(10), IN `typeCode` VARCHAR(10), IN `name` VARCHAR(10), OUT `result` INT)
Select 1$$
DELIMITER ;

With @Procedure

    @Procedure(procedureName = "import.SP_CreateImport")
    Integer createImport(String categoryCode, String typeCode, String name);

result in

bdo-connector-1  | 2024-10-18T14:11:58.212Z DEBUG 1 --- [bdo-timsheet-extractor] [           main] org.hibernate.SQL                        :
bdo-connector-1  |     {call import.SP_CreateImport(?, ?, ?, ?)}
bdo-connector-1  | Hibernate:
bdo-connector-1  |     {call import.SP_CreateImport(?, ?, ?, ?)}
bdo-connector-1  | 2024-10-18T14:11:58.265Z  WARN 1 --- [bdo-timsheet-extractor] [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 8144, SQLState: S0002
bdo-connector-1  | 2024-10-18T14:11:58.266Z ERROR 1 --- [bdo-timsheet-extractor] [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Procedure or function SP_CreateImport has too many arguments specified.

With @Query

    @Query(value = "{CALL import.SP_CreateImport(:categoryCode, :typeCode, :name)}", nativeQuery = true)
    Integer createImport(@Param("categoryCode") String categoryCode, @Param("typeCode") String typeCode, @Param("name") String name);

result in

bdo-connector-1  | 2024-10-18T14:27:43.417Z DEBUG 1 --- [bdo-timsheet-extractor] [           main] org.hibernate.SQL                        :
bdo-connector-1  |     {CALL import.SP_CreateImport(?, ?, ?)}
bdo-connector-1  | Hibernate:
bdo-connector-1  |     {CALL import.SP_CreateImport(?, ?, ?)}
bdo-connector-1  | 2024-10-18T14:27:43.488Z  WARN 1 --- [bdo-timsheet-extractor] [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 50001, SQLState: S0001
bdo-connector-1  | 2024-10-18T14:27:43.489Z ERROR 1 --- [bdo-timsheet-extractor] [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Impossible to find import category [IMPORT -> CATEGORY -> XXX]

I still have work to do but the last issue lies in my SQL stored procedure, not in Java.

[mp911de]

The above @Procedure query works as expected. In addition to your input parameters, Integer createImport(…); also registers an out parameter for the return value. If you switch to void, then you will see only three parameters.

[MarcTerrasson]

Ok it seems I fail to see how to call a stored procedure with 3 parameters and a returned value like the following (I also complete the original post with it)

DELIMITER $$
CREATE PROCEDURE `import.SP_CreateImport`(IN `categoryCode` VARCHAR(10), IN `typeCode` VARCHAR(10), IN `name` VARCHAR(10), OUT `result` INT)
Select 1$$
DELIMITER ;

    @Procedure(procedureName = "import.SP_CreateImport")
    Integer createImport(String categoryCode, String typeCode, String name);

I though it would works. Could you help me please ?

[mp911de]

This is not a general Q&A forum and not a gound for exploring how your tooling is supposed to work.

You can get it to work with the following SQL declaration:

CREATE PROCEDURE SP_CreateImport(@categoryCode VARCHAR(10), @typeCode VARCHAR(10), @name VARCHAR(10), @result INT OUT)
    AS
BEGIN
SET @result = 123;
END

Note that as soon you return a result set (SELECT 1), you're no longer able to obtain a return value.