govert/SQLiteForExcel

Implement Int64 support

govert opened this issue · 0 comments

From an email suggestion:

I have noted that you have commented the following functions as untested:
sqlite3_stdcall_column_int64 and
sqlite3_stdcall_bind_int64

I have successfully managed to use your project to read and write full sized signed 64 bit integers using the little known decimal data type:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/decimal-data-type

I have hence created the following functions:

Public Function SQLite3ColumnInt64(ByVal stmtHandle As Long, ByVal ZeroBasedColIndex As Long) As Variant
SQLite3ColumnInt64 = CDec(sqlite3_stdcall_column_int64(stmtHandle, ZeroBasedColIndex)) * 10000
End Function

and

Public Function SQLite3BindInt64(ByVal stmtHandle As Long, ByVal OneBasedParamIndex As Long, ByVal Value As Variant) As Long
SQLite3BindInt64 = sqlite3_stdcall_bind_int64(stmtHandle, OneBasedParamIndex, CCur(Value * 0.0001))
End Function

In the first function, we read in the 64 bits into a currency data type as per your sqlite3_stdcall_column_int64. As I am sure you are aware this is a 2's compliment signed 64 bit integer with an annoying decimal place four numbers in.
To convert this to a decimal we use CDec and then multiply by 1000. You will note that the return type is a variant. This is because you cannot declare a decimal in VBA directly.
I.e. you cannot say Dim x as Decimal. Instead you have to say Dim x as Variant: x= CDec(0)

The second function reverses the process by taking in a variant (be this a Byte, Single, Double, Integer, Long, LongLong, Currency or Decimal), dividing by 10000 and then converting to a currency value before calling your sqlite3_stdcall_bind_int64.

We can easily convert an unsigned decimal to a signed decimal.

Private Function unsignedDec(signedDec As Variant) As Variant
Dim twoTo64 As Variant: twoTo64 = CDec(2 ^ 32) * CDec(2 ^ 32)
If signedDec < 0 Then
unsignedDec = signedDec + twoTo64
Else
unsignedDec = signedDec
End If
End Function

Sub testDec()

Dim x As Variant: x = CDec(-1)
Debug.Print unsignedDec(x)

End Sub