Results 1 to 9 of 9
Like Tree1Likes
  • 1 Post By pbrockway2

Thread: Can't get Update statement to work

  1. #1
    Reptar693 is offline Member
    Join Date
    Mar 2012
    Posts
    6
    Rep Power
    0

    Unhappy Can't get Update statement to work

    I'm trying to get all the names of my movie files from a directory and i'm trying to store them in a database under the column movies.
    One other column in the database labeled music already has some data, and the rows under the Movie column are null, so I'm trying to update these values using the update statement.

    The program runs successfully but the data i'm trying to get is not stored in the database, the update statement i think is not working.

    what do you think may be the problem? thanks




    package organizer;
    import java.io.File;
    import java.io.IOException;
    import java.sql.*;
    import java.util.ArrayList;
    /
    public class Get_Files {

    static Connection conn;

    public static void main(String args[]) throws SQLException, ClassNotFoundException, InterruptedException, IOException,SQLSyntaxErrorException{

    String driver = "org.apache.derby.jdbc.ClientDriver"; //driver url
    String connectionURL = "jdbc:derby://localhost:1527/Organizer"; // connection url
    String Username= "my_name"; //Username
    String Password= "my_password"; //password

    Class.forName(driver);//loads the driver

    conn = DriverManager.getConnection(connectionURL,Username ,Password); //makes connection


    String file_name;

    File file = new File("G:\\Movies"); //PAth to files
    File[] files = file.listFiles(); //Creates an array of files in the path.
    ArrayList<String> Movies = new ArrayList<>();

    for (int fileInList = 0; fileInList < files.length; fileInList++)
    {

    if (files[fileInList].isFile())
    {
    file_name = files[fileInList].getName().toString();
    Movies.add(file_name); // adds file names into an array list

    }
    //System.out.println(files[fileInList].toString()+fileInList);
    //Movies.add(files[fileInList].toString());

    }

    for(int x=0; x<files.length;x++)

    {
    // statement.executeUpdate("INSERT INTO APP.EXTERNAL_HD_FILES(ID,MUSIC)"//Executes Sql Statement
    // + "values("+x+",'"+Movies.get(x)+"')");
    //System.out.println(Movies.get(x));
    try{


    Statement statement = conn.createStatement();

    String qry="UPDATE APP.EXTERNAL_HD_FILES" +
    "SET MOVIES =\""+Movies.get(x)+"\" "
    +" WHERE ID = '"+x+"'";
    //System.out.println(qry);
    statement.executeUpdate(qry);

    }
    catch(SQLException e){}
    }




    }


    }

  2. #2
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,565
    Rep Power
    12

    Default Re: Can't get Update statement to work


  3. #3
    Reptar693 is offline Member
    Join Date
    Mar 2012
    Posts
    6
    Rep Power
    0

    Default Re: Can't get Update statement to work

    I posted there too, hoping to get various answers.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,967
    Rep Power
    19

    Default Re: Can't get Update statement to work

    Please use [code] tags [/code] when posting code, that's largely unreadable otherwise.

    "The program runs successfully "

    I'm not surprised (if bvy "successful" you mean "with no exceptions").
    Java Code:
    try {
    ....
    } catch(SQLException e){}
    If the execution of the UPDATE threw an exception you'd never know because you are eating the exception.
    Stick an 'e.printStackTrace()' in there.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Can't get Update statement to work

    And, of course, the fact that you no space between the end of the tablename and the keyword SET doesn't help anything. of course, this becomes obvious if you actually (as noted above) pay attention to errors rather than just ignoring them.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,967
    Rep Power
    19

    Default Re: Can't get Update statement to work

    I wasn't even going to look at the statement itself without formatting...:)
    And then the answer was going to be:
    Use a PreparedStatement.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Can't get Update statement to work

    I definately agree there. I just happened to notice it as I was doing a "quickscan". I haven't really looked at the code at all, so there are probably still another dozen errors or so, not to mention the statement cobbling that is always oh so much fun.

  8. #8
    zhaoyx is offline Member
    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Default Re: Can't get Update statement to work

    You should use
    Java Code:
    String qry="UPDATE APP.EXTERNAL_HD_FILES" +"SET MOVIES =\""+Movies.get(x)+"\" "+" WHERE ID = '"+x+"'";
    //System.out.println(qry);
    if(statement.executeUpdate(qry)==0){
        qry="insert into APP.EXTERNAL_HD_FILES" +" (MOVIES,ID ) values(""+Movies.get(x)+"\" "+" , '"+x+"');";
        statement.executeUpdate(qry);
    }
    Last edited by zhaoyx; 09-01-2012 at 10:42 AM.

  9. #9
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,565
    Rep Power
    12

    Default Re: Can't get Update statement to work

    This is a rather old thread. It is unlikely the original poster is around.

    Please format your code and use the "code" tags. You put [code] at the start of your code and [/code] at the end: that way the code is readable when it appears here.

    Also drawn attention to earlier in the thread, was the advisability of using PreparedStatement.
    Tolls likes this.

Similar Threads

  1. Syntax error in UPDATE statement Need Help!
    By mathidioticz in forum New To Java
    Replies: 7
    Last Post: 01-21-2012, 06:48 PM
  2. Update statement gone HeWire
    By _-Blackhawk-_ in forum JDBC
    Replies: 8
    Last Post: 10-06-2011, 11:56 AM
  3. error while using sql update statement
    By jttslg in forum Advanced Java
    Replies: 14
    Last Post: 07-03-2011, 10:38 PM
  4. Replies: 1
    Last Post: 01-10-2011, 01:44 AM
  5. Update statement not working.
    By OMFGITSROHIT in forum JDBC
    Replies: 5
    Last Post: 04-08-2010, 01:03 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
  •