从syscolumns得到的列怎样才能对应systypes得到列的名称

[magicsnake]

我使用syscolumns中的type,usertype与systypes中的type,usertype对应,有些列类型在systypes找不到,比如我有这样一个列:
XSSR numeric(12,2) null
我根据表id和列名称在syscolumns找到的记录对应的type=108,usertype=10
但是在systypes中type=108的只有一条记录,他的usertype=28

这个问题怎么解决?

[hobbylu]

你只要使用usertype相对应的记录就可以了

[wgzymzx]

/********************************************
/* 用于取得表定义: 只能取得表的字段定义
/* 包括字段名，数据类型，数据长度、精度，默认值，是否允许为空，唯一约束关系
/* 需要6个参数：
/* 浣熊 2004-11-2
*********************************************/

import java.util.*;
import java.sql.*;

public class TableDDL
{
public static String getDDL(String HostIp,String Port,String DbName,String UserID,String PassWD,String TableName)
{
String dbUrl="jdbc:sybase:Tds:"+HostIp+":"+Port+"/"+DbName;
String DBDriver = "com.sybase.jdbc2.jdbc.SybDriver";
String sql="";
String str="",str2="";
String s1="";
int i,j,k,flag=0;

try
{
//取得表的DDL定义
sql=" select C.name, "+
" isnull(X.xtname, isnull(get_xtypename(C.xtype,C.xdbid), T.name)), "+
" C.length, "+
" C.prec, "+
" C.scale, "+
" C.status, "+
" T.usertype ,"+
" (select text FROM syscomments D,sysprocedures P "+
" WHERE D.id = C.cdefault AND P.id = D.id "+
" AND P.sequence = 0 AND P.status & 4096 = 4096) "+
" FROM syscolumns C, systypes T, sysxtypes X, sysobjects O "+
" WHERE C.usertype *= T.usertype "+
" AND C.xtype *= X.xtid "+
" AND C.id = O.id "+
" AND O.name = '"+TableName+"' "+
" AND O.type = 'U' "+
" ORDER BY C.colid ";
Class.forName(DBDriver);
Connection conn=DriverManager.getConnection(dbUrl,UserID,PassWD);
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
while(rs!=null && rs.next())
{
flag=1; //该表存在
str=str+AddSpace(rs.getString(1),35); //字段名
s1=rs.getString(2); //字段类型

//定义字段长度、精度
str2="";
if(s1.equals("binary") || s1.equals("char") || s1.equals("nchar") || s1.equals("nvarchar") || s1.equals("unichar") || s1.equals("univarchar") || s1.equals("varbinary") || s1.equals("varchar"))
str2 = str2 + "(" + rs.getString(3) + ")";
if(s1.equals("float"))
{
i = (new Integer(rs.getString(3))).intValue();
str2 = str2 + "(" + i * 2 + ")";
}
if(rs.getInt(7)<=100)
{
if(rs.getString(4)!=null && rs.getString(5)!=null)
{
if(!rs.getString(4).equals("") && !rs.getString(5).equals(""))
str2 = str2 + "(" + rs.getString(4) + "," + rs.getString(5) + ")";
}
}
str=str+AddSpace(s1+str2,20);

//定义字段是否有默认值,是否允许为空，
if(rs.getString(8)!=null)
{
str = str+rs.getString(8);
}
else
{
if(rs.getInt(6) == 0)
str = str +"not null";
else
if((rs.getInt(6) & 0x80) == 128)
str = str + "identity";
else
str = str + "null";
}

str = str+",\r\n";
}
if(conn!=null) conn.close();
if(flag==0) return "";

//定义主键和唯一约束关系
sql = " select b.keycnt, b.name, b.indid, b.status, b.status2 "+
" from sysobjects a, sysindexes b "+
" where a.id=b.id and a.type='U' and b.indid>0 "+
" and b.status2&2=2 and a.name='"+TableName+"'";
Class.forName(DBDriver);
Connection conn2=DriverManager.getConnection(dbUrl,UserID,PassWD);
Statement stmt2=conn2.createStatement();
ResultSet rs2=stmt2.executeQuery(sql);
while(rs2!=null && rs2.next())
{
str=str+"CONSTRAINT ";
str=str+rs2.getString(2)+" ";
if((rs2.getInt(4)&0x800) == 2048)
str=str+" PRIMARY KEY ";
else
str=str+" UNIQUE ";

j=0;

if(rs2.getInt(3)==1)
{
str=str+" CLUSTERED ";
j=rs2.getInt(1)+1;
}

if(rs2.getInt(3) > 1)
if((rs2.getInt(5)&0x200) == 512)
{
str=str+" CLUSTERED ";
j=rs2.getInt(1)+1;
}
else
{
str=str+" NONCLUSTERED ";
j=rs2.getInt(1);
}
str=str+" ( ";

for(k=1;k<j;k++)
{
sql=" select index_col('"+TableName+"',"+rs2.getInt(3)+","+k+")";
Class.forName(DBDriver);
Connection conn3=DriverManager.getConnection(dbUrl,UserID,PassWD);
Statement stmt3=conn3.createStatement();
ResultSet rs3=stmt3.executeQuery(sql);
while(rs3!=null && rs3.next())
str=str+rs3.getString(1)+",";
if(conn3!=null) conn3.close();
}
str=str.substring(0,str.length()-1)+"),\r\n";
}
if(conn2!=null) conn2.close();

str = str.substring(0,str.length()-3);

str = "create table dbo."+TableName+"(\r\n"+str+"\r\n)\r\ngo\r\n";
}
catch(Exception e){
System.out.println("except:="+e.getMessage());
}
return str;
}

public static String AddSpace(String paStr,int palen)
{
int i,j;
j=paStr.length();
for(i=0;i<palen-j;i++)
paStr=paStr+" ";
return paStr;
}
}
这是我照Sybase的文档写的，可以取出表的定义，不过有限制——注释中写了。
自己看看吧，希望对你有帮助