DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_getversioncolumns  31 Aug 14Defects Dependencies

1     
2     /*
3     **  sp_ijdbc_getversioncolumns
4     */
5     
6     create procedure sp_ijdbc_getversioncolumns(
7         @table_qualifier varchar(32) = null,
8         @table_owner varchar(32) = null,
9         @table_name varchar(257))
10    as
11        declare @indid int
12        declare @table_id int
13        declare @dbname char(30)
14        declare @msg char(70)
15    
16    
17        if @@trancount = 0
18        begin
19            set chained off
20        end
21    
22        set transaction isolation level 1
23    
24        /* get database name */
25        select @dbname = db_name()
26    
27        /* we don't want a temp table unless we're in our temporary db */
28        if @table_name like '#%' and @dbname != db_name(tempdb_id())
29        begin
30            exec sp_getmessage 17676, @msg output
31            raiserror 17676 @msg
32            return (1)
33        end
34    
35        if @table_qualifier is not null
36        begin
37            /* if qualifier doesn't match current database */
38            if @dbname != @table_qualifier
39            begin
40                exec sp_getmessage 18039, @msg output
41                raiserror 18039 @msg
42                return (1)
43            end
44        end
45    
46        if (@table_owner is null) select @table_owner = '%'
47        else
48        begin
49            /*        
50            ** NOTE: SQL Server allows an underscore '_' in the table owner, even 
51            **       though it is a single character wildcard.
52            */
53            if (charindex('%', @table_owner) > 0)
54            begin
55                exec sp_getmessage 17993, @msg output
56                raiserror 17993 @msg, @table_owner
57                return (1)
58            end
59            exec sp_ijdbc_escapeliteralforlike @table_owner output
60        end
61    
62        if (@table_name is null)
63        begin
64            exec sp_getmessage 17993, @msg output
65            raiserror 17993 @msg, 'NULL'
66            return (1)
67        end
68    
69        delete #tmp_getversioncolumns
70    
71        if (select count(*)
72                from sysobjects
73                where user_name(uid)
74                    like @table_owner ESCAPE '\'
75                    and name = @table_name) = 0
76        begin
77            exec sp_getmessage 17674, @msg output
78            raiserror 17674 @msg
79            return 1
80        end
81        else
82        begin
83            select table_owner = user_name(uid),
84                full_table_name = case when user_name(uid) is null
85                    then @table_name
86                    else
87                        user_name(uid) + '.' + @table_name
88                end,
89                table_id = case when user_name(uid) is null
90                    then object_id(@table_name)
91                    else object_id(user_name(uid) + '.' + @table_name)
92                end
93            into #tmp_owner
94            from sysobjects
95            where name = @table_name and user_name(uid) like @table_owner
96    
97    
98            insert into #tmp_getversioncolumns select
99                convert(smallint, 0),
100               c.name,
101                   (select data_type from
102                       sybsystemprocs.dbo.spt_jdatatype_info
103                   where type_name = 'binary'),
104               'BINARY',
105               isnull(d.data_precision,
106                   convert(int, c.length))
107               + isnull(d.aux, convert(int,
108                   ascii(substring('???AAAFFFCKFOLS',
109                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
110                   - 60)),
111               18, /* Number of chars = 2^4 byte + '0x' */
112               isnull(d.numeric_scale + convert(smallint,
113                   isnull(d.aux,
114                       ascii(substring('<<<<<<<<<<<<<<?',
115                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
116                       - 60)), 0),
117               1
118           from
119               systypes t, syscolumns c,
120               sybsystemprocs.dbo.spt_jdatatype_info d,
121               #tmp_owner
122           where
123               c.id = table_id
124               and c.type = d.ss_dtype
125               and c.usertype = 80 /* TIMESTAMP */
126               and t.usertype = 80 /* TIMESTAMP */
127       end
128   
129   


exec sp_procxmode 'sp_ijdbc_getversioncolumns', 'AnyMode'
go

Grant Execute on sp_ijdbc_getversioncolumns to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 124
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [#tmp_owner, sybsystemprocs..syscolumns c, sybsystempr... 98
 QCSC 4 Costly 'select count()', use 'exists()' 71
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info sybsystemprocs..spt_jdatatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_getversioncolumns  
 MGTP 3 Grant to public sybsystemprocs..spt_jdatatype_info  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MLCH 3 Char type with length>30 char(70) 14
 MNER 3 No Error Check should check return value of exec 30
 MNER 3 No Error Check should check return value of exec 40
 MNER 3 No Error Check should check return value of exec 55
 MNER 3 No Error Check should check return value of exec 59
 MNER 3 No Error Check should check return value of exec 64
 MNER 3 No Error Check should check @@error after delete 69
 MNER 3 No Error Check should check return value of exec 77
 MNER 3 No Error Check should check @@error after select into 83
 MNER 3 No Error Check should check @@error after insert 98
 MUCO 3 Useless Code Useless Brackets in create proc 6
 MUCO 3 Useless Code Useless Brackets 32
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 66
 QCTC 3 Conditional Table Creation 83
 QISO 3 Set isolation level 22
 QJWT 3 Join or Sarg Without Index on temp table 123
 QNAJ 3 Not using ANSI Inner Join 118
 QNUA 3 Should use Alias: Table sybsystemprocs..spt_jdatatype_info 102
 QNUA 3 Should use Alias: Table #tmp_owner 121
 QNUA 3 Should use Alias: Column table_id should use alias #tmp_owner 123
 QTJ1 3 Table only appears in inner join clause 121
 VUNU 3 Variable is not used @indid 11
 VUNU 3 Variable is not used @table_id 12
 MSUB 2 Subquery Marker 71
 MSUB 2 Subquery Marker 101
 MTR1 2 Metrics: Comments Ratio Comments: 11% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 11dec - 6exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 66 6

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_jdatatype_info  
reads table sybsystemprocs..systypes  
read_writes table tempdb..#tmp_owner (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_ijdbc_escapeliteralforlike  
writes table tempdb..#tmp_getversioncolumns (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)