Problem with connector to get latin data and UTF8 data in one table

Our software gets data from different databases, one of them ist MySQL. Data is stored in tables, fields can defined as UTF8 or non UTF8. But with MariaDb connectors this don't work anylonger.

Spend a lot of time to find a fix or workaround, but without success yet. Thats why we wrote a sample to compare access via MariaDB ODBC connector (3.1) an MySQL ODBC connector (5.3) to a MariaDB database. It based on a sample from MS. The result is different, it looks like the connectors are not compatible.

That is the output reading data with the connectors:

  MariaDB ODBC 3.1 Driver 
  key_field : 1
  char_latin(char)    : Text 1                                                                      // OK 'Text 1' as char  
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 49 - 0 -                                        // OK 'Text 1' as unsigned char (ASCII)
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 0 | 50 - 0 | 0 -            // OK 'Text 2' as 2 x unsigned char   
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 50 -                                            // OK 'Text 2' as widechar  

  key_field : 2
  char_latin(char)    : Text ä
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 228 - 0 -
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 0 | 63 - 0 | 0 -
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 63 -                                            // Error  -63-      SQLBindCol(hstmt, 3, SQL_C_WCHAR,...

-> latin text is OK, utf8 not

  MariaDB ODBC 3.1 Driver using parameter CHARSET = UTF8 
  key_field : 1
  char_latin(char)    : Text 1
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 49 - 0 -
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 0 | 50 - 0 | 0 -
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 50 -

  key_field : 2
  char_latin(char)    : Text ä
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 195 - 164 - 0 -                                 // Error  -195-164-  SQLBindCol(hstmt, 2, SQL_C_CHAR ,...    
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 133 | 150 - 0 | 0 -
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 34198 -

-> now latin is wrong, but utf8 is OK

  MySQL ODBC 5.3 Unicode Driver
  key_field : 1
  char_latin(char)    : Text 1
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 49 - 0 -
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 0 | 50 - 0 | 0 -
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 50 -

  key_field : 2
  char_latin(char)    : Text ä
  char_latin(byte)    : 84 - 101 - 120 - 116 - 32 - 228 - 0 -                                       // OK  -228- 
  char_utf8(2 byte)   : 0 | 84 - 0 | 101 - 0 | 120 - 0 | 116 - 0 | 32 - 133 | 150 - 0 | 0 -
  char_utf8(widechar) : 84 - 101 - 120 - 116 - 32 - 34198 -                                         // OK  -34198-

-> with MySQL connector all data results are ok (and in other rdbms too)

Steps to reproduce:

1. Create the table

-- MySQL dump 10.13  Distrib 5.6.24, for Win64 (x86_64)
--
-- Host: MariaDBServer    Database: test
-- ------------------------------------------------------
-- Server version	5.5.5-10.1.33-MariaDB

--
-- Table structure for table `table_test_charset`
--

DROP TABLE IF EXISTS `table_test_charset`;
CREATE TABLE `table_test_charset` (
  `key_field` int(11) NOT NULL AUTO_INCREMENT,
  `char_latin` char(20) COLLATE latin1_german1_ci DEFAULT NULL,
  `char_utf8` char(20) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`key_field`),
  UNIQUE KEY `key_UNIQUE` (`key_field`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

--
-- Dumping data for table `table_test_charset`
--

LOCK TABLES `table_test_charset` WRITE;
INSERT INTO `table_test_charset` VALUES (1,'Text 1','Text 2'),(2,'Text ä','Text 薖');
UNLOCK TABLES;

2. Short code to read the data

#include <windows.h>  
#include <sqlext.h>  
#include <iostream>
using namespace std;

// Compiler: Charset = Multibyte
#define   UCP     (SQLTCHAR*)

SQLHDBC   hdbc;
SQLHSTMT  hstmt;

int  test_mariadb(const char* dsn_mariadb, const char* dsn_mysql);
void test_select();

int main(int argc, char *argv[])
{
  // confige the connections strings
  test_mariadb("DRIVER={MariaDB ODBC 3.1 Driver};SERVER=MariaDBServer;PORT=3306;DATABASE=test;UID=root;PWD=password;", "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=MariaDBServer;PORT=3306;DATABASE=test;UID=root;PWD=password;");

  cout << "Press <enter>";
  getchar();
  
  return 0;
}


int test_mariadb(const char* dsn_mariadb, const char* dsn_mysql)
{
  SQLHENV henv;
  SQLRETURN retcode;

  SQLCHAR OutConnStr[255];
  SQLSMALLINT OutConnStrLen;

  HWND desktopHandle = 0;

  // Allocate environment handle  
  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

  // Set the ODBC version environment attribute  
  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3_80, 0);

    // Allocate connection handle  
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

      // Set login timeout to 5 seconds  
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);


        // ****************************************************************
        cout << "MariaDB ODBC 3.1 Driver" << endl;
        // ****************************************************************

        SQLCHAR Connection_mariadb[255];

        sprintf_s((char*)Connection_mariadb, 255, "%s", dsn_mariadb);

        retcode = SQLDriverConnect( hdbc, desktopHandle, 
          UCP(LPCTSTR)Connection_mariadb, SQL_NTS,
          UCP(LPCTSTR)OutConnStr, 255, &OutConnStrLen,
          SQL_DRIVER_NOPROMPT);

        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
        {
          test_select();
          cout << endl;

          SQLDisconnect(hdbc);
        }

        // ****************************************************************
        cout << "MariaDB ODBC 3.1 Driver using parameter CHARSET=UTF8" << endl;
        // ****************************************************************

        SQLCHAR Connection_mariadb_utf8[255];

        sprintf_s((char*)Connection_mariadb_utf8, 255, "%sCHARSET=UTF8;", dsn_mariadb );

        retcode = SQLDriverConnect( hdbc,desktopHandle,
          UCP(LPCTSTR)Connection_mariadb_utf8, SQL_NTS,
          UCP(LPCTSTR)OutConnStr, 255, &OutConnStrLen,
          SQL_DRIVER_NOPROMPT);

        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        {
          test_select();
          cout << endl;

          SQLDisconnect(hdbc);
        }

        // ****************************************************************
        cout << "MySQL ODBC 5.3 Unicode Driver" << endl;
        // ****************************************************************

        SQLCHAR Connection_mysql[255];

        sprintf_s((char*)Connection_mysql, 255, "%s", dsn_mysql);

        retcode = SQLDriverConnect(hdbc, desktopHandle,
          UCP(LPCTSTR)Connection_mysql, SQL_NTS,
          UCP(LPCTSTR)OutConnStr, 255, &OutConnStrLen,
          SQL_DRIVER_NOPROMPT);

        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        {
          test_select();
          cout << endl;

          SQLDisconnect(hdbc);
        }

        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
      }
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

  return 0;
}

void test_select()
{
  SQLRETURN retcode;

  retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

  retcode = SQLExecDirect(hstmt, (SQLTCHAR*)"SELECT key_field, char_latin, char_utf8 FROM test.table_test_charset", SQL_NTS);

  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
  {
    long         key_field;
    SQLCHAR      char_latin[20+1];  // one null termination
    SQLWCHAR     char_utf8[20+2];   // double null termination

    SQLLEN       len_key_field;
    SQLLEN       len_char_latin;
    SQLLEN       len_char_utf8;

    // Bind columns 1, 2, and 3  
    retcode = SQLBindCol(hstmt, 1, SQL_C_SLONG, &key_field ,  0, &len_key_field);
    retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR,  &char_latin, 20, &len_char_latin);  // SQL_C_CHAR  -> char *
    retcode = SQLBindCol(hstmt, 3, SQL_C_WCHAR, &char_utf8 , 20, &len_char_utf8);   // SQL_C_WCHAR -> widechar / UTF16 

    // Fetch and print each row of data.
    int index = 0;
    while(true)
    {
      memset(char_utf8, 0, 42);
      retcode = SQLFetch(hstmt);

      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
      {
        cout << "key_field:          " << key_field   << endl;
        cout << "char_latin(char)    :  " << char_latin  << endl;
        cout << "char_latin(byte)    :  ";
        
        for (index = 0; index < len_char_latin+1; index++)
          cout << (int)char_latin[index] << "-";
        cout << endl;

        cout << "char_utf8(2 byte)   :  ";
        for (index = 0; index < len_char_utf8/2+1; index++)
        {
          unsigned char c2 = (unsigned char)((char_utf8[index])&0xFF);
          unsigned char c1 = (unsigned char)(((char_utf8[index]) >> 8 ) & 0xFF);
          cout << (int)c1 << "|";
          cout << (int)c2 << "-";
        }
        cout << endl;

        cout << "char_utf8(widechar) :  ";

        for (index = 0; index < len_char_utf8 / 2; index++)
          cout << char_utf8[index] << "-";

        cout << endl;
        cout << endl;
      }
      else
        break;
    }
  }

  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
  {
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  }

}

Is it a bug in MariaDB connector? Is there a workaround? Any other help? Tia