[#CMP-536] Mirroring Data Changes using Oracle using JdbcGpsDevice

Compass

Mirroring Data Changes using Oracle using JdbcGpsDevice

Created: 10/Feb/08 10:10 PM Updated: 08/Dec/08 01:55 PM

Component/s:

Compass::Gps

Affects Version/s:

1.2.1

Fix Version/s:

2.2.0 M1

File Attachments:

CMP-536-Patch.txt (5 kB)

Description

« Hide

org.compass.gps.device.jdbc.JdbcGpsDeviceException: Failed to execute query for create/update; nested exception is java.sql.SQLException: Unsupported feature
java.sql.SQLException: Unsupported feature

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)*
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)*
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)*
at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseEr ror.java:499)*
at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMeta Data.java:157)*
at org.compass.gps.device.jdbc.dialect.DefaultJdbcDialect.setParameter(DefaultJdbc Dialect.java:112)*
at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener$2.doInCompassWithout Result(ResultSetSnapshotEventListener.java:146)*
at org.compass.core.CompassCallbackWithoutResult.doInCompass(CompassCallbackWithou tResult.java:29)*
at org.compass.core.CompassTemplate.execute(CompassTemplate.java:137)*
at org.compass.core.CompassTemplate.execute(CompassTemplate.java:119)*
at org.compass.gps.impl.SingleCompassGps.executeForMirror(SingleCompassGps.java:16 4)*
at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener.doCreateAndUpdateFor (ResultSetSnapshotEventListener.java:131)*
at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener.onCreateAndUpdate(Re sultSetSnapshotEventListener.java:121)*
at org.compass.gps.device.jdbc.ResultSetJdbcGpsDevice.performMirroring(ResultSetJd bcGpsDevice.java:359)*

Description

When configured to mirror data changes using Oracle, an unsupported feature of the Oracle JDBC is exposed. The Compass method DefaultJdbcDialect.setParameter() calls OracleParameterMetaData.getParameterType() which throws an java.sql.SQLException: Unsupported feature. The trace is listed below. The JDBC driver used is from the Oracle 10g release. I've also tried the most recent Oracle 11g JDBC driver and can still reproduce the problem. org.compass.gps.device.jdbc.JdbcGpsDeviceException: Failed to execute query for create/update; nested exception is java.sql.SQLException: Unsupported feature java.sql.SQLException: Unsupported feature at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)* at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)* at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)* at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseEr ror.java:499)* at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMeta Data.java:157)* at org.compass.gps.device.jdbc.dialect.DefaultJdbcDialect.setParameter(DefaultJdbc Dialect.java:112)* at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener$2.doInCompassWithout Result(ResultSetSnapshotEventListener.java:146)* at org.compass.core.CompassCallbackWithoutResult.doInCompass(CompassCallbackWithou tResult.java:29)* at org.compass.core.CompassTemplate.execute(CompassTemplate.java:137)* at org.compass.core.CompassTemplate.execute(CompassTemplate.java:119)* at org.compass.gps.impl.SingleCompassGps.executeForMirror(SingleCompassGps.java:16 4)* at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener.doCreateAndUpdateFor (ResultSetSnapshotEventListener.java:131)* at org.compass.gps.device.jdbc.ResultSetSnapshotEventListener.onCreateAndUpdate(Re sultSetSnapshotEventListener.java:121)* at org.compass.gps.device.jdbc.ResultSetJdbcGpsDevice.performMirroring(ResultSetJd bcGpsDevice.java:359)*

Show »

All

Comments

Change History

Sort Order:

[ Permlink | « Hide ]

Shay Banon added a comment - 19/Feb/08 04:35 PM

This seems to be a problem with Oracle driver which does not support meta data API. I will need to look into different solution for this without using the metadata API. For now, people can provide their own dialect and not call the metadata API.

[ Permlink | « Hide ]

Mohsen Saboorian added a comment - 29/Jul/08 02:47 AM

Shay, any progress on this bug? I'm at the beginning of a project with Oracle 10G.

[ Permlink | « Hide ]

Shay Banon added a comment - 30/Jul/08 06:02 PM

Are you going to use the JdbcGpsDevice?

[ Permlink | « Hide ]

Fábio Matos added a comment - 18/Nov/08 05:29 AM

I also add this problem, the solution is to use the generic ps.setObject as stated in http://forums.oracle.com/forums/post!reply.jspa?messageID=2286057, like this:

/**

@see org.compass.gps.device.jdbc.dialect.DefaultJdbcDialect#setParameter(java.sql.PreparedStatement, int,
java.lang.String)
*/
@Override
public void setParameter(final PreparedStatement ps, final int paramIndex, final String value) throws SQLException
Unknown macro: { if (value != null) { ps.setObject(paramIndex, value); } else { throw new IllegalArgumentException("Failed to set parameter with index [" + paramIndex + "] and value [" + value + "] , not supported"); } }
:

[ Permlink | « Hide ]

Shay Banon added a comment - 18/Nov/08 12:57 PM

Will this solution work for database types that are not strings? The metadata is used in order to know which type to convert the string to...

[ Permlink | « Hide ]

Fábio Matos added a comment - 24/Nov/08 04:46 AM

I tested with Integers and it worked fine.

[ Permlink | « Hide ]

Fábio Matos added a comment - 24/Nov/08 04:54 AM

From the PreparedStatement JavaDoc:

"The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database. "

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object)

So it should work fine with all the types that where being distinguished.

[ Permlink | « Hide ]

Shay Banon added a comment - 24/Nov/08 04:07 PM

I read the javadoc, what I understood from it is that basically there is a mapping from java.land.Integer to the correspondant number type, not sure about String to number. What I am afraid is that it will work for oracle, but will not work for other databases.

[ Permlink | « Hide ]

Fábio Matos added a comment - 25/Nov/08 03:38 AM

I understand your concern.

How about creating a OracleJdbcDialect that extends the DefaultJdbcDialect and overrides that method.
Then, at AbstractJdbcGpsDevice, instead of doing this: protected JdbcDialect dialect = new DefaultJdbcDialect();
in the doStart method use the datasource metada to figure it out if is a oracle DatasSource and instantiate the dialect with the OracleJdbcDialect instead of DefaultJdbcDialect.

[ Permlink | « Hide ]

Shay Banon added a comment - 25/Nov/08 01:45 PM

Yes, that can be done. I will try and do this, if you can go ahead and implement it, I will gladly apply the patch.

[ Permlink | « Hide ]

Fábio Matos added a comment - 08/Dec/08 04:54 AM

Sorry for the delay, no free time. Here it is.

[ Permlink | « Hide ]

Shay Banon added a comment - 08/Dec/08 01:55 PM

I fixed it in trunk (changed a bit the database dialect resolver). Can you give it a try?