Results 1 to 15 of 15
  1. #1
    anjibman is offline Member
    Join Date
    Dec 2010
    Posts
    48
    Rep Power
    0

    Default DB Update Problem

    Hi All

    I have the following code to get the number and update that value. Basically I am generating sequence number myself. For this I first read the current sequence number to use then update the value for subsequent use.

    I have separate table to store that sequence number. Other table query this table to get the sequence number.

    Java Code:
    private Integer generateId() {
            Integer key = null;
            String getKeyQuery = "SELECT SEQ_VALUE FROM KEYGEN "
                    + "WHERE SEQ_NAME = 'USER_AGENCY'";
            String updateKey = "UPDATE KEYGEN SET SEQ_VALUE = ? "
                    + "WHERE SEQ_NAME = 'USER_AGENCY'";
            try {
                statement = connection.createStatement();
                resultSet = statement.executeQuery(getKeyQuery);
                while (resultSet.next()) {
                    key = resultSet.getInt("SEQ_VALUE");
                }
                preparedStatement =
                        connection.prepareStatement(updateKey);
                preparedStatement.setInt(1, key++);
                preparedStatement.executeUpdate();
                preparedStatement.close();
                return key;
            } catch (SQLException ex) {
                log.error(ex.getErrorCode() + ": " + ex.getMessage());
                System.out.println(ex.getErrorCode() + ": " + ex.getMessage());
            }
            return -1;
        }
    Problem here is I can read the value but can not update the value. So sequence number remains same.


    Can anyone suggest me what I am doing wrong?

    Anjib

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Are your sure that the correct query executed. Check the value before execute. Debug the code before execution.

  3. #3
    anjibman is offline Member
    Join Date
    Dec 2010
    Posts
    48
    Rep Power
    0

    Default Debuged

    Yes I debug it and even after successful call to executeUpdate() DB value doesn't changes.

  4. #4
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    No, what I'm saying is check the query with all relevant values before update. If your code running with an error, we can assume that processing is ok. So there could be something wrong with the query.

  5. #5
    anjibman is offline Member
    Join Date
    Dec 2010
    Posts
    48
    Rep Power
    0

    Default Query works fine

    I run query independently from editor and its working fine.
    Let me know if I miss something.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,091
    Rep Power
    20

    Default

    How are you determining what value the key is after an update?
    Because that code is getting the current key, then running the update, and then returning the current key (not the updated key).

    Also, as a side note, when you get an exception print the stack trace, not just the message. You lose tons of useful information if you only print the message.

    Oh, you're also not closing your statement or result set. And you ought to close these resources in a finally block, in case an exception is thrown.

  7. #7
    ajaay's Avatar
    ajaay is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default

    Well, i dont see him saying there was an exception :(. I think problem is with his code, where he uses post-increment when setting the value to be updated.
    Java Code:
                preparedStatement =
                        connection.prepareStatement(updateKey);
                preparedStatement.setInt(1, key++);
                preparedStatement.executeUpdate();
    anjibman,

    Please post the output / excpetion (if any) for others to help you.

    Regards,
    ajaay.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,091
    Rep Power
    20

    Default

    Oh no, I was simply commenting on this:
    Java Code:
                log.error(ex.getErrorCode() + ": " + ex.getMessage());
                System.out.println(ex.getErrorCode() + ": " + ex.getMessage());
    which will result in the OP losing lots of useful information (ie the stack trace for starters).

    Gah!
    Yes. For some reason I thought he was using a different variable to set the new key.
    Well spotted!

  9. #9
    ajaay's Avatar
    ajaay is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Oh no, I was simply commenting on this:
    Java Code:
                log.error(ex.getErrorCode() + ": " + ex.getMessage());
                System.out.println(ex.getErrorCode() + ": " + ex.getMessage());
    which will result in the OP losing lots of useful information (ie the stack trace for starters).
    Well, I was just saying that to show what could be the issue.

    Quote Originally Posted by Tolls View Post
    Also, as a side note, when you get an exception print the stack trace, not just the message. You lose tons of useful information if you only print the message.

    Oh, you're also not closing your statement or result set. And you ought to close these resources in a finally block, in case an exception is thrown.
    True. This will be a lot of help, especially when creating big applications.

  10. #10
    anjibman is offline Member
    Join Date
    Dec 2010
    Posts
    48
    Rep Power
    0

    Default Solved

    Thanks everyone for the help and Ajaay
    The problem was with post increment. I change the line with
    Java Code:
    preparedStatement.setInt(1, key + 1);
    and working fine.
    Once again thanks to you all.

    PS: How we close these thread once resolved?

    Anjib

  11. #11
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Quote Originally Posted by Tolls View Post
    Oh, you're also not closing your statement or result set. And you ought to close these resources in a finally block, in case an exception is thrown.
    Luckily he's not end with an exception, and tries to continue the process again. :eek:

  12. #12
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,423
    Rep Power
    20

    Default

    Abuse reported on user Java Forums - View Profile: Jaione

    4 posts, all of which are a copy/paste repeat of another post higher up in the thread.

    db

  13. #13
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

  14. #14
    Tina Dabai is offline Member
    Join Date
    Jan 2011
    Posts
    2
    Rep Power
    0

    Default

    how do i use struts to insert, delete and update record in my table using Mysql database?

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,091
    Rep Power
    20

    Default

    Start a new thread on the forum instead of hijacking an old one?

Similar Threads

  1. Java JRE 6 Update 21 - 22 RS Problem
    By mrman912 in forum New To Java
    Replies: 33
    Last Post: 01-28-2011, 08:19 PM
  2. update problem
    By PhQ in forum JDBC
    Replies: 6
    Last Post: 01-25-2011, 07:00 PM
  3. Jlabel update problem
    By fantasyme in forum AWT / Swing
    Replies: 3
    Last Post: 04-14-2010, 05:10 AM
  4. Problem in update
    By Shivraj in forum NetBeans
    Replies: 0
    Last Post: 03-24-2009, 12:11 PM
  5. SQL update problem
    By skiing in forum New To Java
    Replies: 9
    Last Post: 11-21-2008, 06:54 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •