MySql/MariaDB Collation

[betoncu]

I am using charset "latin5" and collation "latin5_turkish_ci"
I have problems with the sort order. The words starts with W,Q,X are ordered after Z.

I have faced some other problems also when I use EditBaserecord in the browses.
The browse does not position the cursor correctly when I insert/edit.

If I use "SELECT * FROM xxx" instead of "SELECT ID,FIRST,SECOND FROM xxx" with EditSource() it works correctly.

Mr. Rao please help.

[nageswaragunupudi]

1) Please build the application with Turkish codepage. ( Please also let me know how do you link Turkish code page and are you using Harbour or xHarbour. I like to try)
2) While connecting to server add charset "latin5" clause also.
Eg: FWCONNECT oCn HOST ................... CHARSET "latin5"

Please let me have your feed back

[Horizon]

Merhaba Birol,

Ben DBFCDX de aşağıdaki satırları eklemeden aynı problemler oluyordu.

Code: Select all  Expand view  RUN

    REQUEST HB_CODEPAGE_TRWIN
    HB_CDPSELECT( "TRWIN" )

[betoncu]

I am using FWH 17.06, BCC7 and Harbour 3.2.0dev (r1703231115)
The sort order at the beginning is correct, but after you press refresh button the sequence changes.
I am using the buildh.bat file in samples folder to compile and link.
I have deleted only the following line:

echo %fwh%\lib\hbpgsql.lib %fwh%\lib\libpq.lib + >> b32.bc

Hakan, bu örnek sende düzgün çalışıyor mu?

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST HB_CODEPAGE_TRWIN

static oCn

function Main()

   local oRs, oDlg, oBrw, oFont

   HB_SetCodePage("TRWIN")

   FWCONNECT oCn HOST "localhost" USER "root" PASSWORD "mypassw" DATABASE "test" PORT 3306 CHARSET "latin5"

   oCn:lShowErrors := .T.

   CreateTables()

   oRs   := oCn:RowSet( "SELECT CUSTID,FIRST FROM test_trk ORDER BY FIRST" )

   DEFINE FONT oFont NAME "Ms Sans Serif" SIZE 0, -12

   DEFINE DIALOG oDlg SIZE 500,600 FONT oFont PIXEL TRUEPIXEL TITLE "EditBaseRecord()"

   @ 70,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
           DATASOURCE oRs AUTOCOLS AUTOSORT ;
           COLSIZES -10, -30 ;
           CELL LINES NOBORDER

   oBrw:CreateFromCode()

   @ 20, 20 BTNBMP PROMPT "ADD"  SIZE 100,30 PIXEL FLAT OF oDlg ;
      ACTION oRs:EditBaseRecord( nil, .t., { |oRec| MyEditDlg( oRec ) }, oBrw )

   @ 20,130 BTNBMP PROMPT "EDIT" SIZE 100,30 PIXEL FLAT OF oDlg ;
      ACTION oRs:EditBaseRecord( nil, .f., { |oRec| MyEditDlg( oRec ) }, oBrw )

   @ 20,240 BTNBMP PROMPT "REFRESH" SIZE 100,30 PIXEL FLAT OF oDlg ;
      ACTION ( oRs:Requery(), oBrw:Refresh(), oBrw:SetFocus() )

   ACTIVATE DIALOG oDlg CENTERED
   oFont:End()

return nil


function CreateTables()

   oCn:DropTable( "test_trk" )

   if !oCn:TableExists( "test_trk" )
      oCn:CreateTable( "test_trk", { ;
         { "CUSTID", 'N',  3, 0, "PRI" }, ;
         { "FIRST",  'C', 20, 0 }, ;
         { "SECOND", 'C', 20, 0 } } )

      oCn:Insert( "test_trk", "CUSTID, FIRST, SECOND", ;
         { {1, 'A', 'B'}, {2, 'Ş', 'B'}, {3, 'Z', 'B'}, {4, 'W', 'B'}, {5, 'B', 'B'}, ;
           {6, 'Q', 'B'}, {7, 'N', 'B'}, {8, 'İ', 'B'}, {9, 'Ü', 'B'} } )
   endif

return nil


static function MyEditDlg( oRec )

   local lNew     := ( oRec:RecNo == 0 )
   local oDlg, oFont

   DEFINE FONT oFont NAME "Ms Sans Serif" SIZE 0, -12

   DEFINE DIALOG oDlg SIZE 400,300 PIXEL TRUEPIXEL FONT oFont

   @ 030,020 SAY "ID:"         SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 060,020 SAY "First"       SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 090,020 SAY "Second"      SIZE 80,20 PIXEL OF oDlg RIGHT

   @ 030,120 GET oRec:CUSTID   SIZE 100,22 PIXEL OF oDlg
   @ 060,120 GET oRec:FIRST    SIZE 240,22 PIXEL OF oDlg
   @ 090,120 GET oRec:SECOND   SIZE 240,22 PIXEL OF oDlg

   @ 140,020 BTNBMP PROMPT "Save"   SIZE 150,30 PIXEL FLAT OF oDlg ;
             ACTION ( If( oRec:Modified(), oRec:Save(), nil ), oDlg:End() )

   @ 140,240 BTNBMP PROMPT "Cancel" SIZE 150,30 PIXEL FLAT OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED ON PAINT oDlg:Box( 15, 10, 125, 390 )

