SimpleJdbcInsert needs to understand synonyms! [SPR-4782]

[spring-projects-issues]

C N opened SPR-4782 and commented

I'm using Spring 2.5.2 and Oracle 10g jdbc drive. This fails

SimpleJdbcInsert x = new SimpleJdbcInsert(dataSource).withTableName('ABC');
x.execute( ... );

when tableName is a synonym to a table in another schema. It won't follow the synonym! Prepending the schema name to the table name doesn't work either.

org.springframework.dao.InvalidDataAccessApiUsageE xception: Unable to locate columns for table 'ABC' so an insert statement can't be generated
org.springframework.jdbc.core.metadata.TableMetaDa taContext.createInsertString(TableMetaDataContext. java:299)
org.springframework.jdbc.core.simple.AbstractJdbcI nsert.compileInternal(AbstractJdbcInsert.java:257)
org.springframework.jdbc.core.simple.AbstractJdbcI nsert.compile(AbstractJdbcInsert.java:240)
org.springframework.jdbc.core.simple.AbstractJdbcI nsert.checkCompiled(AbstractJdbcInsert.java:291)
org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecute(AbstractJdbcInsert.java:329)
org.springframework.jdbc.core.simple.SimpleJdbcIns ert.execute(SimpleJdbcInsert.java:102)

If you can cast java.sql.Connection to oracle.jdbc.OracleConnection, you can setIncludeSynonyms(true) which will cause DatabaseMetadata to understand synonyms.

---

Affects: 2.5.2

Referenced from: commits f70d14e

1 votes, 1 watchers

[spring-projects-issues]

Juergen Hoeller commented

Thomas, assigning this to you for the 3.0 timeframe...

Is there maybe a way to configure that "includeSynonyms" flag more centrally? Otherwise we'd have to use the NativeJdbcExtractor to get access to the raw OracleConnection, modifying that flag for each and every Connection...

Juergen

[spring-projects-issues]

Joseph Hirn commented

I can get this to work if I configure the datasoruce JNDI properties properly.

In Weblogic, there is a JNDI Properties box that I can put includeSynynoms=true into. Only thing is, i can't figure out how do this in a tomcat Resource url.

Seeing how it is a connection property issue, I'm not sure if it's up to spring to fix. Maybe provide some extra documentation for the methods that require this, but it's really unfortunate we aren't allowed to talk directly to the tables in the first place.

[spring-projects-issues]

Joseph Hirn commented

Well I'm assuming most people are using Tomcat or some flavor thereof so I'll post it here for reference. In order to enable includeSynynoms on an Oracle connection you have to use the following in your resource declaration:

connectionProperties="includeSynonyms=true"

As in:

<Resource name="pricingDS" auth="Container" type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
connectionProperties="includeSynonyms=true" maxActive="25" maxIdle="0" maxWait="10000"
username="" password="" driverClassName="oracle.jdbc.OracleDriver"
url=""/>

So I'm not sure what spring itself could do other than put something in javadoc regarding SimpleJdbcInsert this needing DB Metadata to be available.

[spring-projects-issues]

Thomas Risberg commented

If we use the connection from the DatabaseMetaData, then we don't have to worry about extracting the native connection since that is always an Oracle connection. I'm adding an "includeSynonymsForTableColumnMetaData" method to SimpleJdbcInsert and an OracleTableMetaDataProvider that will take this switch into account when the meta data is retrieved.

Other databases that support synonyms seem to include them in the metadata lookup by default. Oracle says that including the synonyms will hurt performance since the lookup will have to do an extra join, so its best to leave the default to false.