Search code examples
sqloracle-databasedateprepared-statementjdbctemplate

How can i put variable into function in SQL query by preparedStatement in JDBC template?

I use Oracle DB. Can you help me? Help pleease who can, i'm tired of this problem =( I have such SQL query and i need to put my variable into function - converter to_date:

select p.mass_id, p.*
from cpm_problem p
where category = '9154812121313217606'
  and type = '9154812121313217612'
  and created_when > TO_DATE(lastPpr = ?, 'YYYY-MM-DD HH24:MI:SS')
order by created_when desc

This is my code in idea:

private static final String LAST_PPR_FROM_RDB = "select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc";

latestPprStr is return 2022-12-31 12:00:00

public Ppr getCpmProblems() {
        String latestPprStr = senderJdbcTemplate.queryForObject(LAST_PPR_FROM_SENDER, String.class);
        Ppr ppr1 = rdbJdbcTemplate.query(LAST_PPR_FROM_RDB, ps -> ps.setString(1, latestPprStr),
                new BeanPropertyRowMapper<>(Ppr.class)).stream().findAny().orElse(null);
        return ppr1;
    }

The key problem is that the value doesn't converts into date, how i think...

Error is here:

Caused by: Error : 907, Position : 153, Sql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS') order by created_when desc, OriginalSql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc,
Error Msg = ORA-00907: missing right parenthesis

Any screening by "'" or similar is not working, also i'm tried to use more simple construction like:

select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = ?

just to check that my code in idea is working and everything was added in sql query successfully. But exactly in to_date(...) i cant to put variable. Every time i have bad sqlgrammar and ":1"

Solution 
  • "to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS')"

    is not a valid SQL expression. If you wanted to bind a string into this, the prepared statement SQL string would be:

    "to_date(:1 , 'YYYY-MM-DD HH24:MI:SS')"

    into which you would then bind a string