Updaing image field in Sql server

[Ramesh Kumar]

Hi

I am trying to update image field using java program. I am getting the following error.

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I did n't have any index defined on the table. Please halp me in this.

I am writing the following method to update image field.


query = "UPDATE PTL_DOCS SET DOC_TYPE=?, DOC_NAME=?, MODIFIED_BY=?, MODIFIED_ON="+DateUtility.getDateTime()+",NOTES=?, + TYPE=?,BACKGROUND_TYPE=?, DOC_MIME_TYPE = ? ,DOC_SIZE = ? , DOC_CONTENT = ?, FRM_ID =? , VERSION_NUMBER = ? WHERE PTL_NUMBER=? AND PTL_DTLS_ID=? AND SEQ_NUMBER=?";

pst = con.prepareStatement(query);

pst.setString(1, ptlDocs.getDoc_Type());
pst.setString(2, ptlDocs.getDoc_Name());
pst.setString(3, userId);
pst.setString(4, (notes != "" ? notes : ptlDocs.getNotes()));
pst.setString(5, ptlDocs.getType());
pst.setString(6, ptlDocs.getBackground_Type());
pst.setString(7, ptlDocs.getDoc_Mime_Type());
pst.setInt(8, ptlDocs.getDoc_Size());


buff = ptlDocs.getDocContent();
InputStream fis = new ByteArrayInputStream(buff);
pst.setBinaryStream(9,fis,(int)buff.length);

if ("STANDARD".equals(ptlDocs.getType())) {
pst.setInt(10, 2);
} else if ("VA".equals(ptlDocs.getType())) {
pst.setInt(10, 4);
} else
pst.setInt(10, 0);

pst.setInt(11, Integer.parseInt(ptlDocs.getVersion_Number()));
pst.setInt(12, ptlDocs.getPtl_Number());
pst.setInt(13, ptlDocs.getPtl_Dtls_Id());
pst.setInt(14, seq_Number);

[Paul Sturrock]

Is there any reason why your table is defined with a clustered key? If there isn't you could just change the DDL to not use one.

[Ramesh Kumar]

My table is not having any index defined. I checked in the Query Analyzer. My table is like the following

CREATE TABLE PTL_DOCS
(
PROJ_ID VARCHAR(30) NOT NULL,
PTL_DTLS_ID INT NOT NULL,
SEQ_NUMBER INT NOT NULL identity(1,1),
PTL_NUMBER INT NOT NULL,
DOC_TYPE VARCHAR(256) NOT NULL,
DOC_NAME VARCHAR(256) NOT NULL,
DOC_MIME_TYPE VARCHAR(256) NOT NULL,
DOC_SIZE INT,
DOC_DATE DATETIME NOT NULL,
APPROVED_DATE DATETIME,
CREATED_BY VARCHAR(30) NOT NULL,
CREATED_ON DATETIME NOT NULL,
MODIFIED_BY VARCHAR(30) NOT NULL,
MODIFIED_ON DATETIME NOT NULL,
FRM_ID INT,
NOTES VARCHAR(2000),
DOC_CONTENT IMAGE,
TYPE VARCHAR(30),
VERSION_NUMBER INT,
CONSENT_LOCK VARCHAR(1) DEFAULT 'Y',
BACKGROUND_TYPE VARCHAR(200)
);


ALTER TABLE PTL_DOCS ADD
CONSTRAINT PTL_DOCS_PK
PRIMARY KEY
(PROJ_ID, DOC_TYPE, DOC_NAME, PTL_DTLS_ID, SEQ_NUMBER);

ALTER TABLE PTL_DOCS ADD
CONSTRAINT PTL_DOCS_PTL_DTLS_FK
FOREIGN KEY (PROJ_ID, PTL_DTLS_ID)
REFERENCES PTL_DTLS (PROJ_ID,PTL_DTLS_ID);

[Paul Sturrock]

SQL Server automatically creates unique indexes when you use a Primary Key constraint. Unless you explicitly specify a non-clustered index, a clustered index is created to enforce the Primary Key constraint, which is probably what is causing your problem.

[Ramesh Kumar]

I am sollve the problem. I was trying to update the image field and one of the key field (One of the filed on which primary key is defined ). Sql server is not allowing update. I am updating image in a seperate query and That filed in a seperate query.