DatabaseProcApplicationCreatedLinks
sybsystemprocssp_processmail  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = %Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** sp_processmail processes all the mail messages in the SQL Server's
6     ** mail box: it locates the next mail in the mail box(xp_findnextmsg), 
7     ** gets the parameters of the mail by reading it(xp_readmail), sends
8     ** a response back to the Sybmail client(xp_sendmail) and finally
9     ** deletes the mail that was just read(xp_deletemail). If the message 
10    ** retrieved thru xp_readmail contained sql queries, these are executed in
11    ** xp_sendmail and results returned to the mail client.
12    **
13    ** Messages for "sp_processmail"
14    ** 18398, "Failed to read mail from SQL Server's mailbox. 
15    **	   Check XP Server log file for more information."
16    ** 18399, "Failed to send mail to %1!. Check XP Server log
17    **	   file for more information."
18    ** 18400, "Failed to delete mail. Check XP Server log
19    **	   file for more information." 
20    */
21    
22    create procedure sp_processmail
23    
24        @subject varchar(255) = NULL, /* subject of mail to look for */
25        @originator varchar(255) = NULL, /* sender of mail to look for */
26        @dbuser varchar(255) = NULL, /* context of query execution */
27        @dbname varchar(255) = NULL, /* name of database to use */
28        @filetype varchar(10) = NULL, /* extention for attachment file */
29        @separator char(10) = NULL /* separator between columns */
30    
31    as
32    
33        declare @msgid binary(255) /* id of msg retrieved */
34        declare @ccrecp varchar(255) /* carbon copy recipients */
35        declare @bccrecp varchar(255) /* blind copy recipients */
36        declare @msubject varchar(255) /* mail subject	*/
37        declare @query varchar(255) /* query batch to be executed */
38        declare @attach varchar(255) /* path to attach file */
39        declare @sender varchar(255) /* sender of mail retrieved */
40        declare @ret int /* return status from sybmail */
41        declare @msg varchar(255)
42        declare @errornum int /* error num from xp_findnextmsg */
43        declare @filenum varchar(3) /* contains file name substring */
44    
45        /* Load the default values for procedure parameters */
46        if (@filetype is NULL)
47            select @filetype = "TXT"
48        if (@dbuser is NULL)
49            select @dbuser = "guest"
50        if (@dbname is NULL)
51            select @dbname = "master"
52    
53        /* Generate the attachment file name */
54        select @filenum = datename(millisecond, getdate())
55        select @attach = ("syb" + @filenum + "00000")
56        select @attach = substring(@attach, 1, 8)
57        select @attach = (@attach + "." + @filetype)
58    
59        /* If a message is found, process it */
60        while (1 < 2)
61        BEGIN
62            /* We select the first unread msg each time. Set @msgid to NULL */
63            select @msgid = NULL
64            exec @ret = xp_findnextmsg @msgid OUTPUT
65            select @errornum = @@error
66    
67            /* if there is no message, this sp is done. 11526 is 'eom' error */
68            if (@ret != 0)
69            BEGIN
70                if (@errornum != 11526)
71                    return 1
72                else
73                    break
74            END
75    
76            exec @ret = xp_readmail @msgid, NULL, @sender OUTPUT, NULL,
77                @msubject OUTPUT, @ccrecp OUTPUT,
78                @bccrecp OUTPUT, @query OUTPUT,
79                NULL, NULL, NULL, NULL,
80                NULL, 0, NULL
81    
82            if (@ret != 0)
83            BEGIN
84                /*
85                ** 18398, "Failed to read mail from SQL Server's mailbox. 
86                **	   Check XP Server log file for more information."
87                */
88                raiserror 18398
89                return 1
90            END
91    
92            /* 
93            ** If a 'subject' or 'originator' filter was provided,
94            ** check against them for a match. If match is found
95            ** send the mail containing query results to sender.
96            */
97            if (@subject is not NULL)
98            BEGIN
99                if (@subject != @msubject)
100                   continue
101           END
102   
103           if (@originator is not NULL)
104           BEGIN
105               if (@originator != @sender)
106                   continue
107           END
108   
109           exec @ret = xp_sendmail @sender, @msubject, @ccrecp,
110               @bccrecp, @query, NULL, @attach,
111               'true', NULL, NULL,
112               NULL, NULL, NULL, @separator,
113               @dbuser, @dbname, NULL, NULL
114   
115           if (@ret != 0)
116           BEGIN
117               /*
118               ** 18399, "Failed to send mail to %1!. Check XP Server log
119               **	   file for more information."
120               */
121               raiserror 18399, @originator
122               return 1
123           END
124   
125           /* Delete the mail from SQL Server's mail box */
126           exec @ret = xp_deletemail @msgid
127   
128           if (@ret != 0)
129           BEGIN
130               /*
131               ** 18400, "Failed to delete mail. Check XP Server log
132               **	   file for more information."
133               */
134               raiserror 18400
135               return 1
136           END
137   
138       END
139   
140       /* sp_processmail successfully completed */
141       return 0
142
DEFECTS
 MNER 3 No Error Check should check return value of exec 64
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 128
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..xp_findnextmsg 64
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..xp_readmail 76
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..xp_sendmail 109
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..xp_deletemail 126
 VUNU 3 Variable is not used @msg 41
 MTR1 2 Metrics: Comments Ratio Comments: 50% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 13dec - 5exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 62 22

DEPENDENCIES