DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mda  31 Aug 14Defects Dependencies

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


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}
106
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 106
 QTYP 4 Comparison type mismatch tinyint = int 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 107
 QTYP 4 Comparison type mismatch tinyint = int 107
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 129
 QTYP 4 Comparison type mismatch tinyint = int 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 130
 QTYP 4 Comparison type mismatch tinyint = int 130
 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 11
 MUCO 3 Useless Code Useless Begin-End Pair 12
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 145
 QCRS 3 Conditional Result Set 88
 QCRS 3 Conditional Result Set 100
 QCRS 3 Conditional Result Set 114
 QGWO 3 Group by/Distinct/Union without order by 88
 QGWO 3 Group by/Distinct/Union without order by 100
 QGWO 3 Group by/Distinct/Union without order by 114
 QISO 3 Set isolation level 31
 QISO 3 Set isolation level 139
 QISO 3 Set isolation level 143
 QISO 3 Set isolation level 147
 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}
94
 QUNI 3 Check Use of 'union' vs 'union all' 88
 QUNI 3 Check Use of 'union' vs 'union all' 100
 QUNI 3 Check Use of 'union' vs 'union all' 114
 VUNU 3 Variable is not used @clientversion 11
 MRST 2 Result Set Marker 88
 MRST 2 Result Set Marker 100
 MRST 2 Result Set Marker 114
 MTR1 2 Metrics: Comments Ratio Comments: 24% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 10dec - 1exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 63 11

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_mda (1)