• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update Problem

 
Sudhin Moorkoth
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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());
 
kshitij raval
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
Sudhin Moorkoth
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
Sudhin Moorkoth
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic