求助：jdbc连接oracle数据库出现错误

Question

我用的oracle8i，classes12.jar和nls_charset12.zip已经放在jdk的lib下面了，也已经配置了classpath。
程序的目的是把远程数据库里面的数据倒到本地数据库的一个表里面来。
错误信息：
Exception in thread "main" java.sql.SQLException: 违反协议
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:124)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:161)
at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:884)
at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:132)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:384)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1
035)
at ExtractData.extData(ExtractData.java:134)
at ExtractData.main(ExtractData.java:143) 

Answer 1

该错误是Oracle的JDBC驱动中的一个标准错误Protocol violation （ORA-17401,即协议冲突
引起该错误的原因有很多，Oracel几乎将不能具体分类的错误都归结于它，所以错误原因也比较难找，只能通过多测试不同记录来找到原因。
建议贴出你的代码

Answer 2

源代码：import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.NumberFormat;
import javax.naming.*;public class ExtractData{
   String driverName = "oracle.jdbc.driver.OracleDriver";
   Driver d;
   Connection remoteConn;
   Connection localConn;
   
   public void connectRemoteDb( ){
      try{
         d = (Driver)Class.forName(driverName).newInstance();
         remoteConn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.200:1521:ORCL","username","password");
      }catch(Exception e){
         System.out.println("Connecting RemoteDB error! " + e.toString());
         return;
      }
   }
   
   public void closeRemoteConn( ){
      try{
         if( remoteConn!=null ){
            remoteConn.close();
         }
      }catch(Exception e){
         System.out.println("Closing remoteConn error! " + e.toString());
         return;
      }  
   }
   
   public void connectLocalDb( ){
      try{
         d = (Driver)Class.forName(driverName).newInstance();
         localConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","username","password");
      }catch(Exception e){
         System.out.println("Connecting localDB error! " + e.toString());
         return;
      }
   }
   
   public void closeLocalConn( ){
      try{
         if( localConn!=null ){
            localConn.close();
         }
      }catch(Exception e){
         System.out.println("Closing LocalConn error! " + e.toString());
         return;
      }  
   }
   
   public boolean extData() throws SQLException{
  PreparedStatement queryStmt = null;
ResultSet queryRs = null;
String      querySql    = "";
PreparedStatement insertStmt = null;
String      insertSql   = "";

try{
querySql = "select khid,khdm,khmc,khsx,khsxmc,ywyid,ywyxm,lxr,dizhi,sfid,sfmc,dqid,dqmc,jjsj,jlrq,yingfu,yifu,yufu,gspqk,gmpqk,bzkhid,bmh,bmmc,jsts from kehu where khdm like 'G%'";
insertSql = "insert into supplier(supplierid,suppliercode,suppliername,countermanid,countermanname,provinceid,provincename,linkman,address,accountpayable,sumpaid,advancepayment,balancedays,gsp,gmp,relationtime,registertime,storatesyscode,departmentid,departmentname,supplieratrribute,supplierattributecode,areaid,areaname) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

queryStmt = remoteConn.prepareStatement(querySql);
insertStmt = localConn.prepareStatement(insertSql);

queryRs = queryStmt.executeQuery();
while(queryRs.next()){
int supplierid = queryRs.getInt("khid");
String suppliercode = queryRs.getString("khdm");
String suppliername = queryRs.getString("khmc");
int countermanid = queryRs.getInt("ywyid");
String counermanname = queryRs.getString("ywyxm");
int provinceid = queryRs.getInt("sfid");
String provincename = queryRs.getString("sfmc");
String linkman = queryRs.getString("lxr");
String address = queryRs.getString("dizhi");
double accountpayable = queryRs.getDouble("yingfu");
double sumpaid = queryRs.getDouble("yifu");
double advancepayment = queryRs.getDouble("yufu");
int balancedays = queryRs.getInt("jsts");
String gsp = queryRs.getString("gspqk");
String gmp = queryRs.getString("gmpqk");
java.sql.Date relationtime = queryRs.getDate("jjsj");
java.sql.Date registertime = queryRs.getDate("jlrq");
int storagesyscode = queryRs.getInt("bzkhid");
int departmentid = queryRs.getInt("bmh");
String departmentname = queryRs.getString("bmmc");
int supplierattributecode = queryRs.getInt("khsx");
String supplierattribute = queryRs.getString("khsxmc");
int areaid = queryRs.getInt("dqid");
String areaname = queryRs.getString("dqmc");

insertStmt.setInt(1,supplierid);
insertStmt.setString(2,suppliercode);
insertStmt.setString(3,suppliername);
insertStmt.setInt(4,countermanid);
insertStmt.setString(5,counermanname);
insertStmt.setInt(6,provinceid);
insertStmt.setString(7,provincename);
insertStmt.setString(8,linkman);
insertStmt.setString(9,address);
insertStmt.setDouble(10,accountpayable);
insertStmt.setDouble(11,sumpaid);
insertStmt.setDouble(12,advancepayment);
insertStmt.setInt(13,balancedays);
insertStmt.setString(14,gsp);
insertStmt.setString(15,gmp);
insertStmt.setDate(16,relationtime);
insertStmt.setDate(17,registertime);
insertStmt.setInt(18,storagesyscode);
insertStmt.setInt(19,departmentid);
insertStmt.setString(20,departmentname);
insertStmt.setString(21,supplierattribute);
insertStmt.setInt(22,supplierattributecode);
insertStmt.setInt(23,areaid);
insertStmt.setString(24,areaname);

insertStmt.executeUpdate();
}
}catch (SQLException sqle) {
throw sqle;
}catch(Exception e){
System.out.println("queryData failed! " + e.toString());
return false;
}finally{
if( queryRs!=null ) queryRs.close();
if( queryStmt!=null ) queryStmt.close();
if( insertStmt!=null ) insertStmt.close();
if( localConn != null ) localConn.close();
if( remoteConn != null ) remoteConn.close();
}
return true;
   }
   
   public static void main(String args[]) throws Exception{
        try{
      ExtractData ext = new ExtractData();
      ext.connectRemoteDb();
      ext.connectLocalDb();
      ext.extData();
      ext.closeRemoteConn();
      ext.closeLocalConn();
}catch (SQLException sqle) {
throw sqle;
}
   }
}

Answer 3

Class.forName(driverName) ;这样就可以了吧

Answer 4

呵呵，弄好了。原来是我的classes12包不对，应该是classes111.zip

Answer 5

是需要注意jar包的，oracle的jdbc驱动比较混乱，一个不行就换另一个是非常必要的。这在9i上更加明显。
同时，如果针对9i使用8i的jar包，有的时候取出的数据甚至都变成0101010101001这种模样。