How to query a registered table using CMIS?

JanardhanBChinta

Is there a way to query/insert/update/delete a registered table in Documentum repository using CMIS?

diamondmote

how to?

diamondmote

I want same question.

KatieMasters

Hello,

Please consider moving this question as-is (no need to recreate) to the proper forum for maximum visibility.  Questions written to the users' own "Discussions" space don't get the same amount of attention and can go unanswered for a long time.

You can do so by selecting "Move" under ACTIONS along the upper-right.  Then search for: "Documentum" and select the very first result that appears.  This will relocate it to the Developer Network space of the Documentum community.

Documentum

For further guidance on engaging with the communities for Documentum and related products, please refer to the

IIG Communities Getting Started Guide.

Kind regards,

Katie

sfaleyev

The DQL Query can be executed against the Content Server repository in the user context.

Just like in RDBMS you can access registered table using the following syntax

Select from dm_dbo.[REGISTERED_TABLE_NAME] where

Example:

Select first_name, last_name, emp_id from dm_dbo.users ;

JanardhanBChinta

I tried the query in Apache Chemistry CMIS Workbench and get back invalidArugument/Bad Request message. Any idea?

Alvaro_de_Andres

I'll have to check in my CMIS test project, but I don't think you can as registered tables are a "documentum feature" and CMIS only covers common ground between different CMS

sfaleyev

Make sure you table is registered

Select * from dm_registered where object_name=’ your table name’

If you need register table use the following dql:

REGISTER TABLE table_name (column_def {,column_def}) [[WITH] KEY (column_list)][SYNONYM ‘table_identification‘]

This DQL will return the r_object_id of the newly created dm_registered object. In this owner_name is the name of the table owner. table_name is the name of the RDBMS table. column_def defines the columns in the registered table.

column_def arguments should have following syntax column_name datatype the valid values for types are float, double, integer, int, char, character, string, date, time.

Length should be specified for character, char, or string data type.

column_list Identifies the columns in the table on which indexes have been built. column_list is usually separated with commas. table_ identification is the name of the table in the Database Example:

REGISTER TABLE “hr.users” (first_name CHAR(30), last_name (char 40), emp_id INT)KEY (“emp_id”)

Granting Rights

You need to give the permission to the users to access the registered tables. The values for various permission levels are as follows 0 (None): No access 1 (Select): The user can retrieve data from the registered table 2 (Update): The user can update existing data in the registered table4 (Insert): The user can insert new data into the registered table8 (Delete): The user can delete rows from the registered table If a user wants update and insert permissions the value should be 2+4 = 6 , The repository owner also should have the same level of permission in the underlying database to grand those permission to those users. Granting Rights full permission to users in the above example

update dm_registered object set world_table_permit = 15 where object_name = ‘users’;

update dm_registered object set owner_table_permit = 15 where object_name = ‘users’;

update dm_registered object set group_table_permit = 15 where object_name = ‘users’;

How to Unregister a Table?

Use the following DQL to Unregister a Table.

UNREGISTER table_name In this owner_name is the name of the table owner. table_name is the name of the RDBMS table. You should be the owner of table or super user to do this

JanardhanBChinta

I am able to query the registered table with repoint. Not able to query it using CMIS Workbench.

I belive this is CMIS functionality question, whether executing a pass through SQL/DQL is possible?

abc123

The answer is NO. Only CMIS base types (cmis:document, cmis:folder, cmis:relationship for Documentum) and their sub types are querable in CMIS Query Service. There is no extension to support to query other Documentum types at this point.

Regards,

William

Archana12

Hi William,

Can we retrieve custom attributes of a custom type using CMIS? I have noticed that it returns, standard attributes only (for which we have a CMIS mapping, like cmis:objectId, cmis:name), but it does not return custom attributes.

Thanks

Archana

Alvaro_de_Andres

Folder tempFolder=(Folder)session.getObjectByPath("/Temp");
ItemIterable<CmisObject> children = tempFolder.getChildren();

for (CmisObject child : children) {
          System.out.println(child.getName() + ":" +child.getPropertyValue("r_content_size"));
}

replace r_content_size with whatever custom attribute

Archana12

Hi,

I have tried this, but it returns value only if the attribute is a standard one, that too in cmis mapped format, otherwise null.

FOr example it returns valid value for cmis:objectId but null for r_object_id.

Thanks

Archana

Alvaro_de_Andres

Archana12 wrote:

Hi,

I have tried this, but it returns value only if the attribute is a standard one, that too in cmis mapped format, otherwise null.
FOr example it returns valid value for cmis:objectId but null for r_object_id.

Thanks
Archana

