DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_datatype_info_cts  31 Aug 14Defects 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

DEFECTS
 QJWI 5 Join or Sarg Without Index 106
 QJWI 5 Join or Sarg Without Index 212
 QJWI 5 Join or Sarg Without Index 311
 QJWI 5 Join or Sarg Without Index 406
 QJWI 5 Join or Sarg Without Index 509
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 142
 MTYP 4 Assignment type mismatch UNSIGNED_ATTRIBUTE: tinyint = smallint 150
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 247
 MTYP 4 Assignment type mismatch UNSIGNED_ATTRIBUTE: tinyint = smallint 255
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 342
 MTYP 4 Assignment type mismatch UNSIGNED_ATTRIBUTE: tinyint = smallint 350
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 438
 MTYP 4 Assignment type mismatch UNSIGNED_ATTRIBUTE: tinyint = smallint 446
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 542
 MTYP 4 Assignment type mismatch UNSIGNED_ATTRIBUTE: tinyint = smallint 550
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 111
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 312
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 407
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 512
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_datatype_info_cursor1 78
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_datatype_info_cursor2 184
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_datatype_info_cursor3 289
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_datatype_info_cursor4 384
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_datatype_info_cursor5 480
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_datatype_info_cts  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after insert 139
 MNER 3 No Error Check should check @@error after insert 244
 MNER 3 No Error Check should check @@error after insert 339
 MNER 3 No Error Check should check @@error after insert 435
 MNER 3 No Error Check should check @@error after insert 539
 MUCO 3 Useless Code Useless Brackets 33
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Begin-End Pair 76
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 537
 MUCO 3 Useless Code Useless Brackets 589
 MUCO 3 Useless Code Useless Brackets 592
 MUIN 3 Column created using implicit nullability 51
 QISO 3 Set isolation level 38
 QNAO 3 Not using ANSI Outer Join 104
 QNAO 3 Not using ANSI Outer Join 210
 QNAO 3 Not using ANSI Outer Join 309
 QNAO 3 Not using ANSI Outer Join 404
 QNAO 3 Not using ANSI Outer Join 507
 CUPD 2 Updatable Cursor Marker (updatable by default) 78
 CUPD 2 Updatable Cursor Marker (updatable by default) 184
 CUPD 2 Updatable Cursor Marker (updatable by default) 289
 CUPD 2 Updatable Cursor Marker (updatable by default) 384
 CUPD 2 Updatable Cursor Marker (updatable by default) 480
 MRST 2 Result Set Marker 586
 MTR1 2 Metrics: Comments Ratio Comments: 7% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 24dec - 1exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 131 3

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