SQLite3 UTF8 Covert

[Tomasek-TMK]

Hey Connecting to SQLite3 database and I have a problem with the output string.
The database is in UTF8 and erroneously appear on my characters.

The database stores: Evženie, Žáček
A Visual Basic 6 returns: Ev??nie ,??ek

Can you advise please?

proceed in accordance with:
http://www.vbforums.com/showthread.p...meters-Example

[Arnoutdv]

How do you retrieve the data and what control do you use to display the data.
Most standard VB6 controls do not support Unicode .

[Tomasek-TMK]

How do you retrieve the data and what control do you use to display the data.
Most standard VB6 controls do not support Unicode .

I use a TextBox. Classic TextBox can display the character "ěščřžý" That's not a problem, but I do not know how to convert text from SQLite.

[Arnoutdv]

Maybe this can work

UTF8 To String on vbArchive

Private Declare Function MultiByteToWideChar Lib "kernel32.dll" ( _
                         ByVal CodePage As Long, _
                         ByVal dwFlags As Long, _
                         ByVal lpMultiByteStr As Long, _
                         ByVal cbMultiByte As Long, _
                         ByVal lpWideCharStr As Long, _
                         ByVal cchWideChar As Long) As Long
                         
Private Const CP_UTF8 As Long = 65001&

Private Function ConvertFromUTF8(sSource As String) As String

    Dim Size As Long
    Dim Pointer As Long
    Dim Length As Long
    Dim Buffer As String
    Dim Source() As Byte
    
    Source = sSource
    Size = UBound(Source) - LBound(Source) + 1
    Pointer = VarPtr(Source(LBound(Source)))
    Length = MultiByteToWideChar(CP_UTF8, 0, Pointer, Size, 0, 0)
    Buffer = Space$(Length)
    MultiByteToWideChar CP_UTF8, 0, Pointer, Size, StrPtr(Buffer), Length
    ConvertFromUTF8 = Buffer
    
End Function

[Tomasek-TMK]

Maybe this can work

Private Declare Function MultiByteToWideChar Lib "kernel32.dll" ( _
                         ByVal CodePage As Long, _
                         ByVal dwFlags As Long, _
                         ByVal lpMultiByteStr As Long, _
                         ByVal cbMultiByte As Long, _
                         ByVal lpWideCharStr As Long, _
                         ByVal cchWideChar As Long) As Long
                         
Private Const CP_UTF8 As Long = 65001&

Private Function ConvertFromUTF8(sSource As String) As String

    Dim Size As Long
    Dim Pointer As Long
    Dim Length As Long
    Dim Buffer As String
    Dim Source() As Byte
    
    Source = sSource
    Size = UBound(Source) - LBound(Source) + 1
    Pointer = VarPtr(Source(LBound(Source)))
    Length = MultiByteToWideChar(CP_UTF8, 0, Pointer, Size, 0, 0)
    Buffer = Space$(Length)
    MultiByteToWideChar CP_UTF8, 0, Pointer, Size, StrPtr(Buffer), Length
    ConvertFromUTF8 = Buffer
    
End Function

It did not help: (

[Tomasek-TMK]

The project is here

vb.zip

[Arnoutdv]

I see the same problem, maybe my method is not the correct way either to retrieve UTF8 from SQLite

The MSHFlexGrid is capable of showing Unicode.

Private Sub Command1_Click()
  Dim oConn As cConnection
  Dim oRS As cRecordset
  Dim lRow As Long, lCol As Long
  
  Set oConn = New cConnection
  oConn.OpenDB "C:\Users\ArnoutV\Downloads\vb\data.sdb"
  Set oRS = oConn.OpenRecordset("select * from customer")
  With MSHFlexGrid1
    .Rows = oRS.RecordCount + 1
    .Cols = oRS.Fields.Count + 1
    For lRow = 0 To oRS.RecordCount - 1
      For lCol = 0 To oRS.Fields.Count - 1
        .TextMatrix(lRow + 1, lCol + 1) = oRS.ValueMatrix(lRow, lCol)
      Next lCol
    Next lRow
  End With
  
End Sub

Maybe Olav Schmidt (the author of vbRichClient5) can help you.

[dilettante]

If we are indeed talking about vbRichClient5 instead of some other SQLite3 library...

I thought we had run into something like this in a different thread here a few months back.

It seemed like at least one version of his library had a bug where "writes" converted String data to UTF-8 but "reads" failed to do so, or vice versa.

I don't know whether it was ever determined to be a bug or not, or if so whether it was ever fixed in a later version.

[Arnoutdv]

I remember the thread, but I believe it was in another object. Something with a file or drive collection.

[Arnoutdv]

It can also be that the data is not stored as UTF8.
Can show the code for storing the data in the DB?

[jay!]

Somewhere internally VB is converting your UNICODE string to ANSI. These functions would be useful if this was not happening, but it is, so you need another solution.

For me personally I've worked on this problem for a few weeks and in the end just decided to turn the string into an array of decimal values, this way I could convert UNICODE characters "at the last minute" for UI effects, such as printing etc.

[Tomasek-TMK]

It is strange that no one never dealt with this problem

[Arnoutdv]

Do you have a small example of an UTF8 file which you store in the a SQLite DB?

[Tomasek-TMK]

There is already listed: vb.zip (6th post)

http://www.vbforums.com/attachment.p...3&d=1386339565

[Arnoutdv]

That's with an already filled DB.
I would like to have an UTF8 text file, a single column with just a few names.

[Tomasek-TMK]

Table custommer you have a column name, surname in UFT8
(select name, surname from customer)

[Tomasek-TMK]

Table custommer you have a column name, surname in UTF8
(select name, surname from customer)

[Arnoutdv]

It seems I can't get the UTF8 data from YOUR table either.
So I want to do a test myself.
Read UTF8 text file and put it in a table and retrieve the data again.

[Tomasek-TMK]

Hi Give me therefore an example.

[DrUnicode]

My best guess is that your data was stored as ANSI because you get "???" when reading the data back even with known SQLite managers.
You do not need to be concerned with UTF-8 because this is handled by default in SQLite3.
When creating the database just supply it with Vb6 strings which are already UTF-16.

Please show us the code you are using to populate the database initially.

[Arnoutdv]

I already asked for it in post #10, but somehow we still get the retrieve code only