Hi Alain,
as far as I can tell we have not deliberately fixed this.
As with most problems: if we cannot find a way to reproduce it - it will be
almost impossible to fix.
At the moment I cannot see, how a statement for Oracle without the TO_DATE()
phrase can be generated at all.
I.E. for the DATE_TIME type is should always look like this:
TO_DATE('2013-03-03 14:22:33', 'YYYY-MM-DD HH24:MI:SS')
What you can do:
1. subclass the DBDatabaseDriverOracle class
2. override getSQLDateTimeString, call the base implementation and log the
result. You may even check for TO_DATE and set a breakpoint if it is missing.
e.g.:
@Override
protected String getSQLDateTimeString(Object value, int sqlTemplate, int
sqlPattern, int sqlCurrentDate)
{
String res = super.getSQLDateTimeString(value, sqlTemplate, sqlPattern,
sqlCurrentDate);
if (res.indexOf('TO_DATE'))<0)
{
log.warn("Something's wrong here: "+value);
}
return res;
}
I'd be curious to know if it even gets in there.
Regards
Rainer
BTW: Have to tried with prepared statements yet?
> from: Alain Becam [mailto:[email protected]]
> to: [email protected]
> re: Re: Strange problem with Date while inserting
>
> Hi Francis,
> Thank you for investigating. Well, unfortunately it is a DATETIME:
>
> C_AT_TIME = addColumn("AT_TIME", DataType.DATETIME, 0,
> DataMode.Nullable);
>
> But somehow I do something that got EmpireDB to be confused about it.
> BTW I updated after my message from 2.2 to the latest release 2.4.2, could
> that be something that has been fixed since? As I said before, it is crazily
> rare,
> so even difficult to know if it is fixed or not...
>
> //Alain
>
>
> On 03/03/2013 21:27, Francis De Brabandere wrote:
> > Hi Alain,
> >
> > I'm trying to reproduce this in a test, could you tell us what the
> > definition of C_AT_TIME looks like?
> >
> > Somehow it's not a DataType.DATETIME as that type of field generates this
> sql:
> > INSERT INTO DATA( NAME, UPDATE_TIMESTAMP) VALUES ( 'test',
> > TO_DATE('2013-03-03 21:23:52', 'YYYY-MM-DD HH24:MI:SS'))
> >
> > Cheers,
> > F
> >
> > On 28 February 2013 11:19, [email protected] <[email protected]>
> wrote:
> >> Hi Alain!
> >>
> >> Can you paste the connection relevant code, too?
> >> Do you use a connection pool?
> >>
> >> Jens
> >>
> >> Sent from my iPhone
> >>
> >> On 28.02.2013, at 10:48, Alain Becam <[email protected]> wrote:
> >>
> >>> Thank you for your feedbacks, here is the relevant part of code, slightly
> simplified. Very simple as you can see. The first method can be called by
> several threads, but as the static method insertSearchTerm is then
> instantiating a new DBRecord each time, that shouldn't be a problem. Using
> prepared statements is a good idea (and in some other places as well), so I
> will try it.
> >>>
> >>>
> >>> public void insertSearchTerm(int operatorId, String searchTerms,
> >>> boolean isLocal, String from) {
> >>> GregorianCalendar today = new GregorianCalendar();
> >>> Date todayDate = today.getTime();
> >>>
> >>> MiscTools.insertSearchTerm(operatorId,connectionToNew,
> >>> searchTerms, isLocal, todayDate, from); }
> >>>
> >>>
> >>> -- MiscTools --
> >>>
> >>> public static void insertSearchTerm(Connection conn, String
> searchTerms, boolean isLocal, Date atTime, String from)
> >>> {
> >>> try
> >>> {
> >>> DBRecord rec = new DBRecord();
> >>> rec.create(GeneralApp.db.T_SEARCH_TERMS_LOG);
> >>>
> rec.setValue(GeneralApp.db.T_SEARCH_TERMS_LOG.C_SEARCH_TERMS,
> >>> searchTerms);
> rec.setValue(GeneralApp.db.T_SEARCH_TERMS_LOG.C_IS_LOCAL, isLocal);
> >>> rec.setValue(GeneralApp.db.T_SEARCH_TERMS_LOG.C_AT_TIME,
> atTime);
> >>>
> rec.setValue(GeneralApp.db.T_SEARCH_TERMS_LOG.C_FROM_IP,
> >>> from);
> >>>
> >>> try
> >>> {
> >>> rec.update(conn);
> >>>
> >>> GeneralApp.db.commit(conn);
> >>> }
> >>> catch (Exception e)
> >>> {
> >>> logger.error("Error while inserting new search terms",e);
> >>> }
> >>> }
> >>> }
> >>>
> >>> On 27/02/2013 19:25, Francis De Brabandere wrote:
> >>>> I was thinking the same, you could try to reproduce this in a test
> >>>> by just generating sql. Could you share that part of code somehow
> >>>> so we can have a deeper look?
> >>>>
> >>>> Cheers,
> >>>> Francis
> >>>>
> >>>> On 27 February 2013 18:26, [email protected] <[email protected]>
> wrote:
> >>>>> Hi Alain!
> >>>>>
> >>>>> Just a shot in the dark: might it be possible you are running into multi
> threading issues? Do you have static date formatter or static members of
> empire classes/instances in your code?
> >>>>>
> >>>>> Jens
> >>>>>
> >>>>> Sent from my iPhone
> >>>>>
> >>>>> On 27.02.2013, at 16:19, Alain Becam <[email protected]> wrote:
> >>>>>
> >>>>>> Hi all,
> >>>>>> I have an insert which is called a lot (several time per seconds,
> 24h/24h), and very rarely, something like once in a week, I get an SQL error
> because the date is not quoted. It is a very simple insert, and so works
> something like 99.9% of the time. Still I am curious to know what happen.
> Here is the relevant part of the raised exception:
> >>>>>>
> >>>>>>
> >>>>>> Insert new search terms
> org.apache.empire.db.exceptions.QueryFailedException: Error executing
> query INSERT INTO SEARCH_TERMS_LOG( SEARCH_TERMS, IS_LOCAL,
> AT_TIME, FROM_IP) VALUES ( 'XXXX', 1, Thu Feb 21 10:27:45 CET 2013,
> '10.11.4.119').
> >>>>>> Native error is INSERT INTO SEARCH_TERMS_LOG( SEARCH_TERMS,
> IS_LOCAL, AT_TIME, FROM_IP) VALUES ( 'XXXX', 1, Thu Feb 21 10:27:45 CET
> 2013, '10.11.4.119').
> >>>>>> at
> org.apache.empire.db.DBDatabase.executeSQL(DBDatabase.java:1037)
> >>>>>> at
> org.apache.empire.db.DBRowSet.updateRecord(DBRowSet.java:711)
> >>>>>> at org.apache.empire.db.DBRecord.update(DBRecord.java:683)
> >>>>>> at
> de.embl.common.core.logging.DBRecordLoggingWrapper.update(DBRecordL
> oggingWrapper.java:89)
> >>>>>> ...
> >>>>>> Caused by: java.sql.SQLSyntaxErrorException: ORA-00917: missing
> >>>>>> comma
> >>>>>>
> >>>>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> >>>>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> >>>>>> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> >>>>>> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> >>>>>> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> >>>>>> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> >>>>>> at
> oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
> >>>>>> at
> oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
> >>>>>> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatemen
> t.java:1315)
> >>>>>> at
> oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatemen
> t.java:1822)
> >>>>>> at
> oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1
> 787)
> >>>>>> at
> oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStateme
> ntWrapper.java:280)
> >>>>>> at
> com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxy
> Statement.java:64)
> >>>>>> at
> org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.jav
> a:535)
> >>>>>> at
> org.apache.empire.db.DBDatabase.executeSQL(DBDatabase.java:1025)
> >>>>>> ... 26 common frames omitted
> >>>>>>
> >>>>>> The DBRecordLoggingWrapper is simply saving udpate/insert
> informations if needed, and in that case it is doing nothing, just calling
> back
> the DBRecord methods. Still it could do something wrong, I simply don't see
> what.
> >>>>>> Best regards,
> >>>>>> Alain
> >>>>>>
>
> --
> ----------------------------
> Alain Becam, PhD
> IT Services, EMBL Heidelberg
> mailto:[email protected]
> Tel +49 (0) 6221 387 8593
> ----------------------------
