DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addengine  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_addengine"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18248, "Validation of Engine Group modification failed. Check server
9     **	   errorlog for any additional information."
10    ** 18249, "Name '%1!' is not valid."
11    ** 18250, "Failed to add engine '%1!' to engine group '%2!'. Check server
12    **	   errorlog for any additional information. List of engines in
13    **	   engine group unchanged.
14    ** 18251, "Cannot modify pre-defined engine groups."
15    ** 19703, "Instance id '%1!' is not valid under instance system view."
16    */
17    
18    create procedure sp_addengine
19        @engine_number int, /* engine number of engine */
20        @engine_group varchar(255), /* group that engine belongs to */
21        @instanceid smallint = NULL /* instance id */
22    as
23    
24        declare @attrib_id int,
25            @action int,
26            @engine_name varchar(5),
27            @object_type varchar(2),
28            @new_list varchar(768),
29            @engine_list varchar(768),
30            @upcase_str varchar(255)
31    
32        IF @@kernelmode != 'process'
33        BEGIN
34            /*
35            ** 17117 - "The '%1!' command is supported only in process kernel mode"
36            */
37            raiserror 17117, "sp_addengine"
38            return 1
39        END
40    
41        /*
42        **  IF we're in a transaction, disallow this since it might make recovery
43        **  impossible.
44        **  This check must be done before any select statement. Otherwise,
45        **  the select statement might start a transaction if the session is
46        **  running in chained mode.
47        */
48        IF @@trancount > 0
49        BEGIN
50            /*
51            ** 17260, "Can't run %1! from within a transaction."
52            */
53            raiserror 17260, "sp_addengine"
54            return (1)
55        END
56        ELSE
57        BEGIN
58            /* Use TSQL mode of unchained transactions */
59            set chained off
60        END
61    
62        select @action = 1 /* add or modify entry */
63        select @attrib_id = 1 /* attribute is ENGINE_GROUP */
64        select @object_type = 'EG'
65        select @engine_list = NULL /* var to hold list of engines in group */
66    
67        /* Dont do "Dirty Reads" */
68        set transaction isolation level 1
69    
70        /*
71        ** Check to see that the input params are correct and then hook up with
72        ** Sysattributes table to enter data.
73        */
74    
75        /* first convert any system defined engine group name to upper case */
76        select @upcase_str = upper(@engine_group)
77        IF ((@upcase_str = "ANYENGINE") OR (@upcase_str = "LASTONLINE"))
78            select @engine_group = @upcase_str
79    
80        /* Cannot add to pre-defined groups */
81        if (@engine_group = "ANYENGINE" OR @engine_group = "LASTONLINE")
82        BEGIN
83            /*
84            ** 18251, "Cannot modify pre-defined engine groups."        
85            */
86            raiserror 18251
87            return (1)
88        END
89    
90        IF ((@engine_group != "ANYENGINE") AND (@engine_group != "LASTONLINE"))
91        BEGIN
92            IF valid_name(@engine_group, 30) = 0
93            BEGIN
94                /*
95                ** 18249, "Name '%1!' is not valid."
96                */
97                raiserror 18249, @engine_group
98                return (1)
99            END
100       END
101   
102       /* For SDC, use @@instanceid if NULL is specified */
103       if (@@clustermode = "shared disk cluster")
104       BEGIN
105           if ((@@system_view = "instance") AND
106                   (@instanceid != NULL) AND
107                   (@instanceid != @@instanceid))
108           BEGIN
109               /*
110               ** 19703, "Instance id '%1!' is not valid under instance 
111               **	   system view."
112               */
113               raiserror 19703, @instanceid
114               return (1)
115           END
116   
117           if (@instanceid = NULL)
118               select @instanceid = @@instanceid
119       END
120       else
121           select @instanceid = NULL
122   
123       /*
124       ** The validation routine checks that the supplied engine is not
125       ** already in the group and that it is a valid engine number
126       */
127       IF attrib_valid(6, @attrib_id, @object_type, NULL, NULL, NULL,
128               @instanceid, @engine_group, @engine_number, NULL, NULL,
129               NULL, "", @action) = 0
130       BEGIN
131           /*
132           ** 18248, "Validation of Engine Group modification failed. Check
133           **	   server errorlog for any additional information."
134           */
135           raiserror 18248
136           return (1)
137       END
138   
139       IF ((@engine_group != "ANYENGINE") AND (@engine_group != "LASTONLINE"))
140       BEGIN
141           /*
142           ** The list of engines belonging to an engine
143           ** group are kept in the format -
144           ** engine # : engine # : ....
145           ** So add the new engine in the correct format
146           */
147           select @engine_name = str(@engine_number, 5)
148           IF exists (select * from master..sysattributes where
149                       class = 6 AND
150                       attribute = 1 AND
151                       object_type = 'EG' AND
152                       object_cinfo = @engine_group AND
153                       object_info3 = @instanceid)
154           BEGIN
155               select @engine_list = (select char_value
156                       from master..sysattributes where
157                           class = 6 AND
158                           attribute = 1 AND
159                           object_type = 'EG' AND
160                           object_cinfo = @engine_group AND
161                           object_info3 = @instanceid)
162   
163               select @new_list = (@engine_list +
164                   @engine_name + ":")
165   
166               update master..sysattributes
167               set char_value = @new_list
168               where class = 6 AND
169                   attribute = 1 AND
170                   object_type = 'EG' AND
171                   object_cinfo = @engine_group AND
172                   object_info3 = @instanceid
173           END
174           ELSE
175           BEGIN
176               select @new_list = (@engine_name + ":")
177               insert master..sysattributes
178               (class, attribute, object_type, object_info3, object_cinfo, char_value)
179               values (6, @attrib_id, @object_type, @instanceid, @engine_group, @new_list)
180           END
181       END
182   
183       if (attrib_notify(6, @attrib_id, @object_type, NULL, @engine_number, NULL,
184               @instanceid, @engine_group, NULL, NULL, NULL, NULL, "",
185               @action)) = 0
186       BEGIN
187           /*
188           ** 18250, "Failed to add engine '%1!' to engine group '%2!'. Check
189           **	   server errorlog for any additional information. List of
190           **	   engines in engine group unchanged."
191           */
192           IF (@engine_list is not NULL)
193               update master..sysattributes
194               set char_value = @engine_list
195               where class = 6 AND
196                   attribute = 1 AND
197                   object_type = 'EG' AND
198                   object_cinfo = @engine_group AND
199                   object_info3 = @instanceid
200           ELSE
201               delete master..sysattributes where
202                   class = 6 AND
203                   attribute = 1 AND
204                   object_type = 'EG' AND
205                   object_cinfo = @engine_group AND
206                   object_info3 = @instanceid
207   
208           raiserror 18250, @engine_number, @engine_group
209           return (1)
210       END
211   
212       return (0)
213
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 129
 MEST 4 Empty String will be replaced by Single Space 184
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 118
 MTYP 4 Assignment type mismatch attribute: smallint = int 179
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 149
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 161
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 172
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 195
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 196
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 199
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 202
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 206
 MGTP 3 Grant to public master..sysattributes  
 MNAC 3 Not using ANSI 'is null' 106
 MNAC 3 Not using ANSI 'is null' 117
 MNER 3 No Error Check should check @@error after update 166
 MNER 3 No Error Check should check @@error after insert 177
 MNER 3 No Error Check should check @@error after update 193
 MNER 3 No Error Check should check @@error after delete 201
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 212
 MUOT 3 Updates outside transaction 201
 QISO 3 Set isolation level 68
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 178
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_info3, object_cinfo, attribute, class}		 149
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_info3, object_cinfo, attribute, class}		 157
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_info3, object_cinfo, attribute, class}		 168
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_info3, object_cinfo, attribute, class}		 195
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_info3, object_cinfo, attribute, class}		 202
 MSUB 2 Subquery Marker 148
 MSUB 2 Subquery Marker 155
 MTR1 2 Metrics: Comments Ratio Comments: 39% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 28dec - 8exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 100 18

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)