UTF-8 in Controls

[Iczer]

Is there a way to display UTF-8 text in ListView and TreeView directly, without re-encoding to UTF-16LE?

[jchd]

No way. Windows has been UTF16-LE native for decades.

[czardas]

I believe you can kind of cheat. For example, use controls that have edit controls which accept UTF-8 (find out if this is possible) or place an edit control next to a button (or similar control) and set the edit to read only. Also you can place an image over a button or label.

Edited May 6, 2017 by czardas

[Iczer]

hmm... speed is issue so, i think, i should stick to UTF-16LE... (its GUI + SQLite with HugeListView from LarsJ)

Another side of this question - if i have UTF-8 SQLite DB filled with UTF-16LE text and then i display this text DB in the GUI/ListView - there  should not be any "wrong encoding" problems - am i right?

[jchd]

You're confusing internal encoding used by an SQLite DB and the encoding used for results of your queries.

When you get results with things like _SQLite_GetTable2d or similar, the strings you get are already UTF16-LE.

10 minutes ago, Iczer said:

if i have UTF-8 SQLite DB filled with UTF-16LE text

No such beast can exist. When your create a DB with default options, it's created with internal UTF8 setting. Every string you insert then from AutoIt using the standard UDF is converted from UTF16 to UTF8 by SQLite internally and transparently. When you query the DB and fetch results, strings are also automagically converted from internal UTF8 to UTF16.

[Iczer]

Thanks, Then internally (for internal sqlite functions) encoding is different from encoding for "external/AutoIt" functions/

I'm filling DB with some string/regexp/hash functions and sometimes i need rehash/remake DB so, i use same functions as "sqlite user functions" in :

expandcollapse popup

#AutoIt3Wrapper_UseX64=Y

#include-once
#include <Crypt.au3>
#include <SQLite.au3>
#include "_SQLite_UserFunctions.au3"
#include "CodeSnippets.au3"

;------------------------------------------------------------------------------------------
Local $sPathTo_DB = @ScriptDir & "\cache\SharpReaderEXDB.sqlite"
Local $sPathTo_SQLiteDLL = @SystemDir & "\sqlite3.dll"
;------------------------------------------------------------------------------------------
_Crypt_Startup()
_SQLite_StartupEX($sPathTo_SQLiteDLL)
$hDB = _SQLite_Open($sPathTo_DB)
;------------------------------------------------------------------------------------------
_SQLite_SetTimeout($hDB, 888888)
;------------------------------------------------------------------------------------------
$dllCb1 = _SQLite_FuncCallbackRegister("_make_hash")
_SQLite_CreateFunction($hDB, "_make_hash", -1, 0, 0, DllCallbackGetPtr($dllCb1), 0, 0)
;------------------------------------------------------------------------------------------



$sQuery = "UPDATE SRDB SET Hash = _make_hash(FeedItemXML,Feed_Title,FeedItem_Title)"

$t = TimerInit()

_SQLite_Exec($hDB, $sQuery)

ConsoleWrite(TimerDiff($t) & @CRLF)

;------------------------------------------------------------------------------------------
DllCallbackFree($dllCb1)
;------------------------------------------------------------------------------------------
_Crypt_Shutdown()
_SQLite_Close()
_SQLite_Shutdown()

Exit

;===========================================================================================
Func _make_hash($pCtx, $iArgs, $pArgs)

    Local $aArgs = _SQLite_FuncArgs($iArgs, $pArgs)

    Local $sArg1 = _SQLite_GetValue($aArgs[0])
    Local $sArg2 = _SQLite_GetValue($aArgs[1])
    Local $sArg3 = _SQLite_GetValue($aArgs[2])

    _SQLite_ResultText($pCtx, GetHashString($sArg1, $sArg2, $sArg3))
