Free Code Collection

Recently I was working on some SQL for a client who wanted to list the full path information for all items under a given Livelink node. As the customer was using SQL Server rather than Oracle I could not use the Connect By functionality. To begin with I tweaked an SQL Server function that Stephen Fisher had posted on the Knowledge Center which returns the full path, seperated by colon’s, to a given Livelink Node, a new version of the SQL is shown below :

 PL/SQL | 
 copy code |
?
 
01
/*
02
Procedure Name     : getFullPath
03
Purpose        : gets full path in Livelink to a specified Node
04
Parameters     :
05
            DataID integer required - Node to generate the path for
06
Version     : 1.0
07
Author        : Greg Griffiths (greg.griffiths@causeway.com)
08
Created Date    : 30 April 2009
09
Version History :
10
            1.0 Original Draft (30 April 2009)
11
Usage         :    
12
            SELECT getFullPath(160272) - returns the full path to node 160272
13
References    :
14
            Stephen Fisher for the base function (https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=3659697&objAction=view&show=2)
15
Installation    :
16
            1. Create this Function in the same DB under the same account as Livelink uses
17
            2. To change the seperator amend line 48 of the procedure and replace the : with a character of your choice
18
*/
19
CREATE FUNCTION getFullPath (@DataID INT)
20
RETURNS VARCHAR(8000) AS
21
BEGIN
22
    DECLARE @TmpParentID int
23
    DECLARE @FullPath VARCHAR(8000)
24
    DECLARE @Name VARCHAR(255)
25
    DECLARE @TmpName VARCHAR(255)
26
 
27
    IF @dataID <> 0
28
    BEGIN
29
        --build up a list of parentID's for the given dataID
30
        WHILE @DataID <> -1
31
        BEGIN
32
            -- get the parentid of the specified node
33
            SELECT @TmpParentID = ParentID FROM dtree WHERE dataID = @DataID
34
 
35
            --Make sure we got at least 1 row - if not, then exit
36
            IF @@rowcount = 0
37
                BREAK
38
 
39
            --this logic is for parentID's that are negative
40
            IF @dataID < -1 AND @TmpParentID = -1
41
                BEGIN
42
                    --we got -1 as the parentID but the dataID is negative so try again with the (+) of the dataID
43
                    SET @DataID = ABS(@DataID)
44
                END
45
            ELSE
46
                BEGIN
47
                    SET @DataID = @TmpParentID
48
                END
49
 
50
            -- put the current name into a variable
51
            SET @TmpName = @Name
52
 
53
            -- populate the name value
54
            SELECT @Name=Name FROM dtree WITH (NOLOCK) WHERE dataID = @DataID
55
 
56
            -- if we already have something in the @FullPath variable
57
            IF @FullPath IS NULL
58
                BEGIN 
59
                    SET @FullPath = @Name
60
                END
61
            ELSE
62
                BEGIN 
63
                    -- if the value of TMPNAME is different to NAME then add it to the path
64
                    IF @TMPName <> @Name
65
                        BEGIN 
66
                            SET @FullPath = @Name + ':' + @FullPath
67
                        END
68
                END
69
        END
70
    END
71
 
72
    -- return the value
73
    RETURN @FullPath
74
END          

Next I needed to create a SQL Server Stored Procedure which would generate a list of Livelink Nodes under a given Livelink Node and then provide this list to the above function. Again I was able to use an existing Stored Procedure from the Knowledge Center, this time written by Etienne De Villiers. This Stored Procedure creates a temporary table, #T and populates this with a list of Livelink Nodes that are under a specific Livelink node. Using this list we then use a cursor to move through the nodes and call the above function to get the complete path to each node. Once we have that information we do a simple select to provide that information as well as pulling in some additional information, the name and subtype of each node, from DTREE and ordering the results. This SQL Stored Procedure is shown below :

 PL/SQL | 
 copy code |
?
 
