Home > database >  How can I use a function say digits with Spring JPA and native query and DB2
How can I use a function say digits with Spring JPA and native query and DB2

Time:03-12

I want to be able to use this function and them pass the parameter. But I get this error, it is almost that spring jpa or java prepared statements are not allowing me to do this.

The code looks like the following in the logs.

Hibernate: UPDATE DB2PROD.GLOBAL_TABLE

SET CONTRACT_ID = DIGITS( ? ) WHERE GLOBAL_ID = ?

@Query(value = "UPDATE DB2PROD.GLOBAL_TABLE \n"  
        " SET CONTRACT_ID = DIGITS( :indicator ) \n"  
        "   WHERE GLOBAL_ID = :id ", nativeQuery = true)
int update(final Integer indicator, final String id);

And the error:

2022-03-11 15:54:25.982 WARN 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -418, SQLState: 42610 2022-03-11 15:54:25.982 ERROR 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.25.13 2022-03-11 15:54:25.982 WARN 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -516, SQLState: 26501 2022-03-11 15:54:25.983 ERROR 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.25.13 2022-03-11 15:54:25.983 WARN 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -518, SQLState: 07003 2022-03-11 15:54:25.983 ERROR 95241 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-518, SQLSTATE=07003, SQLERRMC=null, DRIVER=4.25.13 2022-03-11 15:54:26.017 ERROR 95241 --- [ scheduling-1] c.p.p.m.contacts.service.ContactService : Error on save contact

CodePudding user response:

Db2 can't determine a data type of the parameter in the DIGITS (?) expression.
You must define it explicitly with CAST like:
DIGITS (CAST (? AS INT))
or
DIGITS (CAST (? AS DEC (5)))
etc.

  • Related