DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_primarykey  31 Aug 14Defects Dependencies

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


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) 75
 MTYP 4 Assignment type mismatch TABLE_NAME: varchar(255) = varchar(300) 143
 MTYP 4 Assignment type mismatch KEY_SEQ: smallint = int 144
 QCSC 4 Costly 'select count()', use 'exists()' 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 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 69
 MNER 3 No Error Check should check return value of exec 75
 MNER 3 No Error Check should check return value of exec 87
 MNER 3 No Error Check should check @@error after insert 142
 MUCO 3 Useless Code Useless Brackets 35
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 181
 MUIN 3 Column created using implicit nullability 92
 QISO 3 Set isolation level 58
 CRDO 2 Read Only Cursor Marker (has for read only clause) 103
 MRST 2 Result Set Marker 167
 MSUB 2 Subquery Marker 82
 MTR1 2 Metrics: Comments Ratio Comments: 34% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 17dec - 3exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 80 20

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike  
read_writes table tempdb..#pkeys (1) 
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)