前言
要在sybase上准备一个表, 将全部可能的数据类型都作为表字段.
scjview.exe中添加字段时，可以看到可用的数据类型.
sybase可用的数据类型和sqlserver有点不同。
实验
查看sybase版本
sp_version
goScript,Version,Status
'ODBC MDA Scripts','15.7.0.403.1011/Fri Jul 06 UTC 00:41:49 2012','Complete'
'OLEDB MDA Scripts','15.7.0.403.1011/Fri Jul 06 UTC 00:16:26 2012','Complete'
'installcommit','15.7.0/EBF 20371 SMP ESD#02 /P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 01:45:36 2012','Complete'
'installjdbc','jConnect (TM) for JDBC(TM)/7.07 ESD #4 (Build 26793)/P/EBF20302/JDK 1.6.0/jdbcmain/OPT/Thu Jul 5 22:08:44 PDT 2012','Complete'
'installjsdb','15.7.0/EBF 20371 SMP ESD#02 /P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 01:45:36 2012','Complete'
'installmaster','15.7.0/EBF 20371 SMP ESD#02 /P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 01:45:36 2012','Complete'
'installmodel','15.7.0/EBF 20371 SMP ESD#02 /P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 01:45:36 2012','Complete'
'montables','15.7.0/20371/P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 07:53:17 2012','Complete'
select @@version
go
'Adaptive Server Enterprise/15.7.0/EBF 20371 SMP ESD#02 /P/NT (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07 01:14:05 2012'
版本引起的数据类型缺失
在sybase15.7.0中有数据类型 ：bigtime, bigdatetime.
在sybase15.0.2_demo版中没有bigtime, bigdatetime 这2种数据类型.
建表
-- create table
use my_db_a;
drop table table_full_data_type;
create table table_full_data_type (
field_id int not null ,
field_bit bit DEFAULT 0 not null ,
field_tinyint tinyint null ,
field_smallint smallint null ,
field_unsigned_smallint unsigned smallint null ,
field_int int null ,
field_unsigned_int unsigned int null ,
field_bigint bigint null ,
field_unsigned_bigint unsigned bigint null ,
field_decimal decimal(1,0) null ,
field_numeric numeric(1,0) null ,
field_float float(8) null ,
field_double_precision float(16) null ,
field_real real null ,
field_smallmoney smallmoney null ,
field_money money null ,
field_date date null ,
field_time time null ,
field_bigtime bigtime null ,
field_smalldatetime smalldatetime null ,
field_datetime datetime null ,
field_bigdatetime bigdatetime null ,
field_timestamp timestamp null ,
field_char char(1) null ,
field_nchar nchar(1) null ,
field_unichar unichar(1) null ,
field_varchar varchar(1024) null ,
field_nvarchar nvarchar(1) null ,
field_univarchar univarchar(1) null ,
field_text text null ,
field_unitext unitext null ,
field_sysname sysname(30) null ,
field_longsysname longsysname null ,
field_binary binary(1) null ,
field_varbinary varbinary(256) null ,
field_image image null
);
commit;
插入数据
简单的先插入一行, 用户手工编译或用update语句对单个字段进行试错。
字段的值和属性设置有关系，update单个字段可以试出该设置的正确的值范围。
// insert data
INSERT INTO table_full_data_type
(field_id,
field_bit)
VALUES (1000,
1);
// 数据小块编辑完了，就可以用工具生成SQL的模板(insert, update)
// 在 scjview.exe 中 用Interactive SQL(dbisql.exe)生成SQL模板
INSERT INTO dbo.table_full_data_type (field_id,field_bit,field_tinyint,field_smallint,field_unsigned_smallint,field_int,field_unsigned_int,field_bigint,field_unsigned_bigint,field_decimal,field_numeric,field_float,field_double_precision,field_real,field_smallmoney,field_money,field_date,field_time,field_bigtime,field_smalldatetime,field_datetime,field_bigdatetime,field_timestamp,field_char,field_nchar,field_unichar,field_varchar,field_nvarchar,field_univarchar,field_text,field_unitext,field_sysname,field_longsysname,field_binary,field_varbinary,field_image) VALUES(1000,1,2,3,4,5,6,7,8,9,1,2.345,3.456,4.567,5.6780,6.7890,'2018-05-23','09:53:20','09:53:45','2018-05-23 10:01:00.0','2018-05-23 10:01:01.0','2018-05-23 10:01:01.0',0x0000000000002387,'a','b','c','d_111111',e,'f','g_111111','h_111111','i_111111','j_111111',0x33,0x76617262696e6172795f76616c7565,NULL)
UPDATE dbo.table_full_data_type SET field_id=1000,field_bit=1,field_tinyint=2,field_smallint=3,field_unsigned_smallint=4,field_int=5,field_unsigned_int=6,field_bigint=7,field_unsigned_bigint=8,field_decimal=9,field_numeric=1,field_float=2.345,field_double_precision=3.456,field_real=4.567,field_smallmoney=5.6780,field_money=6.7890,field_date='2018-05-23',field_time='09:53:20',field_bigtime='09:53:45',field_smalldatetime='2018-05-23 10:01:00.0',field_datetime='2018-05-23 10:01:01.0',field_bigdatetime='2018-05-23 10:01:01.0',field_timestamp=0x0000000000002387,field_char='a',field_nchar='b',field_unichar='c',field_varchar='d_111111',field_nvarchar=e,field_univarchar='f',field_text='g_111111',field_unitext='h_111111',field_sysname='i_111111',field_longsysname='j_111111',field_binary=0x33,field_varbinary=0x76617262696e6172795f76616c7565,field_image=null WHERE field_id=1000 AND field_bit=1 AND field_tinyint=2 AND field_smallint=3 AND field_unsigned_smallint=4 AND field_int=5 AND field_unsigned_int=6 AND field_bigint=7 AND field_unsigned_bigint=8 AND field_decimal=9 AND field_numeric=1 AND field_float=2.345 AND field_double_precision=3.456 AND field_real=4.567 AND field_smallmoney=5.6780 AND field_money=6.7890 AND field_date='2018-05-23' AND field_time='09:53:20' AND field_bigtime='09:53:45' AND field_smalldatetime='2018-05-23 10:01:00.0' AND field_datetime='2018-05-23 10:01:01.0' AND field_bigdatetime='2018-05-23 10:01:01.0' AND field_timestamp=0x0000000000002387 AND field_char='a' AND field_nchar='b' AND field_unichar='c' AND field_varchar='d_111111' AND field_nvarchar=e AND field_univarchar='f' AND field_text='g_111111' AND field_unitext='h_111111' AND field_sysname='i_111111' AND field_longsysname='j_111111' AND field_binary=0x33 AND field_varbinary=0x76617262696e6172795f76616c7565 AND field_image IS NULL
DELETE FROM dbo.table_full_data_type WHERE field_id=1000 AND field_bit=1 AND field_tinyint=2 AND field_smallint=3 AND field_unsigned_smallint=4 AND field_int=5 AND field_unsigned_int=6 AND field_bigint=7 AND field_unsigned_bigint=8 AND field_decimal=9 AND field_numeric=1 AND field_float=2.345 AND field_double_precision=3.456 AND field_real=4.567 AND field_smallmoney=5.6780 AND field_money=6.7890 AND field_date='2018-05-23' AND field_time='09:53:20' AND field_bigtime='09:53:45' AND field_smalldatetime='2018-05-23 10:01:00.0' AND field_datetime='2018-05-23 10:01:01.0' AND field_bigdatetime='2018-05-23 10:01:01.0' AND field_timestamp=0x0000000000002387 AND field_char='a' AND field_nchar='b' AND field_unichar='c' AND field_varchar='d_111111' AND field_nvarchar=e AND field_univarchar='f' AND field_text='g_111111' AND field_unitext='h_111111' AND field_sysname='i_111111' AND field_longsysname='j_111111' AND field_binary=0x33 AND field_varbinary=0x76617262696e6172795f76616c7565 AND field_image IS NULL
在Interactive SQL中执行多个SQL语句
sql1
go
sql2
go
sql …
go
然后按F5执行输入的所有sql, 如果每个sql后面没有go，f5报错。
插入多条数据，一起执行的例子
自己用SQL做一些数据出来。
image类型的数据用SQL不知道怎么插入，先用NULL吧。
找到的资料 ： https://stackoverflow.com/questions/1214462/how-to-insert-jpeg-into-a-sql-server-2000-database-field-of-image-type-using-tra
但是装的sybase没有OPENROWSET存储过程.
-- --------------------------------------------------------------------------------
-- 插入数据
-- --------------------------------------------------------------------------------
INSERT INTO dbo.table_full_data_type
(
field_id, field_bit, field_tinyint, field_smallint, field_unsigned_smallint, field_int, field_unsigned_int, field_bigint, field_unsigned_bigint,
field_decimal, field_numeric, field_float, field_double_precision, field_real, field_smallmoney, field_money,
field_date, field_time, field_bigtime, field_smalldatetime, field_datetime, field_bigdatetime, field_timestamp,
field_char, field_nchar, field_unichar, field_varchar, field_nvarchar, field_univarchar, field_text, field_unitext,
field_sysname, field_longsysname, field_binary, field_varbinary, field_image)
VALUES(
1000, 1, 2, 3, 4, 5, 6, 7, 8,
9, 1, 2.345, 3.456, 4.567, 5.6780, 6.7890,
'2018-05-23', '09:53:20', '09:53:45', '2018-05-23 10:01:00.0', '2018-05-23 10:01:01.0', '2018-05-23 10:01:01.0', 0x0000000000002387,
'a', 'b', 'c', 'd_111111', 'e', 'f', 'g_111111', 'h_111111',
'i_111111', 'j_111111', 0x33, 0x76617262696e6172795f76616c7565, NULL)
go
INSERT INTO dbo.table_full_data_type
(
field_id, field_bit, field_tinyint, field_smallint, field_unsigned_smallint, field_int, field_unsigned_int, field_bigint, field_unsigned_bigint,
field_decimal, field_numeric, field_float, field_double_precision, field_real, field_smallmoney, field_money,
field_date, field_time, field_bigtime, field_smalldatetime, field_datetime, field_bigdatetime, field_timestamp,
field_char, field_nchar, field_unichar, field_varchar, field_nvarchar, field_univarchar, field_text, field_unitext,
field_sysname, field_longsysname, field_binary, field_varbinary, field_image)
VALUES(
1001, 1, 2, 3, 4, 5, 6, 7, 8,
9, 1, 2.345, 3.456, 4.567, 5.6780, 6.7890,
'2018-05-23', '09:53:21', '09:53:45', '2018-05-23 10:01:00.0', '2018-05-23 10:01:01.0', '2018-05-23 10:01:01.0', 0x0000000000002387,
'a', 'b', 'c', 'd_111111', 'e', 'f', 'g_111111', 'h_111111',
'i_111111', 'j_111111', 0x33, 0x76617262696e6172795f76616c7565, NULL)
go
INSERT INTO dbo.table_full_data_type
(
field_id, field_bit, field_tinyint, field_smallint, field_unsigned_smallint, field_int, field_unsigned_int, field_bigint, field_unsigned_bigint,
field_decimal, field_numeric, field_float, field_double_precision, field_real, field_smallmoney, field_money,
field_date, field_time, field_bigtime, field_smalldatetime, field_datetime, field_bigdatetime, field_timestamp,
field_char, field_nchar, field_unichar, field_varchar, field_nvarchar, field_univarchar, field_text, field_unitext,
field_sysname, field_longsysname, field_binary, field_varbinary, field_image)
VALUES(
1002, 1, 2, 3, 4, 5, 6, 7, 8,
9, 1, 2.345, 3.456, 4.567, 5.6780, 6.7890,
'2018-05-23', '09:53:22', '09:53:45', '2018-05-23 10:01:00.0', '2018-05-23 10:01:01.0', '2018-05-23 10:01:01.0', 0x0000000000002387,
'a', 'b', 'c', 'd_111111', 'e', 'f', 'g_111111', 'h_111111',
'i_111111', 'j_111111', 0x33, 0x76617262696e6172795f76616c7565, NULL)
go
INSERT INTO dbo.table_full_data_type
(
field_id, field_bit, field_tinyint, field_smallint, field_unsigned_smallint, field_int, field_unsigned_int, field_bigint, field_unsigned_bigint,
field_decimal, field_numeric, field_float, field_double_precision, field_real, field_smallmoney, field_money,
field_date, field_time, field_bigtime, field_smalldatetime, field_datetime, field_bigdatetime, field_timestamp,
field_char, field_nchar, field_unichar, field_varchar, field_nvarchar, field_univarchar, field_text, field_unitext,
field_sysname, field_longsysname, field_binary, field_varbinary, field_image)
VALUES(
1003, 1, 2, 3, 4, 5, 6, 7, 8,
9, 1, 2.345, 3.456, 4.567, 5.6780, 6.7890,
'2018-05-23', '09:53:23', '09:53:45', '2018-05-23 10:01:00.0', '2018-05-23 10:01:01.0', '2018-05-23 10:01:01.0', 0x0000000000002387,
'a', 'b', 'c', 'd_111111', 'e', 'f', 'g_111111', 'h_111111',
'i_111111', 'j_111111', 0x33, 0x76617262696e6172795f76616c7565, NULL)
go
扫一扫
2278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
