KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > sybase > TKDBContentTreeIsDependent


1 package com.teamkonzept.webman.mainint.db.queries.sybase;
2
3 import com.teamkonzept.db.*;
4 import java.sql.*;
5
6 /**
7  * TKDBContentTreeIsDependent
8  * Schaut ob abhaengigkeiten CONTENT_TREE zu DOCUMENT_CONTENT oder
9  * SITE_TREE
10  * Input: CONTENT_NODE_ID
11  * Output: ResultSet der Abhängigkeiten
12  * @author
13  * @version
14  */

15 public class TKDBContentTreeIsDependent extends TKPrepQuery {
16
17     public static final boolean ISPREPARED = true;
18     public static final String JavaDoc[] PARAMORDER = {"CONTENT_NODE_ID"};
19     public static final Object JavaDoc[][] PARAMTYPES = null;
20     public static final boolean[] SETRELEVANTS = {true};
21
22     public static final String JavaDoc SQLSTRING =
23         "DECLARE @L_NR INT " +
24         "DECLARE @R_NR INT " +
25         "DECLARE @RESULT INT " +
26
27         "CREATE TABLE #temp " +
28         "( " +
29         " CONTENT_NODE_ID int " +
30         ") " +
31
32         "CREATE TABLE #temp2 ( " +
33         " SITE_NODE_ID int null, " +
34         " CONTENT_NODE_ID int null, " +
35         " CONTENT_NODE_NAME VARCHAR(254) null, " +
36         " CONTENT_NODE_TYPE int null, " +
37         " DC_SITE_NODE_ID int null, " +
38         " DC_SITE_NODE_DOC_IDX int null)" +
39
40         "CREATE TABLE #temp3 ( " +
41         " ST_SITE_NODE_ID int null, " +
42         " CT_CONTENT_NODE_ID int null, " +
43         " CT_CONTENT_NODE_NAME VARCHAR(254) null, " +
44         " CT_CONTENT_NODE_TYPE int null, " +
45         " SD_SITE_NODE_ID int null, " +
46         " SD_SITE_NODE_DOC_IDX int null," +
47         " SD_DOCUMENT_NAME VARCHAR(254) null," +
48         " SD_DOCUMENT_SHORTNAME VARCHAR(80) null)" +
49
50         "BEGIN TRANSACTION " +
51
52         "SELECT " +
53         " @L_NR = LEFT_NR, @R_NR = RIGHT_NR " +
54         "FROM " +
55         " CONTENT_TREE " +
56         "WHERE " +
57         " CONTENT_NODE_ID = ? " +
58
59         "INSERT INTO " +
60         " #temp " +
61         " (CONTENT_NODE_ID) " +
62         "SELECT " +
63         " CONTENT_NODE_ID " +
64         "FROM " +
65         " CONTENT_TREE " +
66         "WHERE " +
67         " LEFT_NR >= @L_NR " +
68         "AND " +
69         " RIGHT_NR <= @R_NR " +
70
71         "INSERT INTO #temp2 (SITE_NODE_ID, CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_TYPE, DC_SITE_NODE_ID, DC_SITE_NODE_DOC_IDX) " +
72         "SELECT ST.SITE_NODE_ID, CT.CONTENT_NODE_ID, CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_TYPE, DC.SITE_NODE_ID, DC.SITE_NODE_DOC_IDX " +
73         "FROM SITE_TREE ST, DOCUMENT_CONTENT DC, CONTENT_TREE CT, #temp T " +
74         "WHERE T.CONTENT_NODE_ID *= ST.PG_CONTENT_NODE " +
75         "AND T.CONTENT_NODE_ID *= DC.CONTENT_NODE_ID " +
76         "AND T.CONTENT_NODE_ID *= CT.CONTENT_NODE_ID " +
77
78         "INSERT INTO #temp3 " +
79         "SELECT T2.SITE_NODE_ID, T2.CONTENT_NODE_ID," +
80         " T2.CONTENT_NODE_NAME, T2.CONTENT_NODE_TYPE," +
81         " SD.SITE_NODE_ID, SD.SITE_NODE_DOC_IDX," +
82         " SD.DOCUMENT_NAME, SD.DOCUMENT_SHORTNAME " +
83         "FROM #temp2 T2, SITE_DOCUMENT SD " +
84         "WHERE T2.DC_SITE_NODE_ID *= SD.SITE_NODE_ID" +
85         " AND T2.DC_SITE_NODE_DOC_IDX *= SD.SITE_NODE_DOC_IDX " +
86         "ORDER BY T2.CONTENT_NODE_NAME " +
87
88         "SELECT * FROM #temp3 WHERE NOT (SD_SITE_NODE_ID IS NULL AND ST_SITE_NODE_ID IS NULL) " +
89
90         "COMMIT TRANSACTION " +
91
92         "DROP TABLE #temp " +
93         "DROP TABLE #temp2 " +
94         "DROP TABLE #temp3";
95
96     public void initQuery(Connection con) {
97         super.initQuery(
98             con,
99             ISPREPARED,
100             PARAMORDER,
101             PARAMTYPES,
102             SETRELEVANTS,
103             SQLSTRING );
104     }
105 }
106
Popular Tags