Results 1 to 10 of 10
  1. #1
    mr_anderson is offline Member
    Join Date
    May 2010
    Location
    Florida
    Posts
    68
    Rep Power
    0

    Default How to update with dynamic variable?

    Hi
    We all know that the statement used to update the database using (JDBC with mysql) looks like this:
    "assume the table name is NODES and the fields to be updated are value1 and value2"

    PHP Code:
    statement.executeUpdate("update NODES set value1='101', value2='110' where index='30'");
    now assume that I have this variable defined in my java program:
    String var="999";
    how can I put it in value1 or value2, I tried to put it using this statement but I get an error:
    PHP Code:
    statement.executeUpdate("update NODES set value1=var, value2='110' where index='30'");
    java.sql.SQLException: Unknown column 'var' in 'field list'

  2. #2
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    17,902
    Rep Power
    25

    Default

    When you build the String, you need to exclude the variable name from the String to have its value put into the String:
    "....." + var + "...."

  3. #3
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Norm View Post
    When you build the String, you need to exclude the variable name from the String to have its value put into the String:
    "....." + var + "...."
    That can be extremely dangerous (sql injection attacks); better use a PreparedStatement. See this.

    kind regards,

    Jos

  4. #4
    mr_anderson is offline Member
    Join Date
    May 2010
    Location
    Florida
    Posts
    68
    Rep Power
    0

    Default

    Thank you guys very very much
    Norm do you mean to put it like this?
    PHP Code:
    statement.executeUpdate("update NODES set value1="+var+", value2='110' where index='30'");
    JosAH can you please check the link again.

    By the way the value of the variable var is taken from a JTextField:
    PHP Code:
    JTextField textIn=new JTextField(10);
    String var=textIn.getText();
    Thank you gain guys
    Regards.
    Anderson.
    Last edited by mr_anderson; 08-08-2010 at 06:07 PM.

  5. #5
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by mr_anderson View Post
    JosAH can you please check the link again.
    That link works fine for me and shows and xkcd cartoon: it shows one of the finest examples of a sql injection attack. Read all about the PreparedStatement API documentation.

    kind regards,

    Jos

  6. #6
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    17,902
    Rep Power
    25

    Default

    Not knowing anything about databases, I understood the OPs problem to be how to build a String with the variable contents vs the name of the variable.
    I can understand that an unedited String inserted into a command String could have undesired consequences. Its up to the OP to verify the contents of var.

  7. #7
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Norm View Post
    Not knowing anything about databases, I understood the OPs problem to be how to build a String with the variable contents vs the name of the variable.
    I can understand that an unedited String inserted into a command String could have undesired consequences. Its up to the OP to verify the contents of var.
    That's exactly what a PreparedStatement does for you (and better) because it takes the structure of the sql query in account and sets a parameter to the String value you supply; sql injection is powerless against that.

    kind regards,

    Jos

  8. #8
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    17,902
    Rep Power
    25

    Default

    Sorry, you're not going to educate me on databases. It just runs off like water off a duck's back.

  9. #9
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    It has been so long since I have used Statement; I always use PreparedStatement when I do JDBC'ing.

    To help out the OP in the quest for building better data accessors, Below is a complete example of using prepared statements for this update query, as well as the 'proper' handling of closing of the connection and statement objects after use.
    Java Code:
    Connection con = null;
    PreparedStatement stmt = null;
    try {
      con = dataSource.getConnection(); // or how ever else you get a connection object.
      
      // the first difference when using prepared statements is to specify the query string with con.prepareStatement().
      // note, one string, we use ? as place holders where variables will go.
      stmt = con.prepareStatement("update NODES set value1=?, value2=? where index=?");
      
      // now its important to bind the variables to the ? place holders in the query string above.
      // for some reason, jdbc uses 1-based indexes.
      stmt.setInt(1, 101); // see API docs, setString, setDate, set(etc)
      stmt.setInt(2, 110);
      stmt.setInt(3, 30);
      
      // now we may execute the statement, what we are used to do, note no query string here, as we did this above
      stmt.executeUpdate();
    }
    catch (SQLException ex) {
      // error handling here
    }
    finally {
      // close the statement handle now, faster than waiting for garbage collection to get around to it,
      // and better if working in connection pooled environments
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException ex) {
          // ignore
        }
      }
      // close the connection, required for connection pool environments
      if (con != null) {
        try {
          con.close();
        }  catch (SQLException ex) {
          // ignore
        }
      }
    }

  10. #10
    mr_anderson is offline Member
    Join Date
    May 2010
    Location
    Florida
    Posts
    68
    Rep Power
    0

    Default

    Thank you very much this is really well explained and easy to use example.
    Best Regards.
    Anderson.

Similar Threads

  1. Replies: 11
    Last Post: 07-07-2010, 07:02 AM
  2. Dynamic types for a variable.
    By Somelauw in forum New To Java
    Replies: 5
    Last Post: 11-27-2009, 11:38 AM
  3. Dynamic GUI
    By ike2u in forum New To Java
    Replies: 4
    Last Post: 08-08-2009, 03:50 AM
  4. Replies: 0
    Last Post: 02-06-2008, 03:07 PM
  5. dynamic update in swt
    By sandor in forum SWT / JFace
    Replies: 0
    Last Post: 05-14-2007, 09:32 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
  •