Solved

Update Oracle from Java

Posted on 2004-09-27
15
219 Views
Last Modified: 2010-03-31
Can someone clarify something for me?  I have a method that's suppose to do updates, with the calling application passing the actual Update string.  The syntax I'm trying to use is the following:

stmt.executeQuery(Query);

where Query = "UPDATE TABLE SET COLUMN = 'newvalue' where COLUMN2 = 'NEXTVALUE'"

By the way the database is Oracle, but the update doesn't happen.  I verified the update statement in SQL PLUS....

thanks in advance!
0
Comment
Question by:jhughes4
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 92

Expert Comment

by:objects
ID: 12164797
> stmt.executeQuery(Query);

should be:

stmt.executeUpdate(Query);
0
 

Author Comment

by:jhughes4
ID: 12165079
I did try that, but I think I found the actual problem... The SQL statement being passed is the following not what I originally posted, sorry:

"UPDATE TABLE SET COLUMN1 = 'NEWVALUE' WHERE COLUMN1 = 'OLDVALUE'

I've looked in the database and there are multiple rows that contain the 'NEWVALUE' in COLUMN1.  When I execute this query, I don't get an exception, but the database isn't updated either....what should I do in this situation??

thanks again
0
 
LVL 92

Expert Comment

by:objects
ID: 12165083
whats the return value from the above call?
0
 

Author Comment

by:jhughes4
ID: 12165134
Nothing.  Should I be using a ResultSet to loop through the return?
0
 
LVL 92

Expert Comment

by:objects
ID: 12165162
What do you mean by nothing? It returns a value indicating the number of rows updated.
0
 

Author Comment

by:jhughes4
ID: 12165203
Sorry 2.
0
 
LVL 92

Expert Comment

by:objects
ID: 12165231
That indicates it *has* updated two rows.

> I've looked in the database and there are multiple rows that contain the 'NEWVALUE' in COLUMN1

Thats what the column would contain if updated using the above query.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jhughes4
ID: 12165509
That's correct, but when I execute a select statement from SQL PLUS, the updates don't show.  The 'oldvalues' are still there.
0
 
LVL 92

Expert Comment

by:objects
ID: 12165524
I'm confused?  Above you said:
"I've looked in the database and there are multiple rows that contain the 'NEWVALUE' in COLUMN1"

How are you looking into the database in this case.
And are you saying if you look using SQL PLUS then the same rows contain 'OLDVALUE'?
0
 

Author Comment

by:jhughes4
ID: 12166134
Sorry let's try this again....

Say I have a table

COLUMN1     COLUMN2
DATA            SOMEOTHERDATA
DATA            YETSOMEMOREDATA
DATA2          LASTONE

If I execute the following update from the Java program, it returns a value of 2, but when I review the database via SQL PLUS, the table looks as though it hasn't been updated.
UPDATE TABLE SET COLUMN1 = 'NEWDATA' WHERE COLUMN1 = 'DATA'

However if I execute the query
UPDATE TABLE SET COLUMN1 = 'NEWDATA' WHERE COLUMN2 = 'SOMEOTHERDATA'

it works fine.....

The first UPDATE statement works fine within SQL PLUS, just doesn't work in the program using executeUpdate().
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12166901
>UPDATE TABLE SET COLUMN1 = 'NEWDATA' WHERE COLUMN1 = 'DATA'
change the column column1 of all the rows having the column1 equals to DATA to NEWDATA.
After executing this you must have no value of column1 equals to DATA

>UPDATE TABLE SET COLUMN1 = 'NEWDATA' WHERE COLUMN2 = 'SOMEOTHERDATA'
change the column column1 of all the rows having the column2 equals to SOMEOTHERDATA to NEWDATA.
After executing this all the rows having column2 equals SOMEOTHERDATA have column1 equals NEWDATA

This is the significant of the last query you posted. I see each time you post a different query. Please keep attention on what you are writing. A little thing changing in the query changes the operation done!

Bye, Giant.
0
 
LVL 1

Accepted Solution

by:
KartikShah earned 150 total points
ID: 12168358
Looking at the thread, I feel that, the commit is not being executed.

The possiblility is that, autoCommit has been set to false, and at the same time, commit is not being called, and as per the normal procedure, if a connection is closed, all uncommited transactions are rolled back.

What you can try is, after the update,

"UPDATE TABLE SET COLUMN1 = 'NEWVALUE' WHERE COLUMN1 = 'OLDVALUE'

call commit and then try check, i.e.

<Connection Object>.commit();

This will commit the update, and then it should be visible.

Hope it helps. :)

Cheers

Kartik
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12408059
I think a split or delete(no refund).
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12420837
?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
topping2 challenge 13 82
Modeling a class in java 5 47
recursion example 16 111
How to simplify a groovy script that reads a WS because the result being sent back is simpler 1 44
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to implement Singleton Design Pattern in Java.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now