近來用Dengues測試了一個Oracle的Blob類型數據轉移,即同一數據庫下的表之間的Blob類型字段,不同數據庫不同表的Blob字段。但是在測試后者的時候,會發生：
ORA-00942: 表或視圖不存在。后來發現使用java.sql.PreparedStatement pstmt;pstmt.setObject(2,blob);這個方法在傳入的是blob實例的時候再執行.
先用下面方法新建一個表:在SQL*Plus里面執行:
create table
T_IMAGE(T_IMAGE_ID varchar(20),T_IMAGE_BLOB BLOB);
并插入Blob:
1 public static void createBLOB() {
2 OutputStream bos = null;
3 FileInputStream inputStream = null;
4 ResultSet rs2 = null;
5 PreparedStatement ps2 = null;
6 Connection conn = null;
7 try {
8 Class.forName("oracle.jdbc.driver.OracleDriver");
9 conn = DriverManager.getConnection(
10 "jdbc:oracle:thin:@localhost:1521:dengues", "root", "root");
11
12 conn.setAutoCommit(false);
13 // 第一步：插入一個空的BLOB
14 String sql1 = "insert into T_IMAGE(T_IMAGE_ID,T_IMAGE_BLOB) values ('24',EMPTY_BLOB())";
15 PreparedStatement ps1 = conn.prepareStatement(sql1);
16 ps1.executeUpdate();
17 System.out.println("insert success.");
18 ps1.close();
19
20 // 第二步：取出該CLOB
21 String sql2 = "select T_IMAGE_BLOB from T_IMAGE where T_IMAGE_ID='24' for update";
22 ps2 = conn.prepareStatement(sql2);
23 rs2 = ps2.executeQuery();
24 while (rs2.next()) {
25 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs2.getBlob(1);
26 bos = blob.getBinaryOutputStream();
27 inputStream = new FileInputStream("c:/ibm.xml");
28 byte[] b = new byte[blob.getBufferSize()];
29 int len = 0;
30 while ((len = inputStream.read(b)) != -1) {
31 bos.write(b, 0, len);
32 }
33 bos.flush();
34 bos.close();
35 }
36 rs2.close();
37 inputStream.close();
38 ps2.close();
39 conn.setAutoCommit(true);
40 conn.commit();
41 conn.close();
42 } catch (Exception e) {
43 e.printStackTrace();
44 }
45 }
46
但是將這條記錄轉換到另一個數據庫testdb，先取出dengues數據庫里面的數據，并使用pstmt.setObject(2,blob)但是這樣執行pstmt.executeUpdate()。就會出現異常。估計這個是Oracle驅動的一個Bug。
為了改正這個問題:pstmt.setObject(2,cBLOB(blob));
下面是cBLOB的實現:
1 public static Object cBLOB(Object in) {
2 Object blob = in;
3 try {
4 if (in instanceof oracle.sql.BLOB) {
5 oracle.sql.BLOB bo = ((oracle.sql.BLOB) in);
6 InputStream bis = bo.getBinaryStream();
7 BufferedInputStream ins = new BufferedInputStream(bo
8 .getBinaryStream());
9 int bufferSize = (int) bo.length();
10 byte[] bt = new byte[bufferSize];
11 try {
12 ins.read(bt, 0, bufferSize);
13 } catch (IOException e) {
14 e.printStackTrace();
15 }
16 bis.close();
17 blob = bt;
18 }
19 } catch (Exception e) {
20 e.printStackTrace();
21 }
22 return blob;
23 }
就是將這個BLOB實例轉換為一個byte[]返回給傳入.
Dengues論壇(http://groups.google.com/group/dengues/),一個很好的Eclipse開發者樂園.