Backup API does not work on 64-bit Excel
SoerenBusse opened this issue · 13 comments
If I try to run the "AllTests" method in the example project, excel crashes with the error:
"Excel has stopped working". Nothing else?
I'm using Excel 2013
Are you perhaps using the 64-bit version of Excel?
Yop
Are you running the SQLiteForExcel_64.xlsm?
Did you copy the x64 directory and that version of SQLite3.dll too?
On which line does it crash?
I've downloaded the whole repository. Then I started the *_64.xlsm file. There's no line. Whole excel is crashing.
I think it might be a problem with the backup API.
You can comment out the line calling TestBackup
inside Sub AllTests()
.
I'll have a look - the declaration of the backup API was added later, and might not be right for 64-bit.
Confirming 64 bit Excel crash on running AllTests. Yes indeed, you can comment out calling TestBackup and have the other tests run. Takes > minute to run the other tests. Trouble traced to SQLite3.dll, sqlite3_backup_step function.
Module Sqlite3Demo.bas
Sub AllTests
SQLite3Initialize 'in Module Sqlite.bas
LoadLibrary ' SQLite3.dll
SQLite3BackupStep 'in Module Sqlite3.bas, the line that crashes Excel
sqlite3_backup_step 'in Module Sqlite.bas, calls the function in the dll
contained in SQLite3.dll
I wonder what's wrong though - the declaration looks fine to me, comparing with the documentation here: https://www.sqlite.org/c3ref/backup_finish.html
I have zero background in C, or any languages using pointers. (and its been decades since I could PEEK or POKE ...) I have a medium level background in VBA programming. My background in SQL is only as an end user, not as a DBA or DB programmer. So, not sure I can be of much help here?
myBackupHandle = SQLite3BackupInit(myDbBackupHandle, "main", myDbHandle, "main")
In my case returns a large negative number for myBackUpHandle? (Dim myDbHandle As LongPtr) Next statement follows:
If myBackupHandle <> 0 Then
RetVal = SQLite3BackupStep(myBackupHandle, -1)
Debug.Print "SQLite3BackupStep returned " & RetVal
RetVal = SQLite3BackupFinish(myBackupHandle)
Debug.Print "SQLite3BackupFinish returned " & RetVal
End If
Since myBackUpHandle is a negative number the statement above runs. The call in the above statement:
SQLite3BackupStep() leads to the function that actually bombs.
```
So, I'm checking with you, can myBackupHandle be negative, and is -1 number of pages O.K.?
any issues with VBA itself (LongPtr resolving to LongLong in 64-bit versions of Office?)
http://stackoverflow.com/questions/24095500/making-long-variables-work-in-64-bit-and-32-bit-excel-vba#24098334
Another novice question - can we use the Object Browser (perhaps by some type of reference) to browse around the contents of the DLL?
Did you ever figure out what was going on here?
This line in TestBackup() causes the crash:
RetVal = SQLite3BackupStep(myBackupHandle, -1)
I'm running SQLiteForExcel_64.xlsm on Excel 2016 64-bit
@arildj78 I've not taken a closer look yet. I'd be interested if you find anything.
Sqlite3_64.bas or SQLiteForExcel_64.xlsm module "Sqlite3"
sqlite3_backup_init()As Long → As LongPtr
before
Private Declare PtrSafe Function sqlite3_backup_init Lib "SQLite3" (ByVal hDbDest As LongPtr, ByVal zDestName As LongPtr, ByVal hDbSource As LongPtr, ByVal zSourceName As LongPtr) As Long
After
Private Declare PtrSafe Function sqlite3_backup_init Lib "SQLite3" (ByVal hDbDest As LongPtr, ByVal zDestName As LongPtr, ByVal hDbSource As LongPtr, ByVal zSourceName As LongPtr) As LongPtr
good job!