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'