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

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:

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.

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

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", "");

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.

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

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:

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.

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.

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?

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.

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.

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

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

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:

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.

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

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.

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?

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

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.