Jump to content

Recognising SQLite has lost connection to the DB file


Recommended Posts

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

Link to comment
Share on other sites

Post your code, else we're just chatting.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

$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 by joboy2k
Link to comment
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...