Surprising Behaviour of RC6 cConnection Object with Bad File

[jpbro]

Consider the following 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:

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?

[jpbro]

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

         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

[Schmidt]

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

         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

[jpbro]

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:

         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?