aspose file tools*
The moose likes JDBC and the fly likes Weird update vs insert vs select from DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Weird update vs insert vs select from DB" Watch "Weird update vs insert vs select from DB" New topic
Author

Weird update vs insert vs select from DB

john krem
Greenhorn

Joined: Feb 06, 2011
Posts: 22
I'm having a strange thing happen with a db call in my servlet. Here is the code that is 'acting up'.

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


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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60057
    
  65

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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18135
    
    8

Your comment says

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
Greenhorn

Joined: Feb 06, 2011
Posts: 22
Paul Clapham wrote:Your comment says

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.



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
Greenhorn

Joined: Feb 06, 2011
Posts: 22
This makes no sense to me at all, am I missing something here??



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
Bartender

Joined: Oct 14, 2005
Posts: 18135
    
    8

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
Greenhorn

Joined: Feb 06, 2011
Posts: 22
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
Bartender

Joined: Oct 14, 2005
Posts: 18135
    
    8

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
Greenhorn

Joined: Feb 06, 2011
Posts: 22
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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Weird update vs insert vs select from DB
 
Similar Threads
Unable to update database
Returning a 0 value error
need help with Tomcat
JSP synchronization
Update inside Select statement