How to set query parameters with single quotes

Question

I'm using a oracle database.I need to run a update query through jpa repository.This is the query I have tried to execute.

            @Transactional(propagation = Propagation.REQUIRES_NEW)
            @Modifying
            @Query(
                value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= (CURRENT_TIMESTAMP+ interval ':to' second)  WHERE logid IN (:li) ",
                nativeQuery = true)
            public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

But this exception

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that name [to] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that name [to] did not exist

But if i change this method as follows, it works fine.

@Transactional(propagation = Propagation.REQUIRES_NEW)
            @Modifying
            @Query(
                value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= (CURRENT_TIMESTAMP+ interval '5' second)  WHERE logid IN (:li) ",
                nativeQuery = true)
            public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList);

Any idea?

Answer 1

I have found an answer for this problem.

@Transactional(propagation = Propagation.REQUIRES_NEW)
        @Modifying
        @Query(
            value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= CURRENT_TIMESTAMP+ NUMTODSINTERVAL( :to, 'SECOND' )  WHERE logid IN (:li) ",
            nativeQuery = true)
        public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

Answer 2

readytoprocessat= (CURRENT_TIMESTAMP+ interval ':to' second)

This clause is having issue, try to perform it one line prior, separately. Then you would be able to see the problem yourself.

Answer 3

Parameter with name [to] doesn't exist because you put :to between single quotes. Use :to instead of ':to'.

That being said, this will not work anyway. I faced really similar issue and after some hours finally found a solution which I present in answer here. For some reason, when interval comes into play injection of parameters doesn't work as you would expect.

Considering conclusion from the link above - I believe this should work:

@Transactional(propagation = Propagation.REQUIRES_NEW)
@Modifying
@Query(value = "UPDATE transactionlog SET transactionstatus= :ps,
       startedat = CURRENT_TIMESTAMP, 
       readytoprocessat= (CURRENT_TIMESTAMP + (( :to ) || 'second')\\:\\:interval)
       WHERE logid IN (:li) ",nativeQuery = true)
public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

Answer 4

replace the :ps and all other parameter with ?1, ?2, ... and make the methos parameter match SQL parameter (the order will be important) .