ServerSocket App Using SQLite Issues.

General
1

I have a server monitoring app that uses a ServerSocket to handle about 30 client connections. These client connections automatically connect to the server app via a timer in the client app every 3 minutes. When the client app connects, the server app logs the client connection in a SQLite database sitting on the computer running the server monitoring app. I am having issues when multiple clients try to connect to the server app at or close to the same time some of the connections are never logged in the SQLite database. I can see the client apps are connecting fine so it’s not a socket issue. I currently have the ServerSocket set to 50 min 255 max. I moved all of the database methods and any properties or other methods that are used during the logging process into private methods/properties of the ServerSocket. This didn’t solve my issue. Is this a limitation in SQLite? Any advice would be appreciated.

The code that logs the client connection to the SQLite database.

[code]Dim strScanDate As String

Dim Now As New Date

strScanDate = Now.SQLDateTime

Dim dbFile as FolderItem
Dim db as SQLiteDatabase
db=New SQLiteDatabase
dbFile = getDBFile
db.DatabaseFile=dbFile
//sets the encryption key
db.encryptionKey = kEk

If db.Connect() then
Dim ps As SQLitePreparedStatement = db.prepare("Insert into tLogCount (GName, GIP, GMAC, GScanDate, Status) " +_
“Values(?, ?, ?, ?, ?)”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute(strName, strIP, strMAC, strScanDate, “1”)

db.Commit
else
’ Error handling
end if
db.Close[/code]

I have a property in the ServerSocket called myDBFile which is type SQLiteDatabase. This is the method getDBFile from the code above:

[code] // Create database object

myDBFile = New SQLiteDatabase

myDBFile.databaseFile = GetFolderItem(strConfigLocation)

return myDBFile.databaseFile[/code]

2

One connection for all or one for each?
WAL mode on?
You use transactions?
And you use pragma to disable background tasks when you write to log?
And one app instance or several?

3

[quote=356339:@Christian Schmitz]One connection for all or one for each?
WAL mode on?
You use transactions?
And you use pragma to disable background tasks when you write to log?
And one app instance or several?[/quote]
Sorry I just edited the SQLite code I am using in the original post. Thanks.

4

Where is your error checking code?

5

You probably get a database busy error telling you to try later again.

So you could make a loop to try it 5 times with pause between.

6

On other side using #pragma DisableBackgroundTasks, only one connection for all and a mutex to make sure only one uses it, there would only be one writer.

7

[quote=356345:@Christian Schmitz]You probably get a database busy error telling you to try later again.

So you could make a loop to try it 5 times with pause between.[/quote]

How would you handle the pause? App.SleepCurrentThread?

8

So creating a new connection to the SQLite database for each ServerSocket connection is the wrong approach? I was under the impression if I moved all of the database code into the ServerSocket each connection would execute the database code in a queue and the database connections wouldn’t overlap.

9

I think the SQLExecute may yield and than run other thread.

Please add error handling first!

10

[quote=356351:@Christian Schmitz]I think the SQLExecute may yield and than run other thread.

Please add error handling first![/quote]

So I added WAL, a longer timeout period, transactions, and error handling. Thanks Christian we’ll see how it goes. :slight_smile:

[code]
Dim strScanDate As String

Dim Now As New Date

strScanDate = Now.SQLDateTime

Dim dbFile as FolderItem
Dim db as SQLiteDatabase
db=New SQLiteDatabase
dbFile = getDBFile
db.DatabaseFile=dbFile
//sets the encryption key
db.encryptionKey = kEk
db.Timeout = 30

If db.Connect() then
db.MultiUser = True
db.SQLExecute(“BEGIN TRANSACTION”)
Dim ps As SQLitePreparedStatement = db.prepare("Insert into tLogCount (GName, GIP, GMAC, GScanDate, Status) " +_
“Values(?, ?, ?, ?, ?)”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute(StringCleaner(strName), strIP, strMAC, strScanDate, “1”)

db.Commit
else
’ Error Handling
if isDebug then
If db.Error Then
WriteStatusS(CurrentMethodName + " DB Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage, False, “1”)
End If
end
end if

Exception err

if (Not modGlobals.ErrorCatch(err, CurrentMethodName)) then
Raise err
End If[/code]