sock_read error

I have 2 web apps which use a common CubeSQL server. Both apps work fine - except…

Under some circumstance, ONE of the 2 web apps will throw a DB.Error which says “An error occurred while executing sock_read”
Web Apps are compiled under 2019r1 running as cgi apps on ServerWarp linux host.

Both Apps use their Open event to call a method which connects the DB server, and instantiate an App property as a connected CubeSQLServer object (App.DB1)
The sock_read error (so far) has only occurred with 1 app - so tends to suggest that I have a coding error in that app - but i’ve spent days searching the code, and just cannot see where I’m going wrong!

The circumstance which seems to provoke this error as follows:-
An array of web containers are used to simulate rows of a listbox.
A recordset is generated by a Select statement:

Dim rsAnswers As RecordSet Dim ps0 As CubeSQLVM ps0 = App.DB1.VMPrepare("SELECT * FROM answers WHERE (teamid =? AND gameid = ?)") ps0.BindText(1, vTeamID) ps0.BindText(2, Session.CurrentGameID) rsAnswers = ps0.VMSelect
The contents of the rows are then set by iteration through the recordset.
The recordset is then Closed

At this point - no errors - all good.
The above procedure is then repeated at 5 second intervals via a webtimer (to check for any changes) - the relevant row is updated if any change found.

At this point - no errors - all good.

The user can then click on any row, and enter new data to that row. Any such change is then submitted to the database via a Save Method:

[code]Dim ps As CubeSQLVM

ps = App.DB1.VMPrepare(“UPDATE answers SET score =? WHERE (teamid =? AND roundid = ? AND answerid = ? AND gameid = ?)”)
ps.BindInt(1, vScore)
ps.BindText(2, vTeamID)
ps.BindInt(3, index+1)
ps.BindInt(4, row +1)
ps.BindText(5, Session.CurrentGameID)

If App.DB1.Error then
MsgBox("DB1Error - Can’t save score: " +App.DB1.ErrorMessage)
end if[/code]

At this point - still no error - all good (usually!)
However, if the user updates anywhere between 1 and approx 20 rows - then the sock_read error will randomly occur. (sometimes never - but often. Even under test conditions with identical data - the error can occur at seemingly random moment.)
This error NEVER occurs during debug test on local Windows network.

Very much appreciate it if someone can suggest what the issue might be!!

Perhaps I have solved this issue. (10 tests, so far no errors!) - Mmm

So what did I change?
In the App connectDB method I added 1 line of code:-

mDB.AutoCommit = True

Don’t know why that helps? All my VMExecutes are wrapped in BEGIN TRANSACTION / COMMIT - by maybe my VMSelects should also be wrapped in Transactions?? I didn’t think Transactions were relevant to SQL Select commands? Thought they only applied to modifications of data?

We’ll see if any sock_read appear in future…


I believe the Transactions are only relevant to SQL Execute

Hopefully the AutoCommit=True solves the sock_read error for you

Keep us updated

The CubeSQL database has timed out. If you have a long delay in between updates, you will need to refresh the connection.

Each session should have its own database connection to start with.

#David Cox
No - this error happens, even within a few seconds after startup, and even if updates are every second.

But, as above - no more errors seen since AutoCommit = True, but no idea why that helped…

#James Dooley
Yes, I would prefer each Session to have its own connection - but my Cube licence is limited to 2 concurrent connections, so the App connection serves all sessions for all users.

[quote=497864:@Tony Davies]#James Dooley
Yes, I would prefer each Session to have its own connection - but my Cube licence is limited to 2 concurrent connections, so the App connection serves all sessions for all users.[/quote]

It is not optional, it is a technical requirement and I suspect if you read the licensing agreement a legal requirement as well.

Sessions run independent of each other which means multiple sessions can potentially attempt to write to the same socket at the same time. AutoCommit = True will probably help a bit, but as you add more users you will most likely run into the same problem again.

Ooooh!! @James Dooley - you’ve hit the nail on the head!
I had read the Xojo Docs / User Guide for SQLite which says connect the DB to an App property - but now I see that only applies to Desktop! For Web Apps, the DB connection should be instantiated as a property of WebSession. Bet that explains the sock_read errors indeed…
Many thanks for pointing that out.