今天实现了数据从Sybase 12.5到MS SqlServer 2000的移植,可是代理表的问题没解决,MS SqlServer 2000的远程数据要通过存储过程才能实现。可是Sybase的代理表在查询时就跟访问本地库表一样。只能修改后手动更新了。
1. 取得所有用户表名:
SELECT
name
FROM
sysobjects
where
type
=
'
U
'
2. 单表的操作,省略部分,该抓的抓,该关的关就行了。
try
{
Scon = sybaseDBConnection();
// System.out.println("查询语句: " + "select * from " + tableName[i]);
pstmt = Scon.prepareStatement( " select * from " + tableName[i]);
Acon = sql2000DBConnection();
rs = pstmt.executeQuery();
colNum = rs.getMetaData().getColumnCount(); // 取得单表的列数
StringBuffer tempBuf = new StringBuffer();
for ( int j = 1 ; j <= colNum; j ++ ) {
tempBuf.append( " ,? " );
}
tempBuf.deleteCharAt( 0 );
tempSql = " insert into " + tableName[i] + " values( " + tempBuf.toString() +
" ) " ;
// System.out.println("tempSql: " + tempSql);
while (rs.next()) {
try {
pstmt2 = Acon.prepareStatement(tempSql);
for ( int j = 1 ; j <= colNum; j ++ ) {
rs.getString(j); // 必须首先对列调用一个获取方法来尝试读取其值,然后调用 wasNull 方法查看读取的值是否为 SQL NULL
if (rs.wasNull()) { // 报告最后一个读取的列是否具有值 SQL NULL
// System.out.println("null");
// System.out.println("type: " + rs.getMetaData().getColumnType(j));
// 将指定参数设置为 SQL NULL
pstmt2.setNull(j, rs.getMetaData().getColumnType(j)); // rs.getMetaData().getColumnType(j)取得列的类型。例如:VARCHAR 12
}
else {
// System.out.println(rs.getMetaData().getColumnName(j) + ": " + rs.getString(j));
pstmt2.setObject(j, rs.getObject(j));
}
}
pstmt2.executeUpdate();
pstmt2.close();
}
catch (Exception e) {
}
finally {
try {
if (pstmt2 != null )
pstmt2.close();
}
catch (Exception e) {
}
}
}
rs.close();
}
Scon = sybaseDBConnection();
// System.out.println("查询语句: " + "select * from " + tableName[i]);
pstmt = Scon.prepareStatement( " select * from " + tableName[i]);
Acon = sql2000DBConnection();
rs = pstmt.executeQuery();
colNum = rs.getMetaData().getColumnCount(); // 取得单表的列数
StringBuffer tempBuf = new StringBuffer();
for ( int j = 1 ; j <= colNum; j ++ ) {
tempBuf.append( " ,? " );
}
tempBuf.deleteCharAt( 0 );
tempSql = " insert into " + tableName[i] + " values( " + tempBuf.toString() +
" ) " ;
// System.out.println("tempSql: " + tempSql);
while (rs.next()) {
try {
pstmt2 = Acon.prepareStatement(tempSql);
for ( int j = 1 ; j <= colNum; j ++ ) {
rs.getString(j); // 必须首先对列调用一个获取方法来尝试读取其值,然后调用 wasNull 方法查看读取的值是否为 SQL NULL
if (rs.wasNull()) { // 报告最后一个读取的列是否具有值 SQL NULL
// System.out.println("null");
// System.out.println("type: " + rs.getMetaData().getColumnType(j));
// 将指定参数设置为 SQL NULL
pstmt2.setNull(j, rs.getMetaData().getColumnType(j)); // rs.getMetaData().getColumnType(j)取得列的类型。例如:VARCHAR 12
}
else {
// System.out.println(rs.getMetaData().getColumnName(j) + ": " + rs.getString(j));
pstmt2.setObject(j, rs.getObject(j));
}
}
pstmt2.executeUpdate();
pstmt2.close();
}
catch (Exception e) {
}
finally {
try {
if (pstmt2 != null )
pstmt2.close();
}
catch (Exception e) {
}
}
}
rs.close();
}
3. 程序完善,读Properties文件,动态的源数据库,目标数据库,及它们的相应属性,执行过程界面显示。不过对于一个专用程序来说可免了哦,再说吧。。。