EndFunc
;===========================================================================================
Func GetHashString(Const ByRef $sRSSXMLItem, Const ByRef $sFeedTitle, Const ByRef $sItemTitle)

    Local $sLink, $iPos, $idtag, $idGuid, $sPubDate
    ;--------------------------------------------------------------------------------------
    $sHashTag = $sFeedTitle & $sItemTitle
    ;--------------------------------------------------------------------------------------
    $sLink = StringRegExpReplace($sRSSXMLItem, '(?si)(\A.+?<link>)(.+?)(</link>.+\z)', "$2")
    If @extended Then
        $iPos = StringInStr($sLink,"//")
        $sLink = StringTrimLeft($sLink,(($iPos > 0)?($iPos + 1):(0)))
        $sHashTag &= $sLink
    Else
        $sLink = StringRegExpReplace($sRSSXMLItem, '(?si)(\A.+?<link rel.+?href=")(.+?)(".+\z)', "$2")
        If @extended Then
            $iPos = StringInStr($sLink,"//")
            $sLink = StringTrimLeft($sLink,(($iPos > 0)?($iPos + 1):(0)))
            $sHashTag &= $sLink
        Else
            $sLink = ""
        EndIf
    EndIf
    ;--------------------------------------------------------------------------------------
    $idtag = StringRegExpReplace($sRSSXMLItem, '(?si)(\A.+?<id>)(.+?)(</id>.+\z)', "$2")
    If @extended Then
        $sHashTag &= $idtag
    EndIf
    ;--------------------------------------------------------------------------------------
    $idGuid = StringRegExpReplace($sRSSXMLItem, '(?si)(\A.+?<Guid[^>]*>)(.+?)(<.+\z)', "$2")
    If @extended Then
        $iPos = StringInStr($idGuid,"//")
        $idGuid = StringTrimLeft($idGuid,(($iPos > 0)?($iPos + 1):(0)))
        $sHashTag &= $idGuid
    EndIf
    ;--------------------------------------------------------------------------------------
    $sHashTag = StringRegExpReplace($sHashTag,"\x26amp;","&")
    $sHashTag = StringRegExpReplace($sHashTag,"\<\!\[CDATA\[|\]\]\>","")
    $sHashTag = StringRegExpReplace($sHashTag,"(*UCP)\s","")
    ;--------------------------------------------------------------------------------------
    $sHashTag = String(_Crypt_HashData(StringToBinary($sHashTag,4), $CALG_SHA1))
    ;--------------------------------------------------------------------------------------

    Return $sHashTag
EndFunc
;==========================================================================================

"Hash" column is PRIMARY KEY NOT NULL

and after rehashing I'm getting many constraint violations. Auto-convertion from internal UTF8 to UTF16 should not be applied in case User functions?

I think if internal and external encodings different, then culprit is either string functions or hash functions.

if hash, then for filling DB it should be

$sHashTag = String(_Crypt_HashData(StringToBinary($sHashTag,2), $CALG_SHA1))

and for rehashing:

$sHashTag = String(_Crypt_HashData(StringToBinary($sHashTag,4), $CALG_SHA1))

but for string functions i don't know...

[jchd]

18 minutes ago, Iczer said:

"sqlite user functions"

I've no idea what this is and does.

Anyway, your hash should probably be defined as blob and stored/retrieved as binary.

About the constraint violation you obsercve I can't tell anything without the schema and significant code and DB example.

[czardas]

I learned a few new things here today. Although I was under the impression that UTF-16LE covered a much smaller set of characters, having to rely on surrogate pairs outside the BMP. I was a little confused.

[Iczer]

 

"sqlite user functions" - Create Or Redefine SQL Functions

UDF used to create functions :

expandcollapse popup

#include-once
#include <SQLite.au3>

Const $SQLITE_UTF8 = 0
Const $SQLITE_DETERMINISTIC = 0x800

#cs Example
; the following functions are usefull to 'bind' user functions to a database context to be used in your queries

#include <SQLite.au3>
#include <Array.au3>

_SQLite_Startup("sqlite3.dll", True)
_SQLite_Open()

; the user function
Func _simple_func($pCtx, $iArgs, $pArgs)
    ; parameters :
    ;    $pCtx - data base context, use _SQLite_ContextDbHandle($pCtx) to retreive data base handle
    ;    $iArgs - number of arguments passed to the function
    ;    $pArgs - pointer to the arguments, use _SQLite_FuncArgs($iArgs, $pArgs) to get an array of pointers to sqlite_value
    ;             use _SQLite_GetValue($array[n]) to get the n-th argument (0-based)
    ; return value :
    ;    to set the return value of the function, use _SQLite_ResultText($pCtx, $returnValue)
    ;
    ; note :
    ;    as you see, only _SQLite_ResultText is implemented (no ResultInt, ResultBlob...) because it's like this that AutoIt
    ;    handles sqlite databases: all data is UTF8 text

    ; this function will add all it's parameters

    Local $Result = 0
    Local $aArgs = _SQLite_FuncArgs($iArgs, $pArgs), $Arg

    For $i = 0 To UBound($aArgs) - 1
        $Arg = _SQLite_GetValue($aArgs[$i])
        $Result += Int($Arg)
    Next

    _SQLite_ResultText($pCtx, $Result)
EndFunc

; to add function to a database handle, you must first convert it to a DllCallback, and pass a pointer (DllCallbackGetPtr) to _SQLite_CreateFunction
$dllCb = _SQLite_FuncCallbackRegister("_simple_func")
_SQLite_CreateFunction(-1, "simple_func", -1, 0, 0, DllCallbackGetPtr($dllCb), 0, 0)

