首页 > > 网络编程 > 其它 >

Sybase SQL anywhere5.5

2018-06-17 20:03:59来源：未知阅读 ()

https://github.com/mono/old-code

https://wiki.scn.sap.com/wiki/display/SQLANY/SQL+Anywhere+and+Microsoft+.NET

http://www.mono-project.com/docs/database-access/providers/sybase/

http://dcx.sybase.com/sa160/en/pdf/index.html

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbprogramming/adodotnet-development-secta-3832474.html

示例數据庫：C:\Program Files\Sybase\SQL Anywhere 5.0\sademo.db
用戶名:DBA 密碼:sql
示例腳本: jcatalog.sql

if (exists (select * from sysobjects
  where name = 'spt_datatype_info' and type = 'U'))
    drop table spt_datatype_info
go
create table spt_datatype_info
(
 ss_dtype    tinyint not null,
 TYPE_NAME          varchar(30)  not null,
 DATA_TYPE    smallint not null,
 typelength         int          not null,
 LITERAL_PREFIX     varchar(32)  null,
 LITERAL_SUFFIX     varchar(32)  null,
 CREATE_PARAMS      varchar(32)  null,
 NULLABLE           smallint     not null,
 CASE_SENSITIVE     smallint     not null,
 SEARCHABLE         smallint     not null,
 UNSIGNED_ATTRIBUTE smallint     null,
 FIXED_PREC_SCALE   smallint     not null,
 -- MONEY              smallint     not null,
 AUTO_INCREMENT     smallint     null,
 LOCAL_TYPE_NAME    varchar(128) not null,
 MINIMUM_SCALE    smallint null,
 MAXIMUM_SCALE    smallint null,
 SQL_DATA_TYPE    smallint null,
 SQL_DATETIME_SUB   smallint null,
 NUM_PREC_RADIX    smallint null
 -- INTERVAL_PRECISION smallint null
)
go

grant select on spt_datatype_info to PUBLIC
go

insert into spt_datatype_info values
(48, 'tinyint', -6, 3, NULL, NULL, NULL, 1, 0, 2, 1, 0, 0, 'tinyint',   
NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(34, 'image', -4, 2147483647, '0x', NULL, NULL, 1, 0, 1, NULL, 0, NULL,   
'image', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(37, 'varbinary', -3, 255, '0x', NULL, 'max length', 1, 0, 2, NULL, 0,   
NULL, 'varbinary', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(45, 'binary', -2, 255, '0x', NULL, 'length', 1, 0, 2, NULL, 0, NULL,   
'binary', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(35, 'text', -1, 2147483647, '''', '''', NULL, 1, 1, 1, NULL, 0, NULL,   
'text', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(47, 'char', 1, 255, '''', '''', 'length', 1, 1, 3, NULL, 0, NULL,   
'char', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(63, 'numeric', 2, 38, NULL, NULL, 'precision,scale', 1, 0, 2, 0, 0, 0,   
'numeric', 0, 38, NULL, NULL, NULL)
insert into spt_datatype_info values
(55, 'decimal', 3, 38, NULL, NULL, 'precision,scale', 1, 0, 2, 0, 0, 0,   
'decimal', 0, 38, NULL, NULL, NULL)
insert into spt_datatype_info values
(60, 'money', 3, 12, '$', NULL, NULL, 1, 0, 2, 0, 1, 0, 'money', NULL,   
NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(122, 'smallmoney', 3, 8, '$', NULL, NULL, 1, 0, 2, 0, 1, 0,   
'smallmoney', NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(56, 'int', 4, 4, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'int', NULL, NULL,   
NULL, NULL, NULL)
insert into spt_datatype_info values
(52, 'smallint', 5, 2, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'smallint',   
NULL, NULL, NULL, NULL, NULL)
insert into spt_datatype_info values
(62, 'float', 8, 8, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'double', NULL,   
NULL, NULL, NULL, 10)
insert into spt_datatype_info values
(59, 'real', 7, 7, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'real', NULL,   
NULL, NULL, NULL, 10)
insert into spt_datatype_info values
(61, 'datetime', 93, 23, '''', '''', NULL, 1, 0, 3, NULL, 0, NULL,   
'datetime', NULL, NULL, 93, NULL, NULL)
insert into spt_datatype_info values
(58, 'smalldatetime', 93, 16, '''', '''', NULL, 1, 0, 3, NULL, 0, NULL,   
'smalldatetime', NULL, NULL, 93, NULL, NULL)
insert into spt_datatype_info values
(39, 'varchar', 12, 255, '''', '''', 'max length', 1, 1, 3, NULL, 0,   
NULL, 'varchar', NULL, NULL, NULL, NULL, NULL)
go


if exists (select * from sysobjects where name =
    'sp_jdbc_function_escapes')
    begin
        drop procedure sp_jdbc_function_escapes
    end
go

create procedure sp_jdbc_function_escapes
as
    select * from DBA.jdbc_function_escapes

go

grant execute on sp_jdbc_function_escapes to PUBLIC
go

/*
**   sp_jdbc_tables
**
*/

if exists (select * from sysobjects where name = 'sp_jdbc_tables')
begin
    drop procedure sp_jdbc_tables
end
go

create procedure sp_jdbc_tables
 @table_name  varchar(128)  = null,
 @table_owner varchar(128)  = null,
 @table_qualifier        varchar(128)  = null,
 @table_type  varchar(64) = null
as
declare @id int
declare @searchstr char(10)

if @table_name is null select @table_name = '%'

select @id = id from sysobjects where name like @table_name 
if (@id is null)
begin	
 	raiserror 17461 'Table does not exist'
	return (3)
end

if (patindex('%table%',lcase(@table_type)) > 0)
  	select @table_type = 'base'

if (patindex('%base%',lcase(@table_type)) > 0)
	select @searchstr = 'base'

else if (patindex('%view%',lcase(@table_type)) > 0) 
	select @searchstr = 'view' 

else if @table_type is null 
 	select @searchstr = '%' 
else
begin
	raiserror 99998 'Only valid table types are TABLE, BASE, VIEW or null'
	return(3)
end

if @table_owner is null select @table_owner = '%'


select
   TABLE_CAT = 	substring(db_name(),1,length(db_name())),
   TABLE_SCHEM=	substring(user_name(creator),1,length(user_name(creator))),
   TABLE_NAME = substring(table_name,1,length(table_name)),
   TYBLE_TYPE = substring(table_type,1,length(table_type)),
   REMARKS=	remarks
from systable where table_name like @table_name and
user_name(creator) like @table_owner and table_type like @searchstr
order by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME

go