Saving Chinese Characters in MSSQL

Hi All,

I am trying to connect my webapp to MSSQL database. Everything works fine except I cannot save Chinese characters to the database. I tried several combinations of ODBC/MSSQL Database in Xojo, and different collations when creating the database in MSSQL, still doesn’t work. The fields are in nchar already. Any ideas?

Tony

Can you post an example SQL INSERT or UPDATE string which is not working for you?

I use Xojo native insert statement:

InsertRecord(TableName as String, rec as DatabaseRecord) as Boolean

self.InsertRecord(TableName, rec)
if self.error then
msgbox(Self.Errormessage)
Return(False)
else
Return(True)
end

btw: the problem I am facing is the Chinese Characters become “strange characters” when saved to the db

I’ve never used that feature of Xojo. What I would do:

Try to insert a new record in an SQL editor with INSERT INTO …. As soon as that works you know your database settings are good.

Then use that same string test-wise in Xojo with Db.SQLExecute(“INSERT INTO…”). If that works too, you know you’re fine.

Then at last use InsertRecord. If this then does still not work, it could be a Xojo bug.

When you read the data back from the database, make sure you Define the Encoding before you try to display it. The data is most likely intact, it just doesn’t display correctly because once it goes into the database, it loses its encoding. You have to inform Xojo what the encoding is when you read it back.

@Eli, INSERT INTO works!!! While Xojo native INSERT does work, is that I missed something?

@Tim, it is at the time when I try to save the Chinese characters using INSERT, it “translates” to something else.

Before I call the INSERT statement, I use the following code to assign record fields:

rec as DatabaseRecord

rec.Column(ColumnName) = WebTextField(obj).Text

Is that I need to do something right here?

How does the SQL INSERT string look like? Did you use N’stringvalue’?

@Eli, yes, I use NChar in MSSQL database.

loc_sql = “INSERT INTO STREET (GUID, EnglishName, ChineseName) VALUES (100, '” + trim(db_Street_EnglishName_txt.text) + “’, '” + trim(db_Street_ChineseName_txt.text) + “’)”
session.myDB.SQLExecute(loc_sql)

That was not my question. MSSQL supports for nchar fields and non-latin encodings N’…’ strings, which look like that:

N'1st Avenue'

instead of

'1st Avenue'

Since you didn’t use that for the INSERT INTO I don’t know what the difference could be between INSERT INTO and InsertRecord. So this is for Xojo to answer I guess.

Thanks Eli, now the situation is:

  1. using SQL statement “INSERT INTO” works perfectly, Chinese characters can be stored in the database
  2. Xojo native commands “translates” my characters to something else, this I guess may due to field assignments or InsertRecord

Thanks again.

Maybe you can convert the encoding:

trim(db_Street_EnglishName_txt.text.ConvertEncoding(Encodings.MacChineseTrad)

See all encodings: Encodings
Maybe they must be UTF16 instead of UTF8 (which a TextField returns).

I tried already, neither UTF8, UTF16 nor MacChineseTrad work

Are you using Prepared Statements or SQL Injection?

@Wayne, not at the moment, as for testing, I simply do it this way:

I define my database fields in webdialogs as db_, for instance, db_Street_ChineseName_txt

When use pressed save, I pass the webdialog to a method to help me loop through all the fields:

AssignScreentoRecord (byref rec as DatabaseRecord, w as webdialog)
Dim c, pos1, pos2, pos3 as Integer
Dim FieldLabel as String
Dim TableName as String
Dim ColumnName as String
Dim FieldType as String
Dim obj as WebObject

for c = 0 to w.ControlCount - 1
FieldLabel = w.ControlAtIndex©.name
if mid(FieldLabel, 1, 3) = “db_” then //is a databse field
pos1 = InStr(1, FieldLabel, “")
pos2 = InStr(pos1+1, FieldLabel, "
”)
pos3 = InStr(pos2+1, FieldLabel, “_”)
TableName = mid(FieldLabel, pos1+1, pos2-pos1-1)
ColumnName = mid(FieldLabel, pos2+1, pos3-pos2-1)
FieldType = mid(FieldLabel, pos3+1, 5)

  obj = w.ControlAtIndex(c)
  select case FieldType
  case "txt"
    rec.Column(ColumnName) =trim(DefineEncoding(WebTextField(obj).Text, Encodings.MacChineseTrad))
    'rec.Column(ColumnName) =WebTextField(obj).Text
  end select
end if

next

and then I call another method:

InsertRecord (TableName as string, rec as DatabaseRecord)
TableName = uppercase(TableName)

self.InsertRecord(TableName, rec)
if self.error then
msgbox(Self.Errormessage)
Return(False)
else
Return(True)
end

Well my experience with MSSQL & Xojo is mostly Prepared Statements (which I believe is used by databaserecord) and injection.

I have found that in many cases I can’t rely on prepared statements. For example sending more than 255 characters to a VARCHAR column will be truncated. Inserting a record with no data into a Null column will result in random data in the database. However using injection works but removes all the safeguards that prepared statements give.

So as a result of my experience can I suggest you use SQL injection as a test and if that works then the workaround I use is to use a prepared statement into an in memory sqlite database for protection then inject the result into the MSSQL database. What a hack!

Thanks Wayne for your information. So do you think I should use a 3rd party like MBS to handle all around?

Maybe, I don’t use MBS - simply because I believe this basic stuff shouldn’t require a 3rd party plugin (I do own a full set of MBS though). You can try MBS for free, so why not give it a go? If I wasn’t so far down the track in my current project I’d have a look.

Finally give up Xojo InsertRecord, using SQL INSERT INTO instead. Thanks all for your input.