[rareddy]

I think you need to create view using both tables something like

CREATE VIEW UserProfile AS select * from Account a JOIN Data d ON a.ProfileID = d.ProfileID;

Now on this View you can add row filter, UserProfile.uuid = left(user(), 36)

Also, can you point out where the documentation needs more help? Maybe add JIRA and submit any changes from learnings you did?

[cjohn001]

Hello Ramesh,

now I got the big picture. One does not need to create an additional view to join the tables. It is actually a lot more simple. So the full query to just get the rows constraint to the  user submitting the query select * from Data would be

SELECT * from Data WHERE Data.ProfileID IN (SELECT Account.ProfileID FROM Account WHERE Account.uuid = LEFT(user(),36) )

as the row constraints just add an additional WHERE statement to the query submitted from a  user  the row constraint needs to be the second part of the above statement. This means

I need to add the constraint part in the VDB. The row filter than should just be

Data.ProfileID IN (SELECT Account.ProfileID FROM Account WHERE Account.uuid = LEFT(user(),36) )

I think regarding the documentation just additional examples are needed. Once I am convinced that I have a feasible solution with Teiid in place, I will put a master student on the topic to write a tutorial on how to setup TEIID Wildfly with Keycloak and row constraints and use the stuff together with Teiid Designer. My plan is to let him extend what I currently have to the same solution with Wildfly Swarm. This would than become a second tutorial. As you might remember our initial discussion, I like to have a scalable solution for an Openshift environment, hence what we are currently discussing is only half of the solution.

[cjohn001]

Hello Ramesh,

I just run into  the next issue. Not sure if this is an error in Teiid, or Teiid Designer, or if I did something wrong.

I have a table Account in which I would like to insert a record. The row format of table Account is as follows

uuidUser, char(36), PrimaryKey, NotNull, Unique, Binary

idProfile, BIGINT, NotNull, Unique, AutoIncrement

If I use a standard sql statement on the database like the following command I can successfully insert a record without specifying a value for idProfile. This is as expected and as I want it to be, as I specified AutoIncrement on the idProfile column.

INSERT INTO `my_nutri_diary`.`Account` (`uuidUser`) VALUES ('12334455-123232-1232-123122412421');

My problem is now, if I try to do the insert via Teiid odata I get the following error if I do not specify a value for idProfile.

Error:{"error":{"code":"TEIID30089","message":"TEIID30089 Required parameter 'my_nutri_diary.Account.idProfile' has no value was set or is an invalid parameter."}}

My javascript to insert the value is:

                         var oData = {

                            uuidUser: gKeycloak.subject

                        };

                        oData = JSON.stringify(oData);

                        // we need to use the low level jQuery API as we need to speak JSON

                        $.ajax({

                            type: "POST",

                            contentType: "application/json; charset=utf-8",

                            url: oODataModel.sServiceUrl + "Account",

                            data: oData,

                            dataType: "json",

                            success: function (oResult) {

                                console.assert("success");

                            },

                            error: function (oResult) {

                                console.error("Could not create user account. Error:" + oResult.responseText);

                            }

                        });

If I instead add idProfile to the previous javascript command, the insert is working as expected and the record is correcly added to the database. i.e.

var oData = {

                         uuidUser: gKeycloak.subject,

                         idProfile: 5

                    };

Well, it is clear that I cannot add the ids by hand in a real application as the client app cannot know what the next id should be. Did I do something wrong here with my insert statement? Might it be that Teiid needs some special kind of place holder or wildcard for idProfile to let the database do the autoincrement? My vdb file looks correct.

[cjohn001]

Ok, I found the answer. I have to set idProfile to 0 explicitly. The database than replaces the value with the autoincremented id.