return nil

 

[nageswaragunupudi]

Thanks for your sample.
I shall check it and get back to you.

========
Please download 17.06 again
You can retain
echo %fwh%\lib\hbpgsql.lib %fwh%\lib\libpq.lib + >> b32.bc

[Horizon]

Hi Birol,

I have checked and you are right.

I have checked both server default and latin5 - default collation.

[nageswaragunupudi]

Can you please try with these two changes?

Code: Select all  Expand view  RUN

  oRs   := oCn:RowSet( "SELECT CUSTID,FIRST FROM test_trk" ) // ORDER BY FIRST" )
   oRs:Sort := "FIRST"
 

and while creating the table

Code: Select all  Expand view  RUN

    oCn:CreateTable( "test_trk", { ;
         { "CUSTID", 'N',  3, 0, "PRI" }, ;
         { "FIRST",  'C', 20, 0 }, ;
         { "SECOND", 'C', 20, 0 } }, .F., "latin5_turkish_ci" )
 

I added two more parameters '.F., "latin5_turkish_ci" )

Please clarify
Is Z lower than Q and W ?

Depending on your feedback, let me see what more I need to do

[betoncu]

Nages, again the same. Q and W comes after Z.
But this time the created table has charset latin5 and collation latin5_turkish_ci

[nageswaragunupudi]

again the same. Q and W comes after Z.

That is what I asked you.
I found this problem with "TRWIN" codepage.
For a moment keep aside MySql server and Server's collation.

Let us see the sort behavior of TRWIN, independent of any database.
Here is a test:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function main()

   local aData := ;
         { {1, 'A', 'B'}, {2, 'S', 'B'}, {3, 'Z', 'B'}, {4, 'W', 'B'}, {5, 'B', 'B'}, ;
           {6, 'Q', 'B'}, {7, 'N', 'B'}, {8, 'I', 'B'}, {9, 'Ü', 'B'} }

   HB_SetCodePage("TRWIN")

   XBROWSER aData AUTOSORT

return nil
 

Click on header of column-2 to the sort order in ascending and descending.
If we do not use TRWIN, Ü comes at the end and all other characters are sorted normally like English.

First we need to resolve the issues with proper setting up of Turkish codepage in Harbour.
I do not know Turkish. So I request you to find out how to configure the Turkish codepage in such a way that Harbour Sorting is correct.

Next we go to the Server. We need to solve the issues step by step

[betoncu]

Nages, I have got the same result.
As you suggest, Harbour is sorting them in that order.

The correct order:

Code: Select all  Expand view  RUN

A, B, C, Ç, D, E, F, G, Ğ, H, I, İ, J, K, L, M, N, O, Ö, P, Q, R, S, Ş, T, U, Ü, V, W, X, Y, Z
a, b, c, ç, d, e, f, g, ğ, h, ı, i, j, k, l, m, n, o, ö, p, q, r, s, ş, t, u, ü, v, w, x, y, z
 

There is no any problem on the server side. Because the Sql statement below returns the correct order.
SELECT CUSTID,FIRST FROM test_trk ORDER BY FIRST

[nageswaragunupudi]

We need to resolve the issue of Codepage with Harbour. May be we have not been configuring it correctly.
How are you handling the issue of indexing/sorting with DBF?

[betoncu]

I am not using DBF. Since 2009 I am using mysql with tmysql.
After each edit/insert I was refreshing the rowset entirely.
And hence there was not any problem.
I like your method because there is no need for refresh the query.

[nageswaragunupudi]

Let us see if other Turkish programmers can help.
Also please give me time till I return from Seminar

[betoncu]

Mr. Rao,
I have done some more tests and at the end I have realised that the rowset returns with the code:

Code: Select all  Expand view  RUN

oRs   := oCn:RowSet( "SELECT ID, NAME FROM mytable ORDER BY name")

is in correct order. But, after I sort it using the code:

Code: Select all  Expand view  RUN

oRs:Sort := "NAME"

the sort order changes and the characters Q,X,W comes after Z.
As you mentioned before this is because of harbour. What can we do to overcome this problem.

[nageswaragunupudi]

Keep aside mysql or dbf.
We need to address the Harbour collation issue with Turkish codepage.

Try this example (normal English)

Code: Select all  Expand view  RUN

function Main()

   ? "Z" > "W" // --> .T.

return nil
 

Next let us set codepage TRWIN

Code: Select all  Expand view  RUN

function Main()

   HB_SetCodePage( "TRWIN" )
   ? "Z" > "W" // --> .F. : This is not correct

return nil
 

That means we are not able to properly set Turkish codepage and language.
For example, for Spanish we set

Code: Select all  Expand view  RUN

HB_SetCodePage( "ESWIN" )
HB_LangSelect( "es" )
 

If we use

Code: Select all  Expand view  RUN

HB_SetCodePage( "TRWIN" )
HB_LangSelect( "tr" ) // Runtime error
 

We need to know how to correctly set Turkish codepage. We seek help from Turkish Harbour programmers.