Oracle Support

[Marc]

Hi all,

I would like to log with the router to an oracle database and I put
the jdbc driver into the lib folder.

But when the router pases a message I got the following orcale error
message:

java.sql.SQLSyntaxErrorException: ORA-00907: Rechte Klammer fehlt
INFO | jvm 1 | 2011/01/04 15:50:24 |
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:
91)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:
133)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
206)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:
1222)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:
1770)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:
299)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:
264)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:
264)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.interceptor.statistics.StatisticsJDBCInterceptor.createTableIfNecessary(StatisticsJDBCInterceptor.java:
97)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.interceptor.statistics.StatisticsJDBCInterceptor.handleResponse(StatisticsJDBCInterceptor.java:
52)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.transport.http.AbstractHttpThread.invokeResponseHandlers(AbstractHttpThread.java:
83)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.transport.http.AbstractHttpThread.invokeResponseInterceptors(AbstractHttpThread.java:
175)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.transport.http.HttpServerThread.process(HttpServerThread.java:
141)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
com.predic8.membrane.core.transport.http.HttpServerThread.run(HttpServerThread.java:
59)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
INFO | jvm 1 | 2011/01/04 15:50:24 | at
java.lang.Thread.run(Unknown Source)

Is this a problem with the SQL Statement?

Marc

[Marc]

It seems that this error causes another error:

With the exception above the router doesn't route any message to the
target anymore....

[Thomas]

Hi,

we use a standard SQL identity clause to generate unique ids:

CREATE TABLE " + DBTableConstants.TABLE_NAME + " ( id INT GENERATED
ALWAYS AS IDENTITY, " + ....

but Oracle doesn't support GENERATED ALWAYS AS IDENTITY. Will change
this to make it work with Oracle with the next bugfix release.

As a quick fix you can create the needed table for the statistics
manually. The SQL to do this is:

CREATE TABLE statistic ( id INT PRIMARY KEY,
status_code INT,
time VARCHAR(155),
rule VARCHAR(255),
method VARCHAR(50),
path VARCHAR(1000),
client VARCHAR(255),
server VARCHAR(255),
req_content_type VARCHAR(100),
req_content_length INT,
resp_content_type VARCHAR(100),
resp_content_lengt INT,
duration INT )

Than you need a trigger to insert a unique number:

CREATE SEQUENCE stat_seq;

CREATE TRIGGER stat_seq_trigger
BEFORE INSERT ON statistic FOR EACH ROW
BEGIN
IF (:new.id IS NULL) THEN
SELECT stat_seq.nextval INTO :new.id
FROM DUAL;
END IF;
END;

Thomas