DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sql_type_name  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **  Implements RSMDA.getColumnTypeName
4     **  create a procedure that will query 
5     **  spt_jdbc_datatype_info for the correct jdbc mapped datatype or
6     **  the datasource specific systable, to retrieve the correct type
7     **  or user defined datatype name, based on the parameters
8     **  @datatype = the protocol datatype value
9     **  @usrtype = the data source specifc user defined datatype value
10    **  @getlist = 0 -- For No List, 1 -- For System Types, 
11    **  Note: The mechanism in this sproc should be kept in sync with
12    **        The mechanism in Tds.java:getTypeKey() method!!!
13    */
14    create or replace procedure sp_sql_type_name
15        @datatype tinyint,
16        @usrtype smallint,
17        @getlist bit = 0
18    as
19        BEGIN
20    
21            if @@trancount = 0
22            begin
23                set chained off
24            end
25    
26            set transaction isolation level 1
27    
28            /* this will make sure that all rows are sent even if
29            ** the client "set rowcount" is differect
30            */
31    
32            set rowcount 0
33    
34            /* Special case for types numericn, decimaln, daten and timen.
35            ** They do not seem to have the correct mapping of usertype & datatype
36            */
37            /* if type is decimaln(106) map to decimal(55)
38            * if type is numericn(108) map to numeric(63) 
39            * if type is daten (123) map to date (49)
40            * if type is timen (147) map to time (51)
41            * if type is bigdatetimen (187) map to bigdatetime (189)
42            * if type is bigtimen (188) map to bigtime (190)
43            */
44            if (@datatype = 108)
45            begin
46                select @datatype = 63
47            end
48            else if (@datatype = 106)
49            begin
50                select @datatype = 55
51            end
52            else if (@datatype = 123)
53            begin
54                select @datatype = 49
55            end
56            else if (@datatype = 147)
57            begin
58                select @datatype = 51
59            end
60            else if (@datatype = 187)
61            begin
62                select @datatype = 189
63            end
64            else if (@datatype = 188)
65            begin
66                select @datatype = 190
67            end
68    
69    
70            /* if a usertype is greater than 100 that means it is a
71            * user defined datatype, and it needs to be reference in
72            * the datasource specific systype table.  If they are
73            * user-defined numeric/decimal, then only use the usertype
74            * for the search criteria (see the note on SPECIAL CASE below)
75            * This is the fix for Bug 192969.
76            */
77            if (@usrtype > 100)
78            begin
79                select name from systypes
80                where usertype = @usrtype
81            end
82            /* check if we have the special case of a usertype signaling
83            * UNICHAR (34) or UNIVARCHAR (35)
84            */
85            else if (@usrtype = 34 or @usrtype = 35)
86            begin
87                select name from systypes
88                where usertype = @usrtype
89            end
90            /* simply check spt_jdbc_datatype_info for 
91            * the predefined jdbc mapping for the types
92            */
93            else
94            begin
95                if (@getlist = 1) /* get all System Types */
96                begin
97                    select type_name as name, ss_dtype as type, data_type as usertype
98                    from
99                        sybsystemprocs.dbo.spt_jdbc_datatype_info
100                   union
101                   select name, type, usertype
102                   from
103                       systypes
104                   where
105                       usertype = 34 or usertype = 35
106                   union
107                   select type_name as name, 108 as type, data_type as usertype
108                   from
109                       sybsystemprocs.dbo.spt_jdbc_datatype_info
110                   where
111                       ss_dtype = 63
112                   union
113                   select type_name as name, 106 as type, data_type as usertype
114                   from
115                       sybsystemprocs.dbo.spt_jdbc_datatype_info
116                   where
117                       ss_dtype = 55
118                   union
119                   select type_name as name, 123 as type, data_type as usertype
120                   from
121                       sybsystemprocs.dbo.spt_jdbc_datatype_info
122                   where
123                       ss_dtype = 49
124                   union
125                   select type_name as name, 147 as type, data_type as usertype
126                   from
127                       sybsystemprocs.dbo.spt_jdbc_datatype_info
128                   where
129                       ss_dtype = 51
130                   union
131                   select type_name as name, 187 as type, data_type as usertype
132                   from
133                       sybsystemprocs.dbo.spt_jdbc_datatype_info
134                   where
135                       ss_dtype = 189
136                   union
137                   select type_name as name, 188 as type, data_type as usertype
138                   from
139                       sybsystemprocs.dbo.spt_jdbc_datatype_info
140                   where
141                       ss_dtype = 190
142               end
143               else
144               begin
145                   select type_name as name
146                   from sybsystemprocs.dbo.spt_jdbc_datatype_info
147                   where ss_dtype = @datatype
148               end
149           end
150       END
151   


exec sp_procxmode 'sp_sql_type_name', 'AnyMode'
go

Grant Execute on sp_sql_type_name to public
go
RESULT SETS
sp_sql_type_name_rset_004
sp_sql_type_name_rset_003
sp_sql_type_name_rset_002
sp_sql_type_name_rset_001

DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 111
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 117
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 135
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 141
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_sql_type_name  
 MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MUCO 3 Useless Code Useless Begin-End Pair 19
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 95
 QCRS 3 Conditional Result Set 79
 QCRS 3 Conditional Result Set 87
 QCRS 3 Conditional Result Set 97
 QCRS 3 Conditional Result Set 145
 QGWO 3 Group by/Distinct/Union without order by 97
 QISO 3 Set isolation level 26
 QTLO 3 Top-Level OR 105
 QUNI 3 Check Use of 'union' vs 'union all' 97
 MRST 2 Result Set Marker 79
 MRST 2 Result Set Marker 87
 MRST 2 Result Set Marker 97
 MRST 2 Result Set Marker 145
 MTR1 2 Metrics: Comments Ratio Comments: 36% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 12dec - 1exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 57 14

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_sql_type_name_rset_004 
writes table sybsystemprocs..sp_sql_type_name_rset_003 
writes table sybsystemprocs..sp_sql_type_name_rset_002 
reads table sybsystemprocs..spt_jdbc_datatype_info  
reads table sybsystemprocs..systypes  
writes table sybsystemprocs..sp_sql_type_name_rset_001