javaoracle-database

Oracle database - invalid identifier (that is not even part of the SQL query)


I use the QueryRunner to execute SQL commands on the Oracle database.

QueryRunner queryRunner = new QueryRunner();
int result = queryRunner.update(this.conn, "UPDATE system_user SET last_login_timestamp = systimestamp WHERE sys_user_code = ? ", "myuser");

But I get the following error:

ORA-00904: "YSTIMESTAMPHEREYS_USER_CODE": Invalid identified。
 Query: UPDATE system_user SET last_login_timestamp = systimestamp WHERE sys_user_code = ?  Parameters: [myuser]

So my question is, why does it think that there is YSTIMESTAMPHEREYS_USER_CODE in the connection string even if it is not there? Are there some encoding problems?

Note:

When I execute the same script in DBeaver (for example), the query is executed without any problems.

EDIT

After debugging the QueryRunner code I have found out that the error happens here (↓) in PreparedStatement.getParameterMetaData. source code


Solution

  • OK, the answer was that getParameterMetaData() is causing this issue. I was using ojdbc7 v12.1.0 and it does not seem to recognise the systimestamp keyword.

    QueryRunner queryRunner = new QueryRunner();
    int result = queryRunner.update(this.conn, "UPDATE system_user SET last_login_timestamp = systimestamp WHERE sys_user_code = ? ", "myuser");
    

    If I provide systimestamp as a parameter then this error does not occur anymore.

    QueryRunner queryRunner = new QueryRunner();
    int result = queryRunner.update(this.conn, "UPDATE system_user SET last_login_timestamp = ? WHERE sys_user_code = ? ", "2015-03-03 11:45:57.676", "myuser");
    

    It was also possible to avoid this issue by upgrading ojdbc7 to ojdbc8 v19.7.0.