DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addmessage  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /* generic/sproc/addmessage	14.2	4/25/91 */
4     
5     /* 
6     ** Messages from sysmessages
7     **
8     ** 17210, "Message number must be at least 20000."
9     ** 17211, "Cannot add message until Sysusermessages system table is created properly by Upgrade."
10    ** 17201, "'%1!' is not an official language name from Syslanguages."
11    ** 17212, "A message with number %1! in the specified language already exists."
12    ** 17213, "Drop the old message first if you still wish to add this one."
13    ** 17214, "The message has been inserted."
14    ** 17215, "The message has not been inserted."
15    ** 17216, "Only the System Administrator (SA) or the Database Owner (dbo) may add messages which set the WITH_LOG option to `'true'`."
16    ** 17217, "The only valid @with_log values are TRUE or FALSE."
17    ** 17240, "'%1!' is not a valid name."
18    ** 17756, "The execution of the stored procedure '%1!' in database
19    **         '%2!' was aborted because there was an error in writing the
20    **         replication log record."
21    */
22    create or replace procedure sp_addmessage
23        @message_num int,
24        @message_text varchar(1024),
25        @language varchar(255) = NULL,
26        @with_log varchar(5) = 'FALSE',
27        @replace varchar(7) = NULL
28    as
29    
30        declare @msg varchar(1024)
31        declare @returncode int
32        declare @dlevel smallint
33        declare @suid int
34        declare @eventnum int /* event number for add message auditing */
35        declare @mod_ok int /* successful add message auditing  */
36        declare @mod_fail int /* failure add message auditing  */
37        declare @dummy int
38        declare @gp_enabled int
39        declare @nullarg varchar(1) /* Passing NULL as dbname */
40    
41    
42    
43    
44    
45    
46        select @eventnum = 15 /* event number for add message */
47        select @mod_ok = 1
48        select @mod_fail = 2
49        declare @dbname varchar(255)
50    
51    
52        /* Use the default language by default */
53        declare @lang_id smallint
54        select @lang_id = @@langid
55    
56    
57        if @@trancount = 0
58        begin
59            set chained off
60        end
61        set transaction isolation level 1
62    
63        if @message_num < 20000
64        BEGIN
65            /* 17210, "Message number must be at least 20000." */
66            raiserror 17210
67            return (1)
68        END
69    
70        /*
71        ** Check to be sure Sysusermessages exists
72        */
73        if not exists (select * from sysobjects
74                where name = "sysusermessages"
75                    and type = 'S')
76        BEGIN
77            /* 
78            ** 17211, "Cannot add message until Sysusermessages system table is
79            **		created properly by Upgrade."
80            */
81            raiserror 17211
82            return (1)
83        END
84    
85        /*
86        ** Check that language is valid.
87        */
88        if @language is not NULL
89        BEGIN
90            execute @returncode = sp_validlang @language
91            if @returncode != 0
92            begin
93                /* Us_english is always valid */
94                if @language != "us_english"
95                BEGIN
96                    /* 
97                    ** 17201, "'%1!' is not an official language
98                    **	name from Syslanguages."
99                    */
100                   raiserror 17201, @language
101                   return @returncode
102               END
103   
104               /* set to us_english */
105               select @lang_id = NULL
106           end
107   
108           else
109               select @lang_id = langid from master.dbo.syslanguages
110               where @language = name
111       END
112   
113       /* The langid is assigned 0 since it gets its value from @@langid. */
114       /* For us_english, we have to insert it as NULL and not 0, this is */
115       /* to maintain compatibility with the current conventions          */
116   
117       if @lang_id = 0
118       begin
119           select @lang_id = NULL
120       end
121   
122       /*
123       ** @with_log must be 'TRUE' or 'FALSE'
124       */
125       if (upper(@with_log) not in ('TRUE', 'FALSE'))
126       begin
127           /*
128           ** 17217, "The only valid @with_log values are TRUE or FALSE."
129           */
130           raiserror 17217
131           return (1)
132       end
133   
134       if (rtrim(upper(@with_log)) = 'TRUE')
135       begin -- {
136           /*
137           ** Only the DBO or SA (has "sa_role") may add messages which 
138           ** set the WITH_LOG option to `true'
139           */
140           select @nullarg = NULL
141           select @dbname = db_name()
142           execute @returncode = sp_aux_checkroleperm
143               "dbo",
144               "own database",
145               @dbname,
146               @gp_enabled output
147   
148           if (@returncode != 0)
149           begin -- {
150               if (@gp_enabled = 0)
151               begin
152                   execute @dummy = sp_aux_checkroleperm
153                       "sa_role",
154                       @nullarg,
155                       @nullarg,
156                       @gp_enabled output
157                   if (@dummy > 0)
158                   begin
159                       /*
160                       ** 17216, "Only the 
161                       ** System Administrator (SA) or 
162                       ** the Database owner (dbo) may add 
163                       ** messages which set the WITH_LOG 
164                       ** option to `'true''."
165                       */
166                       raiserror 17216
167                       return (1)
168                   end
169               end
170               else
171               begin
172                   select @dummy =
173                       proc_auditperm("own database",
174                           @returncode, @dbname)
175                   return (1)
176               end
177           end -- }
178       end -- }
179   
180       /*
181       ** Set the dlevel bit accordingly
182       */
183       if (rtrim(upper(@with_log)) = 'TRUE')
184           select @dlevel = hextoint("0x80")
185       else
186           select @dlevel = 0x0
187   
188       /*
189       **  Does this message already exist, and if so are we REPLACEing it?
190       */
191       select @suid = uid from sysusermessages
192       where error = @message_num and langid = @lang_id
193   
194       if (@@rowcount > 0)
195       BEGIN
196           if lower(@replace) = 'replace'
197               if (@suid = suser_id())
198               begin
199                   print 'Replacing message.'
200                   delete from sysusermessages where error = @message_num
201                       and langid = @lang_id
202               end
203               else
204               begin
205                   /* 17218, "Message number %1! with language ID %2! was
206                   ** created by user ID %3!. You cannot replace that
207                   ** message; you must first delete the existing message
208                   ** using sp_dropmessage, then add the changed message."
209                   */
210                   raiserror 17218, @message_num, @lang_id, @suid
211                   return (1)
212               end
213           else
214           begin
215               /* 17212, "A message with number %1! in the specified 
216               ** language already exists." 
217               */
218               raiserror 17212, @message_num
219   
220               /* 17213, "Drop the old message first if you still wish to 
221               ** add this one." 
222               */
223               raiserror 17213
224               return (1)
225           end
226       END
227   
228       /*
229       ** Check that the message is a valid one.
230       */
231       print @message_text, "<arg1>", "<arg2>", "<arg3>", "<arg4>",
232           "<arg5>", "<arg6>", "<arg7>", "<arg8>",
233           "<arg9>", "<arg10>", "<arg11>", "<arg12>",
234           "<arg13>", "<arg14>", "<arg15>", "<arg16>",
235           "<arg17>", "<arg18>", "<arg19>", "<arg20>"
236   
237       /*
238       ** If there was an error, @@error will be non-zero
239       */
240       if @@error != 0
241           return (1)
242   
243   
244   
245       /*
246       ** Generate successful audit record.
247       */
248       select @returncode = ad_hoc_audit(@eventnum, @mod_ok, str(@message_num),
249               db_name(), "sysusermessages", suser_name(), 0, object_id("sysusermessages")
250   
251           )
252   
253       /* 
254       ** This transaction also writes a log record for replicating the
255       ** invocation of this procedure. If logexec() fails, the transaction
256       ** is aborted.
257       **
258       ** IMPORTANT: The name rs_logexec is significant and is used by
259       ** Replication Server.
260       */
261       begin transaction rs_logexec
262   
263       /* Now, insert the message */
264       insert into sysusermessages
265       values (@message_num, suser_id(), @message_text, @lang_id, @dlevel)
266       if @@error != 0
267       begin
268           /* 17215, "The message has not been inserted." */
269           raiserror 17215
270   
271           rollback transaction rs_logexec
272           return (1)
273       end
274   
275       /*
276       ** Write the log record to replicate this invocation 
277       ** of the stored procedure.
278       */
279       if (logexec() != 1)
280       begin
281           /*
282           ** 17756, "The execution of the stored procedure '%1!' in
283           **         database '%2!' was aborted because there was an
284           **         error in writing the replication log record."
285           */
286           select @dbname = db_name()
287           raiserror 17756, "sp_addmessage", @dbname
288   
289           rollback transaction rs_logexec
290           return (1)
291       end
292   
293       commit transaction
294   
295       /* 17214, "The message has been inserted." */
296       exec sp_getmessage 17214, @msg output
297       print @msg
298   
299       return (0)
300   


