You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by Alain Becam <Al...@embl.de> on 2013/02/27 16:19:10 UTC
Strange problem with Date while inserting
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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by Becam Alain <al...@embl.de>.
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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by Alain Becam <Al...@embl.de>.
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, mailinglist@j-b-s.de <ma...@j-b-s.de> 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 <Al...@embl.de> 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, 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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>>>>>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>>>>>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>>>>>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>>>>>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>>>>>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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:Alain.Becam@embl.de
Tel +49 (0) 6221 387 8593
----------------------------
Re: Strange problem with Date while inserting
Posted by Francis De Brabandere <fr...@gmail.com>.
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, mailinglist@j-b-s.de <ma...@j-b-s.de> 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 <Al...@embl.de> 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, 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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>>>>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>>>>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>>>>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>>>>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>>>>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by "mailinglist@j-b-s.de" <ma...@j-b-s.de>.
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 <Al...@embl.de> 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, 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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>>>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>>>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>>>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>>>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>>>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by Alain Becam <Al...@embl.de>.
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, 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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by Francis De Brabandere <fr...@gmail.com>.
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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
>> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
>> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
>> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
>> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
>> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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: Strange problem with Date while inserting
Posted by "mailinglist@j-b-s.de" <ma...@j-b-s.de>.
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(DBRecordLoggingWrapper.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(OracleStatement.java:1315)
> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
> at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
> at org.apache.empire.db.DBDatabaseDriver.executeSQL(DBDatabaseDriver.java: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
>