DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getprocedurecolumns  14 déc. 14Defects Propagation Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     
4     create or replace procedure sp_jdbc_getprocedurecolumns(
5         @sp_qualifier varchar(32) = null, /* stored procedure qualifier*/
6         @sp_owner varchar(32) = null, /* stored procedure owner */
7         @sp_name varchar(771), /* stored procedure name */
8         @column_name varchar(771) = null,
9         @parammetadata int = 0, /* Is the call from getParamMetadata() ? */
10        @version int = null, /* Comform to JDBC 4.0 spec if @version is not null */
11        @paramcolids varchar(1000) = null, /* parameter ids in the format 1,2,3...*/
12        @paramnames varchar(1000) = null /* parameter names in the format '@p0','@p1','@p2' */
13    
14    /* @paramcolids Is this variable length enough?? */
15    /* @paramnames Is this variable length enough?? */
16    )
17    as
18        declare @msg varchar(250)
19        declare @group_num int
20        declare @semi_position int
21    
22        declare @outer_select varchar(350)
23        declare @from varchar(6)
24        declare @main_select_p1 varchar(1800)
25        declare @main_select_p2 varchar(1500)
26        declare @main_select_p3 varchar(3000)
27        declare @main_select_p4 varchar(2000) /* Is this variable length enough?? */
28        declare @union varchar(7)
29        declare @return_value_select_p1 varchar(1250)
30        declare @return_value_select_p2 varchar(2500)
31        declare @derivedTableName varchar(20)
32        declare @orderby1 varchar(60)
33        declare @orderby2 varchar(40)
34        declare @orderCond varchar(120)
35        declare @orderTable varchar(2000) /* Is this variable length enough?? */
36        declare @char_bin_types varchar(30)
37        declare @uni_types varchar(10)
38    
39        /* character and binary datatypes */
40        select @char_bin_types =
41            char(47) + char(39) + char(45) + char(37) + char(35) + char(34)
42    
43        /* unichar, univarchar and unitext datatypes */
44        /* Note that the actual type numbers are 155 (unichar), 135 (univarchar)
45        and 174 (unitext), but because of issues that can arise when a server
46        has utf-8 as the default charset and a non-binary sort order, we need
47        to create a character string that is valid in utf-8. Therefore we
48        apply an offset of 60 to move the characters to be valid utf-8 chars.
49        The stored proc later does a similar calculation to utilize these
50        values properly*/
51        select @uni_types =
52            char(95) + char(75) + char(114)
53    
54    
55        if @@trancount = 0
56        begin
57            set chained off
58        end
59    
60        set transaction isolation level 1
61        /* this will make sure that all rows are sent even if
62        ** the client "set rowcount" is differect
63        */
64    
65        set rowcount 0
66    
67        if @sp_qualifier is not null
68        begin
69            if db_name() != @sp_qualifier
70            begin
71                if @sp_qualifier = ''
72                begin
73                    select @sp_name = ''
74                    select @sp_owner = ''
75                end
76                else
77                begin
78                    /* 
79                    ** 18041, 'Stored Procedure qualifer must be name of
80                    ** current database'
81                    */
82                    exec sp_getmessage 18041, @msg out
83                    raiserror 18041 @msg
84                    return (1)
85                end
86            end
87        end
88        else
89            select @sp_qualifier = db_name()
90    
91        select @semi_position = charindex(';', @sp_name)
92        if (@semi_position > 0)
93        begin /* If group number separator (;) found */
94            select @group_num = convert(int, substring(@sp_name, @semi_position + 1, 2))
95            select @sp_name = substring(@sp_name, 1, @semi_position - 1)
96        end
97        else
98        begin /* No group separator, so default to group number of 1 */
99            select @group_num = 1
100       end
101   
102       if (@sp_owner is null) select @sp_owner = '%'
103       if (@sp_name is null) select @sp_name = '%'
104       if (@column_name is null) select @column_name = '%'
105   
106       declare @colcount int
107       declare @tmpParamColids varchar(500)
108       declare @tmpParamNames varchar(500)
109       select @colcount = 1
110       select @tmpParamColids = @paramcolids
111       select @tmpParamNames = @paramnames
112   
113       select @orderTable = '('
114       if (@tmpParamColids is not null and @tmpParamNames is not null)
115       begin
116           WHILE (CHARINDEX(',', @tmpParamColids) > 0)
117           BEGIN
118               select @orderTable = @orderTable + 'select colname=''' +
119                   SUBSTRING(@tmpParamColids, 1, CHARINDEX(',', @tmpParamColids) - 1) +
120                   ''',colnumber=' + convert(varchar, @colcount) + ' union '
121   
122               select @tmpParamColids = SUBSTRING(@tmpParamColids,
123                       CHARINDEX(',', @tmpParamColids) + 1, LEN(@tmpParamColids))
124   
125               select @colcount = @colcount + 1
126           END
127           select @orderTable = @orderTable + 'select colname=''' + @tmpParamColids +
128               ''',colnumber=' + convert(varchar, @colcount) + ' union '
129   
130           select @colcount = @colcount + 1
131       end
132   
133       if (@tmpParamNames is not null)
134       begin
135   
136           WHILE (CHARINDEX(',', @tmpParamNames) > 0)
137           BEGIN
138               select @orderTable = @orderTable + 'select colname = ' +
139                   SUBSTRING(@tmpParamNames, 1, CHARINDEX(',', @tmpParamNames) - 1) +
140                   ', colnumber = ' + convert(varchar, @colcount) + ' union '
141   
142               select @tmpParamNames = SUBSTRING(@tmpParamNames,
143                       CHARINDEX(',', @tmpParamNames) + 1, LEN(@tmpParamNames))
144   
145               select @colcount = @colcount + 1
146           END
147           select @orderTable = @orderTable + 'select colname = ' + @tmpParamNames +
148               ', colnumber = ' + convert(varchar, @colcount)
149       end
150   
151       select @orderTable = @orderTable + ')'
152   
153       /*
154       * select defined parameters (if any)
155       */
156       select @outer_select = "select PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_NAME, 
157                           COLUMN_TYPE, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE,
158                           RADIX, NULLABLE, REMARKS "
159       if (@parammetadata = 0 and @version is not null)
160       begin
161           select @outer_select = @outer_select + " , COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, 
162                                   CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE, SPECIFIC_NAME "
163       end
164       if (@parammetadata = 1)
165       begin
166           select @outer_select = @outer_select + " , SS_DATA_TYPE "
167       end
168       select @from = " from "
169   
170       select @main_select_p1 = "
171   SELECT DISTINCT
172       PROCEDURE_CAT   = db_name(),
173       PROCEDURE_SCHEM = user_name(o.uid),
174       PROCEDURE_NAME  = (select object_name(p.id) + ';' + ltrim(str(p.number,5)) 
175                         from sysprocedures p where p.id = o.id and p.number = @group_num 
176                         group by p.id,p.number),
177       COLUMN_NAME      = convert(varchar(255), c.name),"
178       /* Note that that ASE 12.5+ places a 2 in 
179       syscolumns.status2 (to signify an out-only param) when
180       a parameter is declared as an output param in a
181       Transact-SQL stored proc. This is despite the fact that
182       a TSQL out param can be used for both input and output
183       values. The reason for this is that the status2 column
184       value is based on the text used to create the
185       procedure.
186       SQLJ procedures*do* have out-only params, as well as
187       inout params. Therefore, the status2 column for SQLJ
188       proc params will accurately reflect the paramter usage.
189       In any case, the below COLUMN_TYPE code accounts for
190       these quirks by differentiating between SQLJ procs
191       (the case branch with the 0x2000000 in it) and TSQL
192       procs.*/
193       select @main_select_p1 = @main_select_p1 + "COLUMN_TYPE     = case 
194                          when c.status2 is NULL then 0
195                          when (o.sysstat2 & hextoint('0x2000000') != 0) then
196                              (ascii(substring('AD>B>>>E', c.status2, 1)) -64)
197                          else (ascii(substring('AB>B>>>E', c.status2, 1))
198                          - 64)
199                         end, "
200   
201       /* End of @main_select_p1 variable */
202   
203       select @main_select_p2 =
204           "DATA_TYPE       = jdt.data_type,
205        TYPE_NAME       = 
206                           case
207                               when t.name = 'usmallint' then 'unsigned smallint'
208                               when t.name = 'uint' then 'unsigned int'
209                               when t.name = 'ubigint' then 'unsigned bigint'
210                           else
211                               t.name
212                           end,
213       'PRECISION'     = (isnull(convert(int, c.prec),
214                         isnull(convert(int, jdt.data_precision),
215                         convert(int, c.length)))
216                         +isnull(jdt.aux, convert(int,
217                         ascii(substring('???AAAFFFCKFOLS',
218                         2*(jdt.ss_dtype%35+1)+2-8/c.length,1))-60))),    
219       LENGTH          = (isnull(convert(int, c.length), 
220                         convert(int, jdt.length)) +
221                         convert(int, isnull(jdt.aux,
222                         ascii(substring('AAA<BB<DDDHJSPP',
223                         2*(jdt.ss_dtype%35+1)+2-8/c.length,
224                         1))-64))),
225       SCALE           = (isnull(isnull(convert(smallint, c.scale), 
226                         convert(smallint, jdt.numeric_scale)), 0) +
227                         convert(smallint, isnull(jdt.aux,
228                         ascii(substring('<<<<<<<<<<<<<<?',
229                         2*(jdt.ss_dtype%35+1)+2-8/c.length,
230                         1))-60))), "
231   
232       /* End of @main_select_p2 variable */
233   
234       select @main_select_p3 =
235           "RADIX           = convert(smallint, 0), 
236       NULLABLE        = case
237                             when @parammetadata = 0 then convert(smallint, 2)
238                             /* procedureNullableUnknown */
239                         else
240                             convert(smallint, convert(bit, c.status&8))
241                             /* set nullability from status flag */
242                         end,  
243       REMARKS         = convert(varchar, c.printfmt),
244       SS_DATA_TYPE    = convert(tinyint,jdt.ss_dtype),
245       colid           = c.colid, /* parameter position order */
246       COLUMN_DEF      = null,
247       SQL_DATA_TYPE   = 0, /* future use */
248       SQL_DATETIME_SUB = 0," /* future use */
249   
250       /* CHAR_OCTET_LENGTH */
251       /*
252       ** if the datatype is of type CHAR or BINARY
253       ** then set char_octet_length to the same value
254       ** assigned in the "prec" column.
255       **
256       ** The first part of the logic is:
257       **
258       **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
259       **       set char_octet_length = prec;
260       **   else
261       **       set char_octet_length = null;
262       */
263       /*
264       ** check if in the list
265       ** if so, return a 1 and multiply it by the precision 
266       ** if not, return a null 
267       */
268       select @main_select_p3 = @main_select_p3 + "  CHAR_OCTET_LENGTH = case 
269                   when 
270                       convert(smallint, 
271                       substring('0111111', 
272                           charindex(char(c.type), @char_bin_types) +
273                           charindex(char(c.type-60), @uni_types) + 1, 1)) = 0
274                   then null
275                   /* calculate the precision */
276                   else
277                   isnull(convert(int, c.prec),
278                       isnull(convert(int, jdt.data_precision),
279                           convert(int,c.length)))
280                       +isnull(jdt.aux, convert(int,
281                           ascii(substring('???AAAFFFCKFOLS',
282                               2*(jdt.ss_dtype%35+1)+2-8/c.length,1))-60))
283                   end,
284   
285   
286       ORDINAL_POSITION = c.colid,
287       IS_NULLABLE = '',
288       SPECIFIC_NAME = (select object_name(p.id) + ';' + ltrim(str(p.number,5))
289                            from sysprocedures p where p.id = o.id and p.number = @group_num
290                            group by p.id,p.number)
291   FROM syscolumns c, sysobjects o,
292        sybsystemprocs.dbo.spt_jdbc_datatype_info jdt, systypes t
293   
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 @sp_owner ESCAPE '\'
299       and o.type ='P'
300       and o.name like @sp_name ESCAPE '\'
301       and c.number = @group_num "
302   
303       /* End of @main_select_p3 variable */
304   
305       if (@column_name is not null)
306       begin
307           select @main_select_p4 = " and c.name like @column_name ESCAPE '\' "
308       end
309   
310       if (@paramcolids is not null and @paramnames is not null)
311       begin
312           select @main_select_p4 = @main_select_p4 + " and (c.colid in (" + @paramcolids + ") OR c.name in (" + @paramnames + "))"
313       end
314       else
315       begin
316           if (@paramnames is not null)
317           begin
318               select @main_select_p4 = @main_select_p4 + " and c.name in (" + @paramnames + ")"
319           end
320           else
321           begin
322               if (@paramcolids is not null)
323               begin
324                   select @main_select_p4 = @main_select_p4 + " and c.colid in (" + @paramcolids + ")"
325               end
326           end
327       end
328   
329       if (@parammetadata = 0 or (@paramcolids is not null and substring(@paramcolids, 1, 1) = '0'))
330       begin
331           select @union = " UNION "
332   
333           select @return_value_select_p1 = "
334           /*
335            * add the 'return parameter'
336            */        
337           SELECT DISTINCT
338               PROCEDURE_CAT   = db_name(),
339               PROCEDURE_SCHEM = user_name(o.uid),
340               PROCEDURE_NAME  = (select object_name(p.id) + ';' + ltrim(str(p.number,5))
341                                 from sysprocedures p where p.id = o.id and p.number = @group_num
342                                 group by p.id,p.number),
343               COLUMN_NAME     = convert(varchar,'RETURN_VALUE'),
344               COLUMN_TYPE     = convert(smallint, case @parammetadata 
345                                                       when 1 then 4 /* parameterModeOut */
346                                                       else 5 /* procedureColumnReturn */
347                                                   end),
348               DATA_TYPE       = jdt.data_type,
349               TYPE_NAME       = jdt.type_name,
350               'PRECISION'     = (isnull(convert(int, jdt.data_precision),
351                                 convert(int, jdt.length))
352                                 +isnull(jdt.aux, convert(int,
353                                 ascii(substring('???AAAFFFCKFOLS',
354                                 2*(jdt.ss_dtype%35+1)+2-8/jdt.length,1))-60))),"
355   
356           /* End of variable @return_value_select_p1 */
357   
358           select @return_value_select_p2 = "
359               LENGTH          = (isnull(jdt.length, convert(int, t.length)) +
360                                 convert(int, isnull(jdt.aux,
361                                 ascii(substring('AAA<BB<DDDHJSPP',
362                                 2*(jdt.ss_dtype%35+1)+2-8/t.length,
363                                 1))-64))),
364               SCALE           = (convert(smallint, jdt.numeric_scale) +
365                                 convert(smallint, isnull(jdt.aux,
366                                 ascii(substring('<<<<<<<<<<<<<<?',
367                                 2*(jdt.ss_dtype%35+1)+2-8/jdt.length,
368                                 1))-60))),    
369               RADIX           = convert(smallint, 0), 
370               NULLABLE        = convert(smallint, 0), /* procedureNoNulls */
371               REMARKS         = convert(varchar, 'procedureColumnReturn'),
372               SS_DATA_TYPE    = convert(tinyint,jdt.ss_dtype),
373               colid           = 0, /* always the first parameter */
374               COLUMN_DEF      = null,  /* Not stored in any system table */
375               SQL_DATA_TYPE   = 0, /* Future use */
376               SQL_DATETIME_SUB= 0, /* Future use */
377               CHAR_OCTET_LENGTH = null, /* Integer type this shud be null */
378               ORDINAL_POSITION = 0, /* return value */
379               IS_NULLABLE = 'NO',
380               SPECIFIC_NAME   = (select object_name(p.id) + ';' + ltrim(str(p.number,5))
381                                 from sysprocedures p where p.id = o.id and p.number = @group_num
382                                 group by p.id,p.number)
383           FROM sybsystemprocs.dbo.spt_jdbc_datatype_info jdt, sysobjects o,systypes t
384           WHERE jdt.ss_dtype = 56 /* return parameter is an int */
385               and t.type = jdt.ss_dtype
386               and user_name(o.uid) like @sp_owner ESCAPE '\'
387               and o.type ='P'
388               and o.name like @sp_name ESCAPE '\'
389               and 'RETURN_VALUE' like @column_name ESCAPE '\' "
390       /* End of variable return_value_select_p2 */
391   
392       end
393   
394       select @derivedTableName = " procedureColumns "
395   
396       select @orderby1 = " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, colid "
397       select @orderCond = " where procedureColumns.COLUMN_NAME = orderTable.colname or convert(varchar, colid) = orderTable.colname "
398       select @orderby2 = " ORDER BY orderTable.colnumber"
399   
400       if (@parammetadata = 0)
401       begin
402           execute (@outer_select + @from + '( ' + @main_select_p1 +
403           @main_select_p2 + @main_select_p3 + @main_select_p4 + @union + @return_value_select_p1 +
404           @return_value_select_p2 + ' ) ' + @derivedTableName + @orderby1)
405       end
406       else
407       begin
408           if (@paramnames is not null)
409           begin
410               execute (@outer_select + @from + '( ' + @main_select_p1 +
411               @main_select_p2 + @main_select_p3 + @main_select_p4 + @union + @return_value_select_p1 +
412               @return_value_select_p2 + ' ) ' + @derivedTableName + ',' + @orderTable +
413               ' orderTable ' + @orderCond + @orderby2)
414           end
415           else
416           if (@paramcolids is not null)
417           begin
418               execute (@outer_select + @from + '( ' + @main_select_p1 +
419               @main_select_p2 + @main_select_p3 + @main_select_p4 + @union + @return_value_select_p1 +
420               @return_value_select_p2 + ' ) ' + @derivedTableName + @orderby1)
421           end
422           else
423               select PROCEDURE_CAT = '', PROCEDURE_SCHEM = '', PROCEDURE_NAME = '', COLUMN_NAME = '',
424                   COLUMN_TYPE = 0, DATA_TYPE = 0, TYPE_NAME = '', PRECISION = 0, LENGTH = 0, SCALE = 0,
425                   RADIX = 0, NULLABLE = 0, REMARKS = '', SS_DATA_TYPE = 0 where 1 = 2
426       end
427   
428   


exec sp_procxmode 'sp_jdbc_getprocedurecolumns', 'AnyMode'
go

Grant Execute on sp_jdbc_getprocedurecolumns to public
go
RESULT SETS
sp_jdbc_getprocedurecolumns_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 71
 MEST 4 Empty String will be replaced by Single Space 73
 MEST 4 Empty String will be replaced by Single Space 74
 MEST 4 Empty String will be replaced by Single Space 423
 MEST 4 Empty String will be replaced by Single Space 424
 MEST 4 Empty String will be replaced by Single Space 425
 MTYP 4 Assignment type mismatch @tmpParamColids: varchar(500) = varchar(1000) 110
 MTYP 4 Assignment type mismatch @tmpParamNames: varchar(500) = varchar(1000) 111
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 4
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getprocedurecolumns  
 MNER 3 No Error Check should check return value of exec 82
 MUCO 3 Useless Code Useless Brackets in create proc 4
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 316
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 416
 QCRS 3 Conditional Result Set 423
 QISO 3 Set isolation level 60
 QPNC 3 No column in condition 425
 VNRD 3 Variable is not read @char_bin_types 40
 VNRD 3 Variable is not read @uni_types 51
 VNRD 3 Variable is not read @sp_qualifier 89
 VNRD 3 Variable is not read @group_num 99
 VNRD 3 Variable is not read @sp_owner 102
 VNRD 3 Variable is not read @sp_name 103
 MDYS 2 Dynamic SQL Marker 402
 MDYS 2 Dynamic SQL Marker 410
 MDYS 2 Dynamic SQL Marker 418
 MRST 2 Result Set Marker 423
 MTR1 2 Metrics: Comments Ratio Comments: 17% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 28dec - 2exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 129 4

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
writes table sybsystemprocs..sp_jdbc_getprocedurecolumns_rset_001