This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Updaing image field in Sql server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updaing image field in Sql server" Watch "Updaing image field in Sql server" New topic
Author

Updaing image field in Sql server

Ramesh Kumar
Ranch Hand

Joined: Dec 28, 2004
Posts: 33
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ramesh Kumar
Ranch Hand

Joined: Dec 28, 2004
Posts: 33
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Dec 28, 2004
Posts: 33
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Updaing image field in Sql server
 
Similar Threads
copy data from one table on server1 to another table of server2
How to distinguish between two different button clicks on servlet
Unresolved error
SQL Exception:General Error
setBoolean - not working properly