[QFJ-979] Oracle schema definitions missing from the latest releases Created: 11/Jun/19  Updated: 24/Apr/21  Resolved: 01/Jan/21

Status: Closed
Project: QuickFIX/J
Component/s: Metadata/Specs
Affects Version/s: 2.0.0, 2.1.1
Fix Version/s: 2.3.0

Type: Bug Priority: Default
Reporter: Beirti O'Nunain Assignee: Christoph John
Resolution: Fixed Votes: 0
Labels: None


 Description   

Looking in the QuickFixJ 2.x releases, it looks like the SQL schema definitions for oracle are missing some tables which are defined for other databases. (event_log, messages_log)

I can't find any documentation which suggests that they aren't required for Oracle. I can craft them manually but am curious why they're omitted.

 Comments   
Comment by Beirti O'Nunain [ 11/Jun/19 ]

I'm guessing that this is the correct SQL? (Oracle 12 identity columns)

CREATE TABLE EVENT_LOG (
id NUMBER GENERATED ALWAYS AS IDENTITY,
time TIMESTAMP NOT NULL,
beginstring VARCHAR2(8) NOT NULL,
sendercompid VARCHAR2(64) NOT NULL,
sendersubid VARCHAR2(64) NOT NULL,
senderlocid VARCHAR2(64) NOT NULL,
targetcompid VARCHAR2(64) NOT NULL,
targetsubid VARCHAR2(64) NOT NULL,
targetlocid VARCHAR2(64) NOT NULL,
session_qualifier VARCHAR2(64) NOT NULL,
text VARCHAR2(4000) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE MESSAGES_LOG (
id NUMBER GENERATED ALWAYS AS IDENTITY,
time TIMESTAMP NOT NULL,
beginstring CHAR(8) NOT NULL,
sendercompid VARCHAR2(64) NOT NULL,
sendersubid VARCHAR2(64) NOT NULL,
senderlocid VARCHAR2(64) NOT NULL,
targetcompid VARCHAR2(64) NOT NULL,
targetsubid VARCHAR2(64) NOT NULL,
targetlocid VARCHAR2(64) NOT NULL,
session_qualifier VARCHAR2(64) NOT NULL,
text VARCHAR2(4000) NOT NULL,
PRIMARY KEY (id)
);

Comment by Beirti O'Nunain [ 11/Jun/19 ]

Getting errors on session initialization. It seems to be attempting to insert without specifying all parameters required by the table. I've not had this issue in MSSQL

private void loadCache() throws SQLException, IOException {
Connection connection = null;
PreparedStatement query = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
query = connection.prepareStatement(SQL_GET_SEQNUMS);
setSessionIdParameters(query, 1);
rs = query.executeQuery();
if (rs.next())

{ cache.setCreationTime(SystemTime.getUtcCalendar(rs.getTimestamp(1))); cache.setNextTargetMsgSeqNum(rs.getInt(2)); cache.setNextSenderMsgSeqNum(rs.getInt(3)); }

else

{ insert = connection.prepareStatement(SQL_INSERT_SESSION); int offset = setSessionIdParameters(insert, 1); insert.setTimestamp(offset++, new Timestamp(cache.getCreationTime().getTime())); insert.setInt(offset++, cache.getNextTargetMsgSeqNum()); insert.setInt(offset, cache.getNextSenderMsgSeqNum()); insert.execute(); }

} finally

{ JdbcUtil.close(sessionID, rs); JdbcUtil.close(sessionID, query); JdbcUtil.close(sessionID, insert); JdbcUtil.close(sessionID, connection); }

}

{{Caused by: java.lang.RuntimeException: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("SKYROAD"."FIX_SESSIONS"."SENDERSUBID")

at quickfix.JdbcStoreFactory.create(JdbcStoreFactory.java:47) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.Session.<init>(Session.java:492) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.DefaultSessionFactory.create(DefaultSessionFactory.java:206) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.mina.SessionConnector.createSession(SessionConnector.java:169) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.mina.initiator.AbstractSocketInitiator.createSessions(AbstractSocketInitiator.java:187) ~[quickfixj-core-2.0.0.jar:2.0.0]
... 16 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("SKYROAD"."SESSIONS"."SENDERSUBID")

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) ~[ojdbc7-12.1.0.2.0.jar:12.1.0.2.0]
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[tomcat-dbcp-7.0.47.jar:7.0.47]
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[tomcat-dbcp-7.0.47.jar:7.0.47]
at quickfix.JdbcStore.loadCache(JdbcStore.java:139) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.JdbcStore.<init>(JdbcStore.java:86) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.JdbcStoreFactory.create(JdbcStoreFactory.java:45) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.Session.<init>(Session.java:492) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.DefaultSessionFactory.create(DefaultSessionFactory.java:206) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.mina.SessionConnector.createSession(SessionConnector.java:169) ~[quickfixj-core-2.0.0.jar:2.0.0]
at quickfix.mina.initiator.AbstractSocketInitiator.createSessions(AbstractSocketInitiator.java:187) ~[quickfixj-core-2.0.0.jar:2.0.0]
... 16 more}}

Comment by Beirti O'Nunain [ 11/Jun/19 ]

Following up with our DBA, it seems you can skip specificying a 'not null' column in MSSQL but not in Oracle. I guess nobody is using Oracle with QuickFixJ?!

Comment by Christoph John [ 08/Jul/19 ]

Hi,
yes, that may be the case. I have the assumption that the JDBC store is used pretty rarely. But I might be wrong. Just my personal impression.
Cheers,
Chris.

Comment by Christoph John [ 08/Jul/19 ]

I don't know if it fixes your problem, but did you try setting JdbcSessionIdDefaultPropertyValue? See https://www.quickfixj.org/usermanual/2.1.0/usage/configuration.html
At least it mentions "Oracle".

Comment by Beirti O'Nunain [ 10/Jul/19 ]

Sad times friend! I've been using the mssql one in production for a few months and find it great for managing searching and archiving of raw logs. Just set up the oracle one now too and it seems to be going ok. Fingers crossed?!

Comment by Christoph John [ 16/Jul/19 ]

So did the JdbcSessionIdDefaultPropertyValue work for you?
If you'd like you can open a PR here to supply the missing schema definitions. https://github.com/quickfix-j/quickfixj/pulls

Thanks and best regards,
Chris.

Comment by Beirti O'Nunain [ 14/Mar/20 ]

Hi Chris,

Apologies for the very late reply. I ended up giving up on Oracle. Everything's been running swimmingly on MSSQL in our production setup with 20 or so applications sharing a database so I've left it there. If I get stuck having to use Oracle in the future I'll revisit and submit a PR with the schema defs once I get it working.

Cheers,
Beirti

Comment by Christoph John [ 15/Mar/20 ]

OK, thanks for the update.

Comment by Christoph John [ 01/Jan/21 ]

Fixed by https://github.com/quickfix-j/quickfixj/pull/349

Generated at Thu May 09 20:08:12 UTC 2024 using JIRA 7.5.2#75007-sha1:9f5725bb824792b3230a5d8716f0c13e296a3cae.