File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Update Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Update Problem" Watch "Update Problem" New topic

Update Problem

Sudhin Moorkoth

Joined: Mar 29, 2001
Posts: 8
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("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

Joined: Oct 20, 2001
Posts: 15
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

Joined: Mar 29, 2001
Posts: 8
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.
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Mar 29, 2001
Posts: 8
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.
I agree. Here's the link:
subject: Update Problem
It's not a secret anymore!