Bug #110349 Querying mysql through otl's odbc means that the results obtained in the Chinese
Submitted: 13 Mar 2023 9:14 Modified: 27 Mar 2023 6:32
Reporter: jerry luo Email Updates:
Status: Patch queued Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[13 Mar 2023 9:14] jerry luo
Description:
Through the otl interface, call the sql query to obtain the field value. When the field containing the Chinese value is extracted, the field value is truncated and the complete field value is not obtained.

db:
CREATE TABLE `test_tmp1` (
  `id` int(11) NOT NULL,
  `msg1` varchar(10) DEFAULT NULL,
  `msg2` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into test_tmp1 values(1,'奥迪阿瑟费都是','阿首次发声冯绍峰撒发');
[(none)]> select * from test.test_tmp1;
+----+-----------------------+--------------------------------+
| id | msg1                  | msg2                           |
+----+-----------------------+--------------------------------+
|  1 | 奥迪阿瑟费都是        | 阿首次发声冯绍峰撒发           |
+----+-----------------------+--------------------------------+
1 row in set (0.00 sec)

eg:
otl_stream os(50, "select msg1 from test.test_tmp1", db);
char msg1[31];
while (!os.eof()) {
    os >> msg1;
    std::cout << "msg1=";
    if (os.is_null())
        std::cout << "NULL";
    else
        std::cout << msg1;
    std::cout << std::endl;
}

result:
./tsql
========== COLUMN #1 ===========
name=msg1
dbtype=12
otl_var_dbtype=1
dbsize=10
scale=0
prec=10
nullok=1
msg1=奥迪阿;

How to repeat:
/**
g++ -g -I/data/include/3rd/otl -DLINUX -DBIT64 -L/data/mysql_lib -o tsql tsql.cpp -lmyodbc8w -lmyodbc8a
export LD_LIBRARY_PATH=/data/mysql_lib:$LD_LIBRARY_PATH
**/

#include <iostream>

#define DB_TYPE_MYSQL
#define USE_OTL_BIGINT
#define OTL_BIGINT long long
#define OTL_ODBC
#define OTL_ODBC_SELECT_STM_EXECUTE_BEFORE_DESCRIBE
#define OTL_ODBC_UNIX
#define OTL_DESTRUCTORS_DO_NOT_THROW
#define OTL_STL
#include "otl.4.0.455.h"

int main(int argc,char* argv[])
{
    otl_connect::otl_initialize();
    otl_connect db;
    try {

        // db.rlogon("UID=xuanyuan;PWD=xuanyuan;DSN=examples"); // connect to ODBC
        db.rlogon("test/test@mysql");

        otl_stream os(50, "select msg1 from test.test_tmp1", db);

        otl_column_desc* desc;
        int desc_len;

        desc=os.describe_select(desc_len);
        for(int n=0; n<desc_len; ++n){
            std::cout<<"========== COLUMN #"<<n+1<<" ==========="<<std::endl;
            std::cout<<"name="<<desc[n].name<<std::endl;
            std::cout<<"dbtype="<<desc[n].dbtype<<std::endl;
            std::cout<<"otl_var_dbtype="<<desc[n].otl_var_dbtype<<std::endl;
            std::cout<<"dbsize="<<desc[n].dbsize<<std::endl;
            std::cout<<"scale="<<desc[n].scale<<std::endl;
            std::cout<<"prec="<<desc[n].prec<<std::endl;
            std::cout<<"nullok="<<desc[n].nullok<<std::endl;
        }

        char msg1[31];
        while (!os.eof()) {
            os >> msg1;
            std::cout << "msg1=";
            if (os.is_null())
                std::cout << "NULL";
            else
                std::cout << msg1;
            std::cout << std::endl;
        }
    }
    catch (otl_exception& p) {
        std::cerr << p.msg << std::endl;
        std::cerr << p.stm_text << std::endl;
        std::cerr << p.sqlstate << std::endl;
        std::cerr << p.var_info << std::endl;
    }

    db.logoff();
    return 0;
}

Suggested fix:
Modify the get_display_size function in the odbc source file utility.cc.

case MYSQL_TYPE_JSON:
    {
      unsigned long length;
      if (field->charsetnr == BINARY_CHARSET_NUMBER)
        length= field->length * 2;
      else
        length= field->length / mbmaxlen;
      if (capint32 && length > INT_MAX32)
        length= INT_MAX32;
      return length;
    }
  }

Perhaps the above logic can be modified to like this:

case MYSQL_TYPE_JSON:
    {
      unsigned long length;
      if (field->charsetnr == BINARY_CHARSET_NUMBER)
        length= field->length * 2;
      //else
      //  length= field->length / mbmaxlen;
      if (capint32 && length > INT_MAX32)
        length= INT_MAX32;
      return length;
    }
  }
[24 Mar 2023 12:43] MySQL Verification Team
Hello jerry,

Thank you for the bug report.
Which version of ODBC driver are you using i.e ANSI or UNICODE? 

Regards,
Ashwini Patil
[27 Mar 2023 6:18] Bogdan Degtyariov
Hi Jerry,

I see a number of problems in the way you are building your test application:

g++ -g -I/data/include/3rd/otl -DLINUX -DBIT64 -L/data/mysql_lib -o tsql tsql.cpp -lmyodbc8w -lmyodbc8a

1. Linking directly to the ODBC driver. It can be done, but we do not guarantee that it will work. Instead you need to link to UnixODBC (use -lodbc -lodbcinst) libraries.

2. It seems that the linking is done to both drivers ANSI and UNICODE at the same time. It is not possible to say which driver is used.

I can confirm that the version 8.0.32 had problems with JSON data representation.
This should be fixed in 8.0.33, which is going to be released in the nearest days.