re Strange problem with Date while inserting

[Rainer Döbele <do...@esteam.de>]

Hallo Alain,

if you do a lot of inserts like that, then it would be wise to use prepared statements.
Not only will they probably solve your problem but they should also be considerably faster.

You can simply enable prepared Statements (DBDatabase.setPreparedStatementsEnabled(true)).

But of course it seems to be a bug that has got to be found.
Please let me know which DBMS and database driver you are using.

And - if possible - let us know, if the prepared statement approach worked for you.

Regards
Rainer


> from: Becam Alain [mailto:alain.becam@embl.de]
> to: user@empire-db.apache.org
> re: Re: Strange problem with Date while inserting
> 
> It could be indeed, I will check tomorrow. It could also be possible to share a
> part of code.
> 
> //Alain
> 
> Le 27/02/2013 19:25, Francis De Brabandere a écrit :
> > 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, mailinglist@j-b-s.de <ma...@j-b-s.de>
> 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 <Al...@embl.de> 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
> >>>

Re: re Strange problem with Date while inserting

[Alain Becam <Al...@embl.de>]

Hallo Rainer,
      We are using Oracle 11g, and so the oracle driver within EmpireDB. 
As far as I know the jdbc driver (ojdbc6, through C3P0) does not really 
matter, as the problem happens during the construction of the sql, so 
purely within my application and EmpireDB.
//Alain

On 28/02/2013 09:24, Rainer Döbele wrote:
> Hallo Alain,
>
> if you do a lot of inserts like that, then it would be wise to use prepared statements.
> Not only will they probably solve your problem but they should also be considerably faster.
>
> You can simply enable prepared Statements (DBDatabase.setPreparedStatementsEnabled(true)).
>
> But of course it seems to be a bug that has got to be found.
> Please let me know which DBMS and database driver you are using.
>
> And - if possible - let us know, if the prepared statement approach worked for you.
>
> Regards
> Rainer
>