Spring Boot, JPA, @Query, Sybase stored procedure

Question

Trying to make a Sybase stored procedure call using JPA @Query. I was following the info presented here, https://www.baeldung.com/spring-data-jpa-stored-procedures, as a guideline, even though the info is for MySQL and named parameters. I know the stored procedure works, as I can execute it using a client, DBVis. I also know the values to the stored procedure are correct, as I've turned on hibernate logging and I always see the following snippet before each call :

2020-12-30 13:51:07 TRACE o.h.t.d.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [pub_req_t]
2020-12-30 13:51:07 TRACE o.h.t.d.sql.BasicBinder - binding parameter [2] as [INTEGER] - [1]

The @Query looks like the following :

@Query(value = "call rp_surrogate(?1, ?2);", nativeQuery = true)
int getSurrogateByQueryV1(String surrtype, Integer newsurr );

The stored procedure declaration is :

CREATE PROCEDURE dbo.rp_surrogate (
    @surrtype       char(32),
    @newsurr        domn_num_id    output
)
as begin

It doesn't seem to matter what I pass into the ?2 (output) parameter, as it's ignored anyway. I've tried both jconn4, version 7.0, and jtds, version 1.3.1, JDBC drivers...both produce the same exception :

jconn4 - Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Incorrect syntax near '@p0'.
jtds - Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

Runtime Stack :

Spring Boot 2.2.8
Java 1.8
Sybase ASE 15

I've tried using @Modifying annotation in conjunction with @Trasactional : Thinking the JPA would perhaps utilize @P0 as rowsUpdated or a status code for the stored procedure...Nope.

@Modifying
@Query(value = "call rp_surrogate(?1, ?2);", nativeQuery = true)
@Transactional(rollbackFor=Exception.class)
int getSurrogateByQueryV1(String surrtype, Integer newsurr );

Results in : Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Incorrect syntax near '@p0'.

Also, tried 4 variations on the @Query, all return the same exception...Some with a semi-colon, some with a call.

@Query(value = "call rp_surrogate(?1, ?2)", nativeQuery = true)
int getSurrogateByQueryV1(String surrtype, Integer newsurr );

Results in : Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

@Query(value = "call rp_surrogate(?1, ?2);", nativeQuery = true)
int getSurrogateByQueryV2(String surrtype, Integer newsurr );

Results in : Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

@Query(value = "rp_surrogate(?1, ?2);", nativeQuery = true)
int getSurrogateByQueryV3(String surrtype, Integer newsurr );

Results in : Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

@Query(value = "rp_surrogate(?1, ?2)", nativeQuery = true)
int getSurrogateByQueryV4(String surrtype, Integer newsurr );

Results in : Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

About the only thing I can report that works is the following, which works, but we'd like to have a clean/lean version using @Query, since some of these procedures we need to call are fairly large in parameters.

@Override
public int getSurrogateByType(String typeSurrogate) {
    EntityManager em = secondaryEntityManager.getObject().createEntityManager();

    StoredProcedureQuery query = em.createStoredProcedureQuery("rp_surrogate");
    // JDBC : The jconn and jtDS drivers do NOT understand named parameters...we
    // MUST use positional...
    //query.registerStoredProcedureParameter("surrtype",     String.class,     ParameterMode.IN    );
    //query.registerStoredProcedureParameter("newsurr",     Long.class,     ParameterMode.OUT    );
    query.registerStoredProcedureParameter(1, String.class,     ParameterMode.IN);
    query.registerStoredProcedureParameter(2, Integer.class,    ParameterMode.OUT);
    getLog().error("getSurrogateByType() - Surrogate Type [{}]", typeSurrogate);
    query.setParameter(1, typeSurrogate);

    try {
        query.execute();
    } catch (Exception ltheXcp) {
        getLog().error("{}() - process failure, with exception(s) : ", this.getClass().getSimpleName(), ltheXcp);
        ltheXcp.printStackTrace();
        throw ltheXcp;
    }

    Integer commentCount = (Integer) query.getOutputParameterValue(2);
    return commentCount.intValue();

}

Does anyone know how to make such a call with a Sybase driver and @Query?

can you make queries direct in Sybase? Have you tried what works there? I always had errors with sybase because it expected the owner name for example dbo.rp_surrogate() — Panagiotis Bougioukos
– Panagiotis Bougioukos, Commented Dec 30, 2020 at 19:43
Yup, we can use the JPA Repository and Entities to fetch records from tables and such. Dialect is org.hibernate.dialect.SybaseDialect. — lincolnadym
– lincolnadym, Commented Dec 30, 2020 at 19:56
Have you tried @Procedure like explained here if it makes any difference? — pirho
– pirho, Commented Dec 30, 2020 at 20:57

Senthil K · Accepted Answer · 2022-02-28 06:12:20Z

0

Sybase native query won't support "Call" and use "exec"

@Query(value = "exec rp_surrogate  ?1, ?2 ", nativeQuery = true)
int getSurrogateByQueryV1(String surrtype, Integer newsurr );

answered Feb 28, 2022 at 6:12

Senthil K

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Boot, JPA, @Query, Sybase stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related