Credit: Karel Duchacek and Robert Balagot of CA Clarity Support
When you create an "Attachment" attribute on any object, the actual files uploaded to the object instances via this attribute are not stored in the ODF tables like non-attachment attributes. The files will be stored either in a filestore or in Clarity database depending on what is configured in NSA/CSA. We know that if you store documents in your Clarity database, they are stored in binary fileds and cannot be viewed by querying the field. But if you are storing documents in filestore, we can actually find the file location.
In the example below, I have
1. configured NSA to store documents in filestore
2. created an Attachment attribute on the Incident object
3. uploaded a text file named "testDoc01.txt" with just two words "Text Only" in its content to an incident
See attached image "Incident properties page.jpg"
Attachment Attribute on Incident:
This query finds the location for a file whose file name contains the word "test", and has been uploaded to a custom attachment field defined on the Incident object:
select oi.id as Incident_id,
cdfd.PATH_NAME,
cdf.name as File_name,
SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as
folder_name,
cdh.VERSION_ID as F_name
from IMM_INCIDENTS oi
inner join CLB_DMS_FILES CDF on (oi.DOCUMENT = CDF.PARENT_FOLDER_ID )
inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
where cdf.name LIKE '%test%'
The attached image "query result.jpg" shows my query result, where I can see that
1. Incident's internal ID is 5000000
2. path name that identifies the object type and internal ID of the object instance "/root/DMS/ODF/incident/5000000"
3. file name "testDoc01.txt", folder name "002/005/5002115"
4. file name of 2002115 which is the file name as stored in the filestore (not the actual name of the text file)
The attached image "folder view.jpg" shows the file as stored in my file system, with its full path shown on a Windows Explorer. In this example the file "testDoc01.txt" is stored as file "5002115" on C:\niku\clarity\filestore\files\002\005 on my Clarity sever. If I open up the file "5002115" in a text editor, I can see the words "Text Only".
A couple more similar queries:
Attachment Attribute on IDEA
select oi.id as Idea_id,
cdfd.PATH_NAME,
cdf.name as File_name,
SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as folder_name,
cdh.VERSION_ID as F_name
from odf_ca_idea oi
inner join CLB_DMS_FILES CDF on (oi.APPROVAL = CDF.PARENT_FOLDER_ID or oi.BUSINESSCASE = CDF.PARENT_FOLDER_ID )
inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
Attachments on Project collaboration and Clarity Knowledge Store
select CDV.id as name_in_fs,
SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as folder_name,
cdf.name name_of_file,
cdf.LAST_UPDATED_DATE,
cdfd.PATH_NAME,
FOLDER_TYPE name_of_object,
CDF.PARENT_FOLDER_ID,
cdh.VERSION_ID
from CLB_DMS_FILES CDF
inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
WHERE (FOLDER_TYPE = 'ProjectFolder')
(FOLDER_TYPE = 'ProjectFolder' or FOLDER_TYPE = 'StandardFolder')
Please know that these are "base" queries that we use for troubleshooting, they are not polished and can be quite inefficient since our goal has been to find the problem quickly rather than to implement them for long term maintenance. We hope that the queries give you an idea of where attachments are stored in the filestore, with some tweaking and tuning you could end up with some very powerful tools. If you do, please feel free to share!
For specific issues related to this topic that would require some digging, please allow us to work on them separately with you by logging a new Support case :)