001
/*
002
Procedure Name     : listChildrenFullPath
003
Purpose        : list information - node id, full path to node, node name, node subtype - about nodes in a given tree in a Livelink system.
004
Parameters     :
005
            ParentID integer optional - Node to start with for the treewalk
006
Version     : 1.0
007
Author        : Greg Griffiths (greg.griffiths@causeway.com)
008
Created Date    : 30 April 2009
009
Version History :
010
            1.0 Original Draft (30 April 2009)
011
Usage         :    
012
            exec listChildrenFullPath - show the entire Enterprise Workspace
013
            exec listChildrenFullPath 1234 - show the tree from Object ID 1234
014
References    :
015
            Etienne De Villiers for the base function (https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=4610762&objAction=view)            
016
Installation    :
017
            1. Create this Function in the same DB under the same account as Livelink uses
018
            2. If the database name is not LIVELINK amend line 61 to your correct database name
019
*/
020
CREATE PROCEDURE listChildrenFullPath (@ParentID int = 2000)
021
AS
022
-- set some SQL Constants
023
    SET NOCOUNT ON -- set this so that the results appear in a LiveReport
024
 
025
-- declare some variables
026
    -- number of levels down
027
    DECLARE @LEVEL int
028
    SET @LEVEL = 0
029
 
030
    -- full path to the node
031
    DECLARE @FullPath VARCHAR(255)
032
 
033
    -- dataid of the node
034
    DECLARE @DataID int
035
 
036
-- get the data into the temporary table to start with
037
 
038
    -- get the immediate children of the start node and set their level to 0
039
    SELECT DataID AS DataID, @LEVEL AS ChildLevel, @FullPath AS FullPath 
040
    INTO #T 
041
    FROM livelink.dtree 
042
    WHERE ParentID = @ParentID
043
 
044
    -- process the tree
045
    WHILE @@RowCount > 0
046
    BEGIN
047
        --if we are going 100 levels down, something must have gone wrong
048
        IF (@LEVEL >= 100) 
049
            BREAK 
050
 
051
        -- otherwise
052
        ELSE
053
            -- increment the level
054
            SET @LEVEL = @LEVEL + 1
055
 
056
            -- add the children to the temporary table
057
            INSERT INTO #T
058
                SELECT DataID, @LEVEL, '' FROM
059
                dtree WHERE ParentID IN
060
                    (SELECT DataID FROM #T WHERE ChildLevel = @Level-1)
061
    END
062
 
063
-- get the full path to each node
064
 
065
    -- declare a cursor
066
    DECLARE PathCursor CURSOR FOR 
067
        SELECT dataid 
068
        FROM #T
069
 
070
    -- open the cursor
071
    OPEN PathCursor
072
 
073
    -- get the next record from the cursor
074
    FETCH NEXT FROM PathCursor INTO @DataID
075
 
076
    -- while we have records to process
077
    WHILE @@FETCH_STATUS = 0
078
        BEGIN 
079
            -- update #T and add the full path to the object in
080
            UPDATE #T 
081
            SET FullPath=[livelink].[GetFullPath](@DataID) 
082
            WHERE dataid=@DataID
083
 
084
            -- get the next record from the cursor
085
            FETCH NEXT FROM PathCursor INTO @DataID
086
        END 
087
 
088
    -- close the cursor
089
    CLOSE PathCursor 
090
 
091
    -- deallocate the cursor
092
    DEALLOCATE PathCursor
093
 
094
-- show the output
095
 
096
    -- simple select pulling in the additional columns of info we want
097
    SELECT #T.dataid,#T.fullpath,d.name,d.SUBTYPE
098
    FROM #T, dtree d 
099
    WHERE #T.dataid=d.dataid
100
    ORDER BY childlevel,fullpath,name
101
 
102
-- drop the temp table
103
 
104
    DROP TABLE #T 

Source: http://www.greggriffiths.org/livelink/development/database/listchildrenfullpath.html