|
1
5 package com.teamkonzept.webman.refsel.db.queries.sybase;
6
7 import java.sql.*;
8
9 import com.teamkonzept.db.*;
10
11
16 public class TKWMDBRSDaughters extends TKPrepQuery {
17
18 public final static boolean ISPREPARED =
19 true;
20
21 public final static String  [] PARAMORDER =
22 {"SITE_NODE_ID"};
23
24 public final static Object [][] PARAMTYPES =
25 null;
26
27 public final static boolean[] SETRELEVANTS =
28 { true };
29
30 public final static String SQLSTRING =
31 "DECLARE @LNR int " +
32 "DECLARE @RNR int " +
33 "DECLARE @ID int " +
34
35 "SELECT @ID = ? " +
36
37 "CREATE TABLE #temp " +
38 "( " +
39 "DOCUMENT_SHORTNAME varchar(80) not null, LEFT_NR int not null, " +
40 "DOCUMENT_NAME varchar(254) null " +
41 ") " +
42
43 "BEGIN TRANSACTION " +
44
45 "SELECT " +
46 " @LNR = LEFT_NR, " +
47 " @RNR = RIGHT_NR " +
48 "FROM " +
49 " SITE_TREE " +
50 "WHERE " +
51 " SITE_NODE_ID = @ID " +
52
53 "INSERT INTO " +
54 " #temp " +
55 " (DOCUMENT_SHORTNAME, LEFT_NR) " +
56 "SELECT " +
57 " SD.DOCUMENT_SHORTNAME AS DOCUMENT_SHORTNAME, " +
58 " MAX(LEFT_NR) AS LEFT_NR " +
59 "FROM " +
60 " SITE_TREE ST, SITE_DOCUMENT SD " +
61 "WHERE " +
62 " SD.SITE_NODE_ID = ST.SITE_NODE_ID " +
63 "AND " +
64 "( " +
65 "( " +
66 " ST.LEFT_NR <= @LNR " +
67 "AND " +
68 " ST.RIGHT_NR >= @RNR " +
69 "AND " +
70 " SD.INHERITABLE = 1 " +
71 ") " +
72 "OR " +
73 "( " +
74 " ST.SITE_NODE_PARENT = @ID " +
75 ") " +
76 ") " +
77 "GROUP BY " +
78 " SD.DOCUMENT_SHORTNAME " +
79
80
81 "UPDATE " +
82 " #temp " +
83 "SET " +
84 " DOCUMENT_NAME = SD.DOCUMENT_NAME " +
85 "FROM " +
86 " SITE_DOCUMENT SD, #temp T " +
87 "WHERE " +
88 " T.DOCUMENT_SHORTNAME = SD.DOCUMENT_SHORTNAME " +
89
90 "COMMIT TRANSACTION " +
91
92 "SELECT * FROM #temp " +
93 "ORDER BY " +
94 " LEFT_NR " +
95
96 "DROP TABLE #temp ";
97
98
99 public void initQuery(Connection con) {
100 super.initQuery(
101 con,
102 ISPREPARED,
103 PARAMORDER,
104 PARAMTYPES,
105 SETRELEVANTS,
106 SQLSTRING );
107 }
108 }
109
110 |
Popular Tags |
Java API By Example, From Geeks To Geeks.