Updatable Recordset

[deadlyBuzz]

Hi Folks
I'm attempting to covert some data from a file into a database.
I thought the best way for this would be to use an updatable recordset and dynamically add each record every time I've parsed a line from the file.
I can't seem to get the Updateable recordset to work though.
The Database (Java DB / Derby) and the recordset have both been confirmed as being updatable.
The Code to enter the new Row does not report any errors.
When I query the Database after executing the code, the changes are not reflected in the results.

Below for the Code I am using to update the Table
(Code has been taken from the "JAVA for Dummies" book by Doug Lowe & Barry Burd and modified to utilise an updatable recordet)

package DBase;

import java.sql.*;
import java.text.NumberFormat;
/**
 * Take from the JAVA for Dummies Book Doug Lowe & Barry Burd - Listing 4.1
 * @author deadlyBuzz
 */
public class listMoviesUpdateable {
    public static void main(String[] args){
        Connection con = getConnection();
        try{
            // Create an Updateable result set
            Statement stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet = stmt.executeQuery("Select title, iyear, id, price from ADMIN.movie");

            checkResultSet(resultSet);
            
            // Move the cursor to the "insert row"
            resultSet.moveToInsertRow();

            // Set value for the new row
            resultSet.updateString("title","Monty Python");
            resultSet.updateInt("iyear",1975);
            resultSet.updateInt("ID",12);
            resultSet.updateDouble("price",13.95);
            
            // insert the row
            resultSet.insertRow();           
            //resultSet.updateRow();
            resultSet.moveToCurrentRow();
            System.out.println("Insert Complete");
        }
        catch(SQLException e){
            System.err.println(e.getMessage());
            System.exit(0);
        }

    }

    
    private static Connection getConnection(){
        Connection con = null;
        try{
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            String url = "jdbc:derby://localhost:1527/movies";
            String user = "admin";
            String pw = "password";
            con = DriverManager.getConnection(url, user, pw);
        }
        catch(ClassNotFoundException e){
            System.out.println("C>"+e.getMessage());
            System.exit(0);
        }
        catch(SQLException e){
            System.out.println("S>"+e.getMessage());
            System.exit(0);
        }
        return con;
    }

   
    private static void checkResultSet(ResultSet RS){
        try{
            int concurrency = RS.getConcurrency();

            if (concurrency == RS.CONCUR_UPDATABLE)
                System.out.println("ResultSet is Updateable");
            else
                System.out.println("ResultSet is NOT Updateable");
        }
        catch(SQLException e){
            System.err.println(e.getMessage());
            System.exit(0);
        }
    }

    
}

For reference, the table "Movies" has been set up by the query...

create table movie(
    id int not null,
    title varChar(50),
    iyear int,
    price decimal,
    primary key(id)); 


    insert into movie
    values (1,'its a wonderful life', 1946, 14.95);

    insert into movie
    values (2,'The Great race', 1965, 12.95);

    insert into movie
    values (3,'Young Frankenstein', 1974, 16.95);

    insert into movie
    values (4,'The returm of the pink panther', 1975, 11.95);

    insert into movie
    values (5,'Star Wars', 1977, 17.95);

    insert into movie
    values (6,'The princess bride', 1987, 16.95);

    insert into movie
    values (7,'Glory', 1989, 14.95);

    insert into movie
    values (8,'Apollo 13', 1995, 19.95);

    insert into movie
    values (9,'The Game', 1997, 14.95);

    insert into movie
    values (10,'The Lord of the rings, the fellowship of the ring', 2001, 20.95); --*/

Can anyone point me in the right direction as to why this table is not updating?
Do i have to execute some sort of "commit" statement on the table to update the data?
I can't seem to find this online.

Thanks
Alan

[Tolls]

My suggestion would be to do a PreparedStatement with a plain old INSERT, and batch them up.
I will never understand why people seem to go for updateable result sets.
Each driver has it's own quirks around the damn things, and there is very little reason to need to use them.

[diamonddragon]

Can anyone point me in the right direction as to why this table is not updating?
Do i have to execute some sort of "commit" statement on the table to update the data?
I can't seem to find this online.

Thanks
Alan

As I know, when Connection object is created, autoCommit parameter is set to true by default.
So the table should be updated(row inserted) at the point where You call insertRow() method.

If You want to use prepared statement, and maybe update more than one row in a table, than You should set autoComit property to false.
By commiting connection object, table will be updated with new data(if there are no exceptions, of course).

[deadlyBuzz]

I think I found it.
If you add the line

resultSet.close();

After the line

resultSet.moveToCurrentRow();

to give...

            resultSet.insertRow();           
            //resultSet.updateRow();
            resultSet.moveToCurrentRow();
            resultSet.close();
            System.out.println("Insert Complete");
        }

It seems to work.

This has inserted the data into the table as expected.

[Tolls]

If you want to do a load of inserts (as the OP is saying they do) then you do a batch (addBatch() and executeBatch()).
Commit at the end (as how the executeBatch works in terms of commit and rollback is DB specific).

[Tolls]

It seems to work.

This has inserted the data into the table as expected.

But it's such an arse about face way of doing it.
Selecting the contents of a table for the sole purpose of inserting new rows.
You're doing far more work than is necessary, and it's not exactly a learning experience as any real world app will not do this.

[deadlyBuzz]

As I know, when Connection object is created, autoCommit parameter is set to true by default.
So the table should be updated(row inserted) at the point where You call insertRow() method.

If You want to use prepared statement, and maybe update more than one row in a table, than You should set autoComit property to false.
By commiting connection object, table will be updated with new data(if there are no exceptions, of course).

Tricky one this. Using the solution above am I getting any real benefit from using an updatable recordset compared with exexcuting a query after every line?
Should I only update Close after all items have ben added?
I think I need to perform some more research.
Thanks for the Help! :-)

[diamonddragon]

Tricky one this. Using the solution above am I getting any real benefit from using an updatable recordset compared with exexcuting a query after every line?
Should I only update Close after all items have ben added?
I think I need to perform some more research.
Thanks for the Help! :-)

What do You mean by "Should I only update Close after all items have ben added?"
What is "update Close"?

In Your case, I would simple call createStatement method, to create statement object.
After that call executeUpdate method on statement object (with SQL stamenet in form of INSERT INTO...).

[Tolls]

Tricky one this. Using the solution above am I getting any real benefit from using an updatable recordset compared with exexcuting a query after every line?
Should I only update Close after all items have ben added?
I think I need to perform some more research.
Thanks for the Help! :-)

Am I invisible?
You said in your OP:
"I thought the best way for this would be to use an updatable recordset and dynamically add each record every time I've parsed a line from the file."
That right there is a batch job.
Loop through file, addBatch() for each line, after looping executeBatch(), commit(), close().
Job done.
The only exception might come from a big file, but we're talking thousands of records (database dependent, could be a lot more).

Updateable result set is a nonsense way of doing it.

[deadlyBuzz]

What do You mean by "Should I only update Close after all items have ben added?"
What is "update Close"?

In Your case, I would simple call createStatement method, to create statement object.
After that call executeUpdate method on statement object (with SQL stamenet in form of INSERT INTO...).

Sorry, I meant "Call the Close() method".
I hadn't encountered the executeUpdate() method yet as I was really working from Examples/notes and slowly building the program piece by piece with the different concepts.
the executeUpdate() method looks much more appropriate for what I'm using, Thanks diamonddragon.

[deadlyBuzz]

Am I invisible?

Who said that?

You said in your OP:
"I thought the best way for this would be to use an updatable recordset and dynamically add each record every time I've parsed a line from the file."
That right there is a batch job.
Loop through file, addBatch() for each line, after looping executeBatch(), commit(), close().
Job done.
The only exception might come from a big file, but we're talking thousands of records (database dependent, could be a lot more).

Updateable result set is a nonsense way of doing it.

You're not invisible. I just replied to diamonddragons post as I encountered it and then got sidetracked with other work before getting to the end of the thread.
So you wouldn't reccommend using an updateable resultset, instead the addBatch() , executeBatch(), commit() & close() methods.
Interesting, I hadn't enountered them before.
I was working from examples and notes and these hadn't been mentioned in the ones I have.

I take it these are JDBC functions in the java.sql Package?

The file I am looking to Parse through eventually is approximately 100K Lines long so, yeah, It's a big one.

Is this still the best method of doing this?

[Tolls]

Yep.
It depends on what database you're using as to whether you break it down into chunks, but I can't answer that with the info given so far.

If you haven't got to stuff like that in whatever tutorial you are working through (I'm guessing it's not a tutorial, though), then you could simply loop round and do a single INSERT, committing at the end. Or commit each one (this will be slower) as you go.

But this really is a batch job at heart.