SQL Power Software Forum

Match Maker version 0.9.5 build #217, first spotted in 0.9.4
JRE 1.6.0_11
JDBC Driver ojdbc14.jar
Accessing Oracle 9.2
===================

Match Engine finds matches successfully, but fails when inserting the Match Pool into the RESULTs table.

Failing Oracle calls follow =====================================

Error in SQL Query while storing the Match Pool!
Message: Invalid column type
SQL State: null
Query: INSERT INTO DC_METRICS.MDM_CUSTOMER_RESULT (DUP_CANDIDATE_10, DUP_CANDIDATE_20, MATCH_PERCENT, GROUP_ID, MATCH_STATUS, DUP1_MASTER_IND, MATCH_DATE, MATCH_STATUS_DATE, MATCH_STATUS_USER, DUP_ID0, MASTER_ID0)
VALUES (?, ?, ?, ?, ?, ?, TO_DATE('2009-02-04 14:57:14','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2009-02-04 14:57:14','YYYY-MM-DD HH24:MI:SS'), 'SA', ?, ?)

Error in SQL Query while storing the Match Pool!
Message: Invalid column type
SQL State: null
Query: INSERT INTO DC_METRICS.MDM_CUSTOMER_RESULT (DUP_CANDIDATE_10, DUP_CANDIDATE_20, MATCH_PERCENT, GROUP_ID, MATCH_STATUS, DUP1_MASTER_IND, MATCH_DATE, MATCH_STATUS_DATE, MATCH_STATUS_USER, DUP_ID0, MASTER_ID0)
VALUES (?, ?, ?, ?, ?, ?, TO_DATE('2009-02-04 14:58:53','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2009-02-04 14:58:53','YYYY-MM-DD HH24:MI:SS'), 'SA', ?, ?)

Leading log messages and Java error stack follow =========================

2009-02-04 14:26:56,133 INFO Checking Match Engine Preconditions
2009-02-04 14:27:26,429 INFO Starting Match Engine
2009-02-04 14:27:28,600 INFO Clearing Match Pool
2009-02-04 14:27:29,600 INFO Searching for matches
2009-02-04 14:27:30,553 INFO Munge Process 'MDM_CUSTOMER Munge' found 3 matches
2009-02-04 14:27:30,553 INFO Match Engine found a total of 3 matches
2009-02-04 14:27:30,553 INFO Storing matches
2009-02-04 14:27:31,460 ERROR 800/900: Match Engine failed
2009-02-04 14:27:31,538 ERROR Error during engine run
java.lang.RuntimeException: java.sql.SQLException: Invalid column type
at ca.sqlpower.matchmaker.MatchEngineImpl.call(MatchEngineImpl.java:25

at ca.sqlpower.matchmaker.swingui.engine.EngineWorker.doStuff(EngineWorker.java:107)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:73)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6411)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:135

at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2795)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3221)
at ca.sqlpower.sql.jdbcwrapper.PreparedStatementDecorator.setObject(PreparedStatementDecorator.java:323)
at ca.sqlpower.matchmaker.MatchPool.store(MatchPool.java:599)
at ca.sqlpower.matchmaker.MatchPool.store(MatchPool.java:36

at ca.sqlpower.matchmaker.MatchEngineImpl.call(MatchEngineImpl.java:239)
... 3 more

Hi there,

Would you be able to tell me the column types for the unique key (usually the primary key) that you used to dedupe this table?

Of course Jeff.

The identifying column used for this de-dup project is the 'customer_id' column which is

character varying(20).

Donn

Jeff,

My apologies, I had forwarded the column definition from my Postgres table, not the one from my Oracle test rig.

In my Oracle database, the column definition for the primary index column is:

customer_id VARCHAR2(20) NULL,

Reviewing this, leads me to a possible cause of the problem, which could be the incorrect parsing of this meta-data.

As a primary key (or primary key component) you would never have the column defined as NULLable and the parse was probably developed with this expectation.

It looks like the error messages may indicate the string 'null' as the column type, which was probably returned by the parser when it expected to pick up VARCHAR(20).

I am going to change my schema and remove the NULL for this primary key column and retest. The parse should probably be fixed anyway.

Best wishes.

Donn

Jeff,

Modified my Oracle schema so that the 'customer_id' column was not NULLable:

customer_id VARCHAR2(20) NOT NULL,

No change in behavior - my test results in the same failure

The error looks like it's caused by a data type mismatch between a RESULTs table column and the corresponding java content variable at INSERT time.

Based on your question, it sounds like you are looking into this. If you can think of another test that would be useful, please let me know.

Donn