exec sp_procxmode 'sp_addmessage', 'AnyMode'
go

Grant Execute on sp_addmessage to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..syslanguages master..syslanguages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysusermessages sybsystemprocs..sysusermessages
 MTYP 4 Assignment type mismatch @lang_id: smallint = int 54
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 186
 MGTP 3 Grant to public master..syslanguages  
 MGTP 3 Grant to public sybsystemprocs..sp_addmessage  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusermessages  
 MNER 3 No Error Check should check @@error after delete 200
 MNER 3 No Error Check should check return value of exec 296
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 299
 QISO 3 Set isolation level 61
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
74
 VNRD 3 Variable is not read @mod_fail 48
 VNRD 3 Variable is not read @gp_enabled 156
 VNRD 3 Variable is not read @dummy 172
 VNRD 3 Variable is not read @returncode 248
 MSUB 2 Subquery Marker 73
 MTR1 2 Metrics: Comments Ratio Comments: 45% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 22dec - 11exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 115 22

DATA PROPAGATION detailed
ColumnWritten To
@message_numsysusermessages.error  
@message_textsysusermessages.description   sp_exec_SQL_rset_001.sqlbNoName57 sp_helpsort_rset_001.Collation Name sp_helpsort_rset_003.sqlbNoName79 sp_objectsegment_rset_001.Data_located_on_segment sp_passwordpolicy_rset_002.message sp_passwordpolicy_rset_003.message sp_passwordpolicy_rset_004.Curr_value

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslanguages (1)  
read_writes table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_validlang