Page 1 of 2 12 LastLast
Results 1 to 20 of 24
  1. #1
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default "File" catalog-type. It should be time-persistent, but why it isn't?

    Well I keep working on the SQL side of my library.

    I've already implemented some stuff, but now I'm facing a problem that I'm not able to understand. In the HSQL guide, there's a section explaining this:

    Types of catalog data
    mem: stored entirely in RAM - without any persistence beyond the JVM process's life
    file: stored in filesystem files
    res: stored in a Java resource, such as a Jar and always read-only

    So I've decided myself for file catalog.
    For testing stuff, I'm using this:

    Java Code:
    package utilstest;
    
    import java.util.ArrayList;
    import packageCommon.SQL;
    
    /**
     *
     * @author jorge
     */
    public class run {
    
        public static void main(String[] args) {
            SQL.initialize("testdb", "SA", "");
            ArrayList<String> data = new ArrayList<String>();
            data.add("field1 varchar(255)");
            data.add("field2 varchar(255)");
            SQL.createTable("Table_Test1", data);
            ArrayList<String> toInsert = new ArrayList<String>();
            toInsert.add("filledfield1");
            toInsert.add("filledfield2");
            SQL.insertInto("Table_Test1", toInsert);
            SQL.close();
        }
    }
    That works completely fine. But if I try to test the same commenting the SQL.createTable statement, it should work properly, because the database should have already been previously created. But it doesn't. Something is missing.

    This is the code for the methods I've implemented:

    SQL.initialize()
    SQL.createTable()
    SQL.insertInto()
    SQL.runVoidCommand()
    SQL.close()

    Can anyone see what is happening here?

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    What does the initialize() code look like?
    (I don't click links).

  3. #3
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Here you are. It's a method for performing the common tasks needed at the beginning of the work with the database. Using it, on the main project I just have to write something like SQL.initialize("testdb", "SA", "");

    Java Code:
        /**
         * (Re)stablishes the necessary data for communicating with a local database.
         * Non-local databases are not supported
         * @param databaseName The database to connect to. If a relative path is
         * used, it'll be taken relative to the directory in which the shell
         * command to start the JVM was executed
         * @param user The user to use in the connection
         * @param pass The password to use in the connection
         */
        public static void initialize(String databaseName, String user, String pass) {
            Connection auxConnection = null;
     
            try {
                auxConnection = DriverManager.getConnection("jdbc:hsqldb:file:" + databaseName, user, pass);
            } catch (SQLException ex) {
                Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
                return;
            }
            if (!auxConnection.equals(SQL.connection)) {
                try {
                    SQL.close();
                } catch (NullPointerException ex) {
                    //If an attemp to close the connection is performed but nothing
                    //has been initialized
                }
                SQL.connection = auxConnection;
                try {
                    SQL.statement = SQL.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
                } catch (SQLException ex) {
                    Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
                    return;
                }
            }
        }

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Can you view the database outside of your app, possibly in an IDE?
    I'm trying to figure out where it could be going wrong.
    If you are creating a table then that table should be visible outside of your application.

  5. #5
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Ok solved, I was cleaning the directory before each run so the database was deleted. noob mistake sorry

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I was thinking it was something like that, but I couldn't see where it was happening...glad you sorted it!

  7. #7
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    My error. This isn't solved. I may had checked it wrong or some stuff like that. The fact is that today I finished the implementation of the function representating the SELECT command. I was testing it with the database that I had implemented, but it worked way strange.

    1-If on the main class I simply perform the query, I get the result, a single row. Everything is fine because that was the data that I had inserted up to then.
    2-If on the main class I have lines to add stuff to the code and then to perform the select, I get the data that I also get in 1, as well as the new inserted data. This seems to be OK, but it isn't. When I repeat test case 2, but changing the added stuff, as result to the query I get: the data I got before in test case 1, and the data added on this run, but the data added in run of test case 2 is missing.
    Therefore, I started to suspect that the data is somehow "forgotten" when a run is finished.
    So what I decided was to manual delete, from the explorer, all the files relative to the database. And then I build a new test project. Its main and only class is this:

    Java Code:
    package utilstest;
    
    import java.util.ArrayList;
    import packageCommon.SQL;
    
    /**
     *
     * @author jorge
     */
    public class run {
    
        public static void main(String[] args) {
            SQL.initialize("testdb", "SA", "");
            //Call to the local method saying what I want to do.
            SQL.close();
        }
    
        private static void create() {
            //Create the table
            ArrayList<String> data = new ArrayList<String>();
            data.add("field1 varchar(255)");
            data.add("field2 varchar(255)");
            SQL.createTable("Table_Test1", data);
        }
    
        private static void fill() {
            //Put some info on the table
            ArrayList<String> targets = new ArrayList<String>();
            ArrayList<String> toInsert = new ArrayList<String>();
            toInsert.add("'filledfield1b'");
            toInsert.add("'filledfield2b'");
            SQL.insertInto("Table_Test1", targets, toInsert);
        }
    
        private static void select() {
            //Check the info in the table
            ArrayList<ArrayList<String>> ret = SQL.select("Table_Test1", true, null);
            for (ArrayList<String> target : ret) {
                System.out.println("Row: " + target);
            }
        }
    }
    The run with create works fine. The files are created in the explorer.
    But the run with fill shows up the expected problem: data is deleted after the run. But I have a system output whenever I run a command to check that it's properly written, and it is.
    So please, if any idea, tell me. I'll also be investigating and post the solution as soon as I get to found it.
    Last edited by Stoyicker; 01-23-2012 at 09:31 PM. Reason: Mistypo

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    HSQL may require a commit() call.
    I don't know how it works, but it's quite possible it does a rollback if no explicit commit is done.

  9. #9
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Not that. By default, the database auto-commit mode is enabled, and I don'd disable it. Also, in the close() method, a security commit is performed, for if needed. Anyway, I've checked it, adding several test commits over the code, and we can discard that option.

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I don't know the db well enough, so all I can suggest is debugging through it.
    Are you logging exceptions properly?
    I do note that it looks like you are not using PreparedStatements, so maybe something is throwing errors?

  11. #11
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    In theory, that shouldn't be, as I use normal statement. As far as I understood the guide, normal statements are for performing commands that are not always similar. Opposite to this, the advantage of prepared statements is that they greatly improve normal statements performance, but they just can be used to repeat the same statement as many times as wanted. But I also suspect that it must something relative to statements, the way of executing the command, or something like that...I'll keep on working.

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    If you concatenate SQL strings together you can quite easily muck up the insert/update/whatever.
    It's not massively likely, but then I can't see any of your database interaction code here at all, since it's all hidden in the SQL class.

  13. #13
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I wouldn't mind to post it, but I change it continuously and it'd be like keeping a whole page without sense. BUT, I've just made a discover. It's pretty strange. I thought that it could be in the close method. I deleted it from the test main class, and nothing new. But then I added at the end of that class a while(true) loop. So the way to end the run is stop it. and it works! I let time for the program up to the outputs that mark that the requests have already been performed, then I stop the run, and every insert is then properly performed, and their info remains in the table forever and ever. I can understand nothing...

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I'd read up on how it handles writing to the file itself.

  15. #15
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    It says that when auto-commit is activated, just after each transaction a commit is performed, so that's the data is supposed to be written. Also, I've tested adding a commit after each insertion, but nothing. I've been also been considering and testing the possibility of that the problem was due to locking system.
    BUT I've made a new discover: I thought that maybe something strange was happening with threads, that was preventing the thread that should commit of committing. So I made a new test, asking for an insert, and then making Thread.sleep(300000) (5 min). Surprise! Now it works. That has allowed me to deduce that the problem is in the next method:

    Java Code:
        /**
         * Runs a custom command, that should be guaranteed to not to provide an 
         * interesting return value
         * @param command The command to run
         */
        public static void runVoidCommand(String command) {
            try {
                SQL.statement.executeUpdate(command);
            } catch (SQLException ex) {
                Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
                return;
            }
            System.out.println("Command: " + command);
        }
    I use this method for running commands from the methods I have that modify data: createTable, clearTable, insertInto, dropTable, etc. Which are the methods that were failing. They work building the string representing the command that must be run, and then calling this runVoidCommand(). But I can't allow this. I don't want to have to wait for 5 minutes after each time I insert a row of info. I've thought that making the runVoidCommand() to create a new Thread to run itself would solve the problem, whatever it be, but anyway I keep needing that thread to sleep for a while and, if the main program should end before, it must wait for the sleep to end, so I need to know what's happening in order to know how to properly solve it. I'm going Google for a little bit about the executeUpdate method, because I don't have idea of other thing that can be provoking this.
    Last edited by Stoyicker; 01-24-2012 at 08:18 PM.

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Hang on, are you using a single connection on multiple threads?

  17. #17
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I'm almost sure that no. The same thread does everything. But I think I've got an idea on how to put some "make up" on the problem. I can make that the runVoidCommand() creates a new Thread to do its tasks. That way the main thread could keep working and therefore consume some time. Then I can make that method 'synchronized', so there can be just one instance of it running. Then, I can add a boolean field to the class. This boolean will be initialized to false, and will be true while the runVoidCommand() method is running, becoming false again at the end of it. Finally, at the beginning of the close() method, I can add a while(thatbooleanfield);. Of course this is a very bad solution and it may even not work in some computers, but I can't get to think a better solution.

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    There must be something else going on, though.
    I've never encountered this failure to write before.

    Note that a commit() may not mean "write to file" for an HSQL database, and that looks to be your problem.
    You can access an insert during the same session?
    That is, open connection, insert row, select row?
    Even better open connection, insert row, close connection, open a new connection, select row?

  19. #19
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    Everything is done on the same session. The connection is opened at the beginning and closed at the end. As well I can perform an insertion and a selection on the same run/session and the recently inserted data appears on the query (or that's what happened before, because there's new shit now...). But the select uses a different kind of method because it's a query, so it doesn't use runVoidCommand(). But, as you say, the commit doesn't mean that the data is written to the file, just to the database. The question then is why the f* isn't the data written to the file?
    By the way, in reply to your questions, I've tested asking for two insertions in the same run, and now there's a new thing. Data isn't save from one run to another one, but it doesn't appear even when I perform a select on the same run. And it does neither work when I just perform an insertion and after a selection, on the same run. This is so frustrating...

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

    Default Re: "File" catalog-type. It should be time-persistent, but why it isn't?

    I'd say it's something odd about the way you're controlling everything.
    Different connections or something like that.

    Step away from your SQL class for the time being (and if it's too big to post here it's definitely too big for me to read through).
    Write in a single method code that opens a connection to the db, creates a Statement, inserts a row into a table in the db, commits and closes the connection.
    Then a single method that does a select (again, open a connection etc). Do not use some sub method for creating the connection...open and close it "manually" in the method.

    Then test those.

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 6
    Last Post: 02-10-2011, 09:55 AM
  2. Creating a file "type"
    By Hello World in forum New To Java
    Replies: 1
    Last Post: 03-04-2010, 02:57 AM
  3. Replies: 1
    Last Post: 05-20-2009, 08:46 PM
  4. Replies: 0
    Last Post: 11-22-2008, 01:49 AM
  5. Replies: 1
    Last Post: 10-20-2008, 07:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •