java文件写入blob_java程序向IQ写入blob(image)类型数据问题的解决

最近一个用户的应用使用jConnect jdbc驱动程序访问Sybase IQ 15.2的数据库的一个表，这个表有个类型为long binary类型的字段，在插入数据超过32767个字节时会报如下错误：

SQL Anywhere Error -1000121: binary data not supported on data longer than 32767 Bind host variable,

-- (df_Heap.cxx 2858)

2013-05-12 16:04:41,108 WARN [http-8081-1] [JDBCExceptionReporter] SQL Error: 21, SQLState: QDB21

2013-05-12 16:04:41,108 ERROR [http-8081-1] [JDBCExceptionReporter] SQL Anywhere Error -1000121: binary data not supported on data longer than 32767 Bind host variable

为了诊断问题，在我自己的环境中编写了一个java程序进行再现和解决尝试，下面是测试表以及测试java程序：

1. 测试表

create table blob_test(id int, content blob)

2. 测试java程序

package net.db.test;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

public class JDBCDemo {

private static final String DRIVER = "com.sybase.jdbc3.jdbc.SybDriver";

private static final String URL = "jdbc:sybase:Tds:192.168.0.197:6000/tpchdb?CHARSET=cp936&TEXTSIZE=2147483647"; //OK

public JDBCDemo() {

}

private byte[] getFileContent(String filename) {

try {

File file = new File(filename);

int length = (int)file.length();

InputStream input = new FileInputStream(filename);

byte[] content = new byte[length];

input.read(content, 0, length);

return content;

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return null;

}

public void insert() throws Exception {

Connection con = this.getConnection(DRIVER, URL, "DBA", "sql");

String filename = "ccrmiq.rar";

String sqlString = "insert into test_blob (id,content) values(?,?)";

PreparedStatement pstmt = con.prepareStatement(sqlString);

pstmt.setInt(1,1000);

pstmt.setBytes(2, this.getFileContent(filename));

int state = pstmt.executeUpdate();

this.closeConnection(con, pstmt);

}

public static void main(String[] args) {

JDBCDemo demo = new JDBCDemo();

try {

demo.insert();

//demo.select();

//demo.delete();

} catch (Exception e) {

e.printStackTrace();

}

}

private Connection getConnection(String driver, String url, String user,

String password) throws Exception {

Class.forName(driver);

return DriverManager.getConnection(url, user, password);

}

private void closeConnection(Connection con, Statement stmt)

throws Exception {

if (stmt != null) {

stmt.close();

}

if (con != null) {

con.close();

}

}

}

在执行上面的java程序时再现了问题。

通过查找相关资料，最终的解决方法如下：

(1) 设置jConnect连接属性TEXTSIZE=2147483647

(2)  使用DBA用户登陆IQ，做如下设置：

set option public.ENABLE_LOB_VARIABLES='ON'