The issue will be with encodings. If your columns are defined without the ‘n’ or “national” prefix e.g. nVarChar then the encoding of the string is Windows ANSI and you’ll need to define the encoding and convert it to UTF8. This also applies with the ‘n’ prefix you’ll need to define the encoding that was used to insert the data and then convert it to UTF8.
Public Function FromDB(inputText As String) As String
inputText = inputText.DefineEncoding(Encodings.WindowsANSI).ConvertEncoding(Encodings.UTF8)
Var anArray() As String
anArray = inputText.SplitBytes("")
For i As Integer = 0 To anArray.LastIndex Step 2
if anArray(i).Asc = 194 And anArray(i+1).Asc = 165 Then
inputText = inputText.Left(i/2) + "Ą" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 194 And anArray(i+1).Asc = 185 Then
inputText = inputText.Left(i/2) + "ą" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 195 And anArray(i+1).Asc = 134 Then
inputText = inputText.Left(i/2) + "Ć" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 195 And anArray(i+1).Asc = 166 Then
inputText = inputText.Left(i/2) + "ć" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 195 And anArray(i+1).Asc = 138 Then
inputText = inputText.Left(i/2) + "Ę" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 195 And anArray(i+1).Asc = 170 Then
inputText = inputText.Left(i/2) + "ę" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 194 And anArray(i+1).Asc = 163 Then
inputText = inputText.Left(i/2) + "Ł" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 194 And anArray(i+1).Asc = 179 Then
inputText = inputText.Left(i/2) + "ł" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 195 And anArray(i+1).Asc = 145 Then
inputText = inputText.Left(i/2) + "Ń" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 195 And anArray(i+1).Asc = 177 Then
inputText = inputText.Left(i/2) + "ń" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 195 And anArray(i+1).Asc = 147 Then
inputText = inputText.Left(i/2) + "Ó" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 195 And anArray(i+1).Asc = 179 Then
inputText = inputText.Left(i/2) + "ó" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 197 And anArray(i+1).Asc = 146 Then
inputText = inputText.Left(i/2) + "Ś" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 197 And anArray(i+1).Asc = 147 Then
inputText = inputText.Left(i/2) + "ś" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 194 And anArray(i+1).Asc = 143 Then
inputText = inputText.Left(i/2) + "Ź" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 197 And anArray(i+1).Asc = 184 Then
inputText = inputText.Left(i/2) + "ź" + inputText.Right(inputText.Length - (i/2) - 1)
End If
if anArray(i).Asc = 194 And anArray(i+1).Asc = 175 Then
inputText = inputText.Left(i/2) + "Ż" + inputText.Right(inputText.Length - (i/2) - 1)
End
If anArray(i).Asc = 194 And anArray(i+1).Asc = 191 Then
inputText = inputText.Left(i/2) + "ż" + inputText.Right(inputText.Length - (i/2) - 1)
End If
Next
Return inputText
End Function
An app never hangs. It’s just busy. You didn’t say on which OS you are working. On macOS you can get a rough overview on the app with the Activity Viewer. You can always break into the debugger to see where the app got stuck.
You don’t need to fiddle around with single characters except you got mojibake.
My operating system is Windows 10 Home 64-bit.
There is no message in the debugger.
My program is very simple. Everything works fine, only Polish characters cause the application to stop working.
Where it says stack go to the other thread or click on the debugger triangle to continue. I’ve seen it a couple of times that the app drops into the debugger when it shouldn’t.
if there are Polish characters in column 4, the line does not execute correctly
this code with my “FromDB” function works fine
If data <> Nil Then
For Each row As DatabaseRow In data
Var s4 As String = App.FromDB(row.ColumnAt(4).StringValue)
//ListBox1.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
//row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue, row.ColumnAt(4).StringValue)
ListBox1.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue, s4)
Next
data.Close
End If
from the database, the data is read correctly (attachment), but there are some hidden characters and the s4 variable cannot be assigned to Me.TextFieldOpis.Text.
When I “manually” change the data to the letters “ĄąĆćĘę…” everything works
On the end of that line in the debugger is a magnifying glass. If you click that, what Encoding is shown? In order to assign it to the window you need it to be UTF8.
You need to determine the actual encoding and use DefineEncoding to set it. That may well be enough. If that doesn’t resolve your problem, then after you have used DefineEncoding to set the original encoding, use ConvertEncoding to change it to UTF8.
EDIT: thank you Ian Kennedy, identical answer. I got there by trial and error :)))
I create a table like below:
mDb.Host = host // Or use the instance name, such as ".\SQLEXPRESS"
mDb.UserName = userName
mDb.Password = password
mDb.DatabaseName = databasename
Try
mDb.Connect()
mDb.SQLExecute("SET NAMES utf8;")
Catch err As DatabaseException
mIsConnected = False
//ConnectStatusLabel.Text = "Error connecting to MS SQL Server: " + err.Message
Return
End Try
mIsConnected = True
// Czy jest tabela //
Var sql As String = "SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'rg_kasa';"
Var data As RowSet
try
data = mDB.SelectSQL(sql)
Catch err as DatabaseException
Print("DB Error: " + err.Message)
Return
End Try
If data <> Nil Then
var t as String = ""
For Each row As DatabaseRow In data
t = row.ColumnAt(0).StringValue
Next
Print("<> Nil")
//CreateTableStatusLabel.Text = "<> Nil"
data.Close
if t = "" then
Var sqlCreate As String = "CREATE TABLE rg_kasa ("
sqlCreate = sqlCreate + "id int NOT NULL IDENTITY(1,1) PRIMARY KEY,"
sqlCreate = sqlCreate + "id_users int,"
sqlCreate = sqlCreate + "data char(10),"
sqlCreate = sqlCreate + "wartosc decimal(14,2),"
sqlCreate = sqlCreate + "saldo decimal(14,2),"
sqlCreate = sqlCreate + "opis VARCHAR(200)"
sqlCreate = sqlCreate + ");"
Try
mDB.ExecuteSQL(sqlCreate)
Catch err As DatabaseException
//CreateTableStatusLabel.Text = "DB Error: " + err.Message
Return
End Try
End If
This does not cause the database to store data in UTF8. Only that the column and table names are defined using UTF8 encoding. If you want your data stored in UTF8 you need to define that on the tables, or the database level.
If you are importing data into the database from another source then it will have an encoding, which you need either to respect or convert before storing it. If the database already contains data it would also have to be converted if you want to change the database.
If you already have a working database, that is used by another application or interface, then you should stick to that. In the Xojo app use the DefineEncoding and CovertEncoding to use that data in Xojo. You would also need to do the opposite if storing data back into the database from Xojo.
// Read from the database
s4 = row.ColumnAt(4).StringValue.DefineEncoding(Encodings.WindowsLatin2).ConvertEncoding(Encodings.UTF8)
// Alter the data in what ever way you want.
// Save data back into the database
row.ColumnAt(4).StringValue = s4.ConvertEncoding(Encodings.WindowsLatin2)
That tells the database, “I promise to store data that is UTF8”. The database will take you at your word. It will not convert any data to UTF8 automatically. You can store any encoding in the column and the database doesn’t care. When you pull data from the table, it’s up to you to know the original encoding so you can handle it correctly.
I’m sorry, but it doesn’t do that at all. It tells the database that the SQL command is using UTF8 character set. Not that the data in the columns are stored in UTF8.
To use UTF8 correctly in a database you need to set the “charset” and “collation” to UTF8. Otherwise your data isn’t expected to be in UTF8 and will not be sorted correctly by an index or order by statement.
The best way of dealing with this is to set the database default to UTF8 before adding any data, and obviously ensure that any data stored in the database is actually using UTF8 encoding when it is stored. Otherwise you end up having to specify every char/varchar/text/longtext etc field is using the correct encoding and collation settings.
Attempting to change such things on an existing database is a complicated task that needs thought and planning. Typically reading the existing data, converting the encoding and storing it back again. It is also vital to not have two, or more, applications that are using different encodings and storing data into the same database tables. Unless, obviously, you enjoy looking at every row / column in a database by hand and ensuring that the data is correctly encoded after the fact. (I’ll leave it to you to decide who had to do this. Thankfully it was a relatively small database) .
