Weird update vs insert vs select from DB

[john krem]

I'm having a strange thing happen with a db call in my servlet. Here is the code that is 'acting up'.

        String ids = request.getParameter("ID");
        int id = Integer.parseInt(ids);

        try {
            ResultSet rs = stmt.executeQuery("SELECT * from APP.CUSTOMERINFO where CUSTOMERID =" + id);
            ids = request.getParameter("ID");
            name = request.getParameter("Name");
            address = request.getParameter("Shipping Address");
            id = Integer.parseInt(ids);

           //check to see if the name String is empty and if so, get the name and address from the db
            if (rs.next() && name.isEmpty()) {
                name = rs.getString("CUSTOMERNAME");
                address = rs.getString("SHIPPINGADDRESS");
          //check to see if the name String is not empty and if so, update the db with the new name and address
            } else if (rs.next() && !name.isEmpty()) {
                int update_user = stmt.executeUpdate("UPDATE APP.CUSTOMERINFO SET CUSTOMERNAME='" + name + "', SHIPPINGADDRESS='" + address + "' WHERE CUSTOMERID= " + id);
            // otherwise, add a new user to the db
            } else {
                int update_count = stmt.executeUpdate("INSERT INTO APP.CUSTOMERINFO (CUSTOMERID, CUSTOMERNAME,SHIPPINGADDRESS)" + "VALUES(" + id + ",'" + name + "','" + address + "')");


            }

What is happening is that the only way it will add a new user, is if the CUSTOMERID does not exist, and there is no value in the name String, which, based on how I'm seeing the logic, doesn't make any sense.

I've tried

if (rs.next() && name ==null) {...}
if (rs.next() && name.equals("")){...}

to see if that would work, but had the same things occur. If anyone can see the issue, I would be most grateful!

[Bear Bibeault]

This will probably get more attention in the JDBC forum, so I've moved it there for you.

[Paul Clapham]

Your comment says

//check to see if the name String is not empty and if so, update the db with the new name and address

which would be the right thing to do. But the code actually tries to read the second record from the result set and then do that check only if it found one, although it doesn't use the contents of that second record.

[john krem]

Paul Clapham wrote:Your comment says

//check to see if the name String is not empty and if so, update the db with the new name and address

which would be the right thing to do. But the code actually tries to read the second record from the result set and then do that check only if it found one, although it doesn't use the contents of that second record.

I think I'm missing something.

if (rs.next() && name.isEmpty()) {
                name = rs.getString("CUSTOMERNAME");
                address = rs.getString("SHIPPINGADDRESS");

            //new code here - removed rs.next() and tried name != null / !name.equals("") / this is the update for a valid user id, which is working
            } else if (name != null) {
                int update_user = stmt.executeUpdate("UPDATE APP.CUSTOMERINFO SET CUSTOMERNAME='" + name + "', SHIPPINGADDRESS='" + address + "' WHERE CUSTOMERID= " + id);

           //this is the new user insert - only get here when I have a new id, and the name string is empty
            } else {
                ids = request.getParameter("ID");
                name = request.getParameter("Name");
                address = request.getParameter("Shipping Address");
                id = Integer.parseInt(ids);
                int update_count = stmt.executeUpdate("INSERT INTO APP.CUSTOMERINFO (CUSTOMERID, CUSTOMERNAME,SHIPPINGADDRESS)" + "VALUES(" + id + ",'" + name + "','" + address + "')");

            }

I thought that taking out the rs.next() would solve the problem as I saw how to was running to the next value in rs for the 2nd if stmt, but it is still behaving the way it was before, where it will only insert a new record if the name is blank.

[john krem]

This makes no sense to me at all, am I missing something here??

         } if (!name.equals("a") && !address.equals("a")) {                
                int update_count = stmt.executeUpdate("INSERT INTO APP.CUSTOMERINFO (CUSTOMERID, CUSTOMERNAME,SHIPPINGADDRESS)" + "VALUES(" + id + ",'" + name + "','" + address + "')");
           }

Please correct me if I'm wrong, but this code says: if the name variable does not equal a and the address variable does not equal a, then insert a new record.

But what is happening is that if name is a and address is a, it inserts a new record???

[Paul Clapham]

Sometimes you set the "name" and "address" variables to the parameters from the request. Sometimes you set them to the columns from the database. I find that confusing and I suspect it doesn't help you in framing your logic either.

[john krem]

Paul Clapham wrote:Sometimes you set the "name" and "address" variables to the parameters from the request. Sometimes you set them to the columns from the database. I find that confusing and I suspect it doesn't help you in framing your logic either.

they way I want it to work is like this:

1. if the customer id exists in the db, and the name and address taken from the form are blank, use the variables from the db to fill name and address.
2. if the customer id exists in the db, and the name taken from the form has a value, then use the name and address values from the form and insert them into the db for that customer id
3. if the customer id does not exist in the db, add a new customer to the db with the id, name, and address values from the form

that's what i'm trying to code it to do....

[Paul Clapham]

I would rewrite that logic to something like this:

"If the customer is already in the file, then

... (something)

else

write the customer record with the data from the parameters."

Now what's that something?

"If the name from the forum is not empty, then

... update the customer record with the data from the parameters."

That takes care of the database part of your requirements. Try to get that working first.

[john krem]

Paul Clapham wrote:I would rewrite that logic to something like this:

"If the customer is already in the file, then

... (something)

else

write the customer record with the data from the parameters."

Now what's that something?

"If the name from the forum is not empty, then

... update the customer record with the data from the parameters."

That takes care of the database part of your requirements. Try to get that working first.

Here is how I have it working, which I think covers what you were saying.

Scenario 1
1. user enters a customerid
2. if the customerid exists
3. and the user has NOT entered a new name and address
4. use the name and address stored in the db record

Scenario 2
1. user enters a customerid
2. if the customerid exists
3. and the user has entered a new name and address
4. update the db to reflect the new name and address for that user

Scenario 3
1. user enters a customerid
2. if the customerid does NOT exist
3. create a new record of the user

The issue I am having is on getting Scenario 3 to work when combined with 1 and 2. I have 1 and 2 working perfectly together, and 3 working perfectly on it's own, but when I put the 3 together, Scenario 3 no longer works.