Feeds:
Posts
Comments

Posts Tagged ‘DM_QUERY_E_CURSOR_ERROR’

Few days ago my friend asked me a dql query. He wanted to have a list of all the documents and their folder paths in the repository.

It didn’t appear to be a tough one. I knew that the i_folder_id of dm_document is mapped with the r_object_id of the dm_folder to which the document is linked to. A document can be linked to multiple folders and that explains why this i_folder_id is a repeating attribute. The dm_folder has another repeating attribute r_folder_path which stores the folder path of that particular folder. The reason for r_folder_path being repeating is same as that for i_folder_id in case of dm_document.

I told him this dql to get the folder path of all the documents.

SELECT r_folder_path
FROM dm_folder
WHERE r_object_id in
(SELECT distinct i_folder_id
FROM dm_document)

He didn’t look happy enough. What he actually wanted was a list of documents with the folder path. I told him that it is not a problem; we can put a join and get the object_name from dm_document.

SELECT doc.object_name,fol.r_folder_path
FROM dm_document doc, dm_folder fol
WHERE any doc.i_folder_id = fol.r_object_id

The query looked very simple to me. He seemed to be happy and he told he will try it out. I was happy that I have given him a solution. I was a bit surprised when he told me that he was getting an error while executing this query. I didn’t expect this. The error was:

    :[DM_QUERY2_E_REPEAT_TYPE_JOIN]error: “Your query is selecting repeating attributes and joining types.”

The above query works fine if I select only doc.object_name; but it was not allowing me to select fol.r_folder_path which is a repeating attribute. The conclusion was that a repeating valued attribute cannot be selected in a normal query which uses repeating valued attribute in a join. It can be achieved by using DQL Hints mentioned later. But this friend of mine needs both Document Name and Folder Path together. I asked him to write a dfc code which gets all the documents, finds their folder path and writes this information to a file. But my friend is a real lazy guy. The bad part being that I am also no different.

I thought a lot and in the end I got an idea. I asked him to fire a query on the RDBMS tables. The object types are represented as _s and _r tables in RDBMS for their single and repeating valued attribute respectively. The query has to be fired on dm_document_s, dm_document_r, dm_folder_s and dm_folder_r tables.

So, the new query which I formed was:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_document_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

But when I fire this query on my docbase I again got an error.

    :[DM_QUERY2_E_TABLE_NOT_FOUND]error: “The database table or view was not found in the database. Error from the database was: ‘ — The database object is invalid — STATE=S0002, CODE=208, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dbo.dm_document_r’.'”

I realized that there is no such table called dm_document_r as dm_document doesn’t have any repeating attribute of its own. But in that case from where should I get the value for i_folder_id? If i_folder_id is not an attribute of dm_document then it should be inherited from dm_sysobject. Isn’t so?

So I had my new dql:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

I was so confident. This is the ultimate query which will get me the result. When I fired this query I got the following result.

    :[DM_QUERY_E_CURSOR_ERROR]error: “A database error has occurred during the creation of a cursor (‘ STATE=S0022, CODE=207, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ‘object_name’.’).”

How could I forget that object_name is also an attribute of dm_sysobject. Actually the dm_document_s has just one attribute and that is the r_object_id. As a matter of fact dm_document doesn’t have any attribute of its own. Then what is the use of having the object_type dm_document? I leave this question to be answered by you. This has to be my final query.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

Ultimately I got the result without any errors. But I wasn’t too happy. Many records in object_name as well as r_folder_path were showing as empty.
I did a quick fix:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring

The r_folder_path was not showing any empty records but the case was not so with object_name. Something clicked in my mind. My mind had found a flaw in the query which was looking fine to me till now. My friend was interested in documents; not the sysobjects. I was actually giving him a lot of garbage.

And thus I got my next query:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_type = 'dm_document'

Cool….. My friend looks to be the happiest person on earth. He got the result he was looking for. But I still want to do one more small change.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_type = 'dm_document'
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

The result is same as the earlier query but somehow I look more satisfied with this query. I am not sure how good or how efficient this query is but my friend got what he wanted and I was satisfied that I was able to help him out.

The story is not over yet. I just checked the sql equivalent of this query in the dql component of webtop.

Here is the result:

SELECT all doc_s.object_name, fol_r.r_folder_path
FROM dbo.dm_sysobject_s doc_s, dbo.dm_sysobject_r doc_r, dbo.dm_folder_r fol_r
WHERE ((doc_s.r_object_type=N'dm_document')
AND fol_r.r_folder_path != ' '
AND (doc_s.r_object_id=doc_r.r_object_id)
AND (doc_r.i_folder_id=fol_r.r_object_id))

Do you know what the most interesting part is?

After doing all this crap, I posted it on my blog. I had added tags like DQL, Documentum, etc. When I clicked on the DQL link (The tag which I had added) I got Rajendra’s blog in the result. He has posted a query there.

SELECT A.r_object_id, A.object_name, B.r_folder_path
FROM dm_document A, dm_folder_r B
WHERE any A.i_folder_id = B.r_object_id

Doesn’t that look much simple? :D:D:D

Here is another query from inthewoods. It uses DQL Hint and appears to be a better option than the earlier two.

SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM dm_document doc, dm_folder fld
WHERE doc.i_folder_id = fld.r_object_id ENABLE(ROW_BASED)

So.. Thats the whole story. Hope you enjoyed it.

*Guess it would be a nice idea to add ‘fld.r_folder_path is not nullstring’ and ‘order by r_object_id’ to the final query.

Advertisements

Read Full Post »