Recognising SQLite has lost connection to the DB file

[Joboy2k]

Hello,

 

This may be an issue that I experience just because of my circumstances but I use a MacBook Pro and use a VirtualBox with windows 10 on for my programming.

 

When I have a program created with SQLite abilities it will works fine but when I put my Virtualbox in save state and then restore it, my program still believes its connected to the DB file but will return no results.

 

I thought this line would solve that issue

If $iRival = $SQLITE_OK Then

but after the restore the same query that would return 100 results just before I saved state would return 0 results but $iRival is still set as 0 so its not picked up as an error and just thinks there is no results for the query.

 

Obviously its not really a situation I  create a test script for. I was wondering if there is something I would need to put in my While loop to keep checking connection somehow and realising its not connected it will go through a reconnection process.

 

The DB file is on the MacBook and shared through a folder which is mapped to the VirtualBox

 

Hope this is enough information, anything else needed please ask.

 

Thanks in advance

Jonathan

[jchd]

Post your code, else we're just chatting.

[Joboy2k]

$String = "Select SRC_ID, SRC_Ticket, SRC_Status, SRC_Date, SRC_Fullname, SRC_Address, SRC_Phone, SRC_Type From Search Where SRC_Ticket Like '272%' GROUP BY SRC_Ticket;"

Func SQLQuery($String)
    Global $iRows, $iColumns, $iRval, $aResult
    $iRival = _SQLite_GetTable2d($SQLDB, $String, $aResult, $iRows, $iColumns)
    If $iRval = $SQLITE_OK Then
        If $iRows > 0 Then
            _ArrayDelete($aResult, 0) ; Deletes column titles
            Return($aResult)
        EndIf
        Return
    Else
        If _SQLite_ErrMsg($SQLDB) = 'near ";": syntax error' Then
            MsgBox(0, 0, "ERROR HERE ?")
        Else
            MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg($SQLDB), 2)
            MsgBox(16, "SQLite Error: " & $iRval, "Error With Search. Check Your Input ", 2)
        EndIf
        Return(SetError(1))
    EndIf
EndFunc

This is the code that doesn't work after I have saved the VB state. Can search for 272 and get 100 results and save state and restore seconds later and it will still think its successful but just that there is no results in the database for the 272 criteria.

 

Edited March 10, 2020 by joboy2k

[jchd]

How do you open the MacOS file? Is the Mac directory mounted in Windows, shared ressource, URI, ... ?

It indeed looks like the link is somehow broken when VB "goes to bed".
Even after looking again at the UDF I see no obvious reason why no error is issued in your scenario.

Seems like a deep problem in one of the multiple layers of code between MacOs and Windoze, probably hard to debug at user level.

[Joboy2k]

This DB file is in a folder on Mac which in the VB settings is made a a shared resource and on the windows it is attached as a network drive and has a permanent drive letter also.

Yeah I thought it would be one of those things that was just an issue of using a VB. I used to only work with SQLITE through an SSH connection UDF I made which knows straight away if its lost connection but obviously this is different and if you can't see anything wrong im happy to just chalk it up as one of those things that can't be solved.

Thank you for your time.

[jchd]

Yeah, must be one of these weird things one experiences with network sharing. SQLite is especially sensitive to network file systems not behaving properly, particularly regarding file locking, which SQLite uses intensely.

If you can switch to a reliable known-working solution fitted to your setup, just do that else you'll be wasting your time and energy against a concrete wall.

OTOH it's almost certain that now direct network connections Windows >= 7 PC <--> Windows >= 7 PC work fine in SQLite low-concurrency contexts, provided the network is stable & reliable. If the last condition isn't satisfied, all bets are off as you just experienced.

Of course using the latest SQLite release is highly recommended!