Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem with update and delete though insert and select working fine

 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I am facing problem when I am trying to update the data base or when i am trying to delete a tuple.
*****************************************************
Plese finf my update string below

private final static String UPDATE_TUPLE_STMT= " UPDATE ABC SET A= ?,B = ? WHERE C= ? AND D= ? AND E= ? AND F= ?";
*******************************************************
Pealse find the method i am writing below:

public Integer updateRow(String A, Integer B,
BigDecimal C, String D, Integer E,
String F) {
PreparedStatement stmt = null;
Integer count = null;

Connection aConnection = getConnection();

if (aConnection != null) {
try {
stmt = aConnection
.prepareStatement(UPDATE_TUPLE_STMT);
stmt.setInt(1, A);
stmt.setString(2, B);
stmt.setString(3, C);
stmt.setInt(4, D);
stmt.setBigDecimal(5, E);
stmt.setString(6, F);

logger.info("****count is given" + count);

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
aConnection.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeStatement(stmt);
closeConnection(aConnection);
}
}
return count;
}




Please help me with this I am not able to understand when Insert statements and select statements are working fine what could be the problem with Update and Delete.

I am using WSAD as application server.

Thanks a lot
Suhem
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is your problem that the update in that query doesn't happen? That would be because you don't call "stmt.executeUpdate()" anywhere in the code.
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul

Thanks a lot for the reply.
I am sorry for the inconvenience but in my actual code i have put

stmt.executeUpdate() statment.

Its my bad I didnt copy the code properly on Javaranch.


Thanks
Suhem
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by suhem chauhan:
Its my bad I didnt copy the code properly on Javaranch.
Indeed. And you didn't tell us what the problem was, or I would not have posted that answer. So... what is your problem? Do you have symptoms such as stack traces? It would help to post information such as that which explains why you asked the question.
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Thanks again!

Problem is I am not getting any kind of exceptions nothing.

When I am debugging the code in Debug mode it is taking initially all the values which I am setting for A B ....F
IT is executing the query(at least thats I am assuming since debug cursor is showing me that)
but the database is not been updated.
Is it something related to rights for updates deletes?

I am trying to Log the query in log file or on the consol but I am not sure if I am doing it correctly as I am not saying any query on consol or any logs in log file

I would appreciate it if you or anyone else on the group can help me with this problem.

Thanks
Suhem
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The most likely reason that you aren't seeing any change to the database is that your where-clause selects zero records. The executeQuery() method returns an int which is the number of records updated, probably you will find it is zero.
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Thanks again!

But I am passing values for all the elements in where clause.
Surprisingly when I am running query directly from tool, we are using Oracle SQL developer it is working fine. Its just not working through the access class I have written.

Thanks
Suhem
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

I forgot to mention in previous mail, but yeah I am getting '0' value for the variable count. It is not updating any row.

Thanks
Suhem
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you explain why the types of that method don't match the types of the database columns? For example C is declared to be BigDecimal but you're calling setString(3, C). In fact the parameter F is the only one of the six where the type is consistent.
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

I am sorry again. It should be as below
stmt.setInt(1, E);
stmt.setString(2, F);
stmt.setString(3, A);
stmt.setInt(4, B);
stmt.setBigDecimal(5, c);
stmt.setString(6, D);

But the problem is same.

Thanks
Suhem
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You know, it's really difficult to find problems in people's code when they show you some other code. It looks to me like your problem is that you have columns in your database named A, B, and C and variables named A, B and so on. Doing that makes it extremely difficult to see when you are assigning the value to the wrong variable. Which of these is correct?
It's pretty clear the second isn't right. The first? Who knows?

Not to mention the fact that you put variable E into column A and variable F into column B. That's just totally confusing. Not surprising it doesn't work.
[ May 14, 2008: Message edited by: Paul Clapham ]
 
Suhem Programmer
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All

I got my problem resolved[ ]

First of all I am sorry I had mesed up with my post. As I can not post my original code due to company policy I had to put it in the form of A,B,C.... and my post got messed up.

Anyways problem was with data conversion.
There is one column of my table it datatype is declared as CHAR(3). The value I was passing to Getter method in my accessclass was "A". When I was doing rs.setString(parameterint,"A"). It was not taking value A as the size is for 3 character so in my query i had to use rpad(?,3) function.

After that it worked perfectly fine

Thanks
Suhem
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic