MySQL search with special characters

I have a MySQL database on Unix server and a 64 Bit windows application. When I try to find a database entry which has special characters in the field name (like German umlauts () or any other special character like , , ) the recordSet is alway NIL.
Example.
SELECT location_de FROM locations WHERE location_de LIKE ‘schw%’ ORDER BY location_code

In Xojo the recordSet is emtpy

If I use the same sql select string in Valentina Studio or directly on the unix server, it shows the records from the database which matches the search string.

Charset in database are set to utf8 and collation to utf8_general_ci.

Use Prepared Statements and the correct Encoding and it will work. :slight_smile:

[code]Dim ps As PreparedStatement
Dim rs As RecordSet
Dim s As String =“schw%”

ps = DatabaseObject.Prepare(“SELECT location_de FROM locations WHERE location_de LIKE ? ORDER BY location_code”)
ps.Bind(0, s.ConvertEncoding(Encodings.WindowsANSI), mySQLPreparedStatement.StringType)

rs = ps.SQLSelect[/code]

I wrote the Code in the Forum and cant test it. I am sure it’s not Copy/Paste ready.

Great! It works.
Here is the correct code:
Dim ps As PreparedSQLStatement
Dim rs As RecordSet
Dim s As String =“schw%”
ps = DatabaseObject.Prepare(“SELECT location_de FROM locations WHERE location_de LIKE ? ORDER BY location_code”)
ps.Bind(0, s.ConvertEncoding(Encodings.WindowsANSI), MySQLPreparedStatement.MYSQL_TYPE_STRING)
rs = ps.SQLSelect