DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_getprocedurecolumns  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **  sp_ijdbc_getprocedurecolumns
4     */
5     
6     
7     create or replace procedure sp_ijdbc_getprocedurecolumns(
8         @sp_qualifier varchar(32) = null, /* stored procedure qualifier*/
9         @sp_owner varchar(32) = null, /* stored procedure owner */
10        @sp_name varchar(257), /* stored procedure name */
11        @column_name varchar(257) = null)
12    as
13        declare @msg varchar(250)
14        declare @group_num int
15        declare @semi_position int
16    
17        if @@trancount = 0
18        begin
19            set chained off
20        end
21    
22        set transaction isolation level 1
23    
24        if @sp_qualifier is not null
25        begin
26            if db_name() != @sp_qualifier
27            begin
28                if @sp_qualifier = ''
29                begin
30                    select @sp_name = ''
31                    select @sp_owner = ''
32                end
33                else
34                begin
35                    /* 
36                    ** 18041, 'Stored Procedure qualifer must be name of
37                    ** current database'
38                    */
39                    exec sp_getmessage 18041, @msg out
40                    raiserror 18041 @msg
41                    return (1)
42                end
43            end
44        end
45        else
46            select @sp_qualifier = db_name()
47    
48        select @semi_position = charindex(';', @sp_name)
49        if (@semi_position > 0)
50        begin /* If group number separator (;) found */
51            select @group_num = convert(int, substring(@sp_name, @semi_position + 1, 2))
52            select @sp_name = substring(@sp_name, 1, @semi_position - 1)
53        end
54        else
55        begin /* No group separator, so default to group number of 1 */
56            select @group_num = 1
57        end
58    
59        if (@sp_owner is null) select @sp_owner = '%'
60        if (@sp_name is null) select @sp_name = '%'
61        if (@column_name is null) select @column_name = '%'
62    
63        /*
64        * build a temporary table for holding the results.
65        * The following is from the JDBC specification at
66        * DatabaseMetaData.getProcedureColumns
67        */
68    
69        delete #tmp_getprocedurecolumns
70    
71        create table #jproccols_res
72        (PROCEDURE_CAT varchar(32) null,
73            PROCEDURE_SCHEM varchar(32) null,
74            PROCEDURE_NAME varchar(257) not null,
75            COLUMN_NAME varchar(257) not null,
76            COLUMN_TYPE smallint not null,
77            DATA_TYPE smallint not null,
78            TYPE_NAME varchar(32) not null,
79            PRECISION int not null,
80            LENGTH int not null,
81            SCALE smallint not null,
82            RADIX smallint not null,
83            NULLABLE smallint not null,
84            REMARKS varchar(255) null,
85            colid int not null /* hidden, used for ordering */
86        )
87    
88        /*
89        * insert defined parameters (if any)
90        */
91        INSERT INTO #jproccols_res
92        SELECT DISTINCT
93            PROCEDURE_CAT = db_name(),
94            PROCEDURE_SCHEM = user_name(o.uid),
95            PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)),
96            COLUMN_NAME = c.name,
97            COLUMN_TYPE = convert(smallint, 0), /*No distinction possible in SQL Server */
98            DATA_TYPE = jdt.data_type,
99            TYPE_NAME = jdt.type_name,
100           PRECISION = (isnull(convert(int, c.prec),
101               isnull(convert(int, jdt.data_precision),
102                   convert(int, c.length)))
103           + isnull(jdt.aux, convert(int,
104               ascii(substring('???AAAFFFCKFOLS',
105                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))),
106           LENGTH = (isnull(convert(int, c.length),
107               convert(int, jdt.length)) +
108           convert(int, isnull(jdt.aux,
109               ascii(substring('AAA<BB<DDDHJSPP',
110                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
111                       1)) - 64))),
112           SCALE = (isnull(isnull(convert(smallint, c.scale),
113                   convert(smallint, jdt.numeric_scale)), 0) +
114           convert(smallint, isnull(jdt.aux,
115               ascii(substring('<<<<<<<<<<<<<<?',
116                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
117                       1)) - 60))),
118           RADIX = convert(smallint, 0),
119           NULLABLE = convert(smallint, 2), /* procedureNullableUnknown */
120           REMARKS = printfmt,
121           colid = c.colid /* parameter position order */
122       FROM syscolumns c, sysobjects o, sysusers u, sysprocedures p,
123           sybsystemprocs.dbo.spt_jdatatype_info jdt
124       WHERE jdt.ss_dtype = c.type
125           and c.id = o.id and p.id = o.id
126           and user_name(o.uid) like @sp_owner ESCAPE '\'
127           and u.uid = user_id()
128           and o.type = 'P'
129           and o.name like @sp_name ESCAPE '\'
130           and c.name like @column_name ESCAPE '\'
131           and c.number = @group_num
132           and p.number = @group_num
133   
134   
135       /*
136       * add the 'return parameter'
137       */
138       INSERT INTO #jproccols_res
139       SELECT DISTINCT
140           PROCEDURE_CAT = db_name(),
141           PROCEDURE_SCHEM = user_name(o.uid),
142           PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)),
143           COLUMN_NAME = 'RETURN_VALUE',
144           COLUMN_TYPE = convert(smallint, 5), /* procedureColumnReturn */
145           DATA_TYPE = jdt.data_type,
146           TYPE_NAME = jdt.type_name,
147           PRECISION = (isnull(convert(int, jdt.data_precision),
148               convert(int, jdt.length))
149           + isnull(jdt.aux, convert(int,
150               ascii(substring('???AAAFFFCKFOLS',
151                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / jdt.length, 1)) - 60))),
152           LENGTH = (isnull(jdt.length, convert(int, t.length)) +
153           convert(int, isnull(jdt.aux,
154               ascii(substring('AAA<BB<DDDHJSPP',
155                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / t.length,
156                       1)) - 64))),
157           SCALE = (convert(smallint, jdt.numeric_scale) +
158           convert(smallint, isnull(jdt.aux,
159               ascii(substring('<<<<<<<<<<<<<<?',
160                       2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / jdt.length,
161                       1)) - 60))),
162           RADIX = convert(smallint, 0),
163           NULLABLE = convert(smallint, 0), /* procedureNoNulls */
164           REMARKS = 'procedureColumnReturn',
165           colid = 0 /* always the first parameter */
166       FROM sybsystemprocs.dbo.spt_jdatatype_info jdt,
167           sysobjects o, sysusers u, sysprocedures p,
168           systypes t
169       WHERE jdt.ss_dtype = 56 /* return parameter is an int */
170           and t.type = jdt.ss_dtype
171           and p.id = o.id
172           and user_name(o.uid) like @sp_owner ESCAPE '\'
173           and u.uid = user_id()
174           and o.type = 'P'
175           and o.name like @sp_name ESCAPE '\'
176           and 'RETURN_VALUE' like @column_name ESCAPE '\'
177           and p.number = @group_num
178   
179   
180       /*
181       * return the data to the client
182       */
183       insert #tmp_getprocedurecolumns
184       SELECT PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_NAME,
185           COLUMN_TYPE, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE,
186           RADIX, NULLABLE, REMARKS
187       FROM #jproccols_res
188       ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, colid
189   /*
190   * cleanup
191   */
192   


