Object with only OFS_Link=1

[Michael A. Lowry]

Today I was looking for an object in one of my AE systems. I found it using the quick search function, right-clicked, and chose Explorer to display the object's home folder. A window opened with <No Folder> selected. The object was not listed.

I decided to take a look at the database to see what was going on. The locations of AE objects in the folder hierarchy are stored in the OFS table. In another thread I documented a handy SQL query to list the OFS table entries for an object. If I list the OFS entries for this object, I see that it has a single entry deep in the hierarchy, with OFS_Link=1. If I navigate to that location in the folder hierarchy, sure enough, I see a link. The object has no OFS entry with OFS_Link=0. This means that the object has no home folder, even though it has a single link. Normally this should not happen.

What can cause this problem? Is there a quick fix?

[Michael A. Lowry]

Well, I figured out how the problem originated. I came up with a query to find out the scope of the problem. This query lists all of the objects under a specified path, and all of the folder locations (including of links) of those objects, even if those locations are outside of the specified path.

with
Bind_Parms -- Set client number and folder path here
 as (select 100 as Client_Number,
 '0100\APPS' AS Folder_Path
 from dual),

AllFolderPaths (FOLD_Idnr, FolderPath) AS (
 select OH_IDnr,OH_Name
 from OH
 where OH_OType = 'CLNT'
 and OH_Name = '0100'
 --between '0000' and '9999'
 UNION ALL
 select OFS_OH_Idnr_O,
 FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
 from AllFolderPaths
 inner join OFS on FOLD_Idnr = OFS_OH_Idnr_F
 inner join OH on OFS_OH_Idnr_O = OH_Idnr
 where OH_OType = 'FOLD'
),

child_objects as
(
select OFS_OH_Idnr_O,OFS_Link,OFS_OH_Idnr_F
 from OFS
 start with OFS_OH_Idnr_F =
 (
 select OH_IDNR from OH where OH_OType='FOLD'
 and OH_Idnr = (
 select FOLD_Idnr
 from AllFolderPaths
 where FolderPath = (Select Folder_Path from Bind_Parms)
 )
 and OH_CLIENT=(Select Client_Number from Bind_Parms)
 )
connect by prior OFS_OH_Idnr_O = OFS_OH_Idnr_F
),

child_objects_and_their_links as (
select OH_Idnr, OH_OType, OH_Name, FOLD_Idnr,
replace(substr(FolderPath,5),'\', '/') as Path,
OFS_Link
from AllFolderPaths
join OFS on FOLD_Idnr=OFS_OH_Idnr_F
join OH on OFS_OH_Idnr_O = OH_Idnr
where OH_OType <> 'FOLD'
and OH_Idnr in (select OFS_OH_Idnr_O from child_objects)
order by OH_Name
),

MainQuery as(
select *
from child_objects_and_their_links
inner join (
select OH_Name OH_Name_P, count(distinct(FOLD_Idnr)) as num_unique_paths
from child_objects_and_their_links
group by OH_Name
) subq
on child_objects_and_their_links.OH_Name = subq.OH_Name_P
)

select
OH_Idnr, OH_OType, OH_Name, FOLD_Idnr, Path, OFS_Link, Num_Unique_Paths
from MainQuery
where Num_Unique_Paths = 1
and OFS_Link=1
order by OH_Name,OFS_Link,Path

This query returned just 7 results - 7 links in a single folder. I realized that the problem likely occurred during XML import, and I quickly confirmed this hunch. The XML file in question is version 3 of my general-purpose EXEC VARAs. The first part of the XML file follows.

<?xml version="1.0" encoding="ISO-8859-15"?>
<uc-export clientvers="12.0.5+hf.2.build.1638">
<FolderStruct>
<includeExternalObjects>1</includeExternalObjects>
<FOLD name="GENERAL_PURPOSE_EXEC_VARAS" title="">
<JOBS_UNIX id="5812018" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.JOBS"/>
<SCRI id="5808048" link="0" name="UC4.LOOK_UP_FREE_SPACE.SCRI"/>
<SCRI id="5808049" link="0" name="UC4.LOOK_UP_PWP.SCRI"/>
<SCRI id="5796069" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.SCRI"/>
<VARA id="5790060" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.VARA_EXEC"/>
<JOBS_SQL id="5775096" link="0" name="UC4.GP.JOBS_SQL##TEST##"/>
<JOBS_SQL id="5775097" link="0" name="UC4.GP_SQL.JOBS"/>
<VARA id="5775098" link="0" name="UC4.GP_SQL.VARA_EXEC"/>
<VARA id="5775099" link="0" name="UC4.GP_SQL.VARA_EXEC##TEST##"/>
<JOBS_UNIX id="5775100" link="0" name="UC4.GP_UNIX.JOBS"/>
<VARA id="5775101" link="0" name="UC4.GP_UNIX.VARA_EXEC"/>
<VARA id="5775102" link="0" name="UC4.GP_UNIX.VARA_EXEC##TEST##"/>
<FOLD name="COMMON" title="">
<VARA id="5771045" link="1" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
<VARA id="5790030" link="1" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
<JOBI id="5805033" link="1" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
<JOBI id="5774078" link="1" name="UC4.READ_JOB_OUTPUT.JOBI"/>
<JOBI id="3141355" link="1" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
<JOBI id="5804007" link="1" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
<VARA id="5806033" link="1" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
</FOLD>
</FOLD>
<FOLD name="##INCLUDED_EXTERNALS##">
<VARA link="0" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
<VARA link="0" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
<JOBI link="0" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
<JOBI link="0" name="UC4.READ_JOB_OUTPUT.JOBI"/>
<JOBI link="0" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
<JOBI link="0" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
<VARA link="0" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
</FOLD>
</FolderStruct>

Note that exactly seven links (to objects outside of the folder) are included. It appears that when this XML file was imported, the objects were created as links, but with no home folder. This seems like a bug.

[Michael A. Lowry]

I was able to reproduce the problem:

1. Remove the existing objects from the target system.
2. Remove the ##INCLUDED_EXTERNALS## element and its children from the XML file.
3. Import the XML file, with Replace existing objects enabled and Keep existing folder links disabled.

So, the problem happens in the following set of circumstances:

• The XML file contains a FolderStruct element; and
• An object entry appears under FolderStruct with link=1, but without another corresponding entry with link=0.

The Automation Engine ought to be clever enough to recognize this problematic situation, and should either reject the XML file, or should create OFS entries for these objects with OFS_Link=0, not 1. It should not create links objects without a home folder location.