|
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 TKWMDBRSSisters 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 " ST.LEFT_NR <= @LNR " +
65 "AND " +
66 " ST.RIGHT_NR >= @RNR " +
67 "AND " +
68 "( " +
69 " SD.INHERITABLE = 1 " +
70 "OR " +
71 " ST.SITE_NODE_ID = @ID " +
72 ") " +
73 "GROUP BY " +
74 " SD.DOCUMENT_SHORTNAME " +
75
76 "UPDATE " +
77 " #temp " +
78 "SET " +
79 " DOCUMENT_NAME = SD.DOCUMENT_NAME " +
80 "FROM " +
81 " SITE_DOCUMENT SD, #temp T " +
82 "WHERE " +
83 " T.DOCUMENT_SHORTNAME = SD.DOCUMENT_SHORTNAME " +
84
85 "COMMIT TRANSACTION " +
86
87 "SELECT * FROM #temp " +
88 "ORDER BY " +
89 " LEFT_NR " +
90
91 "DROP TABLE #temp ";
92
93
94 public void initQuery(Connection con) {
95 super.initQuery(
96 con,
97 ISPREPARED,
98 PARAMORDER,
99 PARAMTYPES,
100 SETRELEVANTS,
101 SQLSTRING );
102 }
103 }
104
105 |
Popular Tags |
Java API By Example, From Geeks To Geeks.