Updating image field in Sql Server using java

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating image field in Sql Server using java

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-13 : 07:42:49

Ramesh writes "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);

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);

If i am not having any index why i am getting that error?. If index is created automatically when Primary Key is defined how can i update the table then?.

Please help me.

Regards,
Ramesh Kumar"

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-13 : 15:00:05

Hi Ramesh,

If SEQ_NUMBER is an Identity column then what is the fun here to put composite primary key for the columns ?

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

You have to remove this Primary key and try again....

Regards
Sreenivas Reddy B

With Regards
Sreenivas Reddy B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:18:42

According to the error, you can not update any of the columns in your clustered index (the PK) in the same query that you are updating the image column.

Sreenivas, removing the PK is not a good solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources