We have a serious problem with update. One of the control characters in one field made the sql statement to terminate and ignore the where clause and updated each and every row in the table. In the example provided, the CITY_TX column had some control characters (we don't know what that character is yet)and it updated all rows with same data. We are using JDBC 2.0. Does anyone encounter this problem? StringBuffer sql = new StringBuffer(); sql.append("UPDATE CSD.TBENEF_ADDR_PHONE SET "); sql.append("CITY_TX='" + patientAddressBO.getCity() + "',"); sql.append("ZIP_CD='" + patientAddressBO.getZipCD() + "',"); sql.append("ZIP_SUFFIX_CD='" + DBHelper.getAsString(patientAddressBO.getZipSuffixCD()) + "',"); sql.append("TIME_ZONE_CD='" + DBHelper.getAsString(patientAddressBO.getTimeZoneCD()) + "',"); sql.append("DAY_AREA_CODE_NB='" + DBHelper.getAsString(patientAddressBO.getDayAreaCDNbr()) + "',"); sql.append("DAY_PHONE_NB='" + DBHelper.getAsString(patientAddressBO.getDayPhoneNbr()) + "',"); sql.append("DAY_PHONE_EXT_NB='" + DBHelper.getAsString(patientAddressBO.getDayPhoneExtNbr()) + "',"); sql.append("NIGHT_AREA_CODE_NB='" + DBHelper.getAsString(patientAddressBO.getNightAreaCDNbr()) + "',"); sql.append("NIGHT_PHONE_NB='" + DBHelper.getAsString(patientAddressBO.getNightPhoneNbr()) + "',"); sql.append("HSU_TRN_CD=" + patientAddressBO.getHsuTranCD() + ","); sql.append("HSU_USR_ID='" + patientAddressBO.getHsuUserID() + "',"); sql.append("HSU_TS='" + hsuTimestamp + "',"); sql.append("STATE_CD='" + patientAddressBO.getState().getStateCD() + "',"); sql.append("COUNTRY_CD=" + patientAddressBO.getCountry().getCountryCD()); sql.append(" WHERE CP_BENEFICIARY_ID=" + patientID + " AND"); sql.append(" BENEF_ADDR_SEQ_NB=" + patientAddressBO.getSequenceNbr());
// Create a statement stmt = conn.createStatement(); // Run the update int rowCount = stmt.executeUpdate(sql.toString());
Is the control character in city name a apostophe ('), i.e., a single quote. Like for our city Ahmedabad we generally write A'bad. To avoid this problem we generally use PreparedStatement. If this is not the control character, is it possible for you specify the values you want to set in the field too? [This message has been edited by kshitij raval (edited October 20, 2001).]
Joined: Mar 29, 2001
Actually the city name and address lines (I omitted address lines from the sql that I gave in the previous mail) are being accepted from a screen. One of our users while testing entered different combination of characters (!@# and others, she wasn't sure what she actually entered and the same person did manage to update all rows twice) I have tried with semicolon and apostrophe and it worked fine. Now we have put some trace and probably that will help to identify the problem. Thanks for replying. Sudhin
The advice given above a very good idea - always use a PreparedStatement, for a number of reasons.
It solves representation problems, like date representation problems, for you.
It solves proper escaping of special characters for you.
It adds type safety.
It greatly enhances performance on any database that supports statement caching.
If you can afford cleaning up your application then don't waste any time trying to track down the particular escape character causing this problem - similar problems will be lurking in every corner. - Peter
[This message has been edited by Peter den Haan (edited October 22, 2001).]
Joined: Mar 29, 2001
Thanks to both of you. We will use PreparedStatement. I have done some benchmarking with and without preparedstatement. When SQL statement is executed once preparedstatement is a bit slow. When I put the statement in a loop it is much much faster. Sudhin