Sybase SQL anywhere5.5

2018/3/7 9:55:02 人评论 次浏览 分类:操作技巧

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

  

相关知识

  • 我必须得告诉大家的MySQL优化原理

    说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?我想未必。因而理解这些优化建议背后的原理就尤为…

    2017/7/19 8:56:00
  • 优化SQL查询:如何写出高性能SQL语句

    1、 首先要搞明白什么叫执行计划?执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归…

    2017/7/19 8:56:00
  • memcached与redis实现的对比

    memcached和redis,作为近些年最常用的缓存服务器,相信大家对它们再熟悉不过了。前两年还在学校时,我曾经读过它们的主要源码,如今写篇笔记从个人角度简单对比一下它们的实现方式,权当做复习,有理解错误之处,欢迎指正。文中使用的架构类的图片大多来自于网络,有部分图…

    2017/7/19 8:56:00
  • SQL Server Management Studio 2016 最让人兴奋的10大新功能

    介绍这篇文章列出了SQL Server Management Studio 2016已经取得的一些令人兴奋的改进。我相信,大多数新功能可以帮助我们提升工作速度,更方便地使用SSMS。1.颜色主题随着SSMS 2016的到来,现在你可以选择自己喜欢的颜色主题,类似于Visual Studio中的相似功能。你可以在两种…

    2017/7/19 8:56:00

共有访客发表了评论 网友评论

验证码: 看不清楚?