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