| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-12 : 07:13:55
|
| 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 followingCREATE 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" |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-12 : 09:39:50
|
| PRIMARY KEY(PROJ_ID, DOC_TYPE, DOC_NAME, PTL_DTLS_ID, SEQ_NUMBER);There is your primary key. you are trying to update this clustered key along with an image column. SQL SERVER dont like that.How to update it then? dunno. Bet you Brett can answer this one.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
 |
|
|
|
|
|
java blob