dcsimg
Results 1 to 9 of 9

Thread: Cannot save character on mysql database

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Cannot save character on mysql database

    On my server all charset and collation is set to utf8 and utf8_unicode_ci.
    On my database, tables and fields is also set to utf8 and utf8_unicode_ci.
    My connection charset is also utf8.

    When I insert and update a record using NOT stored procedure code from the server like

    rs.addnew
    rs.fields("field1")=text1.text,
    rs.update

    or

    cmd.execute "action query"

    then the inserting or updating of record with special character like is properly saved or update.
    BUT when I use stored procedure THRU VB6 to insert or update a record with special character like , then that special character like is not inserted or updated along with the other characters. But when I execute the stored procedure in the server to insert or update character, then it can be save or updated.

    So what am I missing here ?

    .
    Last edited by codesearcher; May 28th, 2016 at 02:15 AM.

  2. #2

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Cannot save character on mysql database

    Up on this post

  3. #3
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,681

    Re: Cannot save character on mysql database

    The .Text property of the TextBox control is not UTF8.
    You first need to convert the string to UTF8 and then pass it to the database.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Cannot save character on mysql database

    Didnt know that how to do that. How is it done? Im using vb6.

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,681

    Re: Cannot save character on mysql database

    A module I use for dealing with UTF8
    Code:
    '---------------------------------------------------------------------------------------
    ' Module    : modUTF8.bas
    ' DateTime  : 20-9-2011
    ' Source    : ActiveVB http://www.activevb.de/rubriken/faq/faq0155.html
    ' Purpose   :
    '---------------------------------------------------------------------------------------
    Option Explicit
    
    Private Declare Function WideCharToMultiByte Lib "kernel32.dll" ( _
                             ByVal CodePage As Long, _
                             ByVal dwFlags As Long, _
                             ByVal lpWideCharStr As Long, _
                             ByVal cchWideChar As Long, _
                             ByVal lpMultiByteStr As Long, _
                             ByVal cbMultiByte As Long, _
                             ByVal lpDefaultChar As Long, _
                             ByVal lpUsedDefaultChar As Long) As Long
                             
    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
    
    Public Function ConvertToUTF8(ByRef Source As String) As Byte()
      Dim Length As Long
      Dim Pointer As Long
      Dim Size As Long
      Dim Buffer() As Byte
      
      If Len(Source) > 0 Then
        Length = Len(Source)
        Pointer = StrPtr(Source)
        Size = WideCharToMultiByte(CP_UTF8, 0, Pointer, Length, 0, 0, 0, 0)
        If Size > 0 Then
          ReDim Buffer(0 To Size - 1)
          
          WideCharToMultiByte CP_UTF8, 0, Pointer, Length, VarPtr(Buffer(0)), Size, 0, 0
          ConvertToUTF8 = Buffer
        End If
      End If
      
    End Function
    
    Public Function ConvertFromUTF8(ByRef Source() As Byte) As String
      Dim Size As Long
      Dim Pointer As Long
      Dim Length As Long
      Dim Buffer As String
      
    '<< 3/4/2014, AdV, Check verwijderd, moet aanroepende code zelf maar controleren
      'If ContainsUTF8(Source) Then
    '>>
        Size = UBound(Source) - LBound(Source) + 1
        Pointer = VarPtr(Source(LBound(Source)))
        
        If pUTF8header(Source) Then
          ' De BOM header moet genegeerd worden!
          ' http://en.wikipedia.org/wiki/Byte_order_mark
          Size = Size - 3
          Pointer = Pointer + 3
        End If
        
        Length = MultiByteToWideChar(CP_UTF8, 0, Pointer, Size, 0, 0)
        Buffer = Space$(Length)
        MultiByteToWideChar CP_UTF8, 0, Pointer, Size, StrPtr(Buffer), Length
        ConvertFromUTF8 = Buffer
    '<< 3/4/2014, AdV, Check verwijderd, moet aanroepende code zelf maar controleren
      'Else
      '  ConvertFromUTF8 = StrConv(Source, vbUnicode)
      'End If
    '>>
    End Function
    
    Public Function ReadFile(ByRef FileName As String, ByRef IsUTF8 As Boolean) As String
      Dim FileNumber As Integer
      Dim Buffer() As Byte
        
      If FileExists(FileName) Then
        If FileLen(FileName) > 0 Then
          FileNumber = FreeFile
          Open FileName For Binary Access Read As #FileNumber
          ReDim Buffer(0 To LOF(FileNumber) - 1)
          Get #FileNumber, , Buffer
          Close #FileNumber
          If ContainsUTF8(Buffer) Then
            ReadFile = ConvertFromUTF8(Buffer)
            IsUTF8 = True
          Else
            ReadFile = StrConv(Buffer, vbUnicode)
            IsUTF8 = False
          End If
        End If
      End If
    
    End Function
    
    Public Function ReadUTF8File(ByRef FileName As String) As String
      Dim FileNumber As Integer
      Dim Buffer() As Byte
        
      If FileExists(FileName) Then
        If FileLen(FileName) > 0 Then
          FileNumber = FreeFile
          Open FileName For Binary Access Read As #FileNumber
          ReDim Buffer(0 To LOF(FileNumber) - 1)
          Get #FileNumber, , Buffer
          Close #FileNumber
          ReadUTF8File = ConvertFromUTF8(Buffer)
        End If
      End If
    End Function
    
    Public Sub WriteUTF8File(ByRef FileName As String, ByRef Contents As String)
      Dim FileNumber As Integer
      Dim Buffer() As Byte
      
      If FileExists(FileName) Then Kill FileName
      
      If Len(Contents) > 0 Then
        Buffer = ConvertToUTF8(Contents)
        FileNumber = FreeFile
        Open FileName For Binary Access Write As #FileNumber
        Put #FileNumber, , Buffer
        Close #FileNumber
      End If
    End Sub
    
    Public Function WriteUTF8FileEx(ByRef FileName As String, ByRef Contents As String) As String
      Dim FileNumber As Integer
      Dim Buffer() As Byte
      
      On Error GoTo Proc_Error
      If FileExists(FileName) Then Kill FileName
      
      If Len(Contents) > 0 Then
        Buffer = ConvertToUTF8(Contents)
        If Not IsDimmed(Buffer) Then
          WriteUTF8FileEx = "WriteUTF8FileEx: ConvertToUTF8 -> Returned empty byte array (DataLen=" & Len(Contents) & ")"
        Else
          FileNumber = FreeFile
          Open FileName For Binary Access Write As #FileNumber
          Put #FileNumber, , Buffer
          Close #FileNumber
        End If
      End If
      Exit Function
      
    Proc_Error:
      WriteUTF8FileEx = "WriteUTF8FileEx " & CStr(Err.Number) & ": " & Err.Description
    End Function
    
    '---------------------------------------------------------------------------------------
    ' Procedure : ContainsUTF8
    ' DateTime  : 23-10-2012
    ' Author    : ArnoutV
    ' Purpose   : Herkennen van UTF8 karakters in byte array
    '           : Als byte1 >= "&C2" en byte1 <= "D3" en
    '           :     byte2 >= "&80" en byte2 <= "BF" dan UTF8 encoded karakter
    ' Bron      : http://www.utf8-chartable.de/unicode-utf8-table.pl
    ' Revision  : 23-11-2012
    '           : 02-04-2014 - 3 bytes UTF8 : http://www.utf8-chartable.de/unicode-utf8-table.pl?start=2048
    '---------------------------------------------------------------------------------------
    Public Function ContainsUTF8(ByRef Source() As Byte) As Boolean
      Dim i As Long, lUBound As Long
      Dim hexC2 As Byte, hexDB As Byte, hexBF As Byte, hex80 As Byte, hexE0 As Byte
      Dim CurByte As Byte
      
      If pUTF8header(Source) Then
        ContainsUTF8 = True
      Else
        hexC2 = &HC2
        hexDB = &HDB
        hex80 = &H80
        hexBF = &HBF
        hexE0 = &HE0
        
        lUBound = UBound(Source)
        
        For i = 0 To lUBound - 1
          CurByte = Source(i)
          If CurByte >= hexC2 And CurByte <= hexDB Then
            If (Source(i + 1) And hex80) Then
              ContainsUTF8 = True
              Exit For
            End If
          End If
          If i + 2 <= lUBound Then
            If CurByte >= hexE0 Then
              If (Source(i + 1) And hex80) And (Source(i + 2) And hex80) Then
                ContainsUTF8 = True
                Exit For
              End If
            End If
          End If
        Next i
        
      End If
    End Function
    
    '---------------------------------------------------------------------------------------
    ' Procedure : FileIsUTF8
    ' DateTime  : 7-2-2013
    ' Author    : ArnoutV
    ' Purpose   : Herkennen van UTF8 karakters in een bestand
    '---------------------------------------------------------------------------------------
    Public Function FileIsUTF8(ByVal sFileName As String) As Boolean
      Dim byte1 As Byte, byte2 As Byte, byte3 As Byte
      Dim hexC2 As Byte, hexDB As Byte, hexBF As Byte, hex80 As Byte, hexE0 As Byte
      Dim fID As Integer
      
      On Error GoTo errHandler
      
      If FileExists(sFileName) Then
        hexC2 = &HC2
        hexDB = &HDB
        hex80 = &H80
        hexBF = &HBF
        hexE0 = &HE0
        
        fID = FreeFile
        Open sFileName For Binary Access Read Shared As fID Len = 32760
          Do Until EOF(fID)
            Get #fID, , byte1
            
            If (byte2 >= hexC2) And (byte2 <= hexDB) Then
              If (byte1 And hex80) Then
                FileIsUTF8 = True
                Exit Do
              End If
            End If
            If byte1 >= hexE0 Then
              If (byte3 And hex80) And (byte2 And hex80) Then
                FileIsUTF8 = True
                Exit Do
              End If
            End If
            
            byte3 = byte2
            byte2 = byte1
            
          Loop
        Close fID
      End If
      Exit Function
      
    errHandler:
      On Error Resume Next
      If fID <> 0 Then Close fID
    End Function
    
    '---------------------------------------------------------------------------------------
    ' Procedure : pUTF8header
    ' DateTime  : 14-6-2013
    ' Author    : ArnoutV
    ' Purpose   :
    ' Reference : http://en.wikipedia.org/wiki/Byte_order_mark
    '---------------------------------------------------------------------------------------
    Private Function pUTF8header(Source() As Byte) As Boolean
    
      If UBound(Source) >= 2 Then
        If Source(0) = &HEF Then
          If Source(1) = &HBB Then
            If Source(2) = &HBF Then
              pUTF8header = True
              Exit Function
            End If
          End If
        End If
      End If
    
    End Function

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Cannot save character on mysql database

    I tried

    Text2.text= ConvertToUTF8(Text1.text)

    where the value of text1.text is and the result is ? (question mark character).

    So I am passing now the question mark character to the stored procedure and the end result to the table is , is the right?
    Last edited by codesearcher; May 31st, 2016 at 07:01 PM.

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,681

    Re: Cannot save character on mysql database

    The ConvertToUTF8 returns a byte array in UTF8, you can not assign this to a string or TextBox.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Cannot save character on mysql database

    What do I use with SP to store string to database?

  9. #9
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,681

    Re: Cannot save character on mysql database

    I don't know anything about MySql so I can't help you with the storing and retrieving of utf8 texts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width