topic-43892

Posts: 392

Joined: Tue Mar 10, 2009 11:54 AM

oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Thu Nov 09, 2023 09:14 AM

Encoding fails when inserting a character string contains 2-Byte ANSI character, e. g. '"üäö".
Encoding is OK if the character string contains at least one 2-Byte NON ANSI character, e. g. "П"

Code (fw): Select all Collapse

#include "fivewin.ch"

STATIC aStr := { "127.0.0.1, test, root, frose2023", ;
   "208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", ;
   "209.250.245.152,fwh,fwhuser,FiveTech@2022" }

STATIC oCn
REQUEST HB_CODEPAGE_UTF8

FUNCTION Main()
   LOCAL oRs
   LOCAL cTable := "test"
   LOCAL cVar1  := "üäö"

   FW_SetUnicode( .T. )
   oCn := maria_Connect( aStr[ 1 ], .T. )
   oCn:bOnLostServer := { || Alert( "Connection lost! Try again" ), .T. }
   MsgRun( "Creating Test Table.............", "PLEASE WAIT .......", { || CreateSampleTable( cTable, .T. ) } )
   
   // Encoding is NOT ok with 2-Byte ANSI character
   oCn:Insert( cTable, { "test", "StrToHex" }, { cVar1, StrToHex( cVar1, " " ) }, )
   
   // Encoding is ok with 2-Byte NON ANSI character
   oCn:Insert( cTable, { "test", "StrToHex" }, { cVar1 + " П", StrToHex( cVar1 + " |П", " " ) }, )
   
   oRs := oCn:RowSet( "SELECT * FROM `" + cTable + "`", .F. )

   XBrowse( oRs )
RETURN NIL

STATIC FUNCTION CreateSampleTable( cTable, lDrop )
   LOCAL cSql := ""
   LOCAL uResult
   Default lDrop := .F.
   //
   IF lDrop .OR. !oCn:TableExists( cTable )
      cSql += CRLF + "SET NAMES utf8mb4;"
      cSql += CRLF + "SET FOREIGN_KEY_CHECKS = 0;"
      cSql += CRLF + "DROP TABLE IF EXISTS `" + cTable + "`;"
      cSql += CRLF + "CREATE TABLE `" + cTable + "` ("
      cSql += CRLF + "  `test` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',"
      cSql += CRLF + "  `StrToHex` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',"
      cSql += CRLF + "  `guid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT uuid(),"
      cSql += CRLF + "  `createdt` timestamp NOT NULL DEFAULT current_timestamp(),"
      cSql += CRLF + "  `modifydt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,"
      cSql += CRLF + "  PRIMARY KEY (`GUID`) USING BTREE"
      cSql += CRLF + ") ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;"
      cSql += CRLF + "SET FOREIGN_KEY_CHECKS = 1;"
      
      uResult := oCn:Execute( cSql,, .T. )
   ENDIF
RETURN NIL

Dear Mr. Rao, can you confirm the behavior?

Windows 11 Pro 22H2 22621.1848

Microsoft (R) Windows (R) Resource Compiler Version 10.0.10011.16384

Harbour 3.2.0dev (r2008190002)

FWH 23.10 x86

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Thu Nov 09, 2023 09:59 AM

let me check

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Thu Nov 09, 2023 07:52 PM

For now, please do not use oCn:Insert(...). We are reviewing this.

Can you please test this and let is know if this works correctly.

Code (fw): Select all Collapse

cSql := "INSERT INTO `" + cTable + "` ( `test` ) VALUES ( ? )"
? oCn:Execute( cSql, { AnsiToUtf8( cVar1 ) } )
oRs  := oCn:RowSet( "SELECT `test`, HEX(`test`) FROM `test`" )
XBROWSER oRs

Regards

G. N. Rao.

Hyderabad, India

frose

Posts: 392

Joined: Tue Mar 10, 2009 11:54 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Fri Nov 10, 2023 06:29 AM

Yes, pure sql works!

Without AnsiToUtf8()!

Because I have completely switched from mixed ASCII-Cp850/Ansi environment to UTF8, i.e. source code (UEStudio) and database DBF/MariaDB.

From now on, I no longer have to worry about the encoding.
No more conversions between the code pages HB_OemToAnsi()/HB_AnsiToOem().
Of course also no AnsiToUtf8()/UTF8ToAnsi().
No more 'garbled' characters due to incorrect conversion.
No loss of characters due to different character sets. :D

Windows 11 Pro 22H2 22621.1848

Microsoft (R) Windows (R) Resource Compiler Version 10.0.10011.16384

Harbour 3.2.0dev (r2008190002)

FWH 23.10 x86

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Fri Nov 10, 2023 07:50 AM

source code (UEStudio)

With UEStudio, can we enter constants in Utf8 and save in Utf8?
If so that is far better

But the point is we need to INSERT utf8 text but not ANSI text when the table's charset is utf8 or utf8mb4.

Now, we need to check the method Insert and get back to you.

Regards

G. N. Rao.

Hyderabad, India

frose

Posts: 392

Joined: Tue Mar 10, 2009 11:54 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Fri Nov 10, 2023 08:22 AM

With UEStudio, can we enter constants in Utf8 and save in Utf8?

Do you mean

Code (fw): Select all Collapse

#define cVar3       "üäö ÜÖÄ ß"
STATIC cVar1  := "üäö"

instead of

Code (fw): Select all Collapse

   LOCAL cVar1  := "üäö"

?
If so, yes!

But the point is we need to INSERT utf8 text but not ANSI text when the table's charset is utf8 or utf8mb4.

8) :D

Now, we need to check the method Insert and get back to you.

Don't hurry, I've a workaround

BTW: I missed [<lFindLast>] in ::Seek() :(
have to torture myself with DESCEND and LIMIT :roll:

Windows 11 Pro 22H2 22621.1848

Microsoft (R) Windows (R) Resource Compiler Version 10.0.10011.16384

Harbour 3.2.0dev (r2008190002)

FWH 23.10 x86

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oCn:Insert() - UTF8 encoding fails [Unsolved]
Posted: Fri Nov 10, 2023 09:29 AM

BTW: I missed [<lFindLast>] in ::Seek() :(

Thanks for asking.
We will provide soon and if necessary, will provide with the revised libs.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion