DatabaseProcApplicationCreatedLinks
sybsystemprocssp_inst15015msg_addlimsg  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** SP_INST15015MSG_ADDLIMSG
5     **
6     ** Description:
7     **	sproc to install non-English msgs.
8     **
9     ** Parameter:
10    **      @error
11    **	@message
12    **	@langid
13    **	@debug
14    **
15    */
16    create or replace procedure sp_inst15015msg_addlimsg
17    (
18        @error int, /* Error being installed. */
19        @message varchar(255), /* Text of new message.   */
20        @langid int, /* Message's language ID. */
21        @debug bit = 0 /* Turned on for debug.   */
22    )
23    as
24    
25        /*	Variable declarations.		*/
26    
27        declare @oldseverity int, /* Old message severity.	*/
28            @olddlevel int, /* Old message dlevel.		*/
29            @olddesc varchar(255), /* Old message text.		*/
30            @engseverity int, /* English message severity.	*/
31            @engdlevel int, /* English message dlevel.	*/
32            @engdesc varchar(255), /* English message text.	*/
33            @tstr varchar(4), /* Temporary string holder.	*/
34            @strlength int, /* Temporary string length var.	*/
35            @remaining int, /* Length of string remaining.	*/
36            @cindex int, /* Temporary charindex holder.	*/
37            @pindex int, /* Temporary parameter count.	*/
38            @tindex int, /* Temporary copy of cindex.	*/
39            @engcount int, /* English message param count.	*/
40            @newcount int, /* New message param count.	*/
41            @debugmsg varchar(255), /* Debug message string.	*/
42            @maxparams int /* Maximum number of params.	*/
43    
44        /*	Initialize variables that are used as #DEFINE type constants.	*/
45    
46        select @maxparams = 12 /* Maximum number of params in a msg.	*/
47        /* this value comes from ex_print.c	*/
48        if @debug = 1
49        begin
50            select @debugmsg = "Starting message "
51                + convert(varchar, @error)
52                + "."
53            print @debugmsg
54        end /* IF DEBUG	*/
55    
56        /*	Get the English message from sysmessages.	*/
57    
58        select @engseverity = severity, @engdlevel = dlevel,
59            @engdesc = description
60        from master.dbo.sysmessages
61        where error = @error
62            and langid is NULL
63    
64        if @debug = 1
65        begin
66            select @debugmsg = "English message:  severity = "
67                + convert(varchar, @engseverity)
68                + ", dlevel = "
69                + convert(varchar, @engdlevel)
70                + ", description follows -- "
71            print @debugmsg
72            print @engdesc
73        end /* IF DEBUG	*/
74    
75        /*
76        **	If there is no English message, and the stored procedure is not
77        **	being asked to delete the equivalent translated message, then
78        **	there is a problem; there should not be a translation when there
79        **	is no English equivalent!
80        */
81    
82        if @engseverity is NULL and @message != "delete"
83            return 7 /* 7 = no English equivalent.	*/
84    
85        /*	Get the old translated version of the message.	*/
86    
87        select @oldseverity = severity, @olddlevel = dlevel,
88            @olddesc = description
89        from master.dbo.sysmessages
90        where error = @error
91            and langid = @langid
92    
93        if @debug = 1
94        begin
95            select @debugmsg = "Old message:  severity = "
96                + convert(varchar, @oldseverity)
97                + ", dlevel = "
98                + convert(varchar, @olddlevel)
99                + ", description follows -- "
100           print @debugmsg
101           print @olddesc
102       end /* IF DEBUG	*/
103   
104       /*
105       **	Check to see if we are being asked to delete a message; if so,
106       **	we can skip all the rest of the processing and just perform the
107       **	delete.  If there is an English version of the message, then
108       **	indicate that we are (in essence) replacing the translated version
109       **	with an English one.  Also check to see if it is actually
110       **	necessary to perform the delete (i.e., if there is a message
111       **	to delete).
112       */
113       /* =========================BEGIN add_LImsg3=============================*/
114   
115       if @message = "delete"
116       begin
117   
118           if @oldseverity is NULL /* No old version of msg.	*/
119               return 3 /* 3 = already installed.	*/
120   
121           else begin /* Old version exists.		*/
122               delete from master.dbo.sysmessages
123               where error = @error
124                   and langid = @langid
125               if @@error != 0
126                   return 10 /* 10 = delete failed.		*/
127               if @engseverity is NULL
128                   return 4 /* 4 = message deleted.		*/
129               else return 5 /* 5 = message replaced by Eng.	*/
130           end /* ELSE	*/
131   
132       end /* IF	*/
133   
134       /*
135       **	Having reached this point, we know that we are going to insert
136       **	or update a message.  Therefore, we check to be sure that the
137       **	new message has the same number of parameters as the English
138       **	version, and that the parameters are numbered appropriately.
139       **	We don't check the order of the parameters, because word order
140       **	varies from one language to another.
141       */
142   
143       if @debug = 1
144           print "Searching English message for parameters."
145   
146       /*	Initialize variables, get parameter 1.		*/
147   
148       select @tstr = "%"
149       select @cindex = charindex(@tstr, @engdesc),
150           @pindex = 0, @strlength = datalength(@engdesc)
151       /* @pindex = 0, @strlength = datalength( @engdesc ) */
152   
153       if @cindex = 0
154           select @remaining = 0
155       else select @remaining = @strlength - @cindex
156   
157       /* =========================BEGIN add_LImsg4==============================*/
158   
159       if @debug = 1
160       begin
161           select @debugmsg = "Values:  CINDEX = "
162               + convert(varchar, @cindex)
163               + ", PINDEX = "
164               + convert(varchar, @pindex)
165               + ", STRLENGTH = "
166               + convert(varchar, @strlength)
167               + ", REMAINING = "
168               + convert(varchar, @remaining)
169               + "."
170           print @debugmsg
171       end /* IF DEBUG	*/
172   
173       /*
174       **	Keep getting parameters until there are no more.  We assume
175       **	that the English version has the correct number of parameters.
176       */
177   
178       while @cindex != 0
179       begin
180   
181           if @cindex = @strlength /* Single percent sign is last char.	*/
182               return 14 /* 14 = Corrupted English message.	*/
183   
184           if "%" = /* Look for escaped % characters (%%).	*/
185                   (
186                   select substring(@engdesc, @cindex + 1, 1)
187                   )
188           begin
189   
190               if @debug = 1
191               begin
192                   select @debugmsg = "Escaped percent sign string "
193                       + "'%%' found at character "
194                       + convert(varchar, @cindex)
195                       + "."
196                   print @debugmsg
197               end /* IF DEBUG	*/
198   
199   
200               select @cindex = @cindex + 2,
201                   @remaining = (@strlength - @cindex)
202   
203               if @cindex > @strlength
204                   break /* '%%' at end of string, exit WHILE loop */
205   
206               select @tindex = charindex(@tstr,
207                       substring(@engdesc, @cindex, @remaining + 1))
208   
209               if @tindex = 0
210                   select @cindex = 0 /* No more parameters.	*/
211               else
212                   /* Potential parameter found. */
213                   select @cindex = @cindex + @tindex - 1,
214                       @remaining = @strlength - @cindex
215   
216               if @debug = 1
217               begin
218                   select @debugmsg = "Values:  CINDEX = "
219                       + convert(varchar, @cindex)
220                       + ", PINDEX = "
221                       + convert(varchar, @pindex)
222                       + ", STRLENGTH = "
223                       + convert(varchar, @strlength)
224                       + ", REMAINING = "
225                       + convert(varchar, @remaining)
226                       + "."
227                   print @debugmsg
228               end /* IF DEBUG	*/
229   
230               continue /* Return to beginning of WHILE loop.	*/
231   
232           end /* IF	*/
233   
234   
235           /* =========================BEGIN add_LImsg5=============================*/
236   
237           if @debug = 1
238           begin
239               select @debugmsg = "Parameter found at character "
240                   + convert(varchar, @cindex)
241                   + "."
242               print @debugmsg
243           end /* IF DEBUG	*/
244   
245           /*	Found a valid parameter.	*/
246   
247           select @pindex = @pindex + 1 /* Increment parameter count. */
248   
249           if @debug = 1
250           begin
251               select @debugmsg = "Values:  CINDEX = "
252                   + convert(varchar, @cindex)
253                   + ", PINDEX = "
254                   + convert(varchar, @pindex)
255                   + ", STRLENGTH = "
256                   + convert(varchar, @strlength)
257                   + ", STRLENGTH remaining = "
258                   + convert(varchar, @remaining)
259                   + "."
260               print @debugmsg
261           end /* IF DEBUG	*/
262   
263           /*	Continue searching from current string location on.	*/
264   
265           select @tindex = @cindex + charindex(@tstr,
266                   substring(@engdesc, @cindex + 1, @remaining))
267   
268           if @cindex = @tindex
269               select @cindex = 0 /* No more parameters.	*/
270           else select @cindex = @tindex /* Potential parameter found. */
271   
272           select @remaining = @strlength - @cindex
273   
274       end /* WHILE	*/
275   
276       /*	Store the number of parameters.		*/
277   
278       /* =========================BEGIN add_LImsg6==============================*/
279   
280       select @engcount = @pindex
281   
282       if @debug = 1
283       begin
284           select @debugmsg = "Number of parameters in English message was "
285               + convert(varchar, @engcount)
286               + "."
287           print @debugmsg
288       end /* IF DEBUG	*/
289   
290       /*
291       **	Now perform the same count on the new message to ensure that
292       **	it has the same number of parameters as the English equivalent.
293       **	Also, check that the new message does not skip any parameter
294       **	numbers.
295       */
296   
297       if @debug = 1
298           print "Searching translated message for parameters."
299   
300       /*	Ensure there is no zero-eth parameter.	*/
301   
302       if 0 !=
303               (
304               select charindex("%0!", @message)
305               )
306           return 12 /* 12 = format error; parameter 0 found. */
307   
308       select @tstr = "%1!"
309   
310       select @cindex = charindex(@tstr, @message),
311           @pindex = 0, @tindex = 0, @strlength = datalength(@message)
312       /* @pindex = 0, @tindex = 0, @strlength = datalength( @message ) */
313   
314       /*=========================BEGIN add_LImsg7==================================*/
315   
316       if @debug = 1
317       begin
318           select @debugmsg = "Values:  CINDEX = "
319               + convert(varchar, @cindex)
320               + ", PINDEX = "
321               + convert(varchar, @pindex)
322               + ", STRLENGTH = "
323               + convert(varchar, @strlength)
324               + "."
325           print @debugmsg
326       end /* IF DEBUG	*/
327   
328       while @cindex != 0 /* Got one!	*/
329       begin
330   
331           if @debug = 1
332           begin
333               select @debugmsg = "Parameter '"
334                   + @tstr
335                   + "' found at character "
336                   + convert(varchar, @cindex)
337                   + "."
338               print @debugmsg
339           end /* IF DEBUG	*/
340   
341           /*	Make sure that the parameter number appears only once.	*/
342   
343           if 0 !=
344                   (
345                   select charindex(@tstr,
346                       substring(@message, @cindex + 1,
347                           @strlength - @cindex))
348                   )
349               return 13 /* 13 = format error; duplicate parameter. */
350   
351           select @pindex = @pindex + 1 /* Increment number found.	*/
352           select @tstr = "%"
353               + convert(varchar, @pindex + 1) /* Next one to look for. */
354               + "!"
355   
356           select @cindex = charindex(@tstr, @message) /* Search!	*/
357   
358           /*=========================BEGIN add_LImsg8==================================*/
359   
360           if @debug = 1
361           begin
362               select @debugmsg = "Values:  CINDEX = "
363                   + convert(varchar, @cindex)
364                   + ", PINDEX = "
365                   + convert(varchar, @pindex)
366                   + ", STRLENGTH = "
367                   + convert(varchar, @strlength)
368                   + "."
369               print @debugmsg
370           end /* IF DEBUG	*/
371   
372       end /* WHILE	*/
373   
374       select @newcount = @pindex
375   
376       if @debug = 1
377       begin
378           select @debugmsg = "Highest parameter number was "
379               + convert(varchar, @newcount)
380               + "."
381           print @debugmsg
382       end /* IF DEBUG	*/
383   
384       select @pindex = @pindex + 1 /* Skip over n+1 parameter.	*/
385   
386       /*	Ensure that no parameter number was skipped.	*/
387   
388       if @debug = 1
389           print "Searching new message for skipped parameter numbers."
390   
391       while (@pindex < @maxparams)
392       begin
393   
394           select @pindex = @pindex + 1 /* Next number to look for.	*/
395           select @tstr = "%"
396               + convert(varchar, @pindex)
397               + "!"
398   
399           /*=========================BEGIN add_LImsg9==================================*/
400   
401           select @cindex = charindex(@tstr, @message) /* Search!	*/
402   
403           if @debug = 1
404           begin
405               select @debugmsg = "Values:  CINDEX = "
406                   + convert(varchar, @cindex)
407                   + ", PINDEX = "
408                   + convert(varchar, @pindex)
409                   + ", STRLENGTH = "
410                   + convert(varchar, @strlength)
411                   + "."
412               print @debugmsg
413           end /* IF DEBUG	*/
414   
415           if @cindex != 0 /* Found a match.	*/
416               return 11 /* 11 = message format error.	*/
417   
418       end /* WHILE	*/
419   
420       /*	Now see if the parameter counts match.		*/
421   
422       if @engcount != @newcount
423           return 6 /* 6 = format count mismatch.	*/
424   
425       /*
426       **	Okay to proceed.  Now check to see if the new message is a
427       **	duplicate of the message it is supposed to replace.  We need
428       **	to check severity and dlevel as well as the message text,
429       **	because the upgrade may be to one of those values rather than
430       **	the string itself.
431       */
432   
433       if @oldseverity is not NULL
434           and @olddesc = @message
435           and @oldseverity = @engseverity
436           and @olddlevel = @engdlevel
437           return 3 /* 3 = message already installed.	*/
438   
439       /*
440       **	If we are here, it is not a complete duplicate, so it must
441       **	either be a new message or a replacement message.
442       */
443   
444   
445       /*=========================BEGIN add_LImsg10=================================*/
446   
447       if @oldseverity is not NULL /* Replacement message.	*/
448       begin
449   
450           update master.dbo.sysmessages
451           set severity = @engseverity,
452               dlevel = @engdlevel,
453               description = @message
454           where error = @error
455               and langid = @langid
456   
457           if @@error != 0
458               return 9 /* 9 = update failed.	*/
459   
460           return 2 /* 2 = message updated.	*/
461   
462       end
463   
464       /*	If we are here, then it is a new message.	*/
465   
466       insert into master.dbo.sysmessages
467       (
468           error, severity, dlevel, langid, description
469       )
470       values (
471           @error, @engseverity, @engdlevel, @langid, @message
472       )
473   
474       if @@error != 0
475           return 8 /* 8 = insert failed.	*/
476   
477       return 1 /* 1 = message inserted.	*/
478   

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @engdesc: varchar(255) = varchar(1024) 59
 MTYP 4 Assignment type mismatch @olddesc: varchar(255) = varchar(1024) 88
 MTYP 4 Assignment type mismatch severity: smallint = int 451
 MTYP 4 Assignment type mismatch dlevel: smallint = int 452
 MTYP 4 Assignment type mismatch dlevel: smallint = int 471
 MTYP 4 Assignment type mismatch langid: smallint = int 471
 MTYP 4 Assignment type mismatch severity: smallint = int 471
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 91
 QTYP 4 Comparison type mismatch smallint = int 91
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 QTYP 4 Comparison type mismatch smallint = int 124
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 455
 QTYP 4 Comparison type mismatch smallint = int 455
 MGTP 3 Grant to public master..sysmessages  
 MUCO 3 Useless Code Useless Brackets in create proc 17
 MUCO 3 Useless Code Useless Brackets 391
 MUOT 3 Updates outside transaction 466
 MUPK 3 Update column which is part of a PK or unique index dlevel 451
 QAFM 3 Var Assignment from potentially many rows 58
 QAFM 3 Var Assignment from potentially many rows 87
 QIWC 3 Insert with not all columns specified missing 1 columns out of 6 468
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
61
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
90
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
123
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
454
 VNRD 3 Variable is not read @tindex 311
 MTR1 2 Metrics: Comments Ratio Comments: 40% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 46dec - 10exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 177 16

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

CALLERS
called by proc sybsystemprocs..sp_inst15015msg_oth_mesg  
   called by proc sybsystemprocs..sp_inst15015msg_all_oth_mesgs  
      called by proc sybsystemprocs..sp_inst15015msg  
         called by proc sybsystemprocs..sp_downgrade