DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_datatype_info  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_jdbc_datatype_info
3     as
4     
5         if @@trancount = 0
6         begin
7             set chained off
8         end
9     
10        set transaction isolation level 1
11    
12        /* this will make sure that all rows are sent even if
13        ** the client "set rowcount" is differect
14        */
15    
16        set rowcount 0
17    
18        select /* Real SQL Server data types */
19            case
20                when t.name = 'usmallint' then 'unsigned smallint'
21                when t.name = 'uint' then 'unsigned int'
22                when t.name = 'ubigint' then 'unsigned bigint'
23                else
24                    t.name
25            end as TYPE_NAME,
26            d.data_type as DATA_TYPE,
27            isnull(d.data_precision, convert(int, t.length)) as 'PRECISION',
28            d.literal_prefix as LITERAL_PREFIX,
29            d.literal_suffix as LITERAL_SUFFIX,
30            e.create_params as CREATE_PARAMS,
31            d.nullable as NULLABLE,
32            d.case_sensitive as CASE_SENSITIVE,
33            d.searchable as SEARCHABLE,
34            d.unsigned_attribute as UNSIGNED_ATTRIBUTE,
35            d.money as FIXED_PREC_SCALE,
36            d.auto_increment as AUTO_INCREMENT,
37            d.local_type_name as LOCAL_TYPE_NAME,
38            d.minimum_scale as MINIMUM_SCALE,
39            d.maximum_scale as MAXIMUM_SCALE,
40            d.sql_data_type as SQL_DATA_TYPE,
41            d.sql_datetime_sub as SQL_DATETIME_SUB,
42            d.num_prec_radix as NUM_PREC_RADIX,
43            d.interval_precision as INTERVAL_PRECISION
44        from sybsystemprocs.dbo.spt_jdbc_datatype_info d,
45            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
46        where d.ss_dtype = t.type and t.usertype *= e.user_type
47            /* restrict results to 'real' datatypes, exclude float, date and time*/
48            and t.name not in ('nchar', 'nvarchar', 'sysname', 'timestamp', 'longsysname', 'float',
49                'datetimn', 'floatn', 'intn', 'moneyn', 'unichar',
50                'univarchar', 'daten', 'timen', 'date', 'time', 'uintn')
51            and t.usertype < 100 /* No user defined types */
52        UNION
53        select /* SQL Server user data types */
54            case
55                when t.name = 'usmallint' then 'unsigned smallint'
56                when t.name = 'uint' then 'unsigned int'
57                when t.name = 'ubigint' then 'unsigned bigint'
58                else
59                    t.name
60            end as TYPE_NAME,
61            d.data_type,
62            isnull(d.data_precision, convert(int, t.length)) as 'PRECISION',
63            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
64            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
65            d.auto_increment, t.name, d.minimum_scale,
66            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
67            d.num_prec_radix, d.interval_precision
68        from sybsystemprocs.dbo.spt_jdbc_datatype_info d,
69            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
70        where d.ss_dtype = t.type and t.usertype *= e.user_type
71            /* Restrict to user defined types (value > 100)  and Sybase user defined 
72            ** types (listed)*/
73            and (t.name in ('nchar', 'nvarchar')
74                or t.usertype >= 100) /* User defined types */
75        UNION
76        select /* ADD double precision which is floatn internally*/
77            'double precision', 8, 15,
78            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
79            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
80            d.auto_increment, 'double precision', d.minimum_scale,
81            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
82            d.num_prec_radix, d.interval_precision
83        from sybsystemprocs.dbo.spt_jdbc_datatype_info d,
84            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
85        where d.ss_dtype = t.type and t.usertype *= e.user_type
86            and t.name = 'floatn' and t.usertype < 100
87        UNION /* ADD float now */
88        select
89            'float', 8, 8,
90            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
91            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
92            d.auto_increment, 'float', d.minimum_scale,
93            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
94            d.num_prec_radix, d.interval_precision
95        from sybsystemprocs.dbo.spt_jdbc_datatype_info d,
96            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
97        where d.ss_dtype = t.type and t.usertype *= e.user_type
98            and t.name = 'float' and t.usertype < 100
99        UNION
100       select /* Add date and time now. Special case because we want to use */
101           /* d.sql_data_type for DATA_TYPE for these two types          */
102           case
103               when t.name = 'usmallint' then 'unsigned smallint'
104               when t.name = 'uint' then 'unsigned int'
105               when t.name = 'ubigint' then 'unsigned bigint'
106               else
107                   t.name
108           end as TYPE_NAME,
109           d.sql_data_type,
110           isnull(d.data_precision, convert(int, t.length)),
111           d.literal_prefix,
112           d.literal_suffix,
113           e.create_params,
114           d.nullable,
115           d.case_sensitive,
116           d.searchable,
117           d.unsigned_attribute,
118           d.money,
119           d.auto_increment,
120           d.local_type_name,
121           d.minimum_scale,
122           d.maximum_scale,
123           d.sql_data_type,
124           d.sql_datetime_sub,
125           d.num_prec_radix,
126           d.interval_precision
127       from sybsystemprocs.dbo.spt_jdbc_datatype_info d,
128           sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
129       where d.ss_dtype = t.type and t.usertype *= e.user_type
130           /* restrict results to date and time*/
131           and t.name in ('date', 'time')
132           and t.usertype < 100 /* No user defined types */
133       order by DATA_TYPE, TYPE_NAME
134   
135       return (0)
136   


exec sp_procxmode 'sp_jdbc_datatype_info', 'AnyMode'
go

Grant Execute on sp_jdbc_datatype_info to public
go
RESULT SETS
sp_jdbc_datatype_info_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 46
 QJWI 5 Join or Sarg Without Index 70
 QJWI 5 Join or Sarg Without Index 85
 QJWI 5 Join or Sarg Without Index 97
 QJWI 5 Join or Sarg Without Index 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 51
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 74
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 86
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MUCO 3 Useless Code Useless Brackets 135
 QISO 3 Set isolation level 10
 QNAO 3 Not using ANSI Outer Join 44
 QNAO 3 Not using ANSI Outer Join 68
 QNAO 3 Not using ANSI Outer Join 83
 QNAO 3 Not using ANSI Outer Join 95
 QNAO 3 Not using ANSI Outer Join 127
 QUNI 3 Check Use of 'union' vs 'union all' 18
 MRST 2 Result Set Marker 18
 MTR1 2 Metrics: Comments Ratio Comments: 10% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 53 2

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_datatype_info_ext  
writes table sybsystemprocs..sp_jdbc_datatype_info_rset_001 
reads table sybsystemprocs..spt_jdbc_datatype_info  
reads table sybsystemprocs..systypes