UTF8 & MySql are 100% compatible with FWH?

[dutch]

Dear Mr.Rao,

I got different result of ADO between xHarbour and Harbour with FWH (1512/1509).

1.1 No, I cannot input in Harbour as Richard's WMV file.
1.2 No, I cannot display data correctly. It show ?????, when read from data (input by xHarbour).

2.1 Yes, I can input in xHarbour.
2.2 Yes, I can display data correctly. It show as when I input.

upload img

PS. : I cannot create CHARSET=utf8mb4. Does it make samples different from your testing code?

[richard-service]

Mr.Rao
I add adofuncs.prg all source code within my testadomysql.prg that rebuild it.
http://www.fivetech.com.tw/downloads/Video_Unicode_MySQL_2.wmv

[nageswaragunupudi]

Using ADO with Harbour (not xHarbour), we can achieve 100% compatibility with MySql Work Bench, Excel, etc. Compatibility with MySql WorkBench ensures compatibility with all other MySql connectors for .NET, php, etc.



We can see from the above screen shot a MySql table viewed from FWH XBrowse, MySql workbench and Excel. This table has 6 languages, Telugu, Hindi, Arabic, Hebrew, Thai and Chinese. Personally I do not know 4 languages. I used Google Input Tools for entering Arabic and Hebrew texts and Windows OnScreen KeyBoard to enter Thai and Chinese characters. (Note: I just entered some random characters and I do not know if they mean anything)

This is a link to the video of entering the texts in different languages and how FWH and MySql WorkBench work together.

http://autode.sk/1tejALq

This is the program I used. This works only with Harbour and not xHarbour and requires the modified adofuncs.prg that I personally mailed to Mr Richard and Mr Duch. (This is availble in the next release of FWH)

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

#ifdef __XHARBOUR__
#error Does not run correctly with XHarbour
#endif

static oCn

//----------------------------------------------------------------------------//

function Main()

   local oRs, cSql, aStruct, c
   local cTable   := "testutf8_hrb"

   HB_SETCODEPAGE( "UTF8" )
   FW_SetUnicode( .t. )

   MsgRun( "Connecting to MySql Server", "MYSQL", { || ;
      oCn   := FW_OpenAdoConnection( "MYSQL,localhost,fwh,root,India@1947", .t. ) ;
      } )
   if oCn == nil
      ? MsgStop( "Connect Fail" )
      return nil
   endif

   if .not. FW_AdoTableExists( cTable, oCn ) .or. ;
      MsgYesNo( "Recreate table " + cTable )

      TRY
         oCn:Execute( "DROP TABLE " + cTable )
      CATCH
      END

      aStruct  := { ;
         { "language",    'C', 10, 0 }, ;
         { "inputmethod", 'C', 10, 0 }, ;
         { "unicodestr",  "VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci" } }

      FWAdoCreateTable( cTable, aStruct, oCn )

   endif

   oRs   := FW_OpenRecordSet( oCn, cTable )

   XBROWSER oRs TITLE FWVERSION FASTEDIT SETUP ( ;
      oBrw:lCanPaste := .t., ;
      oBrw:aCols[ 1 ]:nWidth := 40, ;
      oBrw:aCols[ 4 ]:nWidth := 200, ;
      oBrw:bRClicked := { |r,c,f,o| o:oRs:Requery(), o:GoTop(), o:Refresh() } )

   oRs:Close()
   oCn:Close()

return nil

//----------------------------------------------------------------------------//
 

Continuing with the above program, this is a screen shot of 16 different languages in FWH XBrowse and MySql WorkBench.



I shall discuss the issues about xHarbour and Dolphin/TMySql in my next post.

[nageswaragunupudi]

Mr Richard and Mr Dutch informed me that they prefer to use TDolphin or TMySql to ADO.

FWH does work with utf8 perfectly. An FWH+(x)Harbour program needs to communicate with MySql server through an intermediate interface. The interface can be:

1) ADO using TOleAuto() of Harbour --> Works Perfectly
2) ADO using TOleAuto() of xHarbour
3) TDolphin
4) TMySql

So the title of the topic should not be
UTF8 & MySql are 100% compatible with FWH?
The title should be something like
Which interface/3rd party libs are 100% compatible with utf8 and MySql.

Options (2) to (4) have limitations. It is possible to write and read unicode strings. The data is understood only by this software and no other software. MySql Workbench does not understand the data, which means that no other MySql connector also can not understand the data.
Also our software can not understand data written by other software.

Though TDolphin and TMySql are not fully compatible at present, it is easy to make them compatible fully.

This is the change required in tdolphin and tmysql source code:

TDolphin: Module "function.c"
Insert one line of code

Code: Select all  Expand view  RUN

mysql_set_character_set( mysql, "utf8" );
 

in the function HB_FUNC( MYSQLCONNECT )

Code: Select all  Expand view  RUN