I can't test it right now but I think cmis base types can only be retrieved by the cmis:attribute (cmis:objectid), not directly with the attribute name (r_object_id).

Archana12

Exactly !!

So we can query custom types as CMIS base types and their subtypes are queryable, but when we fetch the custom attributes, it won't return. Because it expects only cmis:attribute to be rturned, and as no cmis mapping for custom attributes, so they are never returned.

And this I see as a major drawback.

Thanks

Archana

Alvaro_de_Andres

Archana12 wrote:

Exactly !!
So we can query custom types as CMIS base types and their subtypes are queryable, but when we fetch the custom attributes, it won't return. Because it expects only cmis:attribute to be rturned, and as no cmis mapping for custom attributes, so they are never returned.

And this I see as a major drawback.

Thanks
Archana

I'm not sure if I understand you, but if you have the cmis object you can retrieve every attribute of the object:

name: cmis:name (but not querying directly "object_name")

id: cmis:objectid (but not querying directly "r_object_name")

custom_att1: getPropertyValue("custom_att1") (but not querying cmis:custom_att1)

custom_att2: getPropertyValue("custom_att2")

etc.

However, I think that doesn't work when using the query service as said in a previous post

Archana12

if you have the cmis object you can retrieve every attribute of the object:

name: cmis:name (but not querying directly "object_name")
id: cmis:objectid (but not querying directly "r_object_name")
custom_att1: getPropertyValue("custom_att1") (but not querying cmis:custom_att1)
custom_att2: getPropertyValue("custom_att2")
etc.

However, I think that doesn't work when using the query service as Wei Zhou said in a previous post

Here are the steps I did:

1. Query custom type : select * from custom_type where custom_attr1='ABC'

2. Retrieve document as below:

ItemIterable<QueryResult> queryResult = session.query(query, false);

  for (QueryResult item : queryResult) {

    String objectId = item.getPropertyValueByQueryName(objectIdQueryName);

    Document doc = (Document) session.getObject(session.createObjectId(objectId));

System.out.println("cmis:objectId: "+doc.getPropertyValue("cmis:objectId"));

    System.out.println("Custom Value: "+doc.getPropertyValue(custom_attr1));

3. I get object ID for 1st SOP and null for 2nd one.

Alvaro_de_Andres

    System.out.println("Custom Value: "+doc.getPropertyValue(custom_attr1));

I guess you ommited the double quotes: doc.getPropetyValue("custom_attr1")? I'll try to test it later.

btw you only need to select r_object_id instead of "*" in the query.

Alvaro_de_Andres

Archana12 wrote:


if you have the cmis object you can retrieve every attribute of the object:

name: cmis:name (but not querying directly "object_name")
id: cmis:objectid (but not querying directly "r_object_name")
custom_att1: getPropertyValue("custom_att1") (but not querying cmis:custom_att1)
custom_att2: getPropertyValue("custom_att2")
etc.

However, I think that doesn't work when using the query service as Wei Zhou said in a previous post

Here are the steps I did:

1. Query custom type : select * from custom_type where custom_attr1='ABC'
2. Retrieve document as below:
ItemIterable<QueryResult> queryResult = session.query(query, false);
  for (QueryResult item : queryResult) {
    String objectId = item.getPropertyValueByQueryName(objectIdQueryName);
    Document doc = (Document) session.getObject(session.createObjectId(objectId));
System.out.println("cmis:objectId: "+doc.getPropertyValue("cmis:objectId"));
    System.out.println("Custom Value: "+doc.getPropertyValue(custom_attr1));

3. I get object ID for 1st SOP and null for 2nd one.

try this before initializing String objectId:

for(PropertyData<?> property: item.getProperties()) {
       String queryName = property.getQueryName();
       Object value = property.getFirstValue();
       System.out.println(queryName + ": " + value);
}

that should list every attribute

EDIT: I forgot the direct way to retrieve an attribute: item.getPropertyById("custom_att1").getFirstValue());

Archana12

It still retrieves only object id with that piece of code, even if I put it before initializing string objectId.

This is hat gets printed -

cmis:objectId:<id_value>

Thanks

Alvaro_de_Andres

this works for me with cmis 7.2 and latest opencmis:

ItemIterable<QueryResult> queryResult = session.query("select * from test_type", false);
for (QueryResult item : queryResult) {
     System.out.println(item.getPropertyById("r_content_size").getFirstValue());
     System.out.println(item.getPropertyByQueryName("cmis:objectId").getFirstValue());
     System.out.println(item.getPropertyById("custom_att1").getFirstValue());
}

you can only reference cmis base attributes with the cmis:att_name format. Non cmis base attributes are retrieved just fine with their name.