4.0.4 - Mapping a View to an External RB Object

Hi,

I just came across this : '' 6. If desired, specify a single numeric unique column as the table's primary key." in the documentation here :

documentation.progress.com/.../

So I assume the primary key has to be a NUMERIC UNIQUE Column. Can you confirm please?

So to work around this, I am trying to use a composite key, by selecting two columns as primary key (instead of the concat). It is still not working.

Are they any constraints on the type of the columns we can use in the composite key ?

I am now using two integer fields.

Any ideas why this is not working please?

Thank you.

Meryem

Hi Meryem,

I have created a sample view with three int columns ,pulled this object to RB and data displayed correctly. But when I created a view using three columns(one of them is concatenation of third column) object created successfully but data is not getting visible. I will check with our internal teams and will let you know.

However,as a workaround can you please alter your table by updating column name with combination of other three fields and create a view again?

Regards,

Shiva

Hi Shiva,

What do you mean by :

"as a workaround can you please alter your table by updating column name with combination of other three fields and create a view again" ?

Change the name of the column ?

Meryem

Also in your first test : "I have created a sample view with three int columns ,pulled this object to RB and data displayed correctly.", What are you using as primary key while mapping the view in RB ?

Hi Meryem,

As on date we cannot have composite key(in your case it is ID) as a PRIMARY KEY in rollbase which is why data is not getting populated in RB.

Can you please try creating object in RB  without assigning composite key as PK instead give a separate column and try again?

Regards,

Shiva

Hi Shiva,

So I tried your suggestion, I am mapping the view to an external RB object using a separate column as Primary Key. But I dont have any unique column in that view, that is why I did the concat initially to have something unique.

It is not working. But this makes sense because this column I am using is not unique in the View.

Any other suggestions please?

Also, are you saying we can not have composite keys for now ?

Is that going to be allowed anytime soon ?

Hi Meryem,

In your case you were trying to use "ID" as a primary key in RB ,making it a mandatory field while creating the record. But as per your scenario it should be the combination of other columns which eventually leads to data inconsistency in DB. I would suggest you not to make "ID" as a PK in RB .

Regards,

Shiva

HI Shiva,

Yes I got that. But how do I solve the problem then ? I need a primary key in RB. And none of the columns I have in that view is unique so none can act as primary key.

Thanks

Meryem

Hi Meryk,

You can select multiple columns as primary key. Select combination of columns which make unique key for your case then this will work.

Thanks and Regards

Pradeep

What should be the type of those columns ?

Thanks

Hi,

I am using two columns to build the composite key. One is an Integer, and one is a Text(36).

The object is built, but not populated with data.

Again, this is a VIEW, not a table.

What could be wrong here ?

Thank you.

Meryem

Hi Meryem,

To clear the confusion please see the below steps which I performed:

1)Created table with two columns and made both of them as composite key as follow:

create table abc(name varchar(20), rno int,primary key(name,rno));

I have inserted few records in the above table.

2)Now I have created view as follows:

create view abcview as select * from abc;

Now after pulling this object into RB I have assigned name , rno as primary keys and data gets populated without any issue.

From my understanding you were creating view by concatenating columns as below:

create view abcview as select name,rno,concat(name,rno) from abc; from which you cannot be able to create concat(name,rno) as PRIMARY KEY.

Regards,

Shiva

Hi Shiva,

Thanks for the clarifications.

The issue was not the composite key, it was the name of one of the columns that was containing a '('. As soon as I renamed it, the mapping with the same composite key worked fine.

This is an interesting thing people need to know in my point of view. I presume there are some restrictions on the column names of the external tables that can be mapped to external objects in RB, probably no specific characters like '(' or ')' or any others..

I think this should be specified in the documentation.

Thank you

Meryem