DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_sql_type_name  31 Aug 14Defects Dependencies

1     
2     /*
3     **  sp_ijdbc_sql_type_name
4     */
5     
6     
7     /*
8     **  Implements RSMDA.getColumnTypeName
9     **  create a procedure that will query 
10    **  spt_jdatatype_info for the correct jdbc mapped datatype or
11    **  the datasource specific systable, to retrieve the correct type
12    **  or user defined datatype name, based on the parameters
13    **  @datatype = the protocol datatype value
14    **  @usrtype = the data source specifc user defined datatype value
15    */
16    
17    create procedure sp_ijdbc_sql_type_name
18        @datatype smallint,
19        @usrtype smallint
20    as
21        BEGIN
22    
23            if @@trancount = 0
24            begin
25                set chained off
26            end
27    
28            set transaction isolation level 1
29    
30            /* special case for types numericn and decimaln, they do not seem
31            ** to have the correct mapping of usertype & datatype
32            */
33            /* if type is decimaln(106) map to decimal(55)
34            * if type is numericn(108) map to numeric(63) 
35            */
36    
37            delete #tmp_sql_type_name
38    
39            if (@datatype = 108)
40            begin
41                select @datatype = 63
42            end
43            else if (@datatype = 106)
44            begin
45                select @datatype = 55
46            end
47    
48            /* if a usertype is greater than 100 that means it is a
49            * user defined datatype, and it needs to be reference in
50            * the datasource specific systype table.  If they are
51            * user-defined numeric/decimal, then only use the usertype
52            * for the search criteria (see the note on SPECIAL CASE below)
53            * This is the fix for Bug 192969.
54            */
55            if (@usrtype > 100)
56            begin
57    
58                insert #tmp_sql_type_name
59                select name from systypes
60                where usertype = @usrtype
61            end
62            /* simply check spt_jdatatype_info for 
63            * the predefined jdbc mapping for the types
64            */
65            else
66            begin
67                insert #tmp_sql_type_name
68                select j.type_name as name
69                from sybsystemprocs.dbo.spt_jdatatype_info j
70                where j.ss_dtype = @datatype
71            end
72        END
73    
74    


exec sp_procxmode 'sp_ijdbc_sql_type_name', 'AnyMode'
go

Grant Execute on sp_ijdbc_sql_type_name to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint 70
 TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info sybsystemprocs..spt_jdatatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_sql_type_name  
 MGTP 3 Grant to public sybsystemprocs..spt_jdatatype_info  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after delete 37
 MNER 3 No Error Check should check @@error after insert 58
 MNER 3 No Error Check should check @@error after insert 67
 MUCO 3 Useless Code Useless Begin-End Pair 21
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 55
 QISO 3 Set isolation level 28
 MTR1 2 Metrics: Comments Ratio Comments: 52% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 23 17

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_jdatatype_info  
reads table sybsystemprocs..systypes  
writes table tempdb..#tmp_sql_type_name (1)