Open database in readonly with sqlite_open_v2

Nov 6, 2012 at 1:58 PM

Hello, 

I try to open an "in use" SQLite database from Excel 2007 with read only flags.

To do this, I try to call the sqlite_open_v2 function with :

Private Declare Function sqlite3_stdcall_open_v2 Lib "SQLite3_StdCall" Alias "_sqlite3_stdcall_open_v2@16" (ByVal pwsFileName As Long, ByRef hDb As Long, iFlags As Long, ByVal zVfs As Long) As Long ' PtrDb

And some constants declaration like :

' Flags For File Open Operations
Public Const SQLITE_OPEN_READONLY           As Long = 1  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_READWRITE          As Long = 2  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_CREATE             As Long = 4  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_DELETEONCLOSE      As Long = 8  ' VFS only
Public Const SQLITE_OPEN_EXCLUSIVE          As Long = 16  ' VFS only
Public Const SQLITE_OPEN_AUTOPROXY          As Long = 32  ' VFS only
Public Const SQLITE_OPEN_URI                As Long = 64  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_MEMORY             As Long = 128  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_MAIN_DB            As Long = 256  ' VFS only
Public Const SQLITE_OPEN_TEMP_DB            As Long = 512  ' VFS only
Public Const SQLITE_OPEN_TRANSIENT_DB       As Long = 1024  ' VFS only
Public Const SQLITE_OPEN_MAIN_JOURNAL       As Long = 2048  ' VFS only
Public Const SQLITE_OPEN_TEMP_JOURNAL       As Long = 4096  ' VFS only
Public Const SQLITE_OPEN_SUBJOURNAL         As Long = 8192  ' VFS only
Public Const SQLITE_OPEN_MASTER_JOURNAL     As Long = 16384  ' VFS only
Public Const SQLITE_OPEN_NOMUTEX            As Long = 32768  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_FULLMUTEX          As Long = 65536  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_SHAREDCACHE        As Long = 131072  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_PRIVATECACHE       As Long = 262144  ' Ok for sqlite3_open_v2()
Public Const SQLITE_OPEN_WAL                As Long = 524288  ' VFS only

And the Open function :

Public Function SQLite3OpenV2(ByVal FileName As String, ByRef dbHandle As Long, Flags As Long, ByVal zVfs As Long) As Long
    SQLite3OpenV2 = sqlite3_stdcall_open_v2(StrPtr(FileName), dbHandle, Flags, 0)
End Function

When I call my SQLite3OpenV2 function I get an error --> the return value is SQLITE_MISUSE

Do you think it is possible to do something like this ?

Is Someone could help me ?

Thx

Nov 6, 2012 at 4:36 PM

Hi,

It looks like the sqlite3_stdcall_open_v2 takes UTF8 strings, and not UTF16 strings like sqlite3_stdcall_open16. This means to call it you can't just use StrPtr(FileName), but need to use the StringToUtf8Bytes helper function. The SQLite3BackupInit function would be an example of how to use this.

Please write back if get it to work that way, or need me to take a closer look.

Regards,

Govert

Nov 7, 2012 at 5:21 PM

Hi,

Indeed, I read the Sqlite doc and sqlite_open_v2 must be called with UTF8 string

I try your solution but it doesn't work ... or I'm a noob

Here is my Code :

Public Function SQLite3OpenV2(ByVal FileName As String, ByRef dbHandle As Long, Flags As Long, ByVal zVfs As Long) As Long
    Dim bufFileName() As Byte
    bufFileName = StringToUtf8Bytes(FileName)
    SQLite3OpenV2 = sqlite3_stdcall_open_v2(VarPtr(bufFileName(0)), dbHandle, Flags, 0)
End Function

And the test Function :

Public Sub TestOpenCloseV2()
    Dim testFile As String
    Dim myDbHandle As Long
    Dim myDbHandleV2 As Long
    Dim RetVal As Long
    
    ' Open the database in Read Write Access
    testFile = "C:\TestSqlite3ForExcel.db3"
    RetVal = SQLite3Open(testFile, myDbHandle)
    Debug.Print "SQLite3Open returned " & RetVal
    
    ' Open the database in Read Only Access
    testFile = "C:\TestSqlite3ForExcel.db3"
    RetVal = SQLite3OpenV2(testFile, myDbHandleV2, SQLITE_OPEN_READONLY, Empty)
    Debug.Print "SQLite3OpenV2 returned " & RetVal
    
    RetVal = SQLite3Close(myDbHandleV2)
    Debug.Print "SQLite3Close V2 returned " & RetVal
    
    RetVal = SQLite3Close(myDbHandle)
    Debug.Print "SQLite3Close returned " & RetVal
    
    Kill testFile

End Sub

The same error code : 21 --> MISUSE

In Sqlite online doc : I saw this information --> http://www.sqlite.org/c3ref/temp_directory.html 

For information I also try the URI path without success --> http://www.sqlite.org/c3ref/open.html

I continue my investigations

Regards

Nov 7, 2012 at 10:18 PM
Edited Nov 7, 2012 at 11:01 PM

Hi,

It looks like the iflags parameter needs a ByValue too:

Private Declare Function sqlite3_stdcall_open_v2 Lib "SQLite3_StdCall" Alias "_sqlite3_stdcall_open_v2@16" (ByVal pwsFileName As Long, ByRef hDb As Long, ByVal iFlags As Long, ByVal zVfs As Long) As Long 

 and then:

Public Function SQLite3OpenV2(ByVal fileName As String, ByRef dbHandle As Long, ByVal Flags As Long, ByVal vfsName As String) As Long
    Dim bufFileName() As Byte
    Dim bufVfsName() As Byte
    bufFileName = StringToUtf8Bytes(fileName)
    If vfsName = "" Then
        SQLite3OpenV2 = sqlite3_stdcall_open_v2(VarPtr(bufFileName(0)), dbHandle, Flags, 0)
    Else
        bufVfsName = StringToUtf8Bytes(vfsName)
        SQLite3OpenV2 = sqlite3_stdcall_open_v2(VarPtr(bufFileName(0)), dbHandle, Flags, VarPtr(bufVfsName(0)))
    End If
End Function

Could you have a look?

-Govert

Nov 8, 2012 at 7:50 AM

It works !

I add a test function to check the RetVal with Read Only openned database :

Public Sub TestWriteReadOnly()
    Dim testFile As String
    Dim myDbHandle As Long
    Dim myDbHandleV2 As Long
    Dim myStmtHandle As Long
    Dim RetVal As Long
    
    ' Open the database in Read Write Access
    testFile = "C:\TestSqlite3ForExcel.db3"
    RetVal = SQLite3Open(testFile, myDbHandle)
    Debug.Print "SQLite3Open returned " & RetVal
    
    ' Open the database in Read Only Access
    testFile = "C:\TestSqlite3ForExcel.db3"
    RetVal = SQLite3OpenV2(testFile, myDbHandleV2, SQLITE_OPEN_READONLY, Empty)
    Debug.Print "SQLite3OpenV2 returned " & RetVal
    
    ' Create the sql statement - getting a StmtHandle back
    RetVal = SQLite3PrepareV2(myDbHandle, "CREATE TABLE MyFirstTable (TheId INTEGER, TheText TEXT, TheValue REAL)", myStmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal
    
    ' Start running the statement
    RetVal = SQLite3Step(myStmtHandle)
    Debug.Print "SQLite3Step returned " & RetVal
    
    ' Finalize (delete) the statement
    RetVal = SQLite3Finalize(myStmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
    
    ' Create the sql statement - getting a StmtHandle back with Read Only
    RetVal = SQLite3PrepareV2(myDbHandleV2, "CREATE TABLE MySecondTable (TheId INTEGER, TheText TEXT, TheValue REAL)", myStmtHandle)
    'RetVal = SQLite3PrepareV2(myDbHandleV2, "SELECT * FROM MyFirstTable", myStmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal
    
    ' Start running the statement with Read Only
    RetVal = SQLite3Step(myStmtHandle)
    Debug.Print "SQLite3Step returned " & RetVal
    
    If RetVal = SQLITE_READONLY Then
        Debug.Print "Cannot Write in Read Only database"
    End If
    
    ' Finalize (delete) the statement with Read Only
    RetVal = SQLite3Finalize(myStmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
    
    ' Create the sql statement - getting a StmtHandle back with Read Only
    RetVal = SQLite3PrepareV2(myDbHandleV2, "SELECT * FROM MyFirstTable", myStmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal
    
    ' Start running the statement with Read Only
    RetVal = SQLite3Step(myStmtHandle)
    Debug.Print "SQLite3Step returned " & RetVal
        
    If RetVal = SQLITE_DONE Then
        Debug.Print "But Reading is granted on Read Only database"
    End If
    
    ' Finalize (delete) the statement with Read Only
    RetVal = SQLite3Finalize(myStmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
    
    RetVal = SQLite3Close(myDbHandleV2)
    Debug.Print "SQLite3Close V2 returned " & RetVal
    
    RetVal = SQLite3Close(myDbHandle)
    Debug.Print "SQLite3Close returned " & RetVal
    
    Kill testFile

End Sub

Thank you for your help.

Regards

CrossRobotiK

Nov 8, 2012 at 1:09 PM

Great!

Thanks for getting back and the nice test.

I've made a new version (v 0.9) which incorporates this change, as well as your test function.

Regards,

Govert