HB_FUNC( MYSQLCONNECT ) // -> MYSQL*
{

   <some code here>
      mysql_real_connect( mysql, ....... );
      mysql_set_character_set( mysql, "utf8" ); // New line inserted here
      hb_MYSQL_ret( mysql );
 

TMySql: module "mysql.c"
In the function HB_FUNC( MYSQL_REAL_CONNECT )
Instead of this present code

Code: Select all  Expand view  RUN

      if( mysql_real_connect( mysql, szHost, szUser, szPass, 0, port, NULL, flags ) )
         hb_MYSQL_ret( mysql );
 

modify as

Code: Select all  Expand view  RUN

      if( mysql_real_connect( mysql, szHost, szUser, szPass, 0, port, NULL, flags ) )
      {
        mysql_set_character_set( mysql, "utf8" ); // New line inserted here
        hb_MYSQL_ret( mysql );
      }
 

With this simple change, both Dolphin and TMySql are 100% compatible with utf8 and MySql. We can use either Harbour or xHarbour. Data written by our software is recognized by all outside software and MySql Workbench and our software can read and understand data written by other softwares.

I thank Mr Dutch and Mr Richard for testing and confirming the results with modified dolphin libraries.

ADO with xHarbour has limitations because its TOleAuto() works in ANSI mode only. So for xHarbour users wanting to work with multilingual unicode application using utf8 with MySql, the best choice is modifed dolphin or tmysql.


MySql server can handle not only utf8 different language character sets like big5 (chinese), tis620 (thai), utf8mb4, utf16 and many more. If we connect with 'utf8' it appears we still can read and write in amy othese encodings,

Again the best solution is FWH+ADO+Habour+MySql.
With this option, we can simultaneously read and write any language encoded by MySql in any character set.

Here is an example to show perfect working with 7 different encodings in the same table in many languages.



Program:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

#ifdef __XHARBOUR__
#error Does not run correctly with XHarbour
#endif

static oCn

//----------------------------------------------------------------------------//

function Main()

   local oRs, cSql, aStruct, c
   local cTable   := "testutfmulti"

   HB_SETCODEPAGE( "UTF8" )
   FW_SetUnicode( .t. )

   MsgRun( "Connecting to MySql Server", "MYSQL", { || ;
      oCn   := FW_OpenAdoConnection( "MYSQL,localhost,fwh,root,password", .t. ) ;
      } )
   if oCn == nil
      ? MsgStop( "Connect Fail" )
      return nil
   endif

   if .not. FW_AdoTableExists( cTable, oCn ) .or. ;
      MsgYesNo( "Recreate table " + cTable )

      TRY
         oCn:Execute( "DROP TABLE " + cTable )
      CATCH
      END

      aStruct  := { ;
         { "english",     'C',  30, 0 }, ;
         { "thai_tis620", "VARCHAR(100) CHARACTER SET tis620 COLLATE tis620_thai_ci" }, ;
         { "chinese_big5","VARCHAR(100) CHARACTER SET big5 COLLATE big5_chinese_ci" }, ;
         { "all_utf8mb4", "VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" }, ;
         { "all_utf8",    "VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci" }, ;
         { "all_utf16",   "VARCHAR(100) CHARACTER SET utf16 COLLATE utf16_general_ci" }, ;
         { "all_ucs2",    "VARCHAR(100) CHARACTER SET ucs2 COLLATE ucs2_general_ci" } }

      FWAdoCreateTable( cTable, aStruct, oCn )

   endif

   oRs   := FW_OpenRecordSet( oCn, cTable )

   XBROWSER oRs TITLE FWVERSION + " : ADO + Harbour" FASTEDIT SETUP ( ;
      oBrw:lCanPaste := .t., ;
      oBrw:AutoFit(), ;  // use autofif() only with FWH16.04
      oBrw:bRClicked := { |r,c,f,o| o:oRs:Requery(), o:GoTop(), o:Refresh() } )

   oRs:Close()
   oCn:Close()

return nil

//----------------------------------------------------------------------------//
 

Finally,
FWH on its part works perfectly with utf8. We need never question whether FWH is compabitle or not.
The question is what interface is compatible and with what limitations.
We demonstrated here that the option of ADO+Harbour is the best choice.
We also discussed how to make other alternatives work perfectly as long as the encoding is ANSI or UTF8.

Now it is for the programmer to choose the right interface depending on his requirements and what limitations he can live with.

[richard-service]

Hi Mr.Rao
I add this

Code: Select all  Expand view  RUN

mysql_set_character_set( mysql, "utf8" ); // New line inserted here  

recompile it not work. so I remove it.
I use old MySQL.c work fine.
I ask China Wang and use his TMySQL. He said open MySQL database, TMySQL read Database character set.

[nageswaragunupudi]

You might not have done it correctly.
When I made the same change for Dolphin and it worked, why can not TMySql ?