CODEWITHSUNDEEP
javajdbcoracle11gblob
learner
learner

Reputation: 757

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

I am using a simple interface (in jsf 1.2 and rich faces 3.3.2, Oracle 11g R1) to let user select picture with rich:fileUpload and save in a table. As a test, i created following table.

CREATE TABLE TEST
(
 MIME_TYPE VARCHAR2 (1000),
 PHOTO BLOB,
 STUDENT_ID NUMBER NOT NULL
)

code snippet to save the picture to BLOB field is as follows.

//......From the uploadFile Listener
public void listener(UploadEvent event) throws Exception {
...      
item = event.getUploadItem();
...
StudentPhotoDAO dao = new StudentPhotoDAO();
dao.storePhoto(item.getData(),item.getContentType(),studentId);
...
}


//......From the PhotoDAO ..........................


public void storePhoto(byte data[],String mimeType, Long studentId){
{
 ...
  ByteArrayInputStream bis=new ByteArrayInputStream(data);
  String query = "update  TEST set PHOTO = ? ,MIME_TYPE = ?  where STUDENT_ID=?";
  pstmt = conn.prepareStatement(query);
  pstmt.setAsciiStream(1,(InputStream)bis,data.length);
  pstmt.setString(2,mimeType.toString());
  pstmt.setLong(3,studentId);
  pstmt.executeUpdate();
 }

I get following error:

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

Where is the error in the code please.

Thanks.

Upvotes: 4

Views: 20635

Answers (5)

leonid
leonid

Reputation: 86

I guess using ASCII stream is to blame.

pstmt.setAsciiStream(1, (InputStream) bis, data.length);

Try

pstmt.setBinaryStream(1, new ByteArrayInputStream(data));

(It looks like Oracle interprets ASCII stream with length parameters as LONG which cannot be more than 4000 bytes. But that's only an unconfirmed guess.)

Upvotes: 0

Michael R
Michael R

Reputation: 375

When binding large binary streams to a BLOB column in Oracle, use PreparedStatement.setBlob() rather than setAsciiStream() or setBinaryStream().

Upvotes: 0

svaor
svaor

Reputation: 2245

Look at Oracle LONG type description: "LONG is an Oracle data type for storing character data ...". So LONG is not number in Oracle. It's a text.

I think you got this error because of this: pstmt.setAsciiStream(1,(InputStream)bis,data.length);

Try use pstmt.setBinaryStream(int, InputStream, int) or pstmt.setBinaryStream(int, InputStream, long).

Upvotes: 1

Simon Dorociak
Simon Dorociak

Reputation: 33515

You are calling

pstmt.setLong(3,studentId);

and you specified column as

STUDENT_ID NUMBER NOT NULL

and how docs says:

An attempt was made to insert a value from a LONG datatype into another datatype. This is not allowed.

So just make it like this:

STUDENT_ID INTEGER NOT NULL
pstmt.setInt(3, studentId);

Upvotes: 0

Heiko Rupp
Heiko Rupp

Reputation: 30994

You specify the student_id as number, which seems to map to BigInteger. See e.g. this table.

Either you supply a BigInteger or you need to change the type of student_id.

Upvotes: 1

Related Questions