DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_primarykey  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Altered from the ODBC sp_pkeys defined in sycsp11.sql.
4     **
5     ** To facilitate eventually combining scripts for ODBC and JDBC,
6     ** only the ordering of the arguments and the final select have been modified.
7     */
8     /*
9     ** note: there is one raiserror message: 18040
10    **
11    ** messages for 'sp_jdbc_primarykey'               18039, 18040
12    **
13    ** 17461, 'Object does not exist in this database.'
14    ** 18039, 'table qualifier must be name of current database.'
15    ** 18040, 'catalog procedure %1! can not be run in a transaction.', sp_jdbc_primarykey
16    **
17    */
18    
19    create or replace procedure sp_jdbc_primarykey
20        @table_qualifier varchar(32),
21        @table_owner varchar(32),
22        @table_name varchar(300)
23    as
24        declare @msg varchar(255)
25        declare @keycnt smallint
26        declare @indexid smallint
27        declare @indexname varchar(255)
28        declare @i int
29        declare @id int
30        declare @uid smallint
31        declare @actual_table_name varchar(300)
32        declare @startedInTransaction bit
33    
34        if (@@trancount = 0)
35        begin
36            set chained off
37        end
38    
39        /* see if we're in a transaction, before we try any select statements */
40        if (@@trancount > 0)
41            select @startedInTransaction = 1
42        else
43            select @startedInTransaction = 0
44    
45        /* this will make sure that all rows are sent even if
46        ** the client "set rowcount" is differect
47        */
48    
49        set rowcount 0
50    
51        select @actual_table_name = @table_name
52    
53        select @id = NULL
54    
55        set nocount on
56    
57        set transaction isolation level 1
58    
59        if (@startedInTransaction = 1)
60            save transaction jdbc_keep_temptables_from_tx
61    
62        if @table_qualifier is not null
63        begin
64            if db_name() != @table_qualifier
65            begin
66                /* if qualifier doesn't match current database */
67                /* 'table qualifier must be name of current database'*/
68                exec sp_getmessage 18039, @msg output
69                raiserror 18039 @msg
70                return (2)
71            end
72        end
73    
74        exec sp_jdbc_escapeliteralforlike @table_name
75    
76        if @table_owner is null
77        begin
78            select @table_owner = '%'
79        end
80    
81        if (select count(*) from sysobjects
82                where user_name(uid) like @table_owner ESCAPE '\'
83                    and ('"' + name + '"' = @table_name or name = @table_name)) = 0
84        begin
85            /* 17461, 'Object does not exist in this database.' */
86            exec sp_getmessage 17674, @msg output
87            raiserror 17674 @msg
88            return (3)
89        end
90    
91        create table #pkeys(
92            TABLE_CAT varchar(32),
93            TABLE_SCHEM varchar(32),
94            TABLE_NAME varchar(255),
95            COLUMN_NAME varchar(255),
96            KEY_SEQ smallint,
97            PK_NAME varchar(255))
98    
99    
100       DECLARE jcurs_sysuserobjects CURSOR
101       FOR
102       select id, uid
103       from sysobjects
104       where user_name(uid) like @table_owner ESCAPE '\'
105           and name = @table_name
106       FOR READ ONLY
107   
108       OPEN jcurs_sysuserobjects
109   
110       FETCH jcurs_sysuserobjects INTO @id, @uid
111   
112       while (@@sqlstatus = 0)
113       begin
114   
115           /*
116           **  now we search for primary key (only declarative) constraints
117           **  There is only one primary key per table.
118           */
119   
120           select @keycnt = keycnt, @indexid = indid, @indexname = name
121           from sysindexes
122           where id = @id
123               and indid > 0 /* make sure it is an index */
124               and status2 & 2 = 2 /* make sure it is a declarative constr */
125               and status & 2048 = 2048 /* make sure it is a primary key */
126   
127           /*
128           ** For non-clustered indexes, keycnt as returned from sysindexes is one
129           ** greater than the actual key count. So we need to reduce it by one to
130           ** get the actual number of keys.
131           */
132           if (@indexid >= 2)
133           begin
134               select @keycnt = @keycnt - 1
135           end
136   
137           select @i = 1
138   
139           while @i <= @keycnt
140           begin
141               insert into #pkeys values
142               (db_name(), user_name(@uid), @actual_table_name,
143                   index_col(@actual_table_name, @indexid, @i, @uid), @i, @indexname)
144               select @i = @i + 1
145           end
146   
147           /*
148           ** Go to the next user/object
149           */
150           FETCH jcurs_sysuserobjects INTO @id, @uid
151       end
152   
153       close jcurs_sysuserobjects
154       deallocate cursor jcurs_sysuserobjects
155   
156       /*
157       ** Original ODBC query:
158       **
159       ** select table_qualifier, table_owner, table_name, column_name, key_seq
160       ** from #pkeys
161       ** order by table_qualifier, table_owner, table_name, key_seq
162       */
163       /*
164       ** Primary keys are not explicitly named, so name is always null.
165       */
166       select TABLE_CAT,
167           TABLE_SCHEM,
168           TABLE_NAME,
169           COLUMN_NAME,
170           KEY_SEQ,
171           PK_NAME
172       from #pkeys
173       order by COLUMN_NAME
174   
175       drop table #pkeys
176   
177       if (@startedInTransaction = 1)
178           rollback transaction jdbc_keep_temptables_from_tx
179   
180       return (0)
181   


exec sp_procxmode 'sp_jdbc_primarykey', 'AnyMode'
go

Grant Execute on sp_jdbc_primarykey to public
go
RESULT SETS
sp_jdbc_primarykey_rset_001

DEFECTS
 MTYP 4 Assignment type mismatch @pString: varchar(255) = varchar(300) 74
 MTYP 4 Assignment type mismatch TABLE_NAME: varchar(255) = varchar(300) 142
 MTYP 4 Assignment type mismatch KEY_SEQ: smallint = int 143
 QCSC 4 Costly 'select count()', use 'exists()' 81
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 123
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 122
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_primarykey  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 68
 MNER 3 No Error Check should check return value of exec 74
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check @@error after insert 141
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 180
 MUIN 3 Column created using implicit nullability 91
 QISO 3 Set isolation level 57
 CRDO 2 Read Only Cursor Marker (has for read only clause) 102
 MRST 2 Result Set Marker 166
 MSUB 2 Subquery Marker 81
 MTR1 2 Metrics: Comments Ratio Comments: 33% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 18dec - 3exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 80 19

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_jdbc_primarykey_rset_001.TABLE_NAME °.COLUMN_NAME

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike  
read_writes table tempdb..#pkeys (1) 
reads table sybsystemprocs..sysindexes  
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_jdbc_primarykey_rset_001