Results 1 to 4 of 4

Thread: Surprising Behaviour of RC6 cConnection Object with Bad File

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,895

    Surprising Behaviour of RC6 cConnection Object with Bad File

    Consider the following code:

    Code:
    Sub TestBadDb()
       Dim lo_Cnn As RC6.cConnection
       
       Set lo_Cnn = New_C.Connection(App.Path & "\test.png", DBOpenFromFile)
       Debug.Print lo_Cnn.FileName
       Debug.Print lo_Cnn.DBHdl
       Debug.Print lo_Cnn.DataBases.Count
       Debug.Print lo_Cnn.LastDBError
       
       
       Set lo_Cnn = New RC6.cConnection
       If lo_Cnn.OpenDB(App.Path & "\test.png") Then
          Debug.Print lo_Cnn.FileName
          Debug.Print lo_Cnn.DBHdl
          Debug.Print lo_Cnn.DataBases.Count
          Debug.Print lo_Cnn.LastDBError
       End If
    End Sub
    It tries to open an SQLite DB connection - in 2 difference ways - to a file that is not an SQLite DB formatted file (e.g. a PNG file).

    In both cases, no errors are raised (and the .OpenDB call in the second attempt returns True indicating success). The printout for both attempts also seems to indicate that the DB was successfully opened:

    Code:
    Filename       : C:\users\jbrow\documents\testapp\test.png
    DbHdl          : 152759024 
    Databases.Count: 1 
    LastDbError    : not an error
    It's not until I attempt to run a subsequent DB "touching" method (like opening a recordset, checking integrity, executing an SQL command, etc...) that we get a "not a database" error raised.

    I find this behaviour a bit surprising/counter-intuitive, as I would expect and error to be raised or at least for the the OpenDB method to return False, and various properties like DbHdl, Databases.Count to be 0, and/or LastDbError to be "not a database".

    That said, I can work around it by calling CheckIntegrity and trapping the error there, but that seems like it could cause performance problems. For example, in 99.9% of the cases we will be opening a working SQLite DB file, so checking the integrity might take some time (especially on a large DB with lots of tables, indexes, views, etc...).

    Another option I thought to try is to perform a quick "SELECT" statement that doesn't touch any tables, such as "SELECT 1". But this succeeds as it doesn't try to hit the underlying "corrupt" database. I could add a random table name (e.g. SELECT * FROM asjdhakjsdhakjfhakjdfhsdkjf), but this feels hacky as heck. I would also need to trap errors for the missing table that would be raised for the 99.9% of good databases.

    Anybody have any clever ideas? Maybe an OpenSchema or "pragma application_id" call would be fastest - and only raise an error in the case of a bad/corrupt DB?

  2. #2

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,895

    Re: Surprising Behaviour of RC6 cConnection Object with Bad File

    I've opted for the "pragma" approach for now, trapping the error if found. Kinda ugly, but it works:

    Code:
             On Error Resume Next
             Set lo_Cnn = New_C.Connection(l_OptsDbPath)
             If lo_Cnn Is Nothing Then
                l_IsBrokenDb = True
             Else
                lo_Cnn.ExecCmd "pragma application_id"
                l_IsBrokenDb = (Err.Number <> 0)
             End If
             On Error GoTo Cleanup
    Still open to better ideas though

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,680

    Re: Surprising Behaviour of RC6 cConnection Object with Bad File

    Quote Originally Posted by jpbro View Post
    I've opted for the "pragma" approach for now, trapping the error if found. Kinda ugly, but it works:

    Code:
             On Error Resume Next
             Set lo_Cnn = New_C.Connection(l_OptsDbPath)
             If lo_Cnn Is Nothing Then
                l_IsBrokenDb = True
             Else
                lo_Cnn.ExecCmd "pragma application_id"
                l_IsBrokenDb = (Err.Number <> 0)
             End If
             On Error GoTo Cleanup
    Still open to better ideas though
    The Open-call above does not (yet) perform any content interpretation on any given file.
    (and will therefore always return a valid Cnn-Object, as long as the file exists and is accessible).

    That behaviour matches with the "purpose-theme" of SQLite, which wants to be thought of as an alternative to the fopen() call.

    E.g. (if you want to avoid the CreateDatabase-call) - you can also create a new empty file "by hand" -
    (in Explorer or by any other means) ... with any name and zero-bytes content...
    and the Connection-Open-call will open such an empty file just fine (with succeeding pragma-calls).

    Even with a normally used DB-File, you can never be 100% sure whether it's content was corrupted -
    until you interact with it (or do an integrity-check-call beforehand).

    And yes, a cheaper and faster "rough and simple validity-filecheck" can be accomplished by your above pragma-call...

    Other cheap checks, based on pragma-calls could be:
    - Cnn.Execute "pragma journal_mode='WAL'" ... (which I do anyways these days, when I use SQLite on the serverside)
    - If Cnn.Synchronous <> SynchronousFull Then Cnn.Synchronous = SynchronousFull

    Or "non-pragma-based" (relying on the existing or autogenerated sqlite_master table):
    - Cnn.OpenSchema
    - Cnn.DataBases("main").Tables.Count

    I'm not planning to change that opening-behaviour of a cConnection-Object
    (just keep in mind, that it shows the same behaviour as the VB6 File-Open call,
    which will successfully give you a handle, as long as the file exists and is accessible)

    Olaf

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,895

    Re: Surprising Behaviour of RC6 cConnection Object with Bad File

    Thanks for the additional info. I guess in my mind I assumed that the sqlite3_open() call would be doing a rudimentary check for say the "SQLite format 3" header and return 26 - SQLITE_NOTADB which would be detected and raised as an error in RC6. Well we know where assumptions get us.

    Anyway, it's not something that's been a problem after ~15 years of using RC*/SQLite and I'm not expecting any behaviour changes. I just wanted to post the finding here in case anyone else should bump into it and have questions, and also too see if there were any better ideas for ways to quickly discover if a file is not actually an SQLite database. I think that's been well covered now.

    A bit of background - I have a non-critical DB that I would like to automatically rebuild if it gets corrupted/replaced with a non-DB file rather than fail and cause the app to misbehave. This is why I started testing loading non-DB files with the OpenDB method.

    I have adjusted my code a bit since there should always be a cConnection object returned. If there's not, then there is something very foul going on, but I won't want to delete and re-create the database. Here's my updated "open & perform a simple test" code in case it's useful to anyone:

    Code:
             Set lo_Cnn = New_C.Connection(l_DbPath)
             
             If lo_Cnn Is Nothing Then
                ' This should never happen unless something is wildly wrong, but we'll put this here just in case.
                Debug.Assert False
                Err.Raise vbObjectError, , "Could not open database file at " & l_DbPath
             
             Else
                ' The file was opened at the most rudimentary level.
                ' It might not be an SQLite DB so we will perform a simple pragma check to see if that succeeds.
                
                On Error Resume Next
                lo_Cnn.ExecCmd "pragma application_id"
                On Error GoTo Cleanup
                
                l_LastDbErr = lo_Cnn.LastDBError
                
                Select Case l_LastDbErr
                Case "", "not an error"
                   ' No error, we will leave the open/test loop and use the connection
                   
                Case "file is not a database", "database disk image is malformed"
                   ' Not a database! We will delete and rebuild the DB.
                   l_IsBrokenDb = True
                   
                Case Else
                   ' Some other error we should not try to recover from
                   Err.Raise vbObjectError, , "Could not open database file at " & l_DbPath & ", Last DB Error: " & l_LastDBErr
                
                End Select
                
             End If
    I will make one last comment/request - Testing against error strings always made me feel icky vs. testing against error codes. Any chance we could get a .LastDBErrorCode method that returns the result of sqlite3_errcode?
    Last edited by jpbro; Oct 16th, 2021 at 08:49 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width