Database | Proc | Application | Created | Links |
sybsystemprocs | sp_jdbc_getfunctioncolumns ![]() | ![]() | 31 Aug 14 | Defects 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 | |
![]() | 194 |
![]() | 195 |
![]() | 294 |
![]() | 295 |
![]() | 50 |
![]() | 52 |
![]() | 53 |
![]() | 188 |
![]() | 288 |
![]() (id, number, type, sequence) Intersection: {number} | 95 |
![]() (id, number, type, sequence) Intersection: {number} | 190 |
![]() (id, number, colid) Intersection: {number} | 201 |
![]() (id, number, type, sequence) Intersection: {number} | 216 |
![]() (id, number, type, sequence) Intersection: {number} | 290 |
![]() (id, number, colid) Intersection: {number} | 301 |
![]() | 95 |
![]() | 95 |
![]() | 190 |
![]() | 190 |
![]() | 202 |
![]() | 202 |
![]() | 216 |
![]() | 216 |
![]() | 290 |
![]() | 290 |
![]() | 302 |
![]() | 302 |
![]() | sybsystemprocs..spt_jdbc_datatype_info |
![]() | tempdb..temptable_D1 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 61 |
![]() | 4 |
![]() | 63 |
![]() | 71 |
![]() | 81 |
![]() | 82 |
![]() | 83 |
![]() | 116 |
![]() | 91 |
![]() | 212 |
![]() | 91 |
![]() | 212 |
![]() | 37 |
![]() | 192 |
![]() | 292 |
![]() | 91 |
![]() | 68 |
![]() | 91 |
![]() | 88 |
![]() | 94 |
![]() | 189 |
![]() | 215 |
![]() | 289 |
![]() | 4 |
![]() | 4 |
![]() | 4 |
![]() | 91 |
![]() | 91 |
![]() | 94 |
![]() | 94 |
![]() | 189 |
![]() | 189 |
![]() | 212 |
![]() | 212 |
![]() | 215 |
![]() | 215 |
![]() | 289 |
![]() | 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 ![]() |