; now we can use the new function in our queries!
Dim $aRow
_SQLite_QuerySingleRow(-1, 'SELECT simple_func(1, 2, 3)', $aRow)
ConsoleWrite("simple_func(1, 2, 3) = " & $aRow[0] & @CRLF)

; do not forget to DllCallbackFree the function when you don't need it
DllCallbackFree($dllCb)
_SQLite_Close()
_SQLite_Shutdown()

#ce

; parameters :
;    $hDb       - data base handle (-1 for the last used data base)
;    $sFuncName - name of the SQL function to be created or redefined
;    $iArgs     - number of arguments that the SQL function or aggregate takes (-1 for variable arguments)
;    $iTextRep  - text encoding that sqlite will prefer for the parameters (use allways 0 ($SQLITE_UTF8) because _SQLite_GetValue expects that)
;                 set $SQLITE_DETERMINISTIC (+ $SQLITE_UTF8) to signal that the function will always return the same result given the same inputs within a single SQL statement
;    $pUserData - user data that will be linked to the function, and that can be retreived in the function using _SQLite_UserData
;    $pFunc, $pStep, $pFinal
;               - pointers to C-language functions that implement the SQL function or aggregate
;                 a scalar SQL function requires an implementation of the $pFunc callback only; NULL pointers (0) must be passed as the $pStep and $pFinal parameters
;                 an aggregate SQL function requires an implementation of $pStep and $pFinal and NULL pointer must be passed for $pFunc
;                 to delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks
;
Func _SQLite_CreateFunction($hDB, $sFuncName, $iArgs, $iTextRep, $pUserData, $pFunc, $pStep, $pFinal)
    If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    ; ---
    Local $iRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_create_function16", _
        "ptr", $hDB, _
        "wstr", $sFuncName, _
        "int", $iArgs, _
        "int", $iTextRep, _
        "ptr", $pUserData, _
        "ptr", $pFunc, _
        "ptr", $pStep, _
        "ptr", $pFinal)
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
    If $iRval[0] <> $SQLITE_OK Then
        __SQLite_ReportError($hDB, "_SQLite_CreateFunction")
        Return SetError(-1, 0, $iRval[0])
    EndIf
    Return $iRval[0]
EndFunc

Func _SQLite_FuncCallbackRegister($sFunc)
    Return DllCallbackRegister($sFunc, "none:cdecl", "ptr;int;ptr")
EndFunc

Func _SQLite_ContextDbHandle($pCtx)
    If $__g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
    ; ---
    Local $iRval = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_context_db_handle", "ptr", $pCtx)
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
    ; ---
    Return $iRval[0]
EndFunc

Func _SQLite_UserData($pCtx)
    If $__g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
    ; ---
    Local $iRval = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_user_data", "ptr", $pCtx)
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
    ; ---
    Return $iRval[0]
EndFunc

Func _SQLite_GetValue($pValue, $bBinary = False)
    If $__g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
    ; ---
    If $pValue = 0 Then
        Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
    EndIf
    ; ---
    Local $iType = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_value_type", "ptr", $pValue)
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
    ; ---
    If $iType[0] = $SQLITE_TYPE_NULL Then
        Return ""
    Else
        If Not $bBinary And $iType[0] <> $SQLITE_TYPE_BLOB Then
            Local $aRet = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_value_text16", "ptr", $pValue)
            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; Dllcall error

            Return $aRet[0]
        Else
            Local $aResult = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_value_blob", "ptr", $pValue)
            If @error Then Return SetError(6, @error, $SQLITE_MISUSE) ; Dllcall error

            Local $iColBytes = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_value_bytes", "ptr", $pValue)
            If @error Then Return SetError(5, @error, $SQLITE_MISUSE) ; Dllcall error

            Local $vResultStruct = DllStructCreate("byte[" & $iColBytes[0] & "]", $aResult[0])
            Return Binary(DllStructGetData($vResultStruct, 1))
        EndIf
    EndIf
EndFunc

Func _SQLite_FuncArgs($iArgs, $pArgs)
    If $iArgs = 0 Or $pArgs = 0 Then Return 0
    ; ---
    Local $aRet[$iArgs]
    $pArgs = DllStructCreate("ptr[" & $iArgs & "]", $pArgs)
    For $i = 0 To $iArgs - 1
        $aRet[$i] = DllStructGetData($pArgs, 1, $i + 1)
    Next
    Return $aRet
EndFunc


Func _SQLite_ResultText($pCtx, $sText)
    If $__g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
    ; ---
    Local $iRval = DllCall($__g_hDll_SQLite, "none:cdecl", "sqlite3_result_text16", "ptr", $pCtx, "wstr", $sText, "int", -1, "ptr", -1) ; SQLITE_TRANSIENT
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
    ; ---
    Return 1
EndFunc