Teiid embedded - Oracle Datasource - repeated column names - case sensitive

[hend.amin]

Hello,

I am connecting to oracle DB using Teiid Embedded. When i deploy  my VDB on the embedded server i get this exception:

TEIID60016 Duplicate Column

The problem is that oracle column name is case sensitive. i have two columns with the same name and different Cases for examples TEST and test on my DB. TEIID embedded sees them as a repetition in the column name

Is there a way to by pass that check in TEIID embedded ?

I appreciate your help.

Thanks

[shawkins]

> Is there a way to by pass that check in TEIID embedded ?

For the default importer there is no resolution strategy for this collision.  You would need to exclude the table altogether or exclude it from import and then define it directly in DDL to give different Teiid names to the columns.

[rareddy]

An example VDB  look like                                                 

<vdb name="Accounts" version="1">
    <model visible="true" name="PM1">
       <property name="importer.excludeTables" value="OriginalTable"/>
        <source name="dev" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
        <metadata type = "NATIVE"/>
        <metadata type = "DDL"><![CDATA[       
            CREATE FOREIGN TABLE OriginalTable (
                columnONE integer PRIMARY KEY OPTIONS(NAMEINSOURCE 'columnONE'),
                columnTWO varchar(25) OPTIONS(NAMEINSOURCE 'ColumnOne')
            );
        ]]>
       </metadata>       
    </model> 
</vdb>

Note the "NAMEINSOURCE" property in ColumnTWO. Now any access to this table needs to use the modified name of the column. Essentially what we are doing is chaining the metadata loaders, the second one can add, modify metadata created from previous ones.

Ramesh..

[hend.amin]

Thanks for your reply and help

I have added the property:

<property name="importer.excludeTables" value="tableName"/>

I have other properties too:

        <property name="importer.importKeys" value="false"/> 

        <property name="importer.useFullSchemaName" value="false"/>

        <property name="importer.includeSynonyms" value="false"/> 

        <property name="importer.tableTypes" value="TABLE,VIEW"/>

        <property name="importer.schemaPattern" value="schemaName"/>

But i can still query from the table "tableName" eventhough it is excluded. Note: i have tried upper and lower case table name.

I am using TEIID embedded jars version: 9.0.0.Alpha2

What could be wrong ?

[hend.amin]

I have added the property:

<property name="importer.excludeTables" value="tableName"/>

I have other properties too:

        <property name="importer.importKeys" value="false"/> 

        <property name="importer.useFullSchemaName" value="false"/>

        <property name="importer.includeSynonyms" value="false"/> 

        <property name="importer.tableTypes" value="TABLE,VIEW"/>

        <property name="importer.schemaPattern" value="schemaName"/>

But i can still query from the table "tableName" eventhough it is excluded. Note: i have tried upper and lower case table name.

I am using TEIID embedded jars version: 9.0.0.Alpha2

What could be wrong ?

[rareddy]

The property is a regex expression, so see if need to make any adjustments for that.

[shawkins]

And it's a regular expression that will match the prospective Teiid fully qualified name. See JDBC Translators | Teiid Documentation