最近一个用户的应用使用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'