| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_getprocedurecolumns | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 4 create 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 | |
| DEPENDENCIES |
| PROCS AND TABLES USED 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) |
