Setting TEIID_MULTI_DATA_SOURCE_COLUMN attribute

[shekhar78]

Is it possible to set TEIID_MULTI_DATA_SOURCE_COLUMN as a session parameter instead of having it part of each insert statement?

Workflow:

User is working on Teiid_multi_data_Source VDB . He picks up a data source in the beginning and all his subsequent action should go against the selected datasource.

It would be good, if there is a provision to set TEIID_MULTI_DATA_SOURCE_COLUMN in JDBC Connection once and the  information is used for any subsequent CRUD operation.

Thanks

[rareddy]

How does user select the data source? Give me an example. (the above will not work). I want you think Teiid as just another database like Oracle, postgres etc and devise a way. Or this property can be a user's "role".

Then look at Data Roles, and Access Patterns sections in Teiid documentation.

[shekhar78]

Using a userDefinedFunction ( or stored procedure) which provides list of available datasource associated with the VDB.  I am thinking of datasources as different schema in Oracle. User connects to a URL. Get the list of datasource ( similar to schema of similar types).

Select a datasource to work with. Set the current_Session to the selected data source and all the subsequent query is against the selected datasource.

[rareddy]

Shekhar,

You did not answer the question I was looking for

Select a datasource to work with. Set the current_Session to the selected data source and all the subsequent query is against the selected datasource.

You are thinking like programming language, as I mentioned Teiid behaves like RDBMS.

Let me ask you differently, if we can pass in the user name to the UDF, can you return a data source name from that code?

[shawkins]

Shekhar,

Functionality like this is targeted at Teiid 9.0 [TEIID-3738] Add multi-tenant functionality - JBoss Issue Tracker

The current multi-source feature was designed for maximum flexibility in accessing partitions rather than restricting to a single one.  A workaround is to use multi-source and data roles with a row level permission on each applicable table that adds in a predicate against the partitioning column based upon the user/session.

Steve