ORA-01461: can bind a LONG value only for insert into a LONG column

[Pradeep]

Hi,

I am inserting image in db, its ok with small size image but when large size image, it shows following error


ORA-01461: can bind a LONG value only for insert into a LONG column


table and code details :


create table MyPictures1 (
id INT PRIMARY KEY,
name VARCHAR(11),
myphoto CLOB


java code :


public class InsertPictureToOracle {
public static void main(String[] args) throws Exception, IOException, SQLException {
Connection conn = null;
DBUtils.loadDriver();
conn = DBUtils.fetchConnection();
FileInputStream fis = null;
PreparedStatement ps = null;
Statement stmt = null;
String INSERT_PICTURE = "insert into MyPictures values(?,?,?)";
System.out.println("i am here");
try {
conn.setAutoCommit(false);
File file = new File("com/dca/Banner.jpg");
fis = new FileInputStream(file);
ps = conn.prepareStatement(INSERT_PICTURE);
ps.setString(1, "0013");
//ps.setString(2, "gajanan");
ps.setString(2, file.getName());
ps.setBinaryStream(3, fis, (int) file.length());
//ps.setBinaryStream(3, fis, fis.available());
System.out.println("i am there");
int i=ps.executeUpdate();
if(i!=0)
{
System.out.println("image has been inserted");
}
else
{
System.out.println("image is not inserted");
}
conn.commit();
//System.out.println("insert image successfully");
ResultSet rs =stmt.executeQuery("select myphoto from MyPictures where id=001");
byte[] imgbytes=null;
if(rs.next()) {
imgbytes=rs.getBytes(10);
System.out.println("image value "+imgbytes);
}
rs.close();
System.out.println("populate image successfully");
} catch(Exception SQLexp){
SQLexp.printStackTrace();
System.out.println("Exception while interacting with the database " + SQLexp);
} finally {
DBUtils.closeConnection(conn);
try {
stmt.close();
} catch(Exception e) {
e.printStackTrace();
}

}
// InsertPictureToOracle picture = new InsertPictureToOracle();
// picture.imagePopulate();
}
public void imagePopulate()
{
System.out.println("***********************************************************");
System.out.println("Within Imagepopulate ########### ");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
DBUtils.loadDriver();
conn = DBUtils.fetchConnection();
String query = "select from Mypictures where id=1";
byte[] imgbytes=null;
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()){
imgbytes=rs.getBytes(1);

// int value = rs.getInt(1);
//String myname = rs.getString(2);
//System.out.println(value);
}

stmt.close();
rs.close();
System.out.println("populate successfully");
}catch(Exception SQLexp){
SQLexp.printStackTrace();
System.out.println("Exception while interacting with the database " + SQLexp);
} finally {
DBUtils.closeConnection(conn);
try {
stmt.close();
rs.close();
} catch(Exception e) {
e.printStackTrace();
}

}

}
}

[Ulf Lembcke]

Worked on it quite a while. Oracle JDBC driver has a size limit on binary data. Oracle provides the workaround to wrap the SQL into PL/SQL. It worked happily ever after ..... :

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#06_12