Iczer

UTF-8 in Controls

9 posts in this topic

#1 ·  Posted

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

#4 ·  Posted

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?

Share this post


Link to post
Share on other sites

#5 ·  Posted

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.

1 person likes this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#6 ·  Posted

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 :

#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...

Share this post


Link to post
Share on other sites

#7 ·  Posted

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#8 ·  Posted

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted

 

"sqlite user functions" - Create Or Redefine SQL Functions

UDF used to create functions :

#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

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now