ORA-00904: invalid identifier for rest endpoint attribute

[polganesh]

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

[rareddy]

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..

[polganesh]

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

[rareddy]

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?

[polganesh]

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]

[rareddy]

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..

[polganesh]

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

[rareddy]

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.

[polganesh]

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

[rareddy]

I made couple changes see this fixing the TeiidUser and application.properties by rareddy · Pull Request #1 · polganesh/teiidpoc · GitHub

[polganesh]

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).

[rareddy]

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..

[rareddy]

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..