| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_addtype | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_addtype" 17300 6 ** 7 ** 17300, "Usage: sp_addtype name, 'datatype' [,null | nonull | identity]" 8 ** 17301, "'%1!' is not a valid type name." 9 ** 17302, "A type with the specified name already exists." 10 ** 17303, "Physical datatype does not exist." 11 ** 17304, "User-defined datatypes based on the 'timestamp' datatype are not allowed." 12 ** 17305, "Physical datatype does not allow nulls." 13 ** 17306, "Physical type is fixed length. You cannot specify the length." 14 ** 17307, "You must specify a length with this physical type. 15 ** 17308, "Illegal length specified -- must be between 1 and %1!." 16 ** 17309, "Type added." 17 ** 17751, "Illegal precision specified -- must be between 1 and 38." 18 ** 17752, "Illegal scale specified -- must be less than precision and positive." 19 ** 17754, "Illegal precision specified -- must be between 1 and 48." 20 21 ** 17756, "The execution of the stored procedure '%1!' in database 22 ** '%2!' was aborted because there was an error in writing the 23 ** replication log record." 24 ** 18302, "User '%1!' is not a valid user in the '%2!' database." 25 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 26 ** 18786, "A type with name '%1!' or id '%2!' already exists." 27 */ 28 29 /* 30 ** IMPORTANT: Please read the following instructions before 31 ** making changes to this stored procedure. 32 ** 33 ** To make this stored procedure compatible with High Availability (HA), 34 ** changes to certain system tables must be propagated 35 ** to the companion server under some conditions. 36 ** The tables include (but are not limited to): 37 ** syslogins, sysservers, sysattributes, systimeranges, 38 ** sysresourcelimits, sysalternates, sysdatabases, 39 ** syslanguages, sysremotelogins, sysloginroles, 40 ** sysalternates (master DB only), systypes (master DB only), 41 ** sysusers (master DB only), sysprotects (master DB only) 42 ** please refer to the HA documentation for detail. 43 ** 44 ** Here is what you need to do: 45 ** For each insert/update/delete statement, add three sections to 46 ** -- start HA transaction prior to the statement 47 ** -- add the statement 48 ** -- add HA synchronization code to propagate the change to the companion 49 ** 50 ** For example, if you are adding 51 ** insert master.dbo.syslogins ...... 52 ** the code should look like: 53 ** 1. Before that SQL statement: 54 ** 55 ** 2. Now, the SQL statement: 56 ** insert master.dbo.syslogins ...... 57 ** 3. Add a HA synchronization section right after the SQL statement: 58 ** 59 ** 60 ** You may need to do similar change for each built-in function you 61 ** want to add. 62 ** 63 ** After that, you need to add a separate part at a place where it can not 64 ** be reached by the normal execution path: 65 ** clean_all: 66 ** 67 ** return (1) 68 */ 69 70 create procedure sp_addtype 71 @typename varchar(255), /* name of user-defined type */ 72 @phystype varchar(80), /* physical system type of user-defined type */ 73 @nulltype varchar(8) = "1" /* default is database 'allow null' default */ 74 as 75 76 declare @len int /* length of user type */ 77 declare @type tinyint /* typeid of physical type */ 78 declare @tlen int /* length of physical type */ 79 declare @typeid smallint /* user typeid of physical type */ 80 declare @var bit /* is physical type variable length? */ 81 declare @nonull bit /* default is to allow NO NULLs */ 82 declare @nullegal bit /* does physical type allow NULLs? */ 83 declare @msg varchar(1024) 84 declare @prec int /* precision of the datatype */ 85 declare @scale int /* scale of the datatype */ 86 declare @tprec tinyint /* precision of the datatype read from systypes */ 87 declare @tscale tinyint /* scale of the datatype read from systypes */ 88 declare @u_identity tinyint /* does user type have identity property? */ 89 declare @hierarchy tinyint /* hierarchy level of the datatype */ 90 declare @index int /* index of blank char in the string of datatype*/ 91 declare @rest varchar(80) /* string that holds the temporay portion of the datatype.*/ 92 declare @nulldefault int /* 'allow null' database default */ 93 declare @nationalchar int /* national character type is specified */ 94 declare @logexec int /* For the logexec call */ 95 declare @saved_phystype varchar(80) /* Saved @phystype parameter. This will be 96 ** restored before logging for replication. 97 ** This is necessary because @phystype is 98 ** modified. 99 */ 100 declare @dbname varchar(255) 101 declare @uid int 102 declare @insert_typeid smallint 103 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 104 declare @MAXSYSTYPEID int /* maximum user type id for a system type */ 105 declare @retstat int 106 declare @maxlen int 107 108 109 if @@trancount = 0 110 begin 111 set chained off 112 end 113 114 select @HA_CERTIFIED = 0 115 select @MAXSYSTYPEID = 100 /* maximum user type id for a system type */ 116 117 118 119 120 /* check to see if we are using HA specific SP for a HA enabled server */ 121 exec @retstat = sp_ha_check_certified 'sp_addtype', @HA_CERTIFIED 122 if (@retstat != 0) 123 return (1) 124 125 126 set transaction isolation level 1 127 /* 128 ** Does the user type allow NULLs? Now, the default is to 129 ** use the 'allow null' option setting for the current database. 130 ** This is the same default the create table statement uses. 131 */ 132 if @nulltype = "1" 133 begin 134 /* 135 ** Get database 'allow null default. @nulldefault = 0 136 ** means 'not null allowed' is database default. 137 ** 138 ** 17069, "allow nulls by default" 139 ** 140 ** Make sure not to get the internationalized message 141 ** by "us_english" parameter, even if client is using 142 ** an alternative language. This is because spt_values 143 ** has an us_english message only, even if an alternative 144 ** language is installed. 145 */ 146 147 exec sp_getmessage 17069, @msg output, "us_english" 148 149 select @nulldefault = (a.number & b.status) 150 from master.dbo.spt_values a, master.dbo.sysdatabases b 151 where a.name = @msg and b.name = db_name() 152 if (@nulldefault = 0) 153 select @nonull = 1 154 else 155 select @nonull = 0 156 select @u_identity = 0 157 end 158 else if lower(@nulltype) = "null" or @nulltype is null 159 begin 160 select @nonull = 0 161 select @u_identity = 0 162 end 163 else if lower(@nulltype) = "identity" 164 begin 165 select @nonull = 1 166 select @u_identity = 1 167 end 168 else if lower(@nulltype) in ("not null", "nonull") 169 begin 170 select @nonull = 1 171 select @u_identity = 0 172 end 173 else 174 begin 175 /* 176 ** 17300, "Usage: sp_addtype name, 'datatype' [,null | nonull | identity]" 177 */ 178 raiserror 17300 179 return (1) 180 end 181 182 /* 183 ** Check to see that the @typename is valid. 184 */ 185 select @maxlen = length from syscolumns 186 where id = object_id("systypes") 187 and name = "name" 188 189 if valid_name(@typename, @maxlen) = 0 190 begin 191 /* 192 ** 17301, "'%1!' is not a valid type name." 193 */ 194 raiserror 17301, @typename 195 return (1) 196 end 197 198 /* 199 ** Check to see if the type already exists. In addition to the 200 ** types in systypes we need to also check for their capitalized 201 ** version (for system types) and alternative names for system types 202 ** such as integer for int. 203 */ 204 if exists (select * 205 from systypes 206 where name = @typename or 207 (name = lower(@typename) and usertype < @MAXSYSTYPEID) or 208 (lower(@typename) in ('integer', 'character'))) 209 210 begin 211 /* 212 ** 17302, "A type with the specified name already exists." 213 */ 214 raiserror 17302 215 return (1) 216 end 217 218 /* Save the original value of @phystype. We'll need it if we have 219 ** to log this invocation for replication. 220 */ 221 select @saved_phystype = @phystype 222 223 /* Make physical typename all lower case to ensure case insensitivity. */ 224 select @phystype = lower(@phystype) 225 226 /* initialize the length to be NULL first. */ 227 select @len = NULL 228 /* 229 ** If precision and scale were given with the type extract them 230 */ 231 if @phystype like "_%(_%,_%)" 232 begin 233 select @prec = convert(int, substring(@phystype, 234 charindex("(", @phystype) + 1, 235 charindex(",", @phystype) - 1 - charindex("(", @phystype))) 236 237 select @scale = convert(int, substring(@phystype, 238 charindex(",", @phystype) + 1, 239 charindex(")", @phystype) - 1 - charindex(",", @phystype))) 240 /* 241 ** Extract the physical type name 242 */ 243 select @phystype = substring(@phystype, 1, 244 charindex("(", @phystype) - 1) 245 end 246 else 247 248 /* 249 ** If a length was given with the user datatype, extract it. 250 ** This could also be the precision, at this time assume it's the length 251 ** we'll decide later whether it's the length or precision. 252 ** If the length is not specified, @len will be assigned to NULL by 253 ** the function convert(). Note that like "_%(%)" can match the string 254 ** of phystype(length) or phystype(), such as binary(8) or binary(). 255 */ 256 if @phystype like "_%(%)" 257 begin 258 select @len = convert(int, substring(@phystype, 259 charindex("(", @phystype) + 1, 260 charindex(")", @phystype) - 1 - charindex("(", @phystype))) 261 262 /* 263 ** Extract the physical type name 264 */ 265 select @phystype = substring(@phystype, 1, 266 charindex("(", @phystype) - 1) 267 end 268 else 269 270 /* 271 ** If the type is an unsigned int then we only allow "unsigned int" 272 ** not the systypes name uint. 273 */ 274 if (@phystype in ("uint", "usmallint", "ubigint", "uintn")) 275 begin 276 /* 277 ** 17303, "Physical datatype does not exist." 278 */ 279 raiserror 17303 280 return (1) 281 end 282 283 /* 284 ** Now, squeezes all consective space characters in the datatype string 285 ** into one single space character. By doing this, sp_addtype can recognize 286 ** datatype, such as " national char varying". 287 */ 288 select @phystype = ltrim(rtrim(@phystype)) 289 select @rest = @phystype 290 select @phystype = NULL 291 select @index = charindex(' ', @rest) 292 while (@index != 0) 293 begin 294 select @phystype = @phystype + substring(@rest, 1, @index - 1) + ' ' 295 select @rest = ltrim(stuff(@rest, 1, @index, '')) 296 select @index = charindex(' ', @rest) 297 end 298 select @phystype = rtrim(@phystype + @rest) 299 300 /* 301 ** Assgin value to @nationalchar before do the datatype mapping, 302 ** this value will be used when calculate the actual storage length. 303 */ 304 305 if (@phystype in ("nchar", "nvarchar", "national char", "national character", 306 "nchar varying", "national char varying", "national character varying")) 307 begin 308 select @nationalchar = 1 309 end 310 else 311 begin 312 select @nationalchar = 0 313 end 314 315 /* 316 ** Do the following datatypes mapping now. 317 ** "character"--> "char"; "nchar"--> "char"; 318 ** "[national] char[acter] varying"--> "varchar"; "nchar varying"-->"varchar"; 319 ** "dec" ---> "decimal"; "integer"---> "int"; "double precision" --> "float"; 320 ** Map the default length of datatype "char" ,"varchar", "binary", "varbinary" 321 ** and their various synonyms to 1. 322 */ 323 if (@phystype in ("char", "character", "nchar", "national char", 324 "national character")) 325 begin 326 select @phystype = "char" 327 /* 328 ** If len is not specified, supply the default length to be 1. 329 */ 330 if (@len is NULL) select @len = 1 331 end 332 else 333 if (@phystype in ("varchar", "nvarchar", "nchar varying", "char varying", 334 "character varying", "national char varying", "national character varying")) 335 begin 336 select @phystype = "varchar" 337 /* 338 ** If len is not specified, supply the default length to be 1. 339 */ 340 if (@len is NULL) select @len = 1 341 end 342 else 343 if (@phystype in ("univarchar", "unichar varying", 344 "unicode character varying", "unicode char varying")) 345 begin 346 select @phystype = "univarchar" 347 /* 348 ** If len is not specified, supply the default length to be 1. 349 */ 350 if (@len is NULL) select @len = 1 351 end 352 else 353 if (@phystype in ("unichar", "unicode char", "unicode character")) 354 begin 355 select @phystype = "unichar" 356 /* 357 ** If len is not specified, supply the default length to be 1. 358 */ 359 if (@len is NULL) select @len = 1 360 end 361 else 362 if ((@phystype in ("binary", "varbinary")) and (@len is NULL)) 363 /* 364 ** If len is not specified, supply the default length to be 1. 365 */ 366 select @len = 1 367 else 368 if (@phystype = "unsigned integer") select @phystype = "uint" 369 else 370 if (@phystype = "unsigned int") select @phystype = "uint" 371 else 372 if (@phystype = "unsigned smallint") select @phystype = "usmallint" 373 else 374 if (@phystype = "unsigned bigint") select @phystype = "ubigint" 375 else 376 if (@phystype = "integer") select @phystype = "int" 377 else 378 if (@phystype = "double precision") select @phystype = "float" 379 else 380 if (@phystype = "dec") select @phystype = "decimal" 381 else 382 /* 383 ** If len for float datatype is specified, then the range must 384 ** be between 1 to 48. If len specified is <= 15, map the type 385 ** to real, otherwise map the type to float. 386 */ 387 if (@phystype = "float") and (@len is not NULL) 388 begin 389 if (@len < 1 or @len > 48) 390 begin 391 raiserror 17754 392 return (1) 393 end 394 else 395 begin 396 if (@len <= 15) select @phystype = "real" 397 select @len = NULL 398 end 399 end 400 401 /* 402 ** Make sure that the physical type exists and get its characteristics. 403 ** DataServer physical types have a usertype < 100 and are owned by the 404 ** dbo (userid = 1). 405 ** Datatypes datetimn, decimaln, floatn, intn, moneyn, numericn, daten 406 ** bigdatetimen, bigtimen, and timen are not supported to users. 407 ** Those datatypes must be invisible to users. 408 */ 409 select @type = type, @tlen = length, @typeid = usertype, 410 @var = variable, @nullegal = allownulls, @tprec = prec, 411 @tscale = scale, @hierarchy = hierarchy 412 from systypes 413 where usertype < 100 and name = @phystype and uid = 1 414 and @phystype not in ('datetimn', 'decimaln', 'floatn', 'timen', 415 'intn', 'moneyn', 'numericn', 'daten', 416 'bigdatetimen', 'bigtimen') 417 418 if @type is NULL 419 begin 420 /* 421 ** 17303, "Physical datatype does not exist." 422 */ 423 raiserror 17303 424 return (1) 425 end 426 427 /* 428 ** Disallow user-defined datatypes on timestamps. This is done because 429 ** a timestamp is not a basic type but is really a binary. There is, 430 ** therefore, no way to tell if a user-defined datatype is mapped to 431 ** a binary or a timestamp. Timestamps can't have rules or defaults. 432 */ 433 if @phystype = "timestamp" 434 begin 435 /* 436 ** 17304, "User-defined datatypes based on the 'timestamp' datatype are not allowed." 437 */ 438 raiserror 17304 439 return (1) 440 end 441 442 /* 443 ** Check if the NULL status of the user type is consistent with the NULL status 444 ** of the physical type. Here are the possible cases. 445 ** 446 ** physical type 447 ** NULLs NONULLs 448 ** ----------------- 449 ** user NULLs | ok | no 450 ** type NONULLs | ok | ok 451 */ 452 if @nonull = 0 and @nullegal = 0 453 begin 454 /* 455 ** 17305, "Physical datatype does not allow nulls." 456 */ 457 raiserror 17305 458 return (1) 459 end 460 461 /* 462 ** We'll use the variable @nullegal when we update the systypes table 463 ** so we need to set it to reflect if NULLs are allowed (@nonull = 0) 464 ** or NO NULLs are allowed (@nonull = 1). 465 */ 466 if @nonull = 0 467 begin 468 select @nullegal = 1 469 end 470 else 471 begin 472 select @nullegal = 0 473 end 474 475 /* Decide about precision, scale, length 476 ** First check fro NUMERIC, DECIMAL 477 */ 478 if (@typeid = 26) or (@typeid = 10) 479 begin 480 /* Type is NUMERIC or DECIMAL */ 481 482 if @len > 0 483 begin 484 /* Length is really the precision 485 ** Since no scale is specified then scale 486 ** is minimum(Default, precision). Default = 4 487 */ 488 select @prec = @len 489 select @scale = 0 490 491 end 492 else 493 begin 494 if (@prec is NULL) 495 begin 496 select @prec = 18 497 select @scale = 0 498 end 499 end 500 501 502 503 if (@prec > 38) or (@prec < 1) 504 begin 505 /* 506 ** 17751, "Illegal precision specified -- must be between 1 and 38." 507 */ 508 raiserror 17751 509 return (1) 510 end 511 512 if (@scale > @prec) or (@scale < 0) 513 begin 514 /* 515 ** 17752, "Illegal scale specified -- must be less than precision 516 ** and positive." 517 */ 518 raiserror 17752 519 return (1) 520 end 521 522 /* Compute length from precision */ 523 if (@prec <= 2) 524 select @len = 2 525 526 if (@prec > 2) and (@prec <= 4) 527 select @len = 3 528 529 if (@prec > 4) and (@prec <= 7) 530 select @len = 4 531 532 if (@prec > 7) and (@prec <= 9) 533 select @len = 5 534 535 if (@prec > 9) and (@prec <= 12) 536 select @len = 6 537 538 if (@prec > 12) and (@prec <= 14) 539 select @len = 7 540 541 if (@prec > 14) and (@prec <= 16) 542 select @len = 8 543 544 if (@prec > 16) and (@prec <= 19) 545 select @len = 9 546 547 if (@prec > 19) and (@prec <= 21) 548 select @len = 10 549 550 if (@prec > 21) and (@prec <= 24) 551 select @len = 11 552 553 if (@prec > 24) and (@prec <= 26) 554 select @len = 12 555 556 if (@prec > 26) and (@prec <= 28) 557 select @len = 13 558 559 if (@prec > 28) and (@prec <= 31) 560 select @len = 14 561 562 if (@prec > 31) and (@prec <= 33) 563 select @len = 15 564 565 if (@prec > 33) and (@prec <= 36) 566 select @len = 16 567 568 if (@prec > 36) and (@prec <= 38) 569 select @len = 17 570 end 571 else 572 begin 573 /* 574 ** Typeids 1 (char), 2 (varchar), 3 (binary), 4 (varbinary) 575 ** 24 (nchar), 25 (nvarchar), 34 (unichar), and 35 (univarchar) 576 ** only ones which allow a length to be specified. 577 */ 578 if @typeid > 4 and @typeid not in (24, 25, 34, 35) 579 begin 580 /* 581 ** We can't use a length and we got one. 582 */ 583 if @len > 0 584 begin 585 /* 586 ** 17306, "Physical type is fixed length. You cannot specify the length." 587 */ 588 raiserror 17306 589 return (1) 590 end 591 592 /* 593 ** Use the fixed length of the physical type. 594 */ 595 select @len = @tlen 596 end 597 else 598 begin 599 /* 600 ** We need a length and we didn't get one. 601 */ 602 if @len is NULL 603 begin 604 /* 605 ** 17307, "You must specify a length with this physical type. 606 */ 607 raiserror 17307 608 return (1) 609 end 610 611 /* 612 ** If "nchar" or "nvarchar" is specified, caluculate the real length 613 */ 614 615 if @nationalchar = 1 616 select @len = @len * @@ncharsize 617 618 /* 619 ** If "unichar" or "univarchar", perform additinal checks 620 */ 621 if (@phystype in ("unichar", "univarchar")) 622 begin 623 select @len = @len * @@unicharsize 624 625 if (@len <= 0 or @len > @@maxpagesize) 626 begin 627 /* 628 ** 17308, "Illegal length specified -- must be between 1 and %1!." 629 */ 630 select @len = @@maxpagesize / @@unicharsize 631 raiserror 17308, @len 632 return (1) 633 end 634 end 635 else if @len <= 0 or @len > @@maxpagesize 636 begin 637 /* 638 ** 17308, "Illegal length specified -- must be between 1 and %1!." 639 */ 640 raiserror 17308, @@maxpagesize 641 return (1) 642 end 643 644 end 645 end 646 647 /* 648 ** Check to make sure only numeric(x,0), integer, smallint and tinyint 649 ** types have identity property. 650 */ 651 if @u_identity = 1 652 begin 653 if (@phystype = "numeric" and @scale != 0) or 654 (@phystype not in ("int", "smallint", "tinyint", 655 "bigint", "ubigint", "uint", "usmallint", "numeric")) 656 begin 657 /* 658 ** 17755, "User types with the identity property must be integer, 659 ** smallint, tinyint or numeric with a scale of 0." 660 */ 661 raiserror 17755 662 return (1) 663 end 664 end 665 666 667 /* 668 ** Finally, get the maximum existing user type so we use it + 1 for this 669 ** new type. 670 */ 671 /* Attention: 672 ** Please change the corresponding HA section when type ID generating 673 ** logic is changed here. 674 */ 675 select @typeid = max(usertype) 676 from systypes 677 678 /* 679 ** There are no user defined types yet so use the first number (101). 680 */ 681 if @typeid < 100 682 select @typeid = 100 683 else if (@typeid = 32767) 684 begin 685 print "All usertype ids have been used up" 686 return (1) 687 end 688 689 select @insert_typeid = @typeid + 1 690 691 select @uid = user_id() 692 693 694 695 /* 696 ** This transaction also writes a log record for replicating the 697 ** invocation of this procedure. If logexec() fails, the transaction 698 ** is aborted. 699 ** 700 ** IMPORTANT: The name rs_logexec is significant and is used by 701 ** Replication Server. 702 */ 703 begin transaction rs_logexec 704 705 706 707 insert systypes(uid, variable, type, length, tdefault, 708 domain, name, usertype, allownulls, prec, scale, ident, 709 hierarchy, accessrule) 710 select @uid, @var, @type, @len, 0, 0, @typename, @insert_typeid, @nullegal, 711 @prec, @scale, @u_identity, @hierarchy, 0 712 713 714 715 /* 716 ** The phystype parameter was changed so restore it's original value and 717 ** write the log record to replicate this invocation. 718 */ 719 select @phystype = @saved_phystype 720 721 if (logexec() != 1) 722 begin 723 /* 724 ** 17756, "The execution of the stored procedure '%1!' in 725 ** database '%2!' was aborted because there was an 726 ** error in writing the replication log record." 727 */ 728 select @dbname = db_name() 729 raiserror 17756, "sp_addtype", @dbname 730 731 rollback transaction rs_logexec 732 return (1) 733 end 734 735 736 737 commit transaction rs_logexec 738 739 /* 740 ** 17309, "Type added." 741 */ 742 exec sp_getmessage 17309, @msg output 743 print @msg 744 745 return (0) 746 747 clean_all: 748 rollback transaction rs_logexec 749 return (1) 750 751
exec sp_procxmode 'sp_addtype', 'AnyMode' go Grant Execute on sp_addtype to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..spt_values (1)  read_writes table sybsystemprocs..systypes reads table sybsystemprocs..syscolumns reads table master..sysdatabases (1) calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  |
