DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getversioncolumns  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_jdbc_getversioncolumns(
3         @table_qualifier varchar(32) = null,
4         @table_owner varchar(32) = null,
5         @table_name varchar(255))
6     as
7         declare @indid int
8         declare @table_id int
9         declare @dbname varchar(32)
10        declare @full_table_name varchar(765)
11        declare @msg varchar(765)
12        declare @owner varchar(32)
13    
14        create table #versionhelp(SCOPE smallint null, COLUMN_NAME varchar(255) null,
15            DATA_TYPE int null, TYPE_NAME varchar(8) null, COLUMN_SIZE int null,
16            BUFFER_LENGTH smallint null, DECIMAL_DIGITS smallint null,
17            PSEUDO_COLUMN smallint null)
18    
19        if @@trancount = 0
20        begin
21            set chained off
22        end
23    
24        set transaction isolation level 1
25    
26        /* this will make sure that all rows are sent even if
27        ** the client "set rowcount" is differect
28        */
29    
30        set rowcount 0
31    
32    
33        /* get database name */
34        select @dbname = db_name()
35    
36        /* we don't want a temp table unless we're in tempdb */
37        /* Adding tempdb check here depending on the ASE version ADDTEMPDB */
38    
39        if (@table_name like '#%' and db_name() != db_name(tempdb_id()))
40        begin
41            exec sp_getmessage 17676, @msg output
42            raiserror 17676 @msg
43            return (1)
44        end
45    
46        if @table_qualifier is not null
47        begin
48            /* if qualifier doesn't match current database */
49            if @dbname != @table_qualifier
50            begin
51                exec sp_getmessage 18039, @msg output
52                raiserror 18039 @msg
53                return (1)
54            end
55        end
56    
57        if (@table_owner is null) select @table_owner = '%'
58        else
59        begin
60            /*        
61            ** NOTE: SQL Server allows an underscore '_' in the table owner, even 
62            **       though it is a single character wildcard.
63            */
64            if (charindex('%', @table_owner) > 0)
65            begin
66                exec sp_getmessage 17993, @msg output
67                raiserror 17993 @msg, @table_owner
68                return (1)
69            end
70            exec sp_jdbc_escapeliteralforlike @table_owner output
71        end
72    
73        if (@table_name is null)
74        begin
75            exec sp_getmessage 17993, @msg output
76            raiserror 17993 @msg, 'NULL'
77            return (1)
78        end
79    
80        if (select count(*)
81                from sysobjects
82                where user_name(uid)
83                    like @table_owner ESCAPE '\'
84                    and name = @table_name) = 0
85        begin
86            exec sp_getmessage 17674, @msg output
87            raiserror 17674 @msg
88            return 1
89        end
90        else
91        begin
92            declare version_cur cursor for
93            select @table_owner = user_name(uid) from sysobjects
94            where name = @table_name and user_name(uid) like @table_owner
95    
96            open version_cur
97            fetch version_cur into @owner
98    
99            while (@@sqlstatus = 0)
100           begin
101               if @owner is null
102               begin /* if unqualified table name */
103                   select @full_table_name = @table_name
104               end
105               else
106               begin /* qualified table name */
107                   select @full_table_name = @owner + '.' + @table_name
108               end
109   
110               /* get object ID */
111               select @table_id = object_id(@full_table_name)
112   
113               insert into #versionhelp select
114                   convert(smallint, 0),
115                   c.name,
116                       (select data_type from
117                           sybsystemprocs.dbo.spt_jdbc_datatype_info
118                       where type_name = 'binary'),
119                   'BINARY',
120                   isnull(d.data_precision,
121                       convert(int, c.length))
122                   + isnull(d.aux, convert(int,
123                       ascii(substring('???AAAFFFCKFOLS',
124                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
125                       - 60)),
126                   18, /* Number of chars = 2^4 byte + '0x' */
127                   isnull(d.numeric_scale + convert(smallint,
128                       isnull(d.aux,
129                           ascii(substring('<<<<<<<<<<<<<<?',
130                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
131                           - 60)), 0),
132                   1
133               from
134                   systypes t, syscolumns c,
135                   sybsystemprocs.dbo.spt_jdbc_datatype_info d
136               where
137                   c.id = @table_id
138                   and c.type = d.ss_dtype
139                   and c.usertype = 80 /* TIMESTAMP */
140                   and t.usertype = 80 /* TIMESTAMP */
141               fetch version_cur into @owner
142           end
143       end /* Adaptive Server has expanded all '*' elements in the following statement */
144       select #versionhelp.SCOPE, #versionhelp.COLUMN_NAME, #versionhelp.DATA_TYPE, #versionhelp.TYPE_NAME, #versionhelp.COLUMN_SIZE, #versionhelp.BUFFER_LENGTH, #versionhelp.DECIMAL_DIGITS, #versionhelp.PSEUDO_COLUMN from #versionhelp
145   


exec sp_procxmode 'sp_jdbc_getversioncolumns', 'AnyMode'
go

Grant Execute on sp_jdbc_getversioncolumns to public
go
RESULT SETS
sp_jdbc_getversioncolumns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 138
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [sybsystemprocs..spt_jdbc_datatype_info d, sybsystempr... 113
 QCSC 4 Costly 'select count()', use 'exists()' 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 140
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 137
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getversioncolumns  
 MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check return value of exec 41
 MNER 3 No Error Check should check return value of exec 51
 MNER 3 No Error Check should check return value of exec 66
 MNER 3 No Error Check should check return value of exec 70
 MNER 3 No Error Check should check return value of exec 75
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check @@error after insert 113
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 99
 QISO 3 Set isolation level 24
 QNAJ 3 Not using ANSI Inner Join 133
 QNUA 3 Should use Alias: Table sybsystemprocs..spt_jdbc_datatype_info 117
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
137
 VUNU 3 Variable is not used @indid 7
 MRST 2 Result Set Marker 144
 MSUB 2 Subquery Marker 80
 MSUB 2 Subquery Marker 116
 MTR1 2 Metrics: Comments Ratio Comments: 16% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 14dec - 6exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 77 2
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, sjdi=sybsystemprocs..spt_jdbc_datatype_info} 0 113

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_jdbc_datatype_info  
writes table sybsystemprocs..sp_jdbc_getversioncolumns_rset_001 
reads table sybsystemprocs..syscolumns  
read_writes table tempdb..#versionhelp (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table sybsystemprocs..systypes  
calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike  
reads table sybsystemprocs..sysobjects