2 Replies Latest reply on Apr 15, 2009 5:07 PM by rareddy

    SQL Dialect in Teiid designer

    kirkm

      I am using an Oracle data source and have been sucessful in connecting to the data source and getting a connector setup in teiid designer. I used the generic jdbc driver connector, not the oracle8 jdbc connector that appears in the connectors list. Got errors when attempting to setup the oracle 8 connector. Configuration appears to be Oracle Datadirect driver specific.

      Importing schema information into the model is working fine. When I Preview Data for the imported objects, I get a ORA-00933 SQL command not properly ended. Looking at the teiid generated query, a table alias is being used. An "AS" clause is being used to map the table to the alias which appears to be causing the oracle error. I don't think is standard SQL alias syntax. Is there some way to configure the sql dialect configuration in teiid? Can the auto generated queries be manually modified?

      Kirk

        • 1. Re: SQL Dialect in Teiid designer
          kirkm

          A couple of additional details:

          I am using oracle's ojdbc14.jar jdbc drivers.

          Views generated from the base table exhibit the same error. While you can edit the SQL, the alias is not part of the statement and appears to be added automatically.

          • 2. Re: SQL Dialect in Teiid designer
            rareddy

            You can select "Oracle ANSI Connector", but you need to change the Driver name in the properties panel in the Designer and also provide the correct JAR in the class path entry.

            By using the "Oracle ANSI" entry, the command will be properly converted to be consumed by the Oracle. The auto generated queries are SQL-92 compatible, they are not Oracle specific.

            In future releases, (6.1.0), Teiid would provide Oracle templates for the native Oracle drivers, but until then, if you want to add your own type you can follow the procedure as described in

            http://www.jboss.org/index.html?module=bb&op=viewtopic&t=152908