Can't get Update statement to work

[Reptar693]

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){}
}




}


}

[pbrockway2]

Also at javaprogrammingforums.com

[Reptar693]

I posted there too, hoping to get various answers.

[Tolls]

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").

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.

[masijade]

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.

[Tolls]

I wasn't even going to look at the statement itself without formatting...:)
And then the answer was going to be:
Use a PreparedStatement.

[masijade]

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.

[zhaoyx]

You should use

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);
}

[pbrockway2]

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.