Database | Proc | Application | Created | Links |
sybsystemprocs | sp_jdbc_datatype_info_cts ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 create procedure sp_jdbc_datatype_info_cts 4 as 5 6 declare @type_name varchar(32) 7 declare @data_type int 8 declare @precision int 9 declare @literal_prefix varchar(32) 10 declare @literal_suffix varchar(32) 11 declare @create_params varchar(32) 12 declare @nullable smallint 13 declare @case_sensitive tinyint 14 declare @searchable smallint 15 declare @unsigned_attribute smallint 16 declare @fixed_prec_scale tinyint 17 declare @auto_increment tinyint 18 declare @local_type_name varchar(32) 19 declare @minimum_scale smallint 20 declare @maximum_scale smallint 21 declare @sql_data_type int 22 declare @sql_datetime_sub int 23 declare @num_prec_radix int 24 declare @interval_precision int 25 declare @startedInTransaction bit 26 27 if @@trancount = 0 28 begin 29 set chained off 30 end 31 32 /* check if we're in a transaction, before we try any select statements */ 33 if (@@trancount > 0) 34 select @startedInTransaction = 1 35 else 36 select @startedInTransaction = 0 37 38 set transaction isolation level 1 39 40 if (@startedInTransaction = 1) 41 save transaction jdbc_keep_temptables_from_tx 42 43 44 /* this will make sure that all rows are sent even if 45 ** the client "set rowcount" is differect 46 */ 47 48 set rowcount 0 49 50 51 create table #jdbc_datatype_info_cts 52 ( 53 TYPE_NAME varchar(32) null, 54 DATA_TYPE smallint null, 55 "PRECISION" int, 56 LITERAL_PREFIX varchar(32) null, 57 LITERAL_SUFFIX varchar(32) null, 58 CREATE_PARAMS varchar(32) null, 59 NULLABLE smallint null, 60 CASE_SENSITIVE tinyint null, 61 SEARCHABLE smallint null, 62 UNSIGNED_ATTRIBUTE tinyint null, 63 FIXED_PREC_SCALE tinyint null, 64 AUTO_INCREMENT tinyint null, 65 LOCAL_TYPE_NAME varchar(32) null, 66 MINIMUM_SCALE smallint null, 67 MAXIMUM_SCALE smallint null, 68 SQL_DATA_TYPE int null, 69 SQL_DATETIME_SUB int null, 70 NUM_PREC_RADIX int null 71 72 ) 73 74 75 76 begin 77 declare jdbc_datatype_info_cursor1 cursor for 78 select /* Real SQL Server data types */ 79 case 80 when t.name = 'usmallint' then 'unsigned smallint' 81 when t.name = 'uint' then 'unsigned int' 82 when t.name = 'ubigint' then 'unsigned bigint' 83 else 84 t.name 85 end, 86 d.data_type, 87 isnull(d.data_precision, convert(int, t.length)), 88 d.literal_prefix, 89 d.literal_suffix, 90 e.create_params, 91 d.nullable, 92 d.case_sensitive, 93 d.searchable, 94 d.unsigned_attribute, 95 d.money, 96 d.auto_increment, 97 d.local_type_name, 98 d.minimum_scale, 99 d.maximum_scale, 100 d.sql_data_type, 101 d.sql_datetime_sub, 102 d.num_prec_radix, 103 d.interval_precision 104 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 105 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 106 where d.ss_dtype = t.type and t.usertype *= e.user_type 107 /* restrict results to 'real' datatypes, exclude float, date and time*/ 108 and t.name not in ('nchar', 'nvarchar', 'sysname', 'timestamp', 'longsysname', 'float', 109 'datetimn', 'floatn', 'intn', 'moneyn', 'unichar', 110 'univarchar', 'daten', 'timen', 'date', 'time', 'uintn') 111 and t.usertype < 100 /* No user defined types */ 112 113 open jdbc_datatype_info_cursor1 114 115 fetch jdbc_datatype_info_cursor1 into 116 @type_name, 117 @data_type, 118 @precision, 119 @literal_prefix, 120 @literal_suffix, 121 @create_params, 122 @nullable, 123 @case_sensitive, 124 @searchable, 125 @unsigned_attribute, 126 @fixed_prec_scale, 127 @auto_increment, 128 @local_type_name, 129 @minimum_scale, 130 @maximum_scale, 131 @sql_data_type, 132 @sql_datetime_sub, 133 @num_prec_radix, 134 @interval_precision 135 136 /** start insert the rows by looping thru the cursors */ 137 while (@@sqlstatus = 0) 138 begin 139 insert into #jdbc_datatype_info_cts values ( 140 /* TYPE_NAME */ 141 @type_name, 142 @data_type, 143 @precision, 144 @literal_prefix, 145 @literal_suffix, 146 @create_params, 147 @nullable, 148 @case_sensitive, 149 @searchable, 150 @unsigned_attribute, 151 @fixed_prec_scale, 152 @auto_increment, 153 @local_type_name, 154 @minimum_scale, 155 @maximum_scale, 156 @sql_data_type, 157 @sql_datetime_sub, 158 @num_prec_radix) 159 160 fetch jdbc_datatype_info_cursor1 into 161 @type_name, 162 @data_type, 163 @precision, 164 @literal_prefix, 165 @literal_suffix, 166 @create_params, 167 @nullable, 168 @case_sensitive, 169 @searchable, 170 @unsigned_attribute, 171 @fixed_prec_scale, 172 @auto_increment, 173 @local_type_name, 174 @minimum_scale, 175 @maximum_scale, 176 @sql_data_type, 177 @sql_datetime_sub, 178 @num_prec_radix, 179 @interval_precision 180 end 181 182 deallocate cursor jdbc_datatype_info_cursor1 183 declare jdbc_datatype_info_cursor2 cursor for 184 select /* SQL Server user data types */ 185 case 186 when t.name = 'usmallint' then 'unsigned smallint' 187 when t.name = 'uint' then 'unsigned int' 188 when t.name = 'ubigint' then 'unsigned bigint' 189 else 190 t.name 191 end, 192 d.data_type, 193 isnull(d.data_precision, convert(int, t.length)), 194 d.literal_prefix, 195 d.literal_suffix, 196 e.create_params, 197 d.nullable, 198 d.case_sensitive, 199 d.searchable, 200 d.unsigned_attribute, 201 d.money, 202 d.auto_increment, 203 t.name, 204 d.minimum_scale, 205 d.maximum_scale, 206 d.sql_data_type, 207 d.sql_datetime_sub, 208 d.num_prec_radix, 209 d.interval_precision 210 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 211 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 212 where d.ss_dtype = t.type and t.usertype *= e.user_type 213 /* Restrict to user defined types (value > 100) and Sybase user defined 214 ** types (listed)*/ 215 and (t.name in ('nchar', 'nvarchar') 216 or t.usertype >= 100) /* User defined types */ 217 218 open jdbc_datatype_info_cursor2 219 220 fetch jdbc_datatype_info_cursor2 into 221 @type_name, 222 @data_type, 223 @precision, 224 @literal_prefix, 225 @literal_suffix, 226 @create_params, 227 @nullable, 228 @case_sensitive, 229 @searchable, 230 @unsigned_attribute, 231 @fixed_prec_scale, 232 @auto_increment, 233 @local_type_name, 234 @minimum_scale, 235 @maximum_scale, 236 @sql_data_type, 237 @sql_datetime_sub, 238 @num_prec_radix, 239 @interval_precision 240 241 /** start insert the rows by looping thru the cursors */ 242 while (@@sqlstatus = 0) 243 begin 244 insert into #jdbc_datatype_info_cts values ( 245 /* TYPE_NAME */ 246 @type_name, 247 @data_type, 248 @precision, 249 @literal_prefix, 250 @literal_suffix, 251 @create_params, 252 @nullable, 253 @case_sensitive, 254 @searchable, 255 @unsigned_attribute, 256 @fixed_prec_scale, 257 @auto_increment, 258 @local_type_name, 259 @minimum_scale, 260 @maximum_scale, 261 @sql_data_type, 262 @sql_datetime_sub, 263 @num_prec_radix) 264 265 fetch jdbc_datatype_info_cursor2 into 266 @type_name, 267 @data_type, 268 @precision, 269 @literal_prefix, 270 @literal_suffix, 271 @create_params, 272 @nullable, 273 @case_sensitive, 274 @searchable, 275 @unsigned_attribute, 276 @fixed_prec_scale, 277 @auto_increment, 278 @local_type_name, 279 @minimum_scale, 280 @maximum_scale, 281 @sql_data_type, 282 @sql_datetime_sub, 283 @num_prec_radix, 284 @interval_precision 285 end 286 287 deallocate cursor jdbc_datatype_info_cursor2 288 declare jdbc_datatype_info_cursor3 cursor for 289 select /* ADD double precision which is floatn internally*/ 290 'double precision', 291 8, 292 15, 293 d.literal_prefix, 294 d.literal_suffix, 295 e.create_params, 296 d.nullable, 297 d.case_sensitive, 298 d.searchable, 299 d.unsigned_attribute, 300 d.money, 301 d.auto_increment, 302 'double precision', 303 d.minimum_scale, 304 d.maximum_scale, 305 d.sql_data_type, 306 d.sql_datetime_sub, 307 d.num_prec_radix, 308 d.interval_precision 309 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 310 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 311 where d.ss_dtype = t.type and t.usertype *= e.user_type 312 and t.name = 'floatn' and t.usertype < 100 313 open jdbc_datatype_info_cursor3 314 315 fetch jdbc_datatype_info_cursor3 into 316 @type_name, 317 @data_type, 318 @precision, 319 @literal_prefix, 320 @literal_suffix, 321 @create_params, 322 @nullable, 323 @case_sensitive, 324 @searchable, 325 @unsigned_attribute, 326 @fixed_prec_scale, 327 @auto_increment, 328 @local_type_name, 329 @minimum_scale, 330 @maximum_scale, 331 @sql_data_type, 332 @sql_datetime_sub, 333 @num_prec_radix, 334 @interval_precision 335 336 /** start insert the rows by looping thru the cursors */ 337 while (@@sqlstatus = 0) 338 begin 339 insert into #jdbc_datatype_info_cts values ( 340 /* TYPE_NAME */ 341 @type_name, 342 @data_type, 343 @precision, 344 @literal_prefix, 345 @literal_suffix, 346 @create_params, 347 @nullable, 348 @case_sensitive, 349 @searchable, 350 @unsigned_attribute, 351 @fixed_prec_scale, 352 @auto_increment, 353 @local_type_name, 354 @minimum_scale, 355 @maximum_scale, 356 @sql_data_type, 357 @sql_datetime_sub, 358 @num_prec_radix) 359 360 fetch jdbc_datatype_info_cursor3 into 361 @type_name, 362 @data_type, 363 @precision, 364 @literal_prefix, 365 @literal_suffix, 366 @create_params, 367 @nullable, 368 @case_sensitive, 369 @searchable, 370 @unsigned_attribute, 371 @fixed_prec_scale, 372 @auto_increment, 373 @local_type_name, 374 @minimum_scale, 375 @maximum_scale, 376 @sql_data_type, 377 @sql_datetime_sub, 378 @num_prec_radix, 379 @interval_precision 380 end 381 382 deallocate cursor jdbc_datatype_info_cursor3 383 declare jdbc_datatype_info_cursor4 cursor for 384 select 385 'float', 386 8, 387 8, 388 d.literal_prefix, 389 d.literal_suffix, 390 e.create_params, 391 d.nullable, 392 d.case_sensitive, 393 d.searchable, 394 d.unsigned_attribute, 395 d.money, 396 d.auto_increment, 397 'float', 398 d.minimum_scale, 399 d.maximum_scale, 400 d.sql_data_type, 401 d.sql_datetime_sub, 402 d.num_prec_radix, 403 d.interval_precision 404 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 405 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 406 where d.ss_dtype = t.type and t.usertype *= e.user_type 407 and t.name = 'float' and t.usertype < 100 408 409 open jdbc_datatype_info_cursor4 410 411 fetch jdbc_datatype_info_cursor4 into 412 @type_name, 413 @data_type, 414 @precision, 415 @literal_prefix, 416 @literal_suffix, 417 @create_params, 418 @nullable, 419 @case_sensitive, 420 @searchable, 421 @unsigned_attribute, 422 @fixed_prec_scale, 423 @auto_increment, 424 @local_type_name, 425 @minimum_scale, 426 @maximum_scale, 427 @sql_data_type, 428 @sql_datetime_sub, 429 @num_prec_radix, 430 @interval_precision 431 432 /** start insert the rows by looping thru the cursors */ 433 while (@@sqlstatus = 0) 434 begin 435 insert into #jdbc_datatype_info_cts values ( 436 /* TYPE_NAME */ 437 @type_name, 438 @data_type, 439 @precision, 440 @literal_prefix, 441 @literal_suffix, 442 @create_params, 443 @nullable, 444 @case_sensitive, 445 @searchable, 446 @unsigned_attribute, 447 @fixed_prec_scale, 448 @auto_increment, 449 @local_type_name, 450 @minimum_scale, 451 @maximum_scale, 452 @sql_data_type, 453 @sql_datetime_sub, 454 @num_prec_radix) 455 456 fetch jdbc_datatype_info_cursor4 into 457 @type_name, 458 @data_type, 459 @precision, 460 @literal_prefix, 461 @literal_suffix, 462 @create_params, 463 @nullable, 464 @case_sensitive, 465 @searchable, 466 @unsigned_attribute, 467 @fixed_prec_scale, 468 @auto_increment, 469 @local_type_name, 470 @minimum_scale, 471 @maximum_scale, 472 @sql_data_type, 473 @sql_datetime_sub, 474 @num_prec_radix, 475 @interval_precision 476 end 477 478 deallocate cursor jdbc_datatype_info_cursor4 479 declare jdbc_datatype_info_cursor5 cursor for 480 select /* Add date and time now. Special case because we want to use */ 481 /* d.sql_data_type for DATA_TYPE for these two types */ 482 case 483 when t.name = 'usmallint' then 'unsigned smallint' 484 when t.name = 'uint' then 'unsigned int' 485 when t.name = 'ubigint' then 'unsigned bigint' 486 else 487 t.name 488 end, 489 d.sql_data_type, 490 isnull(d.data_precision, convert(int, t.length)), 491 d.literal_prefix, 492 d.literal_suffix, 493 e.create_params, 494 d.nullable, 495 d.case_sensitive, 496 d.searchable, 497 d.unsigned_attribute, 498 d.money, 499 d.auto_increment, 500 d.local_type_name, 501 d.minimum_scale, 502 d.maximum_scale, 503 d.sql_data_type, 504 d.sql_datetime_sub, 505 d.num_prec_radix, 506 d.interval_precision 507 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 508 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 509 where d.ss_dtype = t.type and t.usertype *= e.user_type 510 /* restrict results to date and time*/ 511 and t.name in ('date', 'time') 512 and t.usertype < 100 /* No user defined types */ 513 open jdbc_datatype_info_cursor5 514 515 fetch jdbc_datatype_info_cursor5 into 516 @type_name, 517 @data_type, 518 @precision, 519 @literal_prefix, 520 @literal_suffix, 521 @create_params, 522 @nullable, 523 @case_sensitive, 524 @searchable, 525 @unsigned_attribute, 526 @fixed_prec_scale, 527 @auto_increment, 528 @local_type_name, 529 @minimum_scale, 530 @maximum_scale, 531 @sql_data_type, 532 @sql_datetime_sub, 533 @num_prec_radix, 534 @interval_precision 535 536 /** start insert the rows by looping thru the cursors */ 537 while (@@sqlstatus = 0) 538 begin 539 insert into #jdbc_datatype_info_cts values ( 540 /* TYPE_NAME */ 541 @type_name, 542 @data_type, 543 @precision, 544 @literal_prefix, 545 @literal_suffix, 546 @create_params, 547 @nullable, 548 @case_sensitive, 549 @searchable, 550 @unsigned_attribute, 551 @fixed_prec_scale, 552 @auto_increment, 553 @local_type_name, 554 @minimum_scale, 555 @maximum_scale, 556 @sql_data_type, 557 @sql_datetime_sub, 558 @num_prec_radix) 559 560 fetch jdbc_datatype_info_cursor5 into 561 @type_name, 562 @data_type, 563 @precision, 564 @literal_prefix, 565 @literal_suffix, 566 @create_params, 567 @nullable, 568 @case_sensitive, 569 @searchable, 570 @unsigned_attribute, 571 @fixed_prec_scale, 572 @auto_increment, 573 @local_type_name, 574 @minimum_scale, 575 @maximum_scale, 576 @sql_data_type, 577 @sql_datetime_sub, 578 @num_prec_radix, 579 @interval_precision 580 end 581 582 deallocate cursor jdbc_datatype_info_cursor5 583 584 end /* Adaptive Server has expanded all '*' elements in the following statement */ /* first begin */ 585 586 select #jdbc_datatype_info_cts.TYPE_NAME, #jdbc_datatype_info_cts.DATA_TYPE, #jdbc_datatype_info_cts. PRECISION, #jdbc_datatype_info_cts.LITERAL_PREFIX, #jdbc_datatype_info_cts.LITERAL_SUFFIX, #jdbc_datatype_info_cts.CREATE_PARAMS, #jdbc_datatype_info_cts.NULLABLE, #jdbc_datatype_info_cts.CASE_SENSITIVE, #jdbc_datatype_info_cts.SEARCHABLE, #jdbc_datatype_info_cts.UNSIGNED_ATTRIBUTE, #jdbc_datatype_info_cts.FIXED_PREC_SCALE, #jdbc_datatype_info_cts.AUTO_INCREMENT, #jdbc_datatype_info_cts.LOCAL_TYPE_NAME, #jdbc_datatype_info_cts.MINIMUM_SCALE, #jdbc_datatype_info_cts.MAXIMUM_SCALE, #jdbc_datatype_info_cts.SQL_DATA_TYPE, #jdbc_datatype_info_cts.SQL_DATETIME_SUB, #jdbc_datatype_info_cts.NUM_PREC_RADIX from #jdbc_datatype_info_cts order by DATA_TYPE, TYPE_NAME 587 drop table #jdbc_datatype_info_cts 588 589 if (@startedInTransaction = 1) 590 rollback transaction jdbc_keep_temptables_from_tx 591 592 return (0) 593
exec sp_procxmode 'sp_jdbc_datatype_info_cts', 'AnyMode' go Grant Execute on sp_jdbc_datatype_info_cts to public go
RESULT SETS | |
sp_jdbc_datatype_info_cts_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..spt_datatype_info_ext ![]() read_writes table tempdb..#jdbc_datatype_info_cts (1) reads table sybsystemprocs..spt_jdbc_datatype_info ![]() reads table sybsystemprocs..systypes ![]() |