| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_datatype_info | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 create procedure sp_jdbc_datatype_info 4 as 5 6 if @@trancount = 0 7 begin 8 set chained off 9 end 10 11 set transaction isolation level 1 12 13 /* this will make sure that all rows are sent even if 14 ** the client "set rowcount" is differect 15 */ 16 17 set rowcount 0 18 19 select /* Real SQL Server data types */ 20 case 21 when t.name = 'usmallint' then 'unsigned smallint' 22 when t.name = 'uint' then 'unsigned int' 23 when t.name = 'ubigint' then 'unsigned bigint' 24 else 25 t.name 26 end as TYPE_NAME, 27 d.data_type as DATA_TYPE, 28 isnull(d.data_precision, convert(int, t.length)) as 'PRECISION', 29 d.literal_prefix as LITERAL_PREFIX, 30 d.literal_suffix as LITERAL_SUFFIX, 31 e.create_params as CREATE_PARAMS, 32 d.nullable as NULLABLE, 33 d.case_sensitive as CASE_SENSITIVE, 34 d.searchable as SEARCHABLE, 35 d.unsigned_attribute as UNSIGNED_ATTRIBUTE, 36 d.money as FIXED_PREC_SCALE, 37 d.auto_increment as AUTO_INCREMENT, 38 d.local_type_name as LOCAL_TYPE_NAME, 39 d.minimum_scale as MINIMUM_SCALE, 40 d.maximum_scale as MAXIMUM_SCALE, 41 d.sql_data_type as SQL_DATA_TYPE, 42 d.sql_datetime_sub as SQL_DATETIME_SUB, 43 d.num_prec_radix as NUM_PREC_RADIX, 44 d.interval_precision as INTERVAL_PRECISION 45 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 46 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 47 where d.ss_dtype = t.type and t.usertype *= e.user_type 48 /* restrict results to 'real' datatypes, exclude float, date and time*/ 49 and t.name not in ('nchar', 'nvarchar', 'sysname', 'timestamp', 'longsysname', 'float', 50 'datetimn', 'floatn', 'intn', 'moneyn', 'unichar', 51 'univarchar', 'daten', 'timen', 'date', 'time', 'uintn') 52 and t.usertype < 100 /* No user defined types */ 53 UNION 54 select /* SQL Server user data types */ 55 case 56 when t.name = 'usmallint' then 'unsigned smallint' 57 when t.name = 'uint' then 'unsigned int' 58 when t.name = 'ubigint' then 'unsigned bigint' 59 else 60 t.name 61 end as TYPE_NAME, 62 d.data_type, 63 isnull(d.data_precision, convert(int, t.length)) as 'PRECISION', 64 d.literal_prefix, d.literal_suffix, e.create_params, d.nullable, 65 d.case_sensitive, d.searchable, d.unsigned_attribute, d.money, 66 d.auto_increment, t.name, d.minimum_scale, 67 d.maximum_scale, d.sql_data_type, d.sql_datetime_sub, 68 d.num_prec_radix, d.interval_precision 69 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 70 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 71 where d.ss_dtype = t.type and t.usertype *= e.user_type 72 /* Restrict to user defined types (value > 100) and Sybase user defined 73 ** types (listed)*/ 74 and (t.name in ('nchar', 'nvarchar') 75 or t.usertype >= 100) /* User defined types */ 76 UNION 77 select /* ADD double precision which is floatn internally*/ 78 'double precision', 8, 15, 79 d.literal_prefix, d.literal_suffix, e.create_params, d.nullable, 80 d.case_sensitive, d.searchable, d.unsigned_attribute, d.money, 81 d.auto_increment, 'double precision', d.minimum_scale, 82 d.maximum_scale, d.sql_data_type, d.sql_datetime_sub, 83 d.num_prec_radix, d.interval_precision 84 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 85 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 86 where d.ss_dtype = t.type and t.usertype *= e.user_type 87 and t.name = 'floatn' and t.usertype < 100 88 UNION /* ADD float now */ 89 select 90 'float', 8, 8, 91 d.literal_prefix, d.literal_suffix, e.create_params, d.nullable, 92 d.case_sensitive, d.searchable, d.unsigned_attribute, d.money, 93 d.auto_increment, 'float', d.minimum_scale, 94 d.maximum_scale, d.sql_data_type, d.sql_datetime_sub, 95 d.num_prec_radix, d.interval_precision 96 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 97 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 98 where d.ss_dtype = t.type and t.usertype *= e.user_type 99 and t.name = 'float' and t.usertype < 100 100 UNION 101 select /* Add date and time now. Special case because we want to use */ 102 /* d.sql_data_type for DATA_TYPE for these two types */ 103 case 104 when t.name = 'usmallint' then 'unsigned smallint' 105 when t.name = 'uint' then 'unsigned int' 106 when t.name = 'ubigint' then 'unsigned bigint' 107 else 108 t.name 109 end as TYPE_NAME, 110 d.sql_data_type, 111 isnull(d.data_precision, convert(int, t.length)), 112 d.literal_prefix, 113 d.literal_suffix, 114 e.create_params, 115 d.nullable, 116 d.case_sensitive, 117 d.searchable, 118 d.unsigned_attribute, 119 d.money, 120 d.auto_increment, 121 d.local_type_name, 122 d.minimum_scale, 123 d.maximum_scale, 124 d.sql_data_type, 125 d.sql_datetime_sub, 126 d.num_prec_radix, 127 d.interval_precision 128 from sybsystemprocs.dbo.spt_jdbc_datatype_info d, 129 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 130 where d.ss_dtype = t.type and t.usertype *= e.user_type 131 /* restrict results to date and time*/ 132 and t.name in ('date', 'time') 133 and t.usertype < 100 /* No user defined types */ 134 order by DATA_TYPE, TYPE_NAME 135 136 return (0) 137
exec sp_procxmode 'sp_jdbc_datatype_info', 'AnyMode' go Grant Execute on sp_jdbc_datatype_info to public go
| RESULT SETS | |
| sp_jdbc_datatype_info_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..systypes reads table sybsystemprocs..spt_jdbc_datatype_info reads table sybsystemprocs..spt_datatype_info_ext |
