SQLite does not complete the transactions

[Alaster Wolf]

I am a happy new user of the NWNX4. I need help regarding the SQLite. It seems that it does not complete all the transactions before the server is closed.

I understand a fair amount of SQL, although I am far from a DB expert. I was able to implement a persistent journal from these forums and to update it to work with SQLite and my module.

I can see that the SQL has been executed by reading the "sqlite.txt" but it does not appear in the db file (I can browse the db file with an application).

I suspect that the changes are cached in the "sqlite.db-journal" file and that they are possibly commited to the db file at some point. Well, if that is the case then it did not happen at first. The "sqlite.db-journal" file has remained on disk even after the server has been stopped and NWNX window is closed. Next time I run the server, that file disappeares until the module makes new interaction with the SQL. And the changes that module has made before the server restart are lost.

I have resolved this problem by adding "COMMIT" and "BEGIN" directives. Now, "sqlite.db-journal" file does not stay on disk, which probably means that the cache is regularly commited to the db file with my "COMMIT". This is OK with me if it does not lower the performance?

I have also noticed that this problem does not happen if I don't include an override to my module which contains a bit more than 1000 of item blueprint files. So, I suspected that maybe the timing is an issue and that the engine is busy processing all these files. But, I have tested the delays in the scripts and it did not help. So, maybe some memory issues or what?

Next, the problem reappeared when I have added some new SQL to update the records. Now, even the "COMMIT" does not help. I don't see any errors in sqlite.txt, except for a bit of "! SQL Error: cannot commit - no transaction is active (1)" probably beause of my extensive use of "COMMIT; BEGIN;". But that should be harmless, or not? Anyway, it seems that this is not an error with my SQL directives, since the problem has been resolved before and then reappeared.

I am sorry for this long description of the case, but I really hope that someone can help, please.

EDIT:
Maybe it could be a file permission issue? I will try to check about that. I am using Win7 with my admin rights user. NWNX is in its default folder (C:\NWNX4). I run my NWN2 client on the same PC.

EDIT:
I have tested whatever I could about the file permissions. Everything should be fine with it and it seems that's not the issue.

EDIT:
I have now switched to the MySQL and it works nicely. I guess I'll stick to it. Still, it would be nice to know what was wrong with the SQLite.