exec sp_procxmode 'sp_ijdbc_getprocedurecolumns', 'AnyMode'
go

Grant Execute on sp_ijdbc_getprocedurecolumns to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 124
 QJWI 5 Join or Sarg Without Index 170
 MEST 4 Empty String will be replaced by Single Space 28
 MEST 4 Empty String will be replaced by Single Space 30
 MEST 4 Empty String will be replaced by Single Space 31
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns c and [sybsystemprocs..sysusers u], 1 tables with rc=1 92
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..spt_jdatatype_info jdt and [sybsystemprocs..sysobjects o, sybsystempr... 139
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
130
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
132
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
177
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 131
 QTYP 4 Comparison type mismatch smallint = int 131
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch smallint = int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 177
 QTYP 4 Comparison type mismatch smallint = int 177
 TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info sybsystemprocs..spt_jdatatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 125
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 125
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 125
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 171
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_getprocedurecolumns  
 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..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 39
 MNER 3 No Error Check should check @@error after delete 69
 MNER 3 No Error Check should check @@error after insert 91
 MNER 3 No Error Check should check @@error after insert 138
 MNER 3 No Error Check should check @@error after insert 183
 MUCO 3 Useless Code Useless Brackets in create proc 7
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 61
 QDIS 3 Check correct use of 'select distinct' 92
 QDIS 3 Check correct use of 'select distinct' 139
 QGWO 3 Group by/Distinct/Union without order by 92
 QGWO 3 Group by/Distinct/Union without order by 139
 QISO 3 Set isolation level 22
 QNAJ 3 Not using ANSI Inner Join 122
 QNAJ 3 Not using ANSI Inner Join 166
 QNUA 3 Should use Alias: Column printfmt should use alias c 120
 QPNC 3 No column in condition 176
 VNRD 3 Variable is not read @sp_qualifier 46
 MTR1 2 Metrics: Comments Ratio Comments: 12% 7
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 9dec - 2exi + 2 7
 MTR3 2 Metrics: Query Complexity Complexity: 75 7
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures, sji=sybsystemprocs..spt_jdatatype_info} 0 92
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures, sji=sybsystemprocs..spt_jdatatype_info, t=sybsystemprocs..systypes} 0 139

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