DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getfunctioncolumns  31 Aug 14Defects Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     
4     create procedure sp_jdbc_getfunctioncolumns(
5         @fn_qualifier varchar(32) = null, /* function qualifier*/
6         @fn_owner varchar(32) = null, /* function owner */
7         @fn_name varchar(771), /* function name */
8         @column_name varchar(771) = null)
9     as
10        declare @msg varchar(250)
11        declare @group_num int
12        declare @semi_position int
13        declare @char_bin_types varchar(30)
14        declare @uni_types varchar(10)
15    
16        /* character and binary datatypes */
17        select @char_bin_types =
18            char(47) + char(39) + char(45) + char(37) + char(35) + char(34)
19    
20        /* unichar, univarchar and unitext datatypes */
21        /* Note that the actual type numbers are 155 (unichar), 135 (univarchar)
22        and 174 (unitext), but because of issues that can arise when a server
23        has utf-8 as the default charset and a non-binary sort order, we need
24        to create a character string that is valid in utf-8. Therefore we
25        apply an offset of 60 to move the characters to be valid utf-8 chars.
26        The stored proc later does a similar calculation to utilize these
27        values properly*/
28        select @uni_types =
29            char(95) + char(75) + char(114)
30    
31    
32        if @@trancount = 0
33        begin
34            set chained off
35        end
36    
37        set transaction isolation level 1
38        /* this will make sure that all rows are sent even if
39        ** the client "set rowcount" is differect
40        */
41    
42        set rowcount 0
43    
44    
45    
46        if @fn_qualifier is not null
47        begin
48            if db_name() != @fn_qualifier
49            begin
50                if @fn_qualifier = ''
51                begin
52                    select @fn_name = ''
53                    select @fn_owner = ''
54                end
55                else
56                begin
57                    /* 
58                    ** 18041, 'Stored Procedure qualifer must be name of
59                    ** current database'
60                    */
61                    exec sp_getmessage 18041, @msg out
62                    raiserror 18041 @msg
63                    return (1)
64                end
65            end
66        end
67        else
68            select @fn_qualifier = db_name()
69    
70        select @semi_position = charindex(';', @fn_name)
71        if (@semi_position > 0)
72        begin /* If group number separator (;) found */
73            select @group_num = convert(int, substring(@fn_name, @semi_position + 1, 2))
74            select @fn_name = substring(@fn_name, 1, @semi_position - 1)
75        end
76        else
77        begin /* No group separator, so default to group number of 0 */
78            select @group_num = 0
79        end
80    
81        if (@fn_owner is null) select @fn_owner = '%'
82        if (@fn_name is null) select @fn_name = '%'
83        if (@column_name is null) select @column_name = '%'
84    
85        /*
86        * insert defined parameters (if any)
87        */
88        SELECT FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, COLUMN_NAME, COLUMN_TYPE, DATA_TYPE,
89            TYPE_NAME, PRECISION, LENGTH, SCALE, RADIX, NULLABLE, REMARKS, CHAR_OCTET_LENGTH,
90            ORDINAL_POSITION, IS_NULLABLE, SPECIFIC_NAME FROM
91            (SELECT DISTINCT
92                FUNCTION_CAT = db_name(),
93                FUNCTION_SCHEM = user_name(o.uid),
94                FUNCTION_NAME = (select user_name(o.uid) + '.' + object_name(p.id)
95                    from sysprocedures p where p.id = o.id and p.number = @group_num
96                    group by p.id, p.number),
97                COLUMN_NAME = c.name,
98                /* Note that that ASE 12.5+ places a 2 in 
99                syscolumns.status2 (to signify an out-only param) when
100               a parameter is declared as an output param in a
101               Transact-SQL stored proc. This is despite the fact that
102               a TSQL out param can be used for both input and output
103               values. The reason for this is that the status2 column
104               value is based on the text used to create the
105               procedure.
106               SQLJ procedures*do* have out-only params, as well as
107               inout params. Therefore, the status2 column for SQLJ
108               proc params will accurately reflect the paramter usage.
109               In any case, the below COLUMN_TYPE code accounts for
110               these quirks by differentiating between SQLJ procs
111               (the case branch with the 0x2000000 in it) and TSQL
112               procs.*/
113   
114               COLUMN_TYPE = case
115                   when c.status2 is NULL then 0
116                   when (o.sysstat2 & hextoint('0x2000000') != 0) then
117                   (ascii(substring('AD>B>>>E', c.status2, 1)) - 64)
118                   else (ascii(substring('AB>B>>>E', c.status2, 1))
119                       - 64)
120               end,
121   
122               DATA_TYPE = jdt.data_type,
123               TYPE_NAME =
124               case
125                   when t.name = 'usmallint' then 'unsigned smallint'
126                   when t.name = 'uint' then 'unsigned int'
127                   when t.name = 'ubigint' then 'unsigned bigint'
128                   else
129                       t.name
130               end,
131               'PRECISION' = (isnull(convert(int, c.prec),
132                   isnull(convert(int, jdt.data_precision),
133                       convert(int, c.length)))
134               + isnull(jdt.aux, convert(int,
135                   ascii(substring('???AAAFFFCKFOLS',
136                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))),
137               LENGTH = (isnull(convert(int, c.length),
138                   convert(int, jdt.length)) +
139               convert(int, isnull(jdt.aux,
140                   ascii(substring('AAA<BB<DDDHJSPP',
141                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
142                           1)) - 64))),
143               SCALE = (isnull(isnull(convert(smallint, c.scale),
144                       convert(smallint, jdt.numeric_scale)), 0) +
145               convert(smallint, isnull(jdt.aux,
146                   ascii(substring('<<<<<<<<<<<<<<?',
147                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
148                           1)) - 60))),
149               RADIX = convert(smallint, 0),
150               NULLABLE = convert(smallint, 2), /*functionNullableUnknown*/
151               REMARKS = c.printfmt,
152               /* CHAR_OCTET_LENGTH */
153               /*
154               ** if the datatype is of type CHAR or BINARY
155               ** then set char_octet_length to the same value
156               ** assigned in the "prec" column.
157               **
158               ** The first part of the logic is:
159               **
160               **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
161               **       set char_octet_length = prec;
162               **   else
163               **       set char_octet_length = null;
164               */
165               /*
166               ** check if in the list
167               ** if so, return a 1 and multiply it by the precision 
168               ** if not, return a null 
169               */
170               CHAR_OCTET_LENGTH = case
171                   when
172                       convert(smallint,
173                       substring('0111111',
174                           charindex(char(c.type), @char_bin_types) +
175                           charindex(char(c.type - 60), @uni_types) + 1, 1)) = 0
176                   then null
177                   /* calculate the precision */
178                   else
179                       isnull(convert(int, c.prec),
180                           isnull(convert(int, jdt.data_precision),
181                               convert(int, c.length)))
182                       + isnull(jdt.aux, convert(int,
183                           ascii(substring('???AAAFFFCKFOLS',
184                                   2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
185               end,
186   
187               ORDINAL_POSITION = c.colid, /* parameter position order */
188               IS_NULLABLE = '',
189               SPECIFIC_NAME = (select object_name(p.id) + ';' + ltrim(str(p.number, 5))
190                   from sysprocedures p where p.id = o.id and p.number = @group_num
191                   group by p.id, p.number)
192           FROM syscolumns c, sysobjects o,
193               sybsystemprocs.dbo.spt_jdbc_datatype_info jdt, systypes t
194           WHERE jdt.ss_dtype = c.type
195               and t.type = jdt.ss_dtype
196               and c.usertype = t.usertype
197               and c.id = o.id
198               and user_name(o.uid) like @fn_owner ESCAPE '\'
199               and o.type = 'SF'
200               and o.name like @fn_name ESCAPE '\'
201               and c.name like @column_name ESCAPE '\'
202               and c.number = @group_num
203               and c.name <> 'Return Type'
204           /* selected all parameters */
205   
206           UNION
207   
208           /*
209           * add the 'return parameter'
210           */
211   
212           SELECT DISTINCT
213               FUNCTION_CAT = db_name(),
214               FUNCTION_SCHEM = user_name(o.uid),
215               FUNCTION_NAME = (select user_name(o.uid) + '.' + object_name(p.id)
216                   from sysprocedures p where p.id = o.id and p.number = @group_num
217                   group by p.id, p.number),
218               COLUMN_NAME = 'RETURN_VALUE',
219               COLUMN_TYPE = convert(smallint, 4), /* functionReturn */
220   
221               DATA_TYPE = jdt.data_type,
222               TYPE_NAME =
223               case
224                   when t.name = 'usmallint' then 'unsigned smallint'
225                   when t.name = 'uint' then 'unsigned int'
226                   when t.name = 'ubigint' then 'unsigned bigint'
227                   else
228                       t.name
229               end,
230               'PRECISION' = (isnull(convert(int, c.prec),
231                   isnull(convert(int, jdt.data_precision),
232                       convert(int, c.length)))
233               + isnull(jdt.aux, convert(int,
234                   ascii(substring('???AAAFFFCKFOLS',
235                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))),
236               LENGTH = (isnull(convert(int, c.length),
237                   convert(int, jdt.length)) +
238               convert(int, isnull(jdt.aux,
239                   ascii(substring('AAA<BB<DDDHJSPP',
240                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
241                           1)) - 64))),
242               SCALE = (isnull(isnull(convert(smallint, c.scale),
243                       convert(smallint, jdt.numeric_scale)), 0) +
244               convert(smallint, isnull(jdt.aux,
245                   ascii(substring('<<<<<<<<<<<<<<?',
246                           2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length,
247                           1)) - 60))),
248               RADIX = convert(smallint, 0),
249               NULLABLE = convert(smallint, 2), /*functionNullableUnknown*/
250               REMARKS = c.printfmt,
251               /* CHAR_OCTET_LENGTH */
252               /*
253               ** if the datatype is of type CHAR or BINARY
254               ** then set char_octet_length to the same value
255               ** assigned in the "prec" column.
256               **
257               ** The first part of the logic is:
258               **
259               **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
260               **       set char_octet_length = prec;
261               **   else
262               **       set char_octet_length = null;
263               */
264               /*
265               ** check if in the list
266               ** if so, return a 1 and multiply it by the precision 
267               ** if not, return a null 
268               */
269               CHAR_OCTET_LENGTH = case
270                   when
271                       convert(smallint,
272                       substring('0111111',
273                           charindex(char(c.type), @char_bin_types) +
274                           charindex(char(c.type - 60), @uni_types) + 1, 1)) = 0
275                   then null
276                   /* calculate the precision */
277                   else
278                       isnull(convert(int, c.prec),
279                           isnull(convert(int, jdt.data_precision),
280                               convert(int, c.length)))
281                       + isnull(jdt.aux, convert(int,
282                           ascii(substring('???AAAFFFCKFOLS',
283                                   2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
284               end,
285   
286   
287               ORDINAL_POSITION = 0, /* Return value is always first*/
288               IS_NULLABLE = '',
289               SPECIFIC_NAME = (select object_name(p.id) + ';' + ltrim(str(p.number, 5))
290                   from sysprocedures p where p.id = o.id and p.number = @group_num
291                   group by p.id, p.number)
292           FROM syscolumns c, sysobjects o,
293               sybsystemprocs.dbo.spt_jdbc_datatype_info jdt, systypes t
294           WHERE jdt.ss_dtype = c.type
295               and t.type = jdt.ss_dtype
296               and c.usertype = t.usertype
297               and c.id = o.id
298               and user_name(o.uid) like @fn_owner ESCAPE '\'
299               and o.type = 'SF'
300               and o.name like @fn_name ESCAPE '\'
301               and c.name like @column_name ESCAPE '\'
302               and c.number = @group_num
303               and c.name = 'Return Type'
304           /* selected return parameter */
305           ) temptable order by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME, ORDINAL_POSITION
306   
307   


exec sp_procxmode 'sp_jdbc_getfunctioncolumns', 'AnyMode'
go

Grant Execute on sp_jdbc_getfunctioncolumns to public
go
RESULT SETS
sp_jdbc_getfunctioncolumns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 194
 QJWI 5 Join or Sarg Without Index 195
 QJWI 5 Join or Sarg Without Index 294
 QJWI 5 Join or Sarg Without Index 295
 MEST 4 Empty String will be replaced by Single Space 50
 MEST 4 Empty String will be replaced by Single Space 52
 MEST 4 Empty String will be replaced by Single Space 53
 MEST 4 Empty String will be replaced by Single Space 188
 MEST 4 Empty String will be replaced by Single Space 288
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
95
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
190
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
201
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
216
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
290
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
301
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 95
 QTYP 4 Comparison type mismatch smallint = int 95
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch smallint = int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 202
 QTYP 4 Comparison type mismatch smallint = int 202
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch smallint = int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 290
 QTYP 4 Comparison type mismatch smallint = int 290
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 302
 QTYP 4 Comparison type mismatch smallint = int 302
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 TNOI 4 Table with no index tempdb..temptable_D1 tempdb..temptable_D1
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getfunctioncolumns  
 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..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check return value of exec 61
 MUCO 3 Useless Code Useless Brackets in create proc 4
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 116
 QDIS 3 Check correct use of 'select distinct' 91
 QDIS 3 Check correct use of 'select distinct' 212
 QGWO 3 Group by/Distinct/Union without order by 91
 QGWO 3 Group by/Distinct/Union without order by 212
 QISO 3 Set isolation level 37
 QNAJ 3 Not using ANSI Inner Join 192
 QNAJ 3 Not using ANSI Inner Join 292
 QUNI 3 Check Use of 'union' vs 'union all' 91
 VNRD 3 Variable is not read @fn_qualifier 68
 MDRV 2 Derived Table Marker 91
 MRST 2 Result Set Marker 88
 MSUC 2 Correlated Subquery Marker 94
 MSUC 2 Correlated Subquery Marker 189
 MSUC 2 Correlated Subquery Marker 215
 MSUC 2 Correlated Subquery Marker 289
 MTR1 2 Metrics: Comments Ratio Comments: 28% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 8dec - 2exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 92 4
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sjdi=sybsystemprocs..spt_jdbc_datatype_info, t=sybsystemprocs..systypes} 0 91
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sjdi=sybsystemprocs..spt_jdbc_datatype_info, t=sybsystemprocs..systypes} 0 91
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 94
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 94
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 189
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 189
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sjdi=sybsystemprocs..spt_jdbc_datatype_info, t=sybsystemprocs..systypes} 0 212
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sjdi=sybsystemprocs..spt_jdbc_datatype_info, t=sybsystemprocs..systypes} 0 212
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 215
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 215
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 289
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 289

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysprocedures  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table sybsystemprocs..spt_jdbc_datatype_info  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..sysobjects