Database	Proc	Application	Created	Links
sybsystemprocs	sp_sql_type_name		31 Aug 14	Defects Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     /*
4     **  Implements RSMDA.getColumnTypeName
5     **  create a procedure that will query 
6     **  spt_jdbc_datatype_info for the correct jdbc mapped datatype or
7     **  the datasource specific systable, to retrieve the correct type
8     **  or user defined datatype name, based on the parameters
9     **  @datatype = the protocol datatype value
10    **  @usrtype = the data source specifc user defined datatype value
11    */
12    create procedure sp_sql_type_name
13        @datatype tinyint,
14        @usrtype smallint
15    as
16        BEGIN
17    
18            if @@trancount = 0
19            begin
20                set chained off
21            end
22    
23            set transaction isolation level 1
24    
25            /* this will make sure that all rows are sent even if
26            ** the client "set rowcount" is differect
27            */
28    
29            set rowcount 0
30    
31            /* Special case for types numericn, decimaln, daten and timen.
32            ** They do not seem to have the correct mapping of usertype & datatype
33            */
34            /* if type is decimaln(106) map to decimal(55)
35            * if type is numericn(108) map to numeric(63) 
36            * if type is daten (123) map to date (49)
37            * if type is timen (147) map to time (51)
38            * if type is bigdatetimen (187) map to bigdatetime (189)
39            * if type is bigtimen (188) map to bigtime (190)
40            */
41            if (@datatype = 108)
42            begin
43                select @datatype = 63
44            end
45            else if (@datatype = 106)
46            begin
47                select @datatype = 55
48            end
49            else if (@datatype = 123)
50            begin
51                select @datatype = 49
52            end
53            else if (@datatype = 147)
54            begin
55                select @datatype = 51
56            end
57            else if (@datatype = 187)
58            begin
59                select @datatype = 189
60            end
61            else if (@datatype = 188)
62            begin
63                select @datatype = 190
64            end
65    
66    
67            /* if a usertype is greater than 100 that means it is a
68            * user defined datatype, and it needs to be reference in
69            * the datasource specific systype table.  If they are
70            * user-defined numeric/decimal, then only use the usertype
71            * for the search criteria (see the note on SPECIAL CASE below)
72            * This is the fix for Bug 192969.
73            */
74            if (@usrtype > 100)
75            begin
76                select name from systypes
77                where usertype = @usrtype
78            end
79            /* check if we have the special case of a usertype signaling
80            * UNICHAR (34) or UNIVARCHAR (35)
81            */
82            else if (@usrtype = 34 or @usrtype = 35)
83            begin
84                select name from systypes
85                where usertype = @usrtype
86            end
87            /* simply check spt_jdbc_datatype_info for 
88            * the predefined jdbc mapping for the types
89            */
90            else
91            begin
92                select j.type_name as name
93                from sybsystemprocs.dbo.spt_jdbc_datatype_info j
94                where j.ss_dtype = @datatype
95            end
96        END
97    

exec sp_procxmode 'sp_sql_type_name', 'AnyMode'
go

Grant Execute on sp_sql_type_name to public
go

DEFECTS
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	16
MUCO 3 Useless Code Useless Brackets	41
MUCO 3 Useless Code Useless Brackets	45
MUCO 3 Useless Code Useless Brackets	49
MUCO 3 Useless Code Useless Brackets	53
MUCO 3 Useless Code Useless Brackets	57
MUCO 3 Useless Code Useless Brackets	61
MUCO 3 Useless Code Useless Brackets	74
MUCO 3 Useless Code Useless Brackets	82
QCRS 3 Conditional Result Set	76
QCRS 3 Conditional Result Set	84
QCRS 3 Conditional Result Set	92
QISO 3 Set isolation level	23
MRST 2 Result Set Marker	76
MRST 2 Result Set Marker	84
MRST 2 Result Set Marker	92
MTR1 2 Metrics: Comments Ratio Comments: 53%	12
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 10dec - 1exi + 2	12
MTR3 2 Metrics: Query Complexity Complexity: 38	12

DEPENDENCIES

PROCS AND TABLES USED reads table sybsystemprocs..systypes   reads table sybsystemprocs..spt_jdbc_datatype_info