CODEWITHSUNDEEP
javasqloracle-databasespring-batch
Manel BEN AMARA
Manel BEN AMARA

Reputation: 25

oracle sql date errors

I need your help!

I have a sql query that is executed within a jdbcTemplate like shown here :

public String Insertquery(FichierEclate e) {
            String var="(null)";
            String tableName="TF02_HISTO_"+Country;
            String sql = "INSERT INTO " + tableName + " (TF02TFID, TF02STID, TF02DATE, TF02UID) VALUES (" + e.getAffaireID() + ", 4, TO_TIMESTAMP('" +e.getDate()+ "','yyyy-mm-dd hh24:mi:ss')," + var +")";
            return sql;
        }

Then i just make a call :

String Inquery=Insertquery(item);

 vJdbcTemplate.execute(Inquery);

I made the same steps for a Update query and it works perfectly fine!! BUt in Insert query, it shows this error :

 StatementCallback; SQL [INSERT INTO TF02_HISTO_MN (TF02TFID, TF02STID, TF02DATE, TF02UID) VALUES (25472563, 4, TO_TIMESTAMP('2021-03-01 00:45:00.0','yyyy-mm-dd hh24:mi:ss'),(null))]; ORA-01830: date format picture ends before converting entire input string
; nested exception is java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string

I think i don't need TO_TIMESTAMP or TO_DATE because jdbcTemplate makes the conversion automatically!! When i removed TO_TIMESTAMP, i get error of type :

missing comma

What to do !!!! By the way this object : FichierEclate e is using a property "date" of type timestamp and i am using oracle ! here is the format example of date written in my database:

2021-03-01 00:45:00

What to do ?!!!!!!!!!!

Upvotes: 0

Views: 205

Answers (2)

Manel BEN AMARA
Manel BEN AMARA

Reputation: 25

This was the solution ! thank you all for help :)

vJdbcTemplate.update(
  "INSERT INTO TF02_HISTO_" + country
    + " ( TF02TFID, TF02STID, TF02DATE, TF02UID ) VALUES ( ?, 4, ?, null )",
  item.getAffaireID(), item.getDate()
);

Upvotes: 0

MT0
MT0

Reputation: 168740

From this answer, use bind variables:

vJdbcTemplate.update(
  "INSERT INTO TF02_HISTO_" + country
    + " ( TF02TFID, TF02STID, TF02DATE, TF02UID ) VALUES ( ?, 4, ?, null )",
  item.getAffaireID(), item.getDate()
);

Upvotes: 2

Related Questions