oCn:Insert() - UTF8 encoding fails [Unsolved]

[frose]

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: Select all  Expand view  RUN

#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?

[nageswaragunupudi]

let me check

[nageswaragunupudi]

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: Select all  Expand view  RUN

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

[frose]

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.

[nageswaragunupudi]

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.

[frose]

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

Do you mean

Code: Select all  Expand view  RUN

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

instead of

Code: Select all  Expand view  RUN

  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.

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

[nageswaragunupudi]

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

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