• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Updaing image field in Sql server

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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);
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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's a tiny ad. At least, that's what she said.
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic