#1 2026-11-10 14:38:23

nikolaj
Member
Registered: 2026-11-09
Posts: 4

ODBC: use SQLDriverConnectW so MariaDB gets Unicode SQL text

TSqlDBOdbcConnection.Connect (mormot.db.sql.odbc.pas) connects with the ANSI functions ConnectA and SqlDriverConnectA, while statements are then prepared with PrepareW.

Some ODBC drivers decide the character handling of the whole connection from the connect call. The MariaDB Connector/ODBC (tested with 3.1.20, 32-bit, Windows, MariaDB 10.3) marks a connection made with an ANSI connect function as an "ANSI connection". It then converts the UTF-16 text of SQLPrepareW to the Windows ANSI code page (cp1252), not to the connection's character set.

Result with CharSet=utf8mb4 in the connection string (server side character_set_client = utf8mb4):

INSERT INTO t (v) VALUES ('Ærø åøæ ÄÖÜ')
-> [22007] [ma-3.1.20][10.3.14-MariaDB]Incorrect string value: '\xC6r\xF8 \xE5\xF8...' for column `t`.`v` at row 1 (1366)
The bytes are cp1252, labelled as utf8mb4. With CharSet=latin1 the statement works, but then results lose every character outside Latin-1, so neither setting gives full Unicode.

The same applies on Linux through unixODBC, where the driver manager and driver also see an ANSI connect.

Suggestion: connect with SqlDriverConnectW / ConnectW (the connection string converted to UTF-16), as the statements already use the W functions. If some driver needs the ANSI connect, an option on TSqlDBOdbcConnectionProperties (e.g. UseAnsiConnect) could keep the old behaviour.

SQL Server ({SQL Server}, ODBC Driver 17/18) works either way, so the change should not affect it.

Thanks for mORMot 2. We are moving a Delphi BI server to mORMot's database layer for FreePascal and Linux, and the ODBC and libpq layers otherwise work very well for us.

Offline

#2 2026-11-10 18:03:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 15,629
Website

Re: ODBC: use SQLDriverConnectW so MariaDB gets Unicode SQL text

Can you create an issue on github?

And if you can access some AI service, try to propose a pull request.
For such refactoring problems, modern LLM are very capable.

Thanks and welcome to the forum!
smile

Offline

#3 2026-11-10 19:27:06

nikolaj
Member
Registered: 2026-11-09
Posts: 4

Re: ODBC: use SQLDriverConnectW so MariaDB gets Unicode SQL text

Thanks for the welcome!

I can't open an issue or a pull request on GitHub at the moment, so here is the change as a patch against current master (2.4.17982; TSqlDBOdbcConnection.Connect is unchanged there). It is small: Connect uses ConnectW and SqlDriverConnectW (already imported in mormot.db.raw.odbc) with the strings converted to UTF-16, as the statements already do with PrepareW. fSqlDriverFullString is converted back to UTF-8, so the property gives the same value as before. Nothing else changes.

Tested on Windows, 32-bit, Delphi 11, through SqlDriverConnect (full connection string):

MariaDB Connector/ODBC 3.1.20, MariaDB 10.3, CharSet=utf8mb4: before the patch, an INSERT with 'Ærø åøæ ÄÖÜ Łódź Ωμέγα 日本' fails with "[22018] Invalid character value for cast specification". With the patch, the INSERT, a WHERE with the same literal and reading it back all give the exact text.
SQL Server Express through {SQL Server}: the same before and after.
The unit also compiles with FreePascal 3.2.2 (i386-win32).
Not tested: the DSN path (ConnectW with aServerName), only compiled.

If some old ODBC driver turned out to need the ANSI connect, an option on TSqlDBOdbcConnectionProperties could keep the *A() calls for it, but I did not want to add one without a known case.

--- a/src/db/mormot.db.sql.odbc.pas
+++ b/src/db/mormot.db.sql.odbc.pas
@@ -366,6 +366,7 @@
 var
   Log: ISynLog;
   Len: SqlSmallint;
+  server, user, pwd, conn, full: RawByteString; // UTF-16 for the *W() API
 begin
   SynDBLog.EnterLocal(Log, self, 'Connect');
   Disconnect; // force fDbc=nil
@@ -382,26 +383,35 @@
     Check(self, nil,
       AllocHandle(SQL_HANDLE_DBC, fEnv, fDbc),
       SQL_HANDLE_ENV, fEnv);
+    // use the Unicode API, as for the statements: some drivers (e.g. MariaDB
+    // Connector/ODBC) mark a connection opened with the *A() API as ANSI, and
+    // then convert the SQL text of PrepareW() to the system code page
     with fOdbcProperties do
       if fServerName <> '' then
+      begin
+        server := Utf8DecodeToUnicodeRawByteString(fServerName);
+        user := Utf8DecodeToUnicodeRawByteString(fUserID);
+        pwd := Utf8DecodeToUnicodeRawByteString(fPassWord);
         Check(self, nil,
-          ConnectA(fDbc, pointer(fServerName), length(fServerName),
-          pointer(fUserID), length(fUserID), pointer(fPassWord), length(fPassWord)),
-        SQL_HANDLE_DBC, fDbc)
+          ConnectW(fDbc, pointer(server), length(server) shr 1,
+          pointer(user), length(user) shr 1, pointer(pwd), length(pwd) shr 1),
+        SQL_HANDLE_DBC, fDbc);
+      end
       else if fDatabaseName = '' then
         EOdbcException.RaiseU(
           'Missing ServerName=DataSourceName or DataBaseName=FullConnectString')
       else
       begin
-        FastSetString(fSqlDriverFullString, 1024);
-        fSqlDriverFullString[1] := #0;
+        conn := Utf8DecodeToUnicodeRawByteString(fDatabaseName);
+        SetLength(full, 2048); // 1024 WideChar
+        FillCharFast(pointer(full)^, length(full), 0);
         Len := 0;
         Check(self, nil,
-          SqlDriverConnectA(fDbc, GetDesktopWindow, pointer(fDatabaseName),
-            length(fDatabaseName), pointer(fSqlDriverFullString), length(fSqlDriverFullString),
+          SqlDriverConnectW(fDbc, GetDesktopWindow, pointer(conn),
+            length(conn) shr 1, pointer(full), length(full) shr 1,
             Len, DRIVERCOMPLETION[fOdbcProperties.fSqlDriverConnectPrompt]),
           SQL_HANDLE_DBC, fDbc);
-        SetLength(fSqlDriverFullString, Len);
+        fSqlDriverFullString := RawUnicodeToUtf8(pointer(full), Len);
       end;
     // retrieve information of the just created connection
     GetInfoString(fDbc, SQL_DRIVER_NAME, fDriverName);

Offline

#4 2026-11-12 11:29:52

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 15,629
Website

Re: ODBC: use SQLDriverConnectW so MariaDB gets Unicode SQL text

Using RawByteString here is tricky because length() return the number of bytes, whereas the *W() API requires the number of UTF-16 "characters".
So I rather use SynUnicode to avoid any confusion here.

Please try with
https://github.com/synopse/mORMot2/commit/236ce73bc

Offline

#5 2026-11-12 22:27:00

nikolaj
Member
Registered: 2026-11-09
Posts: 4

Re: ODBC: use SQLDriverConnectW so MariaDB gets Unicode SQL text

Awesome, thanks

Offline

Board footer

Powered by FluxBB