MySql/MariaDB Collation
MySql/MariaDB Collation
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.
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
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
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
2) While connecting to server add charset "latin5" clause also.
Eg: FWCONNECT oCn HOST ................... CHARSET "latin5"
Please let me have your feed back
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
Merhaba Birol,
Ben DBFCDX de aşağıdaki satırları eklemeden aynı problemler oluyordu.
Ben DBFCDX de aşağıdaki satırları eklemeden aynı problemler oluyordu.
Code: Select all
REQUEST HB_CODEPAGE_TRWIN
HB_CDPSELECT( "TRWIN" )
Regards,
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Re: MySql/MariaDB Collation
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?
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
#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
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
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
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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
Hi Birol,
I have checked and you are right.
I have checked both server default and latin5 - default collation.
I have checked and you are right.
I have checked both server default and latin5 - default collation.
Regards,
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
Can you please try with these two changes?
and while creating the table
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
Code: Select all
oRs := oCn:RowSet( "SELECT CUSTID,FIRST FROM test_trk" ) // ORDER BY FIRST" )
oRs:Sort := "FIRST"
Code: Select all
oCn:CreateTable( "test_trk", { ;
{ "CUSTID", 'N', 3, 0, "PRI" }, ;
{ "FIRST", 'C', 20, 0 }, ;
{ "SECOND", 'C', 20, 0 } }, .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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
Nages, again the same. Q and W comes after Z.
But this time the created table has charset latin5 and collation latin5_turkish_ci
But this time the created table has charset latin5 and collation latin5_turkish_ci
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
That is what I asked you.again the same. Q and W comes after Z.
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
#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
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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
Nages, I have got the same result.
As you suggest, Harbour is sorting them in that order.
The correct order:
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
As you suggest, Harbour is sorting them in that order.
The correct order:
Code: Select all
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
SELECT CUSTID,FIRST FROM test_trk ORDER BY FIRST
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
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?
How are you handling the issue of indexing/sorting with DBF?
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
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.
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
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
Let us see if other Turkish programmers can help.
Also please give me time till I return from Seminar
Also please give me time till I return from Seminar
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: MySql/MariaDB Collation
Mr. Rao,
I have done some more tests and at the end I have realised that the rowset returns with the code:
is in correct order. But, after I sort it using the code:
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.
I have done some more tests and at the end I have realised that the rowset returns with the code:
Code: Select all
oRs := oCn:RowSet( "SELECT ID, NAME FROM mytable ORDER BY name")
Code: Select all
oRs:Sort := "NAME"
As you mentioned before this is because of harbour. What can we do to overcome this problem.
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: MySql/MariaDB Collation
Keep aside mysql or dbf.
We need to address the Harbour collation issue with Turkish codepage.
Try this example (normal English)
Next let us set codepage TRWIN
That means we are not able to properly set Turkish codepage and language.
For example, for Spanish we set
If we use
We need to know how to correctly set Turkish codepage. We seek help from Turkish Harbour programmers.
We need to address the Harbour collation issue with Turkish codepage.
Try this example (normal English)
Code: Select all
function Main()
? "Z" > "W" // --> .T.
return nil
Code: Select all
function Main()
HB_SetCodePage( "TRWIN" )
? "Z" > "W" // --> .F. : This is not correct
return nil
For example, for Spanish we set
Code: Select all
HB_SetCodePage( "ESWIN" )
HB_LangSelect( "es" )
Code: Select all
HB_SetCodePage( "TRWIN" )
HB_LangSelect( "tr" ) // Runtime error
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India