DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mda  14 d├ęc. 14Defects Propagation Dependencies

1     
2     /*
3     ** create the well-known sp_mda procedure for accessing the data
4     */
5     
6     
7     /*
8     ** requesttype 0 == Returns the mdinfo:MDAVERSION and mdinfo:MDARELEASEID rows.
9     ** requesttype 1 == JDBC
10    ** requesttype 2 == JDBC - but only send back the minimal frequently used info.
11    ** 
12    ** mdaversion 
13    */
14    create or replace procedure sp_mda(@requesttype int, @requestversion int, @clientversion int = 0) as
15        begin
16    
17            declare @min_mdaversion int, @max_mdaversion int
18            declare @mda_version int
19            declare @srv_version int
20            declare @mdaver_querytype tinyint
21            declare @mdaver_query varchar(255)
22            declare @orginal_isolation_level int
23    
24            select @min_mdaversion = 1
25            select @max_mdaversion = 9
26            select @mda_version = @requestversion
27    
28    
29            if @@trancount = 0
30            begin
31                set chained off
32            end
33            select @orginal_isolation_level = @@isolation
34            set transaction isolation level 1
35    
36            /* this will make sure that all rows are sent even if
37            ** the client "set rowcount" is differect
38            */
39    
40            set rowcount 0
41    
42            /* get the Server version */
43            /* Server dependent select ADDPOINT_ASEVERSION*/
44            select @srv_version = convert(integer, convert(varchar, @@version_number) + substring(convert(varchar, @@sbssav), 9, 9))
45    
46            /*
47            ** if the client is requesting a version too old
48            ** then we return our lowest version supported
49            **
50            ** the client needs to be able to just handle this
51            */
52            if (@requestversion < @min_mdaversion)
53            begin
54                select @mda_version = @min_mdaversion
55            end
56    
57            /*
58            ** if the client is requesting a version too new
59            ** we will return our highest version available
60            */
61            if (@mda_version > @max_mdaversion)
62            begin
63                select @mda_version = @max_mdaversion
64            end
65    
66            /*
67            ** if the client's requested version is between 1 and 3, 
68            ** then the mda version returned needs to be 1.  The reason
69            ** for this is the jConnect driver would pass in it's own 
70            ** major version number as the @requestversion.  We need to
71            ** keep older version's of the driver working ok since 
72            ** they expect a '1' to be returned.
73            */
74            if (@mda_version < 4)
75            begin
76                select @mda_version = 1
77                select @mdaver_querytype = 2
78                select @mdaver_query = 'select 1'
79            end
80            else
81            begin
82                select @mdaver_querytype = 5
83                select @mdaver_query = convert(varchar(255), @mda_version)
84            end
85    
86            /*
87            ** process the @requesttype
88            */
89            if (@requesttype = 0)
90            begin
91                select "mdinfo" = convert(varchar(30), 'MDAVERSION'),
92                    "querytype" = @mdaver_querytype,
93                    "query" = @mdaver_query
94                union
95                select mdinfo, querytype, query
96                from master..spt_mda
97                where mdinfo in (
98                        'MDARELEASEID'
99                    )
100           end
101           else if (@requesttype = 1)
102           begin
103               select "mdinfo" = convert(varchar(30), 'MDAVERSION'),
104                   "querytype" = @mdaver_querytype,
105                   "query" = @mdaver_query
106               union
107               select mdinfo, querytype, query
108               from master..spt_mda
109               where @mda_version >= mdaver_start
110                   and @mda_version <= mdaver_end
111                   and ((@srv_version >= srvver_start)
112                       and (@srv_version <= srvver_end
113                           or srvver_end = - 1))
114           end
115           else if (@requesttype = 2)
116           begin
117               select "mdinfo" = convert(varchar(30), 'MDAVERSION'),
118                   "querytype" = @mdaver_querytype,
119                   "query" = @mdaver_query
120               union
121               select mdinfo, querytype, query
122               from master..spt_mda
123               where mdinfo in (
124                       'CONNECTCONFIG',
125                       'SET_CATALOG',
126                       'SET_AUTOCOMMIT_ON',
127                       'SET_AUTOCOMMIT_OFF',
128                       'SET_ISOLATION',
129                       'SET_ROWCOUNT',
130                       'DEFAULT_CHARSET'
131                   )
132                   and @mda_version >= mdaver_start
133                   and @mda_version <= mdaver_end
134                   and ((@srv_version >= srvver_start)
135                       and (@srv_version <= srvver_end
136                           or srvver_end = - 1))
137           end
138   
139           -- default isolation level for ASE is 1    
140           if (@orginal_isolation_level = 0)
141           begin
142               set transaction isolation level 0
143           end
144           if (@orginal_isolation_level = 2)
145           begin
146               set transaction isolation level 2
147           end
148           if (@orginal_isolation_level = 3)
149           begin
150               set transaction isolation level 3
151           end
152       end
153   


exec sp_procxmode 'sp_mda', 'AnyMode'
go

Grant Execute on sp_mda to public
go
RESULT SETS
sp_mda_rset_003
sp_mda_rset_002
sp_mda_rset_001

DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_mda.spt_mda_ind unique
(mdinfo, mdaver_end, srvver_end)
Intersection: {srvver_end, mdaver_end}
109
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 109
 QTYP 4 Comparison type mismatch tinyint = int 109
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 110
 QTYP 4 Comparison type mismatch tinyint = int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 132
 QTYP 4 Comparison type mismatch tinyint = int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 133
 QTYP 4 Comparison type mismatch tinyint = int 133
 MGTP 3 Grant to public master..spt_mda  
 MGTP 3 Grant to public sybsystemprocs..sp_mda  
 MUCO 3 Useless Code Useless Brackets in create proc 14
 MUCO 3 Useless Code Useless Begin-End Pair 15
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 148
 QCRS 3 Conditional Result Set 91
 QCRS 3 Conditional Result Set 103
 QCRS 3 Conditional Result Set 117
 QGWO 3 Group by/Distinct/Union without order by 91
 QGWO 3 Group by/Distinct/Union without order by 103
 QGWO 3 Group by/Distinct/Union without order by 117
 QISO 3 Set isolation level 34
 QISO 3 Set isolation level 142
 QISO 3 Set isolation level 146
 QISO 3 Set isolation level 150
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: spt_mda.spt_mda_ind unique
(mdinfo, mdaver_end, srvver_end)
Intersection: {mdinfo}
97
 QUNI 3 Check Use of 'union' vs 'union all' 91
 QUNI 3 Check Use of 'union' vs 'union all' 103
 QUNI 3 Check Use of 'union' vs 'union all' 117
 MRST 2 Result Set Marker 91
 MRST 2 Result Set Marker 103
 MRST 2 Result Set Marker 117
 MTR1 2 Metrics: Comments Ratio Comments: 24% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 11dec - 1exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 63 14

DATA PROPAGATION detailed
ColumnWritten To
@requestversionsp_mda_rset_001.query sp_mda_rset_002.query sp_mda_rset_003.query

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_mda_rset_002 
writes table sybsystemprocs..sp_mda_rset_001 
reads table master..spt_mda (1)  
writes table sybsystemprocs..sp_mda_rset_003