1 package com.teamkonzept.webman.mainint.db.queries.sybase; 2 3 import com.teamkonzept.db.*; 4 import java.sql.*; 5 6 16 public class TKDBContentTreeGetOpen extends TKPrepQuery { 17 18 public final static boolean isPrepared = 19 true; 20 21 public final static String [] paramOrder = 22 { "NODE_ID" }; 23 public final static Object [][] paramTypes = 24 null; 25 26 public final static boolean[] setRelevants = 27 { true }; 28 29 public final static String sqlString = 30 "DECLARE @N_ID int " + 31 "DECLARE @LNR int " + 32 "DECLARE @RNR int " + 33 34 "SELECT @N_ID = ? " + 35 36 "IF (@N_ID IS NULL) " + 37 "BEGIN " + 38 "SELECT " + 39 " @N_ID = CONTENT_NODE_ID " + 40 "FROM " + 41 " CONTENT_TREE " + 42 "WHERE " + 43 " CONTENT_NODE_PARENT = NULL " + 44 "END " + 45 46 "SELECT " + 47 " @LNR = LEFT_NR, " + 48 " @RNR = RIGHT_NR " + 49 "FROM " + 50 " CONTENT_TREE " + 51 "WHERE " + 52 " CONTENT_NODE_ID = @N_ID " + 53 54 "SELECT " + 55 " CONTENT_NODE_ID " + 56 "INTO " + 57 " #temp " + 58 "FROM " + 59 " CONTENT_TREE " + 60 "WHERE " + 61 " LEFT_NR <= @LNR " + 62 "AND " + 63 " RIGHT_NR >= @RNR " + 64 65 "CREATE TABLE " + 66 " #temp2 " + 67 " (CONTENT_NODE_ID int null, NODE_ID int null) " + 68 69 "INSERT INTO " + 70 " #temp2 " + 71 "SELECT " + 72 " CONTENT_NODE_ID, CONTENT_NODE_ID " + 73 "FROM " + 74 " CONTENT_TREE " + 75 "WHERE " + 76 " CONTENT_NODE_PARENT IN " + 77 "( SELECT CONTENT_NODE_ID FROM #temp ) " + 78 "OR " + 79 " CONTENT_NODE_ID IN " + 80 "( SELECT CONTENT_NODE_ID FROM #temp ) " + 81 82 "UPDATE " + 83 " #temp2 " + 84 "SET " + 85 " NODE_ID = CT.CONTENT_NODE_PARENT " + 86 "FROM " + 87 " #temp T, CONTENT_TREE CT " + 88 "WHERE " + 89 " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " + 90 "AND " + 91 " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID " + 92 93 "UPDATE " + 94 " #temp2 " + 95 "SET " + 96 " NODE_ID = NULL " + 97 "FROM " + 98 " CONTENT_TREE CT " + 99 "WHERE " + 100 " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " + 101 "AND " + 102 " CT.CONTENT_NODE_TYPE > 2 " + 103 104 "SELECT " + 105 " CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, " + 106 " CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_SHORTNAME, " + 107 " CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR, CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, " + 108 " T.NODE_ID " + 109 "FROM " + 110 " CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T " + 111 "WHERE " + 112 " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID " + 113 "ORDER BY " + 114 " CT.LEFT_NR " + 115 116 "DROP TABLE #temp " + 117 "DROP TABLE #temp2 "; 118 119 public void initQuery(Connection con) { 120 super.initQuery( 121 con, 122 isPrepared, 123 paramOrder, 124 paramTypes, 125 setRelevants, 126 sqlString ); 127 } 128 } 129 | Popular Tags |