Hello, I have a problem. I have an application connected to an access .MDB database. There are years of uploaded data. Now, when I go to read a string field there are values, such as the EURO symbol €. When I read it, the right symbol comes out, if I go to insert it, it displays this €. Because ?
e mi trasforma anche la à in à … come si trattano i caratteri speciali ?
Per copiare i dati nel database utilizzo:
rows2.Column(“Descrizione”).StringValue=textMessaggio.text
Sbaglio io ?
Set the encoding to the strings (both in Read and Write) to UTF8.
and where is it, how is it done?
I do not know why, but I was sure you will ask just that.
In the mean time, I checked here with a code example that uses a SQLite db (it creates the file, add a TABLE and save a Record).
the Euro character (€) is saved and read correctly without setting an encoding.
For the record, here’s the Xojo Code I (re-)used:
Var db As New SQLiteDatabase
Var DB_FI As New FolderItem("FG_DB.sqlite")
// Set the FolderItem to the db Reference
db.DatabaseFile = DB_FI
Try
db.CreateDatabase
Catch Error As DatabaseException
MessageBox "Unable to create the Data Base file."
Return
End Try
If db.Connect Then
// Add a TABLE And Rows
Var sql_Cmd As String
sql_Cmd = "CREATE TABLE IF NOT EXISTS Employee(" +_
"Name_First TEXT, " +_
"Name_Last TEXT, " +_
"Job_Title TEXT, " +_
"Job_Salary Text, " +_
"Employe_ID TEXT, " +_
"Employe_Photo TEXT, " +_
"Employe_Entry_Date DATE, " +_
"ID INTEGER NOT NULL, PRIMARY KEY(ID));"
Try
db.BeginTransaction
// Add the TABLE
db.ExecuteSQL(sql_Cmd)
db.CommitTransaction
Catch error As DatabaseException
MsgBox("Database error: " + db.ErrorMessage)
db.RollbackTransaction
End Try
// Add a Row
Var row_DBR As New DatabaseRow
// Fill the row
row_DBR.Column("Name_First") = "Federico"
row_DBR.Column("Name_Last") = "Giannetti"
row_DBR.Column("Job_Title") = "DB Developer"
row_DBR.Column("Job_Salary") = "20,000 €"
row_DBR.Column("Employe_Entry_Date") = "2016-08-16" // Added for FG
Try
db.AddRow("Employee", row_DBR)
Catch error As DatabaseException
MessageBox("(AddRow) DB Error: " + error.Message)
End Try
Else
MsgBox("Unable to open the Data Base File. Error: " + db.ErrorMessage)
End If
You can put this code in a PushButton and run: it will create a file named “FG_DB.sqlite”.
For reading, it is a different matter (I used a custom application).
This will not sole your problem, but you know it is posssible to Write and Read all characters (EURO as asked €) to a data base using Xojo.
Now, you have to search in your Xojo code where and how you read and write to the MS-Access db. There lies the trouble. More specifically, probably when you use the ODBC driver (you use an ODBC driver to talk to MS-Access, right ?)
The provided code:
rows2.Column(“Descrizione”).StringValue=textMessaggio.text
Is probably where you have to set the encoding (if it is not set in the Driver / MS-Access db.
The entry to read in the documentation is: TextEncoding
I do not have MS-Access to make test (i never used that sofrtware), sorry.
thanks for directing me … I wrote the following code:
rows2.Column (“Description”). StringValue = textMessage.text.ConvertEncoding (Encodings.WindowsANSI)
it seems to work well … now he reads me and inserts me correctly both the € and the à.
Fine !
Thank you for letting me know.
A last word.
Maybe you may check using MS-Access what you get back: if WindowsANSI is the correct value to use.
This is for the eventual case someone read the data base file using MS-Access.
Access DBs don’t use UTF-8 as default as Federico found. Xojo uses UTF-8 by default. So he needs to do such I/O conversion, as he did, so people using Access interfaces won’t see “garbage” and Xojo could handle it too.
You should also use DefineEncoding when you read it back into Xojo. eg.,
TextMessage.Text = rows2.Column("Description").StringValue.DefineEncoding(Encodings.WindowsANSI)