Tip of the day: StringValueX for database access

If you like to use databases in Xojo, you can use various plugins. There are built-in plugins for MySQL, SQLite, Microsoft SQL Server, PostgreSQL, Oracle and ODBC. And as an alternative you have MBS Xojo SQL Plugin with support for over 15 clients: CubeSQL, Centura SQLBase, DB2, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase.

Unless you use our plugin, which handles unicode well, you may run into the problem that StringValue doesn't return a string with encoding set. So we usually refer to code like the one below in a module to get a StringValueX to return string in a defined encoding:

Module DBUtil

Function StringValueX(extends d as DatabaseColumn) As string // query database field Dim s As String = d.StringValue If s.Encoding = Nil Then // is valid UTF8? If not, we fall back to Windows ANSI If encodings.UTF8.IsValidData(s) Then s = DefineEncoding(s, encodings.UTF8) Else s = DefineEncoding(s, encodings.WindowsANSI) End If End If Return s // use like this: Dim r As RowSet Dim FirstName As String = r.Column("FirstName").StringValueX End Function

Function StringValueX(extends d as DatabaseField) As string // query database field Dim s As String = d.StringValue If s.Encoding = Nil Then // is valid UTF8? If not, we fall back to Windows ANSI If encodings.UTF8.IsValidData(s) Then s = DefineEncoding(s, encodings.UTF8) Else s = DefineEncoding(s, encodings.WindowsANSI) End If End If Return s // use like this: Dim r As RecordSet Dim FirstName As String = r.Field("FirstName").StringValueX End Function

End Module

As you see we provide the method both for older DatabaseField and newer DatabaseColumn class. And if the database plugin does provide text encoding to strings, the function will do nothing. But if the string is data and not valid UTF-8, we define it as Windows ANSI encoding to help with some older databases which may not use UTF-8. For MySQL or Maria DB don't forget to do once after connection a SQL Execute with "SET NAMES UTF8" to let MySQL know that you like to receive UTF-8 via this connection. Our MBS Xojo SQL Plugin does this and similar commands for various database clients to enable unicode.

Keep this ready to Copy & Paste into your Xojo projects if you use the built-in database plugins for database access to make sure all strings have proper encoding.