0

So far I tried

jdbcTemplate.update("INSERT INTO INFO (id, my_date) 
"VALUES(?, ?)", 1, "sysdate");

Also tried with namedParameterJdbcTemplate by just using a map. 

Map namedParameters = new HashMap();
namedParameters.put("id", 1);
namedParameters.put("my_date", "sysdate");

namedParameterJdbcTemplate.update("INSERT INTO INFO (id, my_date) 
    "VALUES(:id, :my_date)",namedParameters);

After trying all the above the the Exception is is below

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO INFO (id, my_date) 
"VALUES(?, ?)]; ORA-01858: a non-numeric character was found where a numeric was expected
; nested exception is java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:82) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]

Found this link, where they claim is possible. I don't see how, unless I am doing something wrong. Any suggestion? http://forum.spring.io/forum/spring-projects/data/99020-is-it-possible-to-use-oracle-s-sysdate-with-simplejdbcinsert

user2342259
  • 345
  • 2
  • 9
  • 27

2 Answers2

0

As an alternative, you could use the current timestamp available in Java:

Timestamp sysdate = new Timestamp(System.currentTimeMillis());
Map namedParameters = new HashMap();
namedParameters.put("id", 1);
namedParameters.put("my_date", sysdate);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I could indeed, is just that my Oracle date accepted format is dd-MMM-yyyy. I already had a work around it, like so: `LocalDate today = new LocalDate( ); DateTimeFormatter formatter = DateTimeFormat.forPattern( "dd-MMM-yyyy" ); String output = formatter.print(today);`. I was just hoping that there is a way to declare sysdate into your INSERT statement without having to do the above...Anyway, jobs done:) – user2342259 Jul 12 '18 at 07:12
  • This doesn't sound completely right to me. If you were using a basic JDBC prepared statement, you should be able to directly insert a `LocalDate` (or `java.sql.Timestamp`) without having to do such manual formatting. – Tim Biegeleisen Jul 12 '18 at 07:17
0

Your Java code doesn't know what sysdate is, it's a specific variable within the Oracle database. In a similar vein to Tim's answer I would specify a java variable in your code, albeit in a similar format to your second attempt:

Map<String, Object> namedParameters = new HashMap<String, Object>();
namedParameters.put("id", 1);
namedParameters.put("my_date", new Date());//java.util.Date

jdbcTemplate.update("INSERT INTO info (id, my_date) VALUES (:id, :my_date)", namedParameters);
mohammedkhan
  • 953
  • 6
  • 14