Download a list of objects with their references

[Mylene Chalut]

​Good day guys !

We are using version 12.3.1

I would like to download a list of objects with all their references from the database. I was able to find a piece of code to download all the selected OType objects from a specific client, which was pretty simple:

Select OH_Name from OH where OH_Client = ? and OH_OType = ? and OH_DeleteFlag = 0

However, I don't have access to the database to see it's structure.  Can somebody here provide me with the missing SQL code I need to complete my report ?

Thanks in advance for your time :-)
Mylene

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Pete Wirfs]

There are different types of references to consider.  Tasks in a workflow is one type of reference.  Tasks in a schedule is another type of reference.  Promptsets in a job is another type of reference.  VARA objects in a script is another type of reference.

For instance, tasks in a workflow are stored in the jpp table.  This query returns all of the tasks for one workflow object;

select jpp.*
from uc4.dbo.jpp
   , uc4.dbo.oh
where jpp_oh_idnr = oh_idnr
and oh_name = workflowname'​

https://docs.automic.com/documentation/WEBHELP/English/all/components/AE/latest/DB%20Schema/db/_structure/HTML/index.htm

------------------------------
Pete
------------------------------

[Mylene Chalut]

That's why I stated that I wanted them all :-)​

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Carsten Schmitz]

​If one had them all, wouldn't one be half way to writing a conversion tool from Automic to something else, and all the way towards a "search for use" function that beats the one Automic provides by finding truly everything?

Consider me intrigued ... ;)

[Carsten Schmitz]

​Not sure if this is any use, but this is a query that I made years ago. It's actually part of a cgi that generates dependency trees. By trial and error, it works for listing all dependencies for JOBP, JSCH and some other objects. Not script though, i.e. it won't get references by ACTIVATE_UC_OBJECT and such.

select OH_Name, JPP_Otype, JPP_Object, JPP_Lnr, JPPA_PreLnr from OH inner join JPP on OH_Idnr = JPP_OH_Idnr left outer join JPPA on OH_Idnr = JPPA_OH_Idnr and JPPA_JPP_Lnr = JPP_Lnr where OH_Client = 10 and OH_Name = '$BASE_ELEMENT' order by JPP_Lnr;"

The web form lets users enter a "base element", and then it parses the output and generates a list of dependencies, and it has some superficial options like letting the user generate the nesting levels as numbers in a csv, or as poor man's tree structure. If you want the cgi and surrounding html page, let me know.

Hth,

[Mylene Chalut]

OMG!!!  You are my super hero!!  Thank you so much...  this is exactly what I need :-)​

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Carsten Schmitz]

Hehe, glad it helps, and thanks for the nice feedback!

> super hero!! 

I shall now proceed to source myself a spandex cape with "Automic" printed on it and shall henceforth be known as the mysterious ...

SQL-by-Trial-and-Error-Man! ;)

[Mylene Chalut]

Thank you :-)​

------------------------------
Programmer-analyst
Statistics Canada
------------------------------