DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_datatype_info  14 déc. 14Defects Propagation Dependencies

1     
2     
3     
4     
5     
6     /* 
7     ** Sccsid = "%Z% generic/sproc/%M% %I% %G%"
8     **
9     ** History:
10    **	mm/yy		Author					Comments
11    **	12/06		Meena Ramakrishnan		Changes to conform to the ODBC API 
12    **										of returning the system types in any
13    **										given sql data type first before the user defined types
14    **	
15    **	This stored procedure has been ported from the ASE stored procedure sp_datatype_info
16    **
17    **	10.0	steven	1.1	07/13/93	sproc/src/datatype_info
18    **						Ported from MS catalog SP's
19    **
20    ** Implementation Notes:
21    ** 	The messiness of 'sp_odbc_data_type_info' was to get around the
22    ** problem of returning the correct lengths for user defined types.  The
23    ** join on the type name ensures all user defined types are returned, but
24    ** this puts a null in the data_type column.  By forcing an embedded
25    ** select and correlating it with the current row in systypes, we get the
26    ** correct data_type mapping even for user defined types.  
27    **
28    **	parameters: data_type  of type int
29    */
30    
31    CREATE OR REPLACE PROCEDURE sp_odbc_datatype_info
32        @data_type int = 0, /* Provide datatype_info for type # */
33        @odbc_ver int = 3 /* Provide result set for ODBC 3.0 API # */
34    as
35    
36        declare @startedInTransaction bit
37    
38        if (@@trancount > 0)
39            select @startedInTransaction = 1
40        else
41            select @startedInTransaction = 0
42    
43    
44        if (@startedInTransaction = 1)
45            save transaction odbc_keep_temptable_tx
46    
47        set @data_type =
48            case @data_type
49                when null then 0
50                when 91 then 9
51                when 92 then 10
52                when 93 then 11
53                else @data_type
54            end
55    
56    
57        select
58            TYPE_NAME =
59            cast(
60            case t.name
61                when 'usmallint' then 'unsigned smallint'
62                when 'uint' then 'unsigned int'
63                when 'ubigint' then 'unsigned bigint'
64                else t.name
65            end
66            as varchar(30)),
67            DATA_TYPE =
68            cast(
69            case
70                when d.data_type = 9 and @odbc_ver = 3 then 91
71                when d.data_type = 10 and @odbc_ver = 3 then 92
72                when d.data_type = 11 and @odbc_ver = 3 then 93
73                when t.name = 'unichar' and @odbc_ver in (2, 3) then - 8
74                when t.name = 'univarchar' and @odbc_ver in (2, 3) then - 9
75                else d.data_type
76            end
77            as smallint),
78            COLUMN_SIZE = cast(isnull(d.data_precision, t.length / case when t.name in ('univarchar', 'unichar') then 2 else 1 end) as int),
79            LITERAL_PREFIX = cast(d.literal_prefix as varchar(30)),
80            LITERAL_SUFFIX = cast(d.literal_suffix as varchar(30)),
81            CREATE_PARAMS = cast(case when e.create_params = 'max length' or d.data_type in (1, 12) then 'length' else e.create_params end as varchar(30)),
82            NULLABLE = cast(d.nullable as smallint),
83            CASE_SENSITIVE = cast(d.case_sensitive as smallint),
84            SEARCHABLE = cast(d.searchable as smallint),
85            UNSIGNED_ATTRIBUTE = cast(d.unsigned_attribute as smallint),
86            FIXED_PREC_SCALE = cast(d.money as smallint),
87            AUTO_UNIQUE_VALUE = cast(d.auto_increment as smallint),
88            LOCAL_TYPE_NAME = cast(case when t.name = 'timestamp' then 'timestamp' else d.local_type_name end as varchar(30)),
89            MINIMUM_SCALE = cast(case when d.data_type in (10, 11) then 0 else d.minimum_scale end as smallint),
90            MAXIMUM_SCALE = cast(case when d.data_type in (10, 11) then 3 else d.minimum_scale end as smallint),
91            SQL_DATA_TYPE =
92            cast(
93            case
94                when d.data_type in (9, 10, 11) then 9
95                when t.name = 'unichar' then - 8
96                when t.name = 'univarchar' then - 9
97                else isnull(d.sql_data_type, d.data_type + d.aux)
98            end
99            as smallint),
100           SQL_DATETIME_SUB =
101           cast(case d.data_type
102               when 9 then 1
103               when 10 then 2
104               when 11 then 3
105               else d.sql_datetime_sub
106           end
107           as smallint),
108           NUM_PREC_RADIX = cast(case when d.data_type in (2, 3, 4, 5, 6, 7, - 7, - 6) then 10 else d.num_prec_radix end as int),
109           INTERVAL_PRECISION = cast(d.interval_precision as smallint)
110       from sybsystemprocs.dbo.spt_datatype_info d
111           join systypes t
112               on t.type = d.ss_dtype
113           left join sybsystemprocs.dbo.spt_datatype_info_ext e
114               on e.user_type = t.usertype
115       where d.data_type = case @data_type when 0 then d.data_type else @data_type end
116           and t.name not in ('longsysname', 'datetimn', 'floatn', 'intn', 'moneyn', 'uintn') -- not real datatypes
117       order by
118           DATA_TYPE,
119           t.usertype,
120           TYPE_NAME
121   
122       if (@startedInTransaction = 1)
123           rollback transaction odbc_keep_temptable_tx
124   
125       return (0)
126   


exec sp_procxmode 'sp_odbc_datatype_info', 'AnyMode'
go

Grant Execute on sp_odbc_datatype_info to public
go
RESULT SETS
sp_odbc_datatype_info_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 112
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 70
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 71
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 72
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 125
 MRST 2 Result Set Marker 57
 MTR1 2 Metrics: Comments Ratio Comments: 21% 31
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 31
 MTR3 2 Metrics: Query Complexity Complexity: 42 31
 PRED_QUERY_COLLECTION 2 {sdi=sybsystemprocs..spt_datatype_info, sdie=sybsystemprocs..spt_datatype_info_ext, t=sybsystemprocs..systypes} 0 57

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_datatype_info_ext  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..spt_datatype_info  
writes table sybsystemprocs..sp_odbc_datatype_info_rset_001