JBossDeveloper

Thanks Van and Ramesh for the response.

Yes, may be I did not express my requirement correctly.

So give you more detailed view on it.

I want to support my project for both SQL and Oracle database (similar data strutures and only one database at a time based on some parameter)

We also want to define row level securities for both these databases.

Thats why we thought of implementing VDB, where we can write virtual procedures to implement row level securities which can be applicable to any database.

Therefore I thought of having one VDB which can connected to either Oracle or SQL when execute my procedures.

I hope I made my point clear.

As both of you suggested, I guess I can acheive my goals as below

- create multi-source VDB from Teiid designer

- The I can write my queries as

select * from table where SOURCE_NAME = 'oracle'

I am going to try this feature.

But VDB-> Source Binding Definition , all the check-boxes and buttons are disabled (I am using Teiid 8.3)

Can you please let me know the solution for it

Thanks,

Neeta

Thanks Van.

So I have

- Created two source models - OracleSourceModel1 (importing metadata from oracle source) and SQLSourceModel1

- Have created multi-source VDB - having both above sources and have added column (SOURCE_NAME)

- Now I have created a view called CommonView

- I have created a virtual procedure on this view to access my data from either Oracle/SQL source

- But I am not able to run my procedure the way I want.

- Do I have to define SOURCE_NAME column on all the tables?

- Do I have to define SOURCE_NAME as IN parameter on my virtual procedure . (For now I have defined it, else it was giving error : TEIID31118 Element "SOURCE_NAME" is not defined by any relevant group)

My SampleProcedure

CREATE VIRTUAL PROCEDURE

BEGIN

    SELECT ItemId FROM tblItems WHERE SOURCE_NAME = 'OracleSourceModel1';

END

When I try to preview above procedure, it gives following error

TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 OracleSourceModel1: 904 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.ITEMID FROM "USER_XXX"."TBLITEMS" g_0 WHERE g_0.SOURCE_NAME = 'OracleSourceModel1']

I guess I have to set following VDB properties

multisource.addColumn property = true

multisource.columnName = VDB Name

But I am not sure where to set these properties in Teiid Designer?

When I create VDB in designer, all configuration is stored in standalone-teiid.xml and no seperate VDB.xml file is generated. Is it correct ??

Can you please let me know how can I use SOURCE_NAME in my procedure?

Thanks,

Neeta

Thanks Van and Ramesh for your inputs till now.

Finally I was able to achieve what I wanted.

I have done following steps

- Created one source model and imported metadata from Oracle

- Created multi-source VDB with 2 data sources OracleDS and SQLServerDS

- Created view and a virtual procedure on it

- Manually added IN procedure parameter SOURCE_NAME to the virtual procedure

My sample procedure is

CREATE VIRTUAL PROCEDURE

BEGIN

    SELECT SOURCE_NAME, ItemId FROM tblItems where SOURCE_NAME=sourceNameInput;

END

Now when I run the procedure with OracleDS name, it gives me correct results from Oracle and similarly give me correct results when I execute it with SQLServerDS name

But I want to modify my query to join 2 tables as below

select distinct A.ItemId , B.UserRole from dbo.tblItems A inner Join tblUserSecurity B on A.RLS_ID=B.Id where UserRole='UserA';

So if I try to specify SOURCE_NAME as mentioned below ,but it gives me error :ERROR: TEIID31118 Element "A.SOURCE_NAME" is not defined by any relevant group.

select distinct A.ItemId , B.UserRole from dbo.tblItems A inner Join tblUserSecurity B on A.RLS_ID=B.Id where (A.SOURCENAME=sourceNameInput) AND (A.SOURCENAME=sourceNameInput) AND UserRole='UserA';

Can you please let me know how can I resolve this.

Thanks,

Neeta

Sorry Van, it was typo mistake in my comments here. I have specified as SOURCE_NAME in my procedure properly.

So if I use below query, it works fine (Where SOURCE_NAME is the multi-source column. I guess it is automatically added to metdata tables during runtime. I have not added this column seperately in my metadata tables). I t gives me results from both the data sources, as expected.

SELECT ID, USERROLE, SOURCE_NAME FROM tblUserSecurity AS A;

But when I query as below, it gives me exception in Teiid designer. (ERROR: TEIID31118 Element "A.SOURCE_NAME" is not defined by any relevant group.)

SELECT ID, USERROLE, SOURCE_NAME FROM tblUserSecurity AS A where A.SOURCE_NAME='OracleDataSource';

Do I have to add SOURCE_NAME in all my tables seperately?

Steve - My requirement is to get data from only one source at a time.

Thanks,

Neeta

Hello Steve,

I am running this query from my virtual procedure.

But if run the same query from Eclipse -> Teiid-> SQL Scrapbook, it gives correct results.

But same query is not working from the virtual procedure.

But if I add SOURCE_NAME column in my source model table tblUserSecurity, then it works fine.

So the conclusion is :

If we want to use multi-source column in virtual procedure and want to use as part of alias, we need to add it in source model table. So I guess its only if you want to use column name as part of alias.

If I use below query , I do not require to add SOURCE_NAME in my table, but I require to add as procedure IN parameter.

SELECT ID, USERROLE, SOURCE_NAME FROM tblUserSecurity where SOURCE_NAME='OracleDataSource';

Please let me know if my understanding is correct? and is it as expected?

Thanks,

Neeta