Unicode and Database

[JoernMaas]

Hello,

I have a question about the Unicode support in general. I hope I am just making a mistake here and just miss something obvious...

The following code (at the moment) just draws a small gui and reads a text field from a table in a database. This text in the database (MSSQL) is stored in unicode.

My problem now is, that I always receive ?????. I tried several of the unicode encoding/decoding routines proposed in the forum but they do not seem to work.

I changed the SQL part so it would have to be adjusted to local circumstances to test.

If you could give me a push in the right direction it would be appreciated.

Thanks!

CODE

#include <GUIConstants.au3>

#include <GuiListView.au3>

Opt("GUIOnEventMode", 1)

Opt('MustDeclareVars', 1)

Global $sCOMErrMsg = ""

Global $oCOMError = ObjEvent("AutoIt.Error", "_COMErrHandler"); Install a COM error handler

Global $oDBid = 0

Global $MAIN_GUI, $lvBasic, $lvTranslations, $cbLanguages

_CreateGUI()

While 1

Sleep(1000)

WEnd

Func _CreateGUI()

Local $tempArr, $x, $y, $height

$tempArr = FileGetTime(@ScriptFullPath)

$MAIN_GUI = GUICreate("Translator [" & $tempArr[2] & $tempArr[1] & $tempArr[0] & "]", 540, 490)

GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $MAIN_GUI)

$height = 20

$x = 20

$y = 5

$lvBasic = GUICtrlCreateListView("", $x, $y, 500, $height * 10)

_GUICtrlListView_InsertColumn($lvBasic, 0, "Code", 100)

_GUICtrlListView_InsertColumn($lvBasic, 1, "Text", 380)

$y = $y + (10 * $height) + 5

$lvTranslations = GUICtrlCreateListView("", $x, $y, 500, $height * 4)

_GUICtrlListView_InsertColumn($lvTranslations, 0, "Language", 100)

_GUICtrlListView_InsertColumn($lvTranslations, 1, "Translation", 380)

_GUICtrlListView_SetUnicodeFormat($lvTranslations, True)

_createBaseList()

$y = $y + (4 * $height) + 5

$cbLanguages = GUICtrlCreateCombo("item1", $x, $y, 100, $height)

; Edit item 1 label with time out

GUISetState(@SW_SHOW)

EndFunc ;==>_CreateGUI

Func _exit()

Exit

EndFunc ;==>_exit

Func _createBaseList()

Local $result, $row, $aArray, $element, $text = ""

$oDBid = _SQLOpen("LOGIN", "PWD", "DATABASE_NAME", "ODBC_DRIVER")

If @error Then

MsgBox(64, "Error", $sCOMErrMsg, 5);

_SQLClose($oDBid)

Exit

EndIf

$result = _SQLQuery($oDBid, "select text from column where something")

$row = _SQLGetRow($result)

_SQLClose($oDBid)

ConsoleWrite($row & @CRLF)

$aArray = StringSplit($row, "|")

_GUICtrlListView_InsertItem($lvTranslations, $aArray[1], 0)

EndFunc ;==>_createBaseList

; ==============================================================

; SQL Stuff

; ==============================================================

; Open database connection, on empty DSN a ADO will ask for specification

Func _SQLOpen($user, $pwd, $database, $s_DSN = "")

Local $o_ADOcn

; Create ADO connection

$o_ADOcn = ObjCreate("ADODB.Connection")

; Open ADO connection, only prompting for missing params adPromptComplete=2

$o_ADOcn.Properties("Prompt") = 2

$o_ADOcn.Properties("User ID") = $user

$o_ADOcn.Properties("Password") = $pwd

$o_ADOcn.Properties("Initial Catalog") = $database

$o_ADOcn.Open($s_DSN)

If @error Then Return 0

Return $o_ADOcn

EndFunc ;==>_SQLOpen

; Close database connection

Func _SQLClose($o_ADOcn)

$o_ADOcn.Close

EndFunc ;==>_SQLClose

; Send a schema request, optional pass a filter

Func _SQLschema($o_ADOcn, $i_SchemaID, $as_Filter = 0) ; 1="", $s_filter2="", $s_filter3="", $s_filter4="", $s_filter5="" )

Local $o_ADOrs

If Not IsObj($o_ADOcn) Then

SetError(1)

Else

If IsArray($as_Filter) Then

$o_ADOrs = $o_ADOcn.OpenSchema($i_SchemaID, $as_Filter)

Else

$o_ADOrs = $o_ADOcn.OpenSchema($i_SchemaID)

EndIf

EndIf

If @error Then Return 0

Return $o_ADOrs

EndFunc ;==>_SQLschema

; Send a query, optional pass a max number of records to retrieve

Func _SQLQuery($o_ADOcn, $s_Query, $i_MaxRecords = 0)

Local $o_ADOrs

If Not IsObj($o_ADOcn) Then

SetError(1)

Else

$o_ADOrs = ObjCreate("ADODB.Recordset")

$o_ADOrs.CursorType = 0 ; adOpenForwardOnly = 0

$o_ADOrs.LockType = 3 ; adLockOptimistic = 3

$o_ADOrs.MaxRecords = $i_MaxRecords ; maximum records returned by query

$o_ADOrs.Open($s_Query, $o_ADOcn)

EndIf

If @error Then Return 0

Return $o_ADOrs

EndFunc ;==>_SQLQuery

; --- retrieve fieldnames from the given qryid

Func _SQLGetFields($o_ADOrs, $s_Seperator = "|")

Local $i, $s_Fields

$s_Fields = "" ; Get information about Fields collection

With $o_ADOrs

For $i = 0 To .Fields.Count - 1

$s_Fields &= .Fields($i).Name & $s_Seperator

Next

EndWith

Return $s_Fields

EndFunc ;==>_SQLGetFields

; --- retrieve fieldvalues and move to next row of given qryid

Func _SQLGetRow($o_ADOrs, $s_Seperator = "|")

Local $s_RowValues

$s_RowValues = ""

With $o_ADOrs

If Not .EOF Then

For $i = 0 To .Fields.Count - 1

$s_RowValues &= .Fields($i).Value & $s_Seperator

Next

.MoveNext

Else

SetError(1)

EndIf

EndWith

Return $s_RowValues

EndFunc ;==>_SQLGetRow