B4J Question SQLite Hangs without Logs [Solved]

lip

Active Member
Licensed User
Longtime User
B4J Server program running on Raspberry Pis.

The program has been stable for 4 years. It is tweaked and developed every month.

Uses a SQLite database. I use direct calls like SQLite.ExecQuery("SELECT * FROM Table") and SQLite.ExcecNonQuery("INSERT...") rather than the SQLite Library. There are about 300 such calls in the program.

I monitor and control the program with server pages that display sets of data, using SQLite queries as above. There are no .HTML files, I just create responses on the fly using resp.write("TextLine"). There are about 20 such pages.

In the last few days, several Server pages have started to hang. I've put my own logs between lines of code and they always hang on a SQLite read call; Either creating a ResultSet, or returning a COUNT(*) value. After each compile, a random set of pages hangs. The same pages consistently hang until I recompile. The problem does NOT relate to the SQL in the calls that are failing; they are simple SELECT statements or COUNT(*) statements using tables that DO exist. The same Query string(s) are working perfectly well in other parts of the program, just not on the server page.

I religiously open and close cursors, as I've been caught out before by exceeding the open cursors limit. But the problem seems to be similar, like I'm exceeding some kind of limit relating to SQLite calls.

My question is: how can I find out what is failing? Is there some way of seeing logs of the SQLite calls? I think there used to be a "Filter" tickbox in the IDE which you could untick to show more verbose logs, but I can't see that in B4J now?
 

lip

Active Member
Licensed User
Longtime User
No idea why the problem started, but the resolution was to add a SQL Object into the Process Globals of each Handler.

Until now I had one SQL object in Process Globals of Main, which I initialised in AppStart() then called from every module. There are about 150 references to the SQLite object in Main class ie EnergyDB.ExceQuery() and 200 queries and non-queries in the other code modules and Handlers ie Main.EnergyDB.ExecQuery(). This has worked fine for many years until last week.

I have now added a SQL Object into each handler which I initialise in Sub Handle(req As ServletRequest, resp As ServletResponse) and then use SQL.Close at the end of the Handler Sub
 
Upvote 0
Top