B4J Question Jetty Server SQLite References in Handler Class

[lip]

I've had problems for ever with this. See This Post.

My SQL Object is declared as Public in Main.

Process Globals
    Public EnergyDB as SQL
AppStart
    EnergyDB.InitializeSQLite(...)

Various Timers in Main constantly Tick, using the SQL Object, no problem. It never fails when called from Main.

When I make any reference to the use it within a Handler class, the handler doesn't crash, but hangs.

Sub Handle(req As ServletRequest, resp As ServletResponse)
...
Log("Before")
Log(Main.EnergyDB.IsInitialized)
Dim Hours as ResultSet = Main.EnergyDB.ExecQuery("SELECT * FROM EnergyHours")
Log("After")

Logs:
Before
True

And nothing else. No errors, and the page hangs.

The problem goes away in Debug mode (single thread) which is a clue, but not much help.

I sort of solved it a year ago by having SQL Objects in the Handler Class, but Erel said that this would corrupt the Database. I have noticed occasional database errors so I guess this is not the right solution.

Having spent the last three days experimenting, I've discovered something that might help Erel or another genius solve this for me:

I can make the Handler pages run by simply Initialising the Main.EnergyDB object again in a Handler:

Sub Handle(req As ServletRequest, resp As ServletResponse)
...
Log("Start")
Main.EnergyDB.InitializSQLite(...)
Log("ReInitialized")
Main.EnergyDB.ExecQuery("SELECT * FROM EnergyHours")
Log("Queried")

Logs:
Start
ReInitialized
Queried

What's interesting, is that once I have served any page that has this 'ReInitialize" statement, all of the other Handlers now work, whether they have this statement or not.

My thinking is that the SQL Object in Main has to be Initialized in Main, so that Main has reference to the Database file, then it needs to be Initialized for Jetty, so that Jetty has a reference to the SQLite file, then all is well.

I'm interested to know if my latest 'fix' is likely to cause problems? And if anyone understands what's really going on?

 

[MathiasM]

I don't know if it's a good advice, but anyway: I use a public SQL object like you, but declared in a module, and I have never experienced the problem you describe. So maybe you can try that.

I wouldn't use your 'fix', what if 2 calls are handled at exactly the same time, and both handlers try to initialize the SQLite file? This could potentially fail because of file locks?
Also, have you ever tested if initializing takes longer with a big database? (I would presume that) It could cause severe slowdowns to do this each and every call.

 

[Erel]

Carefully check that you aren't forgetting to close a transaction.

 

[lip]

Carefully check that you aren't forgetting to close a transaction.

The handlers don't have any transactions as they don't update data. They only query the data.

The Main program does update data. Where I update one record at a time I am not using transactions. Where I update more than one record I follow:

.BeginTransaction
Try
...Insert/Update
.TransactionSuccessful
Catch
.Rollback
End Try

Does this mean that I am closing the transactions correctly?

 

[Erel]

Yes.

Have you set the pragma mode to WAL ?
[WebApp] Concurrent access to SQLite databases

 

[lip]

Yes.

Sub UpdatePcDB(myDB As SQL)
myDB.ExecNonQuery("PRAGMA journal_mode=WAL")
Log("Database PRAGMA set to WAL")

This runs during AppStart and I see the log

 

[lip]

Yes.

Have you set the pragma mode to WAL ?
[WebApp] Concurrent access to SQLite databases

Yes.

Sub UpdatePcDB(myDB As SQL)
myDB.ExecNonQuery("PRAGMA journal_mode=WAL")
Log("Database PRAGMA set to WAL")

This runs on startup and I see the log

I also clean up the database immediately after setting WAL with vacuum:

    myDB.ExecNonQuery("VACUUM")
    Log("Database Vacuumed")

 

[Erel]

FWIW, I run several servers that use SQLite like this and never encountered such issues.

Maybe an exception is thrown during the ExecQuery? Add Try / Catch to test it.

 

[lip]

I don't know if it's a good advice, but anyway: I use a public SQL object like you, but declared in a module, and I have never experienced the problem you describe. So maybe you can try that.

I wouldn't use your 'fix', what if 2 calls are handled at exactly the same time, and both handlers try to initialize the SQLite file? This could potentially fail because of file locks?
Also, have you ever tested if initializing takes longer with a big database? (I would presume that) It could cause severe slowdowns to do this each and every call.

Thanks for getting involved. Do you mean a Code module? I've never declared anything in Process_Globals of a code module.

Or do you mean a class module? If so, I guess you don't mean a page handler, just a class that you initialise once?

 

[lip]

FWIW, I run several servers that use SQLite like this and never encountered such issues.

Maybe an exception is thrown during the ExecQuery? Add Try / Catch to test it.

I've just spotted a snippet from you in a different post, and see that you use ExecQuery for PRAGMA. I am using ExecNonQuery. Which is correct? Also you mention that you should get the returned value?

Dim c As Cursor
   c = sql.ExecQuery("PRAGMA journal_mode=DELETE")
   c.Close

 

[lip]

Also - might be relevant. There are two SQLite Databases in the App. Both behave in the same way, ie they must be initialised from within a Handler before they will work in Handlers, despite having been initialised and working perfectly in Main.

 

[LucaMs]

FWIW

I learned another "abbreviation" (Too bad I'll have already forgotten it tomorrow )

 

[Erel]

ie they must be initialised from within a Handler before they will work in Handlers, despite having been initialised and working perfectly in Main.

This is not good.

I've just spotted a snippet from you in a different post, and see that you use ExecQuery for PRAGMA. I am using ExecNonQuery. Which is correct? Also you mention that you should get the returned value?

It doesn't matter.

If you are able to reproduce it in a small project then please do so.

 

[lip]

This is not good.

It doesn't matter.

If you are able to reproduce it in a small project then please do so.

Good plan. I'll start a project from scratch and add bits until I either replicate the problem, or find that a new project from scratch works. If I replicate it, I'll start a new post and upload the project. Thanks for your help.

 

[lip]

Final update in case it's of any interest. I compromised by just re-initialising once from any Handler and it works fine.

I put this in Main:

Process_Globals
Dim DatabasesHandlerInitialised As Boolean = False

And this in every Handler, so which ever page you click first, the databases get Reinitialised once and only once.

    If Not(Main.DatabasesHandlerInitialised) Then
        Main.EnergyDB.InitializeSQLite("/home/pi/Database1", "DB1.db", False)
        Main.IntSQL.InitializeSQLite("/home/pi/Database1", "DB2.db", False)
        Main.DatabasesHandlerInitialised = True
    End If

 

[Erel]

This shouldn't be needed. Initialize the SQL objects once in AppStart. They will remain initialized.

 

[tchart]

As per others comments I have several apps that work in similar ways and have never seen any issues like this. One app uses many timers to collect data and push into SQLite - these timers often occur at the same time.

What version is your SQLite library?

Can you open the database in SQLite DB browser and show us the current pragma settings?