Insert timestamp with JdbcTemplate in Oracle database ( ORA-01858 )

Question

I've read a lot of stuff about this error, and still not found the mistake.

I'm using JdbcTemplate to insert a row in some table with some timestamp column I'm pretty sure the timestamp is the problem, as if delete from the insert it works fine)

My code:

        private static final String INSERT_CITAS = "INSERT INTO CITAS (" 
        + "idCita, idServicio, " + "fechaCita, "
        + "idEstado, idUsuarioInicial) " + "VALUES (?, ?, ?, ?, ?)";

        Object[] params = {
                idCita,
                citaQuenda.getIdServicio(),
                getDateToDBFormat(citaQuenda.getFechaCita()),
                ESTADO_INICIAL,
                USUARIO_INICIAL };

        String queryCitas = INSERT_CITAS;

        super.getJdbcTemplate().update(queryCitas, params);


        protected String getDateToDBFormat(Date fechaCreacion){
        return  "TO_TIMESTAMP('" + 
                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(fechaCreacion)
                    + "', 'yyyy-mm-dd hh24:mi:ss')" ;
        }

And having the next error:

    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO citas_55 (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (?, ?, ?, ?, ?)];
    ORA-01858: a non-numeric character was found where a numeric was expected

I've tried to execute the sql in some SQL editor having success, so I can't be more confused.

Being my params: [461, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1, 8888] This actually works.

    INSERT INTO citas (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (457, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss') , 1, 8888);

Any kind of help would be appreciated. Thanks in advance!

I'm not familiar with the template, but is the update using the table metadata to decide what kind of parameter to set - so it'll try to `setTimestamp()` with your string value, possibly introducing implicit conversions? — Alex Poole, Jan 28 '15 at 17:30
A DATE type and a TIMESTAMP type are different. Your example shows TO_DATE and TO_TIMESTAMP, pleases show the table definition. — OldProgrammer, Jan 28 '15 at 17:31
[The docs say](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#update-java.lang.String-java.lang.Object...-) "leaving it to the PreparedStatement to guess the corresponding SQL type", so maybe not quite as wild a guess as I thought... so yes, why not pass it as a timestamp? — Alex Poole, Jan 28 '15 at 17:40

score 9 · Accepted Answer · answered Jan 28 '15 at 17:41

Don't convert back and forth between dates/timestamps and Strings.

Just pass a java.sql.Timestamp instance as a parameter:

Object[] params = {
         idCita,
         citaQuenda.getIdServicio(),
         new java.sql.Timestamp(citaQuenda.getFechaCita()),
         ESTADO_INICIAL,
         USUARIO_INICIAL };

String queryCitas = INSERT_CITAS;
super.getJdbcTemplate().update(queryCitas, params);

OldProgrammer · Answer 2 · 2015-01-28T17:41:15.197

I will go out on a limb here, and think I may see the problem. getDateToDBFormat() method is returning a String type, which contains the text, "TO_TIMESTAMP(...)". That is not a date or timestamp parameter. It is a string parameter. You need to do this instead:

Remove the TO_TIMESTAMP stuff from getDateToDBFormat() and have it just return the formatted DATE/TIME value (the format you show is not an oracle timestamp, but a DATE type).

change your insert to:

"INSERT INTO CITAS ... VALUES (?, ?, TO_DATE(?,?) , ?, ?)"

Where the parameters to the TO_DATE call are the return from getDateToDBFormat() and the second parameter is the date format mask. However, can't you just get rid of that mess and bind a Java Date type (or jdbc sql equivalent) directly?

That should work.

Insert timestamp with JdbcTemplate in Oracle database ( ORA-01858 )

2 Answers2

Linked