Results 1 to 7 of 7
  1. #1
    Jazzperson is offline Member
    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0

    Lightbulb Detect "on duplicate key" - Mysql

    Hey out there!

    I'm using the attached code to assign an phidgetID from a table with ID's (phidgetids), delete the ID from the ID table, and put the data together with some data from a servlet into the table users.

    My problem is that even though i'm not assigning a new id "on dublicate key", the function getPhID() is called and a ID deleted without being used.

    Hope somone can give me an idea how to slove this..:)

    Best regards form Denmark!
    Attached Files Attached Files

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    843
    Rep Power
    6

    Default

    My doubt here is in "getPhID()" why u have to call deletePhId method outside the if condition.Place it inside rs.next() condition.

    if (res.next()) {
    pid = res.getString("phidid");
    deletePhID(pid);
    }
    Ramya:cool:

  3. #3
    Jazzperson is offline Member
    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0

    Default

    Yup - Both will function, but my problem is that getPhID() should't be called at all from save() when it's only an "on duplicate key update"..:-S

  4. #4
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    843
    Rep Power
    6

    Default

    Could you please explain your problem clearly? Iam not eble to see any comments for your method.
    Ramya:cool:

  5. #5
    Jazzperson is offline Member
    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0

    Default

    // Prepare a statement. Used "insert on duplicate key update" to create or update in same statement.

    The getPhID() should only be called when the statement mentioned above creates a new user and not when the statement updates a user.

    stmt.setString(1, this.uid); // Set CPR
    to
    stmt.setString(13, getPhID()); is for creation af a new user.

    stmt.setString(14, this.pic_big);
    to
    stmt.setString(24, this.film); is for updating a user (on dublicate key)

    My problem is that "stmt.setString(13, getPhID()); " is called even though an user is updated - it should only be called when a new user is created.

  6. #6
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    843
    Rep Power
    6

    Default

    Hi,

    1) The statement (stmt) object just contains the query you want to fire. The 'insert' statement you are passing to the prepareStatement() is just a string at that point. It is not recognized as an SQL statement.

    2) You are setting the parameter values to the query using the 'stmt' object.

    3)The query is fired only by the executeUpdate() call. And this is the point where internally the jdbc driver will come to know whether the key is duplicate or not, but not at the time of the setting the parameter values.

    4) Hence your question of calling the getPhId() on 'duplicate key' found before 'executeUpdate' call is invalid.

    Solution:
    1) You have to write your own logic for whatever you want. You need separate queries for insert and the update.

    2) You have to first find out whether the key is already is existing in the table. For this you need to have another query.

    3) Then based on the result fire your insert or update statements. And ofcourse you have to set the parameters to your statements accordingly.

    4) For all the above you could have separate methods to handle the process.

    Please note that java cannot determine the statement logic automatically. It is upto you to build that as a programmer.

    I hope you understood the crux here.

    ~Ramya
    Ramya:cool:

  7. #7
    Jazzperson is offline Member
    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0

    Default

    I just tought there might be a smarter way to do it useing "on dublicate key". My solution became a checkUser boolean function which checks if the user exists or not, and a if/else in the save function with insert and update.

    Thanks a lot for your answeres..:-)

Similar Threads

  1. Java, Military Format using "/" and "%" Operator!!
    By sk8rsam77 in forum New To Java
    Replies: 11
    Last Post: 02-26-2010, 04:03 AM
  2. Replies: 2
    Last Post: 01-24-2009, 07:56 PM
  3. Replies: 1
    Last Post: 10-20-2008, 08:35 AM
  4. <core:forEach var="" begin="+<%=j%>+">???
    By freddieMaize in forum JavaServer Pages (JSP) and JSTL
    Replies: 1
    Last Post: 09-27-2008, 02:20 AM
  5. ZipOutputStream "duplicate entry"
    By new_2_java in forum New To Java
    Replies: 0
    Last Post: 02-14-2008, 05:28 PM

Posting Permissions

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