[RESOLVED] RC6 SQLite - how to store/read a LONG-Array?

[Mith]

VB6Sp6
RC6 SQLite 6.0.0.8

I use the .SetBlob-function to store/read my BYTE-arrays.

Is it somehow possible to use a blob field to store/read a LONG-arrays?

Maybe the .SetBlobPtr can do this but i cant find any example or help for this function...

[Zvoni]

Should be possible, since a Long-Array is basically just a consecutive memory-block like a byte-array,
which you can even map to one another

Quick Test in Excel-VBA (Don't have VB6), ignoring Endianess

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Sub main()
Dim LongArray() As Long
Dim ByteArray(1 To 16) As Byte
    ReDim LongArray(1 To 4)
    LongArray(1) = 100000
    LongArray(2) = 200000
    LongArray(3) = 300000
    LongArray(4) = 400000
    
    CopyMemory ByVal VarPtr(ByteArray(1)), ByVal VarPtr(LongArray(1)), 16  'A Long is 4 Bytes * 4 Elements
    'Insert here writing ByteArray to SQLite


    'Insert here reading ByteArray from SQLite
    Erase LongArray
    ReDim LongArray(1 To 4)    'resp. calculate Upper Bound needed --> Length/Size of ByteArray Div 4 + Length/Size of ByteArray Mod 4 --> Add 1 if Result>0
    CopyMemory ByVal VarPtr(LongArray(1)), ByVal VarPtr(ByteArray(1)), 16  'Restores LongArray to initial Values

End Sub

[Schmidt]

Maybe the .SetBlobPtr can do this ...

It could (when you pass it the Pointer to the first member - and the right ByteLen)...

But generally, when you deal with Blobs, you deal with ByteArrays.
So, what you'll get back (in a Recordset) when you read it out later, will be a ByteArray.

The question boils down to:
"How to serialize/deserialize a VB-LongArr to/from a VB-ByteArray?"

In case these LongArrays only contain a handful of Fields, I'd store them joined in a String.

Here's a OneLiner for the IDE-ImmediateWindow, which shows an easy way to do this:
?New_c.ArrayList(vbLong, Array(1,2,3)).Join(",")

In your code you'd just replace the blue marked part with your Long-Array-Variable.

HTH

Olaf

[Zvoni]

It could (when you pass it the Pointer to the first member - and the right ByteLen)...

*snipp*

You mean like (Aircode):

AddressOfFirstMember=VarPtr(MyLongArray(LBound(MyLongArray)))

and

ByteLen=(UBound(MyLongArray)-LBound(MyLongArray)+1)*LenB(MyLongArray(LBound(MyLongArray)))

[Mith]

i have successfully stored the LONG-array in the blob-field:

Dim LArray(3) As Long
LArray(0) = 1
LArray(1) = 200
LArray(2) = 3000
LArray(3) = 40000
cmdDB.SetBlobPtr 1, VarPtr(LArray(0)), (UBound(LArray) + 1) * LenB(LArray(0))

and i have successfully read it back as a BYTE-array:

Dim bArr() As Byte
bArr = RS("arraydata")

and the byte-array contains the correct values from the LONG-array!

now i try to copy the byte-array-mem-block into the LONG-Array:

Dim LArray(3) As Long
CopyMemory VarPtr(LArray(0)), ByVal VarPtr(bArr(0)), (UBound(bArr) + 1) * LenB(bArr(0))

but the values of the LArray-items are all ZERO...any ideas?

[Schmidt]

i have successfully stored the LONG-array in the blob-field:

Dim LArray(3) As Long
LArray(0) = 1
LArray(1) = 200
LArray(2) = 3000
LArray(3) = 40000
cmdDB.SetBlobPtr 1, VarPtr(LArray(0)), (UBound(LArray) + 1) * LenB(LArray(0))

and i have successfully read it back as a BYTE-array:

Dim bArr() As Byte
bArr = RS("arraydata")

and the byte-array contains the correct values from the LONG-array!

now i try to copy the byte-array-mem-block into the LONG-Array:

Dim LArray(3) As Long
CopyMemory VarPtr(LArray(0)), ByVal VarPtr(bArr(0)), (UBound(bArr) + 1) * LenB(bArr(0))

but the values of the LArray-items are all ZERO...any ideas?

Your CopyMemory-call needs to be adjusted to either:
Redim LArray(0 To UBound(bArr) \ 4 ) As Long
CopyMemory ByVal VarPtr(LArray(0)), ByVal VarPtr(bArr(0)), UBound(bArr) + 1

or...:
CopyMemory LArray(0), bArr(0), UBound(bArr) + 1

or (not needing an API-Declare):
New_c.MemCopy VarPtr(LArray(0)), VarPtr(bArr(0)), UBound(bArr) + 1

All 3 methods should work (as long as your Arrays have an Lbound of Zero).

Also note, what I've marked magenta in the above examples (meaning the proper resizing of the LongArray, according to the current length of the ByteBlob)...

HTH

Olaf

[Zvoni]

CopyMemory ByVal VarPtr(LArray(0)), ByVal VarPtr(bArr(0)), (UBound(bArr) + 1) * LenB(bArr(0))

EDIT: Err....Olaf.
Please look at your Size-Field in CopyMemory..... that doesn't look correct

EDIT2: Forget what i said. It's correct. You're using the "Length" of the Byte-Array

[Mith]

using ByVal with the first parameter fixed the problem:

CopyMemory ByVal VarPtr(newFile.HashAudioArray2(0)), ByVal VarPtr(bArr(0)), (UBound(bArr) + 1) * LenB(bArr(0))

now the LONG-Array containing the stored values!

thx guys!

[Zvoni]

*snipp*
Redim LArray(0 To UBound(bArr) \ 4 ) As Long
*snipp*
Also note, what I've marked magenta in the above examples (meaning the proper resizing of the LongArray, according to the current length of the ByteBlob)...

HTH

Olaf

Olaf, that obviously only works if both Arrays have LBound=0
And no, i'm not going to start a Jihad, if an Array always starts at zero or somewhere else....

[Schmidt]

Olaf, that obviously only works if both Arrays have LBound=0...

Yep, that's the reason I made the comment in #6:

"All 3 methods should work (as long as your Arrays have an Lbound of Zero)."

Olaf

[softv]

It could (when you pass it the Pointer to the first member - and the right ByteLen)...

But generally, when you deal with Blobs, you deal with ByteArrays.
So, what you'll get back (in a Recordset) when you read it out later, will be a ByteArray.

The question boils down to:
"How to serialize/deserialize a VB-LongArr to/from a VB-ByteArray?"

In case these LongArrays only contain a handful of Fields, I'd store them joined in a String.

Here's a OneLiner for the IDE-ImmediateWindow, which shows an easy way to do this:
?New_c.ArrayList(vbLong, Array(1,2,3)).Join(",")

In your code you'd just replace the blue marked part with your Long-Array-Variable.

HTH

Olaf

Thanks a TON again, Olaf.

But, what if I wish to store the byte array as string itself (in a TEXT field, say 'ts')? I tried but could not succeed.

I thought it might be to do with the database's encoding. So, before creating the table, I executed "pragma encoding='utf-16le'" in the connection. But, the string stored in 'ts' was always different from what was set to it.

So, how to store a UTF-16 string in TEXT field correctly, if at all it can be done? Kindly let me know.

If it cannot be done and BLOB only should be used, then what exactly is the purpose of setting the encoding using pragma to utf-8 or utf-16? Kindly educate me.

Ever in Gratitude.

Kind Regards.

[Schmidt]

So, how to store a UTF-16 string in TEXT field correctly, if at all it can be done?

Rs("MyTextField") = MyUTF16_VBString

That's how you store Unicode-Text via the Rs-interface of the SQLite-wrapper.

It does not matter, whether the SQLite-DB was switched to utf16- or utf8- Text-storagemode...

At VB-Level, you will get back the exact same String, you've put into the DB-Text-Field.
(any conversion between utf-8 and uft-16 is completely lossless regarding string-content).

Cannot see, how ByteArrays or Blobs come into the game, when it's only about String-storage.

Olaf

[Mith]

Cannot see, how ByteArrays or Blobs come into the game, when it's only about String-storage.

This topic is about storing LONG-Arrays into a Blob field and its already resolved.

I dont understand why the user "softv" starts a new topic inside this topic...

[softv]

This topic is about storing LONG-Arrays into a Blob field and its already resolved.

I dont understand why the user "softv" starts a new topic inside this topic...

You are right.

Its indeed a mistake on my part. Sorry about that. My sincere apologies.

Kind Regards.