12 Replies Latest reply on Oct 10, 2017 10:46 AM by Ramesh Reddy

    ORA-00904:  invalid identifier for rest endpoint attribute

    ganesh pol Newbie

      Hi,

      from last couple of days i started exploring Teiid and refer session presented by rareddy

      Teiid Data Virtualization With Spring Boot - YouTube

      but facing following problem

       

      Problem statement

      i am getting ORA-00904 for attributes which are available from REST endpoint

      i.e. java.sql.SQLSyntaxErrorException: ORA-00904: "TEIIDUSER0_"."AGE": invalid identifier

       

      Code

      Part of Entity which is taking care of REST endpoint

      this endpoint provide me two attributes age and gender.

       

      @Entity
      @JsonTable(endpoint="http://<IP of my service>:9090/findAllUsersfromExternal" ,rootIsArray=true)
      public class ExternalSystemUser implements Serializable{
        @Column(name="age")
        private int age;
        @Column(name="gender")
        private String gender;
        //few more attributes with getters and setters
      }
      

       

      Entity which is joining rest end point

       

      import java.io.Serializable;
      import javax.persistence.Entity;
      import javax.persistence.Id;
      import org.teiid.spring.annotations.SelectQuery;
      /*
      * age and gender are missing in system which are available in other system
       * hosted at http://<other_system_ip>:9090/findAllUsersfromExternal make getters and
      * setters for them in this class
      */
      @Entity
      @SelectQuery( " select thisAppUser.userid," +
      " thisAppUser.firstname,"
      + " thisAppUser.username," 
      + " externalUser.age,"
      + " externalUser.gender " 
      + " from teiiduserexample.TeiidUSER as thisAppUser"
      + " ,ExternalSystemUser as externalUser "
      + " WHERE thisAppUser.username=externalUser.username " + "   ")
      public class TeiidUSER implements Serializable {
        @Id
        private long userid;
        private String firstname;
        private String username;
        private int age;
        private String gender;
        public TeiidUSER() {
        // TODO Auto-generated constructor stub
        }
        public long getUserid() {
           return userid;
        }
        public void setUserid(long userid) {
          this.userid = userid;
        }
        public String getFirstname() {
          return firstname;
        }
        public void setFirstname(String firstname) {
          this.firstname = firstname;
        }
        public String getUsername() {
          return username;
        }
        public void setUsername(String username) {
          this.username = username;
        }
        public int getAge() {
         return age;
        }
        public void setAge(int age) {
          this.age = age;
        }
        public String getGender() {
          return gender;
        }
        public void setGender(String gender) {
          this.gender = gender;
        }
      }
      

       

      Other important notes

      i am using Oracle RDBMS

        • 1. Re: ORA-00904:  invalid identifier for rest endpoint attribute
          Ramesh Reddy Master

          Ganesh,

           

          Are you working from this repo? GitHub - teiid/teiid-spring-boot: Teiid Spring Boot Starter  ? See an example here teiid-spring-boot/UserGuide.adoc at master · teiid/teiid-spring-boot · GitHub and also in the "samples" directory there is a "JsonTable" example.

           

          Now coming back to your example, your JSON bean with ExternalSystemUser seems to be correct. But on TeiidUser is partially correct. The usage of "teiiduserexample.TeiidUSER" in the @SelectQuery is wrong. You are trying to use the Entity defined in it's own transformation. Entity is being defined must be based on other tables in different data sources or other entities defined like ExternalSystemUser.

           

          1) Here "thisAppUser" needs to represent your table in Oracle where you are reading data from. For example "ora.user" where "user" is a table in your Oracle database, and "ora" is the name of the datasource you configured in the spring boot. see the Datasources.java class for this.

           

          2) When using the other Entities defined in the application in the @SelectQuery or any other annotation, you need to use name what JPA would issue to datasource. For example using the default JPA/Hibernate naming strategy, it will be "external_system_user". That is the name by which the Teiid generates its metadata.

           

          @SelectQuery( " select thisAppUser.userid," +  
           " externalUser.age,"  
           " thisAppUser.firstname,"  
           " externalUser.gender "   
           " thisAppUser.username,"   
           " from ora.user as thisAppUser,"  
           " external_system_user as externalUser "  
           " WHERE thisAppUser.username=externalUser.username ") 
          

           

          Another IMPORTANT note is if you see above, I rearranged your columns in the above query as @id first, then columns by alpha sorted, then any join columns. This is required because when JPA system issues a command to read the entity it expects the "select" be in that order, and Teiid query MUST match to that order. These are some idiosyncrasies enforced by JPA layer, once you conform to those everything works!!!

           

          BTW, this spring boot based layer is still very new, I am trying to ramp up documents and examples. I am very interested in your feedback. So, thanks for trying it out.

           

          Ramesh..

          • 2. Re: ORA-00904:  invalid identifier for rest endpoint attribute
            ganesh pol Newbie

            Hi Ramesh,

            Thanks for taking time for responding to my query.

             

            following are details from my ds

            import javax.sql.DataSource;

            import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

            import org.springframework.boot.context.properties.ConfigurationProperties;

            import org.springframework.context.annotation.Bean;

            import org.springframework.context.annotation.Configuration;

            @Configuration

            public class DataSources {

             

            public DataSources() {

            // TODO Auto-generated constructor stub

            }

            @ConfigurationProperties(prefix="spring.datasource.teiiduserexample")

            @Bean

            public DataSource teiiduserexampleDs() {

            return DataSourceBuilder.create().build();

            }

            }

             

            i also made changes suggested by you for @selectQuery but still i am unable to get valid response.

            i also observed that i am unable to see virtual database related logs as found in your video from 32:44 till 32:55  Teiid Data Virtualization With Spring Boot - YouTube

            • 3. Re: ORA-00904:  invalid identifier for rest endpoint attribute
              Ramesh Reddy Master

              For debug, you need to add following the application.properties file.

               

              logging.level.org.teiid.spring=DEBUG

               

              (if you do logging.level.org.teiid=DEBUG or TRACE) you will see all the Teiid engine's log too)

               

              What I said from the previous comment, "ora" needs to be "teiiduserexampleDs" based on your Datasources.java class. First test to see if you are reading data fine from Json data, then you can focus on TeiidUser class.

               

              What is the error you are seeing?

              • 4. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                ganesh pol Newbie

                Hi Ramesh,

                As per your earlier response I made changes , but now I am getting some different error.

                Related to teiid log level

                Changed log level from DEBUG to TRACE in my application.properties

                logging.level.org.teiid.spring=TRACE

                 

                Changes Made

                Changes A and B are as per your inputs for datasource name

                Change C is inline with teiid-spring-boot project available on Github

                Change D,E to explicitely mention table name

                 

                A]Changes in datasource bean configuration

                From

                @ConfigurationProperties(prefix="spring.datasource.teiiduserexample")
                @Bean
                public DataSource teiiduserexampleDs() {
                       return DataSourceBuilder.create().build();
                }
                

                To

                @ConfigurationProperties(prefix="spring.datasource. teiiduserexampleDs")
                @Bean
                public DataSource teiiduserexampleDs() {
                       return DataSourceBuilder.create().build();
                }
                

                B] Changes in application.properties

                a. spring.datasource.teiiduserexampleDs.url=jdbc:oracle:thin:@<DB-IP>:1521/XE
                b. spring.datasource.teiiduserexampleDs.username=<DB-USER>
                c. spring.datasource.teiiduserexampleDs.password=<DB-password>
                d. spring.datasource.teiiduserexampleDs.driver-class-name=oracle.jdbc.OracleDriver
                
                

                it was earlier referring

                1. a. spring.datasource.teiiduserexample.url etc (please note Ds suffix was missing for all spring.datasource related keys)

                 

                 

                C]Changes made in Pom.xml

                1. A. C.1] changed version for spring-boot-starter-parent

                Changed spring-boot-starter-parent version from 1.3.5 to 1.5.3. it is inline with demo project.

                     

                <parent>
                              <groupId>org.springframework.boot</groupId>
                              <artifactId>spring-boot-starter-parent</artifactId>
                              <version>1.5.3.RELEASE</version>
                       </parent>
                
                1. A. C.2]Added dependency for jdbc
                <dependency>
                       <groupId>org.springframework.boot</groupId>
                       <artifactId>spring-boot-starter-jdbc</artifactId>
                </dependency>
                

                D] Changes in TeiidUser Entity

                1. A. D.1] Added explicit Table annotation for TeiidUser
                2. B. D.2]replace teiiduserexample to teiiduserexampleDs

                Please refer in code marked in bold

                @Entity
                @Table (name="TEIIDUSER")
                @SelectQuery( " select thisAppUser.userid," +    
                " externalUser.age,"   
                 +" thisAppUser.firstname,"   
                 +" externalUser.gender "    
                 +" thisAppUser.username,"    
                 +"    from teiiduserexampleDs.TEIIDUSER as thisAppUser,"   
                 +" external_system_user as externalUser "   
                 +"    WHERE thisAppUser.username=externalUser.username ")   
                public class TeiidUSER implements Serializable {
                //fields
                }
                

                 

                E] For External user added explicit Table annotation

                @Entity
                @Table (name="external_system_user")
                @JsonTable(endpoint="http://<IP:PORT>/findAllUsersfromExternal" ,rootIsArray=true)
                public class ExternalSystemUser implements Serializable{
                

                 

                Logs Observation

                A] Found logs for VDB in console

                 

                2017-10-06 12:40:07.704  INFO 8968 --- [           main] o.h.e.j.e.i.LobCreatorBuilderImpl : HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
                2017-10-06 12:40:08.118  INFO 8968 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
                2017-10-06 12:40:09.126  INFO 8968 --- [           main] o.t.spring.autoconfigure.TeiidServer : Added teiiduserexampleDs to the Teiid Database
                2017-10-06 12:40:09.127  INFO 8968 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener : TEIID40120 VDB spring.1.0.0 will be removed from the repository
                2017-10-06 12:40:09.128  INFO 8968 --- [ main] org.teiid.RUNTIME.VDBLifeCycleListener : TEIID40119 VDB spring.1.0.0 removed from the repository
                2017-10-06 12:40:09.149  INFO 8968 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener : TEIID40118 VDB spring.1.0.0 added to the repository
                2017-10-06 12:40:09.586  INFO 8968 --- [           main] org.teiid.CONNECTOR : OracleExecutionFactory Commit=true;DatabaseProductName=Oracle;DatabaseProductVersion=Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production;DriverMajorVersion=12;DriverMajorVersion=1;DriverName=Oracle JDBC driver;DriverVersion=12.1.0.1.0;IsolationLevel=2
                2017-10-06 12:40:09.978  WARN 8968 --- [           main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'teiiduserexampleDs' defined in class path resource [com/mastekpoc/teiidpoc/DataSources.class]: Initialization of bean failed; nested exception is org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table USERS
                2017-10-06 12:40:09.979  INFO 8968 --- [           main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
                2017-10-06 12:40:09.980  INFO 8968 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory : Shutting down embedded database: url='jdbc:teiid:spring;useCallingThread=true;autoFailover=true;waitForLoad=5000;autoCommitTxn=OFF'
                2017-10-06 12:40:10.413  INFO 8968 --- [           main] o.apache.catalina.core.StandardService : Stopping service Tomcat
                2
                

                 

                B]And got following exception

                 

                • org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'teiiduserexampleDs' defined in class path resource [com/mypoc/teiidpoc/DataSources.class]: Initialization of bean failed; nested exception is org.teiid.metadata.DuplicateRecordException:
                TEIID60013 Duplicate Table USERS
                
                 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                 at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866) ~[spring-context-4.3.8.RELEASE.jar:4.3.8.RELEASE]
                
                Caused by: org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table USERS
                 at org.teiid.metadata.Schema.addTable(Schema.java:47) ~[teiid-api-10.0.0.Beta3-SNAPSHOT.jar:10.0.0.Beta3-SNAPSHOT]
                 at org.teiid.metadata.MetadataFactory.addTable(MetadataFactory.java:181) ~[teiid-api-10.0.0.Beta3-SNAPSHOT.jar:10.0.0.Beta3-SNAPSHOT]
                 at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataProcessor.java:379) ~[translator-jdbc-10.0.0.Beta3-SNAPSHOT.jar:10.0.0.Beta3-SNAPSHOT]
                 at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataProcessor.java:362) ~[translator-jdbc-10.0.0.Beta3-SNAPSHOT.jar:10.0.0.Beta3-SNAPSHOT]
                 at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:335) ~[translator-jdbc-10.0.0.Beta3-SNAPSHOT.jar:10.0.0.Beta3-SNAPSHOT]
                
                • 5. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                  Ramesh Reddy Master

                  Still couple of issues.

                   

                  1) "teiiduserexampleDs.TEIIDUSER" is the table that represents the table in your Oracle database, not the entity you are creating.

                  2) Looks like in your Oracle data there are two tables with the same name "Users", maybe in different schemas. This is particularly an issue with Oracle driver where it provides the metadata about all the schema in data source rather what you connected to. The way to filter this out is to define schema to import from Oracle. you can do that by adding the following property in your application.properties

                   

                  spring.datasource.teiiduserexampleDs.importer.schemaPattern=<your-schema-name-in-oracle>
                  

                   

                  basically, you can control all the tables, views that you want in "teiiduserexampleDs" model, the selection can be controlled through what we call importer properties on a translator. See this for more information JDBC Translators · Teiid Documentation  at "Importer Properties". I need to document this in Spring boot docs.

                   

                  Ramesh..

                  • 6. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                    ganesh pol Newbie

                    Hi Ramesh,

                    Following are inputs from my end

                     

                    1. 1. Related to importer.schemaPattern

                    As per your inputs earlier inputs I need to use

                    spring.datasource.teiiduserexampleDs.importer.schemaPattern=<my-schema-name>

                      • While looking at standard properties available for spring boot at location I am unable to find this specific property.
                      • Even when I tried this property I am getting same error.
                      • Since I cannot delete ‘users’ table from oracle db hence I tried on different oracle instance which is not having ‘Users’ table for which we are getting error.
                      • But now I am getting problem for different table ‘AQ$_SCHEDULES’ this table is owned by sys and system users.

                    From this I can conclude that importer.schemaPattern is not working as per expectation.

                     

                     

                    1. 2. Other important notes
                    2. 1. As per my understanding in oracle user and schema are same. Hence provided username for importer.schemaPattern
                    3. 2. URL for connection :- jdbc:oracle:thin:@<IP-PORT>/XE
                    4. 3. username new DS:- spring.datasource.teiiduserganesh.username=teiiduserganesh
                    5. 4. spring.datasource.teiiduserganesh.importer.schemaPattern=teiiduserganesh
                    • 7. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                      Ramesh Reddy Master

                      You cloned and using the copy of "Teiid" using Spring Boot defined here, right? GitHub - teiid/teiid-spring-boot: Teiid Spring Boot Starter

                       

                      You will not find this property in Spring Boot application, this is property specific to Teiid, so it will be Teiid documentation, and I provided the link where it was. When this property used with-in the spring-boot based Teiid, the prefix always is (see code teiid-spring-boot/TeiidServer.java at master · teiid/teiid-spring-boot · GitHub )

                       

                      spring.datasource.{MyDataSourceName}
                      

                       

                      There is le bit of confusion going on with the way you defined your DataSource.java where the "prefix" being used "datasource" method name defined are not same, so lets start by making them SAME.

                       

                      @ConfigurationProperties(prefix="spring.datasource.teiiduserexampleDs")  
                      @Bean  
                      public DataSource teiiduserexampleDs() {  
                             return DataSourceBuilder.create().build();  
                      }
                      

                       

                      Now all data source properties need to match

                       

                      spring.datasource.teiiduserexampleDs.url=jdbc:oracle:thin:@<DB-IP>:1521/XE  
                      spring.datasource.teiiduserexampleDs.username=<DB-USER>  
                      spring.datasource.teiiduserexampleDs.password=<DB-password>  
                      spring.datasource.teiiduserexampleDs.driver-class-name=oracle.jdbc.OracleDriver 
                      spring.datasource.teiiduserexampleDs.importer.schemaPattern=teiiduserganesh
                      
                      logging.level.org.teiid=DEBUG
                      

                       

                      If you want to share your code in like GitHub, I will take look.

                      • 8. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                        ganesh pol Newbie

                        Hi Ramesh,

                        I created github repo at

                        https://github.com/polganesh/teiidpoc.git

                         

                        you will find datasources at com.gp.poc.teiidpoc

                        it has datasource name inline with application.properties.

                         

                        TEIIDLOCALUSER is is table in oracle database

                        Explicit entry for same is present in com.gp.poc.teiidpoc.entity.TeiidUSER

                         

                        com.gp.poc.teiidpoc.entity.ExternalSystemUser represent mapping for REST endpoint

                        • 10. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                          ganesh pol Newbie

                          I made changes as per your inputs but unable to get success with this. in order to avoid this problem i created different REST API for  TEIIDLOCALUSER.

                          created seperate Aggregator project which will join two virtual entities.

                          in this aggregator project i am treating these two entities(Teiidlocaluser,Externalsystemuser) as normal JPA entities.

                          Created JPA Repository for both Teiidlocaluser,Externalsystemuser

                          then used @Query annotation in Teiidlocaluser repo to join both entities where i join it with JPQL(define constructor in Teiidlocaluser to accept all required values, used this annotation in @Query annotation for method defined in Teiidlocaluser repo).

                          • 11. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                            Ramesh Reddy Master

                            Can you try adding these following properties to your application.properties and see if we get past the issue?

                             

                            spring.datasource.teiiduserexampleDs.importer.useQualifiedName=true
                            spring.datasource.teiiduserexampleDs.importer.useFullSchemaName=false
                            spring.datasource.teiiduserexampleDs.importer.schemaPattern=teiiduserganesh
                            

                             

                            Ramesh..

                            • 12. Re: ORA-00904:  invalid identifier for rest endpoint attribute
                              Ramesh Reddy Master

                              Yes, Teiid is data integration engine first where it can can integrate data from different sources, in your case entities from different sources. Then virtualization next where this integration can be captured into an other reusable Views, where consumers of the view do not know how the data has been collated. See http://teiid.org for a full scope of the project, as there are plenty of material.


                              Ramesh..