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 Jens Breitenstein <ma...@j-b-s.de> on 2012/10/17 22:04:47 UTC
SCHEMA
Hi all!
I am dealing with several hundreds of tables spread over multiple
schema's in one database.
As "schema" is an attribute on the DBDatabase itself I worked around it
by creating several empire DBDatabase objects, each containing the
required tables per schema (correct?).
This works as expected but unfortunately I can not join tables across
schemas, means across empire DBDatabases while it is perfectly working SQL.
I tried to set "schema" attribute to "null" and including the schema in
the tables name e.g. SCHEMA.TABLENAME but empire detects the "." as
"illegal" character and the name is quoted, thus the final name becomes
"SCHEMA.TABLE" which is rejected by oracle (table not found).
Did I miss something?
If not: a possible workaround might be:
Add a (optional) schema attribute on the DBTable. Due to the fact it is
a new attribute and two new methods (setter/getter to the attribute)
existing code is not affected.
When generating SQL the existing logic has to be changed to something
like this:
String schema = null;
if (null != table.getSchema()) {
schema = table.getSchema()
} else {
if (null != database.getSchema()) {
schema = database.getSchema()
}
}
// continue with prepending schema before "." + tablename
// addFrom()...
What you think?
(Again)
Jens
Re: SCHEMA
Posted by "mailinglist@j-b-s.de" <ma...@j-b-s.de>.
Hi Rainer,
Sorry to bother you guys and thx for always answering quickly.
Unfortunately the database is nothing I can change. It's grown over decades and a schema seems to be used more like a structural/grouping thing. Adding aliases, changing user roles/rights is not an option.
Lets assume the database contains
SchemaA, TableA1, TableA2
SchemaB, TableB
we have hundreds of schema definitions in one db and this is sometimes not consistent across different db's. So a table might have a different name or is located in a different schema.
Lets further assume only one JDBC url is used than it looks like this
MyDbA extends DbDatabase {
MyDbA() {
super();
setSchema("SchemaA");
addTable("TableA1");
addTable("TableA2");
}
}
and now the trouble starts:
In case "TableA1" has a different name in another system, I am screwed up. I solved this by making all table names configurable instead of relying on hardcoded strings.
Lets now assume a particular table is copied into another schema (call it backup or user playground, however), defining the tables in the ctor is no longer possible, means I have to change source code to run the same software on a different env, because schema is part of the database definition. As a result I need two database instances now to deal with different schemas.
Now source will change to:
MyDbA extends DbDatabase {
MyDbA() {
super();
setSchema("SchemaA");
addTable("TableA1");
// addTable("TableA2") won't work any longer, because table is not existing in this schema on a different environment
}
}
MyDbACopy extends DbDatabase {
MyDbCopy() {
super();
setSchema("SchemaACopy");
addTable("TableA2"); // hey here it is!
}
}
Because I can't configure the schema name per DbDatabase table a new DBInstance is required, thus a source change as a consequence is required, too. and only due to the fact the same software uses a different database and a table is located in a different schema.
What I am essentially looking for is:
MyDb extends DbDatabase {
MyDb() {
super();
addTable("TableA1", "SchemaA);
addTable("TableA2", "SchemaACopied");
addTable("TableB", "SchemaB");
}
}
that's the only thing I know: it's guaranteed tables exist per system, but neither name nor schema can be expected to be the same. So I am looking for a more dynamic approach as long the schema is affected, the table name is not an issue any longer.
Maybe it helps if you give me a hint how cross joining works in your env. I assume your db env is considerably more stable than mine or I'm using empire wrong?
Jens
Von meinem iPad gesendet
Am 18.10.2012 um 22:44 schrieb Rainer Döbele <do...@esteam.de>:
> Hi Jens,
>
> here are my anwsers:
>
>> from: Jens Breitenstein [mailto:mailinglist@j-b-s.de]
>> to: user@empire-db.apache.org
>> re: SCHEMA
>>
>> Hi all!
>>
>> I am dealing with several hundreds of tables spread over multiple
>> schema's in one database.
>> As "schema" is an attribute on the DBDatabase itself I worked around it
>> by creating several empire DBDatabase objects, each containing the
>> required tables per schema (correct?).
>
> Yep.
>
>> This works as expected but unfortunately I can not join tables across
>> schemas, means across empire DBDatabases while it is perfectly working
>> SQL.
>
> The question is why. If the SQL is correct, then the reason might be, that access privileges in your DBMS are wrong.
> Can you post some of your statements?
>
>> I tried to set "schema" attribute to "null" and including the schema in
>> the tables name e.g. SCHEMA.TABLENAME but empire detects the "." as
>> "illegal" character and the name is quoted, thus the final name becomes
>> "SCHEMA.TABLE" which is rejected by oracle (table not found).
>> Did I miss something?
>
> Well, you should not set the schema to null - cause that is what is is for.
> And if it is not working properly we need to fix it.
>
> Actually I use cross schema joins myself a lot with Oracle, so I doubt there is a problem with Empire-db.
>
> Please check your table grants - or simply give the user (or schema) that your connecting with the "IMPORT_FULL_DATABASE" role.
>
>>
>> If not: a possible workaround might be:
>>
>> Add a (optional) schema attribute on the DBTable. Due to the fact it is
>> a new attribute and two new methods (setter/getter to the attribute)
>> existing code is not affected.
>>
>> When generating SQL the existing logic has to be changed to something
>> like this:
>>
>> String schema = null;
>> if (null != table.getSchema()) {
>> schema = table.getSchema()
>> } else {
>> if (null != database.getSchema()) {
>> schema = database.getSchema()
>> }
>> }
>>
>> // continue with prepending schema before "." + tablename //
>> addFrom()...
>>
>
> Doesn't make sense to me. In fact the schema name is a property of the Database (which is in fact the schema) and not a property of each individual table within a database.
>
>>
>> What you think?
>>
>> (Again)
>>
>> Jens
>
re: SCHEMA
Posted by Rainer Döbele <do...@esteam.de>.
Hi Jens,
here are my anwsers:
> from: Jens Breitenstein [mailto:mailinglist@j-b-s.de]
> to: user@empire-db.apache.org
> re: SCHEMA
>
> Hi all!
>
> I am dealing with several hundreds of tables spread over multiple
> schema's in one database.
> As "schema" is an attribute on the DBDatabase itself I worked around it
> by creating several empire DBDatabase objects, each containing the
> required tables per schema (correct?).
Yep.
> This works as expected but unfortunately I can not join tables across
> schemas, means across empire DBDatabases while it is perfectly working
> SQL.
The question is why. If the SQL is correct, then the reason might be, that access privileges in your DBMS are wrong.
Can you post some of your statements?
> I tried to set "schema" attribute to "null" and including the schema in
> the tables name e.g. SCHEMA.TABLENAME but empire detects the "." as
> "illegal" character and the name is quoted, thus the final name becomes
> "SCHEMA.TABLE" which is rejected by oracle (table not found).
> Did I miss something?
Well, you should not set the schema to null - cause that is what is is for.
And if it is not working properly we need to fix it.
Actually I use cross schema joins myself a lot with Oracle, so I doubt there is a problem with Empire-db.
Please check your table grants - or simply give the user (or schema) that your connecting with the "IMPORT_FULL_DATABASE" role.
>
> If not: a possible workaround might be:
>
> Add a (optional) schema attribute on the DBTable. Due to the fact it is
> a new attribute and two new methods (setter/getter to the attribute)
> existing code is not affected.
>
> When generating SQL the existing logic has to be changed to something
> like this:
>
> String schema = null;
> if (null != table.getSchema()) {
> schema = table.getSchema()
> } else {
> if (null != database.getSchema()) {
> schema = database.getSchema()
> }
> }
>
> // continue with prepending schema before "." + tablename //
> addFrom()...
>
Doesn't make sense to me. In fact the schema name is a property of the Database (which is in fact the schema) and not a property of each individual table within a database.
>
> What you think?
>
> (Again)
>
> Jens
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
>
Strange problem with Date while inserting
Posted by Alain Becam <Al...@embl.de>.
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