Excel/VBA hangs when trying to change journal mode of an SQLite database via ADODB/SQLiteODBC-CodePudding

The code below creates a new blank SQLite database in the Temp folder, creates a table, populates it, and pauses. Then the database is opened in DB Browser for SQLite interactively, and one value is changed, but the change is not committed. Execution is resumed, and Excel/VBA hangs for about 1.5 min before raising the "database is locked" error when trying to change journal mode via ADODB/SQLiteODBC.

To clarify, this code is specifically designed to reproduce the problem. It is clear why the error is raised. The problem is the duration of required waiting.

Private Sub ConnectSQLiteAdoCommandSource()
    Dim Driver As String
    Driver = "SQLite3 ODBC Driver"
    Dim Database As String
    Database = Environ("Temp") & "\" & CStr(Format(Now, "yyyy-mm-dd_hh-mm-ss.")) _
        & CStr((Timer * 10000) Mod 10000) & CStr(Round(Rnd * 10000, 0)) & ".db"
    Debug.Print Database
    Dim Options As String
    Options = "JournalMode=DELETE;SyncPragma=NORMAL;FKSupport=True;"

    Dim AdoConnStr As String
    AdoConnStr = "Driver=" & Driver & ";" & "Database=" & Database & ";" & Options
    
    Dim SQLQuery As String
    Dim RecordsAffected As Long
    Dim AdoCommand As ADODB.Command
    Set AdoCommand = New ADODB.Command
    With AdoCommand
        .CommandType = adCmdText
        .ActiveConnection = AdoConnStr
        .ActiveConnection.CursorLocation = adUseClient
    End With
    
    '''' ===== Create Functions table ===== ''''
    SQLQuery = Join(Array( _
        "CREATE TABLE functions(", _
        "    name    TEXT COLLATE NOCASE NOT NULL,", _
        "    builtin INTEGER             NOT NULL,", _
        "    type    TEXT COLLATE NOCASE NOT NULL,", _
        "    enc     TEXT COLLATE NOCASE NOT NULL,", _
        "    narg    INTEGER             NOT NULL,", _
        "    flags   INTEGER             NOT NULL", _
        ")" _
    ), vbLf)
    With AdoCommand
        .CommandText = SQLQuery
        .Execute RecordsAffected, Options:=adExecuteNoRecords
    End With
    
    '''' ===== Insert rows into Functions table ===== ''''
    SQLQuery = Join(Array( _
        "INSERT INTO functions", _
        "SELECT * FROM pragma_function_list" _
    ), vbLf)
    With AdoCommand
        .CommandText = SQLQuery
        .Execute RecordsAffected, Options:=adExecuteNoRecords
    End With
    
    '@Ignore StopKeyword
    Stop '''' Lock Db. For example, open in GUI admin tool and start a transaction
    '''' ===== Try changing journal mode ===== ''''
    On Error Resume Next
    With AdoCommand
        .CommandText = "PRAGMA journal_mode = 'WAL'"
        .Execute RecordsAffected, Options:=adExecuteNoRecords
    End With
    If Err.Number <> 0 Then
        Debug.Print "Error: #" & CStr(Err.Number) & ". " & vbNewLine & _
                    "Error description: " & Err.Description
    End If
    On Error GoTo 0
    
    AdoCommand.ActiveConnection.Close
End Sub

CodePudding user response：

ADODB Connection and Command have the timeout setting, which defaults to 30s. Changing this default has no observable effect. The actual timeout is 100s, and this number comes from the SQLiteODBC source. The driver must have a bug. It has its default timeout value, takes a custom value from the connection string, but ignores the values set via the ADODB library. So, adding "Timeout=XXX;" to the connection string options does the job. It appears that setting it to 0 means waiting infinitely, but setting it to 1 ms solves the timeout problem. Another option, which is off by default, - StepAPI - should probably be used by default. And here is another driver bug or limitation: when StepAPI is enabled, the busy handler is not set, and the timeout problem also goes away regardless of the "Timeout=XXX;" option.