Results 1 to 12 of 12
  1. #1
    deadlyBuzz is offline Member
    Join Date
    Feb 2012
    Posts
    15
    Rep Power
    0

    Default Updatable Recordset

    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)

    Java Code:
    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...
    Java Code:
    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

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default Re: Updatable Recordset

    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.

  3. #3
    diamonddragon is offline Senior Member
    Join Date
    Jan 2012
    Posts
    210
    Rep Power
    3

    Default Re: Updatable Recordset

    Quote Originally Posted by deadlyBuzz View Post
    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).
    Last edited by diamonddragon; 02-09-2012 at 12:22 PM.

  4. #4
    deadlyBuzz is offline Member
    Join Date
    Feb 2012
    Posts
    15
    Rep Power
    0

    Default Re: Updatable Recordset

    I think I found it.
    If you add the line

    Java Code:
    resultSet.close();
    After the line

    Java Code:
    resultSet.moveToCurrentRow();
    to give...

    Java Code:
                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.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default Re: Updatable Recordset

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

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default Re: Updatable Recordset

    Quote Originally Posted by deadlyBuzz View Post
    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.

  7. #7
    deadlyBuzz is offline Member
    Join Date
    Feb 2012
    Posts
    15
    Rep Power
    0

    Default Re: Updatable Recordset

    Quote Originally Posted by diamonddragon View Post
    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! :-)

  8. #8
    diamonddragon is offline Senior Member
    Join Date
    Jan 2012
    Posts
    210
    Rep Power
    3

    Default Re: Updatable Recordset

    Quote Originally Posted by deadlyBuzz View Post
    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...).

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default Re: Updatable Recordset

    Quote Originally Posted by deadlyBuzz View Post
    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.

  10. #10
    deadlyBuzz is offline Member
    Join Date
    Feb 2012
    Posts
    15
    Rep Power
    0

    Default Re: Updatable Recordset

    Quote Originally Posted by diamonddragon View Post
    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.

  11. #11
    deadlyBuzz is offline Member
    Join Date
    Feb 2012
    Posts
    15
    Rep Power
    0

    Default Re: Updatable Recordset

    Quote Originally Posted by Tolls View Post
    Am I invisible?
    Who said that?
    Quote Originally Posted by Tolls View Post
    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?

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default Re: Updatable Recordset

    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.

Similar Threads

  1. Strange JSTL recordset result problem (Question)
    By jeremy.wilson in forum JavaServer Pages (JSP) and JSTL
    Replies: 6
    Last Post: 01-07-2010, 06:04 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
  •