DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drv_column_default  14 déc. 14Defects Propagation Dependencies

1     
2     CREATE OR REPLACE PROCEDURE sp_drv_column_default(@obj_id int, @default_value varchar(1024) output)
3     as
4         declare @text_count int
5         declare @default_holder varchar(255)
6         declare @rownum int
7         declare @create_default_starts int
8         declare @default_starts int
9         declare @actual_default_starts int
10        declare @as_starts int
11        declare @length int
12        declare @check_case_one int
13        declare @check_last_char int
14        declare @linefeed_char char(2)
15        declare @last_char char(2)
16    
17        /* make sure @default_value starts out as empty */
18        SELECT @default_value = null
19    
20        /* initialize @check_case_one to false (0) */
21        SELECT @check_case_one = 0
22    
23        /* initialize @check_last_char to false (0) */
24        SELECT @check_last_char = 0
25    
26        /* initialize the @linefeed_char variable to linefeed */
27        SELECT @linefeed_char = char(10)
28    
29        /* Find out how many rows there are in syscomments defining the 
30        default. If there are none, then we return a null*/
31        SELECT @text_count = count(*) FROM syscomments
32        WHERE id = @obj_id
33    
34        if @text_count = 0
35        begin
36            return 0
37        end
38    
39        /* See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set).
40        If it is, best we can do is return null*/
41        if exists (SELECT 1 FROM syscomments WHERE (status & 1 = 1)
42                    AND id = @obj_id)
43        begin
44            return 0
45        end
46    
47        SELECT @rownum = 1
48        declare default_value_cursor cursor for
49        SELECT text FROM syscomments WHERE id = @obj_id
50        order by number, colid2, colid
51    
52        open default_value_cursor
53    
54        fetch default_value_cursor into @default_holder
55    
56        while (@@sqlstatus = 0)
57        begin
58    
59            if @rownum = 1
60            begin
61                /* find the default value                                       
62                **  Note that ASE stores default values in more than one way:    
63                **    1. If a client declares the column default value in the     
64                **       table definition, ASE will store the word DEFAULT (in    
65                **       all caps) followed by the default value, exactly as the  
66                **       user entered it (meaning it will include quotes, if the
67                **       value was a string constant). This DEFAULT word will
68                **       be in all caps even if the user did something like this:
69                **           create table foo (col1 varchar (10) DeFaULT 'bar')
70                **    2. If a client does sp_bindefault to bind a default to 
71                **       a column, ASE will include the text of the create default
72                **       command, as entered. So, if the client did the following:
73                **           create DeFAULt foo aS 'bar'
74                **       that is exactly what ASE will place in the text column
75                **       of syscomments.
76                **       In this case, too, we have to be careful because ASE
77                **       will sometimes include a newline character 
78                **       at the end of the create default statement. This
79                **       can happen if a client uses C isql to type in the
80                **       create default command (if it comes in through java, then
81                **       the newline AND null are not present).
82                **  Because of this, we have to be careful when trying to parse out
83                **  the default value. 
84                */
85    
86                SELECT @length = char_length(@default_holder)
87                SELECT @create_default_starts =
88                    charindex('create default', lower(@default_holder))
89                SELECT @as_starts = charindex(' as ', lower(@default_holder))
90                SELECT @default_starts = charindex('DEFAULT', @default_holder)
91    
92                if (@create_default_starts != 0 AND @as_starts != 0)
93                begin
94    
95                    /* If we get here, then we likely have case (2) above.
96                    ** However, it's still possible that the client did something
97                    ** like this:
98                    **     create table foo (col1 varchar (20) default 
99                    **         'create default foo as bar')            
100                   ** The following if block accounts for that possibility  
101                   */
102   
103                   if (@default_starts != 0 and
104                           @default_starts < @create_default_starts)
105                   begin
106                       SELECT @check_case_one = 1
107                   end
108                   else
109                   begin
110                       SELECT @actual_default_starts = @as_starts + 4
111                       SELECT @check_last_char = 1
112   
113                       /* set @default_starts to 0 so we don't fall into the
114                       ** next if block. This is important because we would
115                       ** fall into the next if block if a client had used the
116                       ** following sql:
117                       **     CREATE DEFAULT foo as 'bar'               
118                       */
119                       SELECT @default_starts = 0
120                   end
121               end
122   
123               if (@default_starts != 0 OR @check_case_one != 0)
124                   /* If we get here, then we have case (1) above */
125   
126                   SELECT @actual_default_starts = @default_starts + 7
127   
128               /* The ltrim removes any left-side blanks, because ASE appears
129               ** to insert several blanks between the word DEFAULT AND the
130               ** start of the default vale */
131   
132               SELECT @default_holder =
133                   ltrim(substring
134                       (@default_holder, @actual_default_starts, @length))
135   
136           end
137   
138           SELECT @default_value = @default_value + @default_holder
139           SELECT @rownum = @rownum + 1
140   
141           fetch default_value_cursor into @default_holder
142   
143       end /* while loop */
144   
145       close default_value_cursor
146   
147       /* trim off any right-side blanks */
148       SELECT @default_value = rtrim(@default_value)
149   
150       /* trim off the newline AND null characters, if they're the last 
151       ** two characters in what remains 
152       */
153       if (@check_last_char = 1)
154       begin
155   
156           SELECT @length = char_length(@default_value)
157           SELECT @last_char = substring(@default_value, @length, 1)
158           if (@last_char = @linefeed_char)
159               SELECT @default_value = substring(@default_value, 1, (@length - 1))
160       end
161   
162       return 0
163   
164   
165   


exec sp_procxmode 'sp_drv_column_default', 'AnyMode'
go

Grant Execute on sp_drv_column_default to public
go
DEFECTS
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause default_value_cursor 49
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 32
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 42
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 49
 MGTP 3 Grant to public sybsystemprocs..sp_drv_column_default  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 158
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
32
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
41
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
49
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 49
 MSUB 2 Subquery Marker 41
 MTR1 2 Metrics: Comments Ratio Comments: 49% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 14dec - 3exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 71 2

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscomments  

CALLERS
called by proc sybsystemprocs..sp_odbc_columns  
called by proc sybsystemprocs..sp_jdbc_columns