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

Question

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

Mark Barinstein · Accepted Answer · 2022-03-11 22:09:17Z

1

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.

answered Mar 11, 2022 at 22:09

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

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

1 Comment

Berlin Brown Over a year ago

This worked in terms of digit and cast

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related