Alaska Software Inc. - DESCEND problem
Username: Password:
AuthorTopic: DESCEND problem
Andrea UrbaniDESCEND problem
on Tue, 10 Apr 2018 18:14:40 +0200
Hello to everybody,
I'm trying the Postgresql interface and I have a problem with the 
DESCEND inside the indexes.
In the following raw example if you active the FOXCDX version setting 
lPostgres to .F. you will see the records in the right descending order 
and the dbseek will work fine, but with lPostgres set to .T. you will 
have wrong order and seek too.
Can anybody teach me where I'm wrong?
I'm using "PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit"

/*
CREATE DATABASE descendtest
   WITH OWNER = postgres
        ENCODING = 'UTF8'
        TABLESPACE = pg_default
        LC_COLLATE = 'Italian_Italy.1252'
        LC_CTYPE = 'Italian_Italy.1252'
        CONNECTION LIMIT = -1;
*/

#pragma library("ADAC20B.LIB")

#include "Common.ch"
#include "pgdbe.ch"
#include "collat.ch"
PROCEDURE Main
    LOCAL oSession
    LOCAL cConnStr
    LOCAL cLookFor
    LOCAL lPostgres := .T.

    SET CHARSET TO ANSI
    SET COLLATION TO ITALIAN
    SET DELETED ON

    if ( lPostgres )
       Load the PostgreSQL DatabaseEngine
      DbeLoad("pgdbe")
      DbeSetDefault("pgdbe")

       Connect to the Server
      cConnStr := 
"DBE=pgdbe;server=localhost;db=descendtest;uid=postgres;pwd=thesafepassword"
      oSession := DacSession():New(cConnStr)
      IF(!oSession:IsConnected())
        MsgBox("connection failed, database missing")
        QUIT
      ENDIF
    else
      IF ! DbeLoad( "FOXDBE", .T.)
         Alert( "Database engine FOXDBE not loaded" , {"OK"} )
         QUIT
      ENDIF
      IF ! DbeLoad( "CDXDBE",.T.)
         Alert( "Database-Engine CDXDBE not loaded" , {"OK"} )
         QUIT
      ENDIF
      IF ! DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )
         Alert( "FOXCDX Database-Engine;is not created" , {"OK"} )
         QUIT
      ENDIF
      DbeSetDefault( "FOXCDX" )
    endif

    DbCreate("TestTable",{ {"Dom","C",12,0},   ;
                           {"Sin","C",12,0},   ;
                           {"Ele","N",9,0},    ;
                           {"Pro","N",9,0},    ;
                           {"Nam","C",5,0} } )

    select 0
    Use ("TestTable") SHARED
    TestTable->(OrdCreate( "TestTable", ;
               "idxPK",     ;
 
"Dom+Sin+PadL(Var2Char(Ele),9,'0')+PadL(Var2Char(Pro),9,'0')", ;
               NIL,         ;
               .T. ))
    TestTable->(OrdCreate( "TestTable", ;
               "idxDescend",;
 
"Dom+Sin+PadL(Var2Char(Ele),9,'0')+Descend(PadL(Var2Char(Pro),9,'0'))", ;
               NIL,         ;
               .F. ))
    select 0
    Use ("TestTable") index ("TestTable") SHARED
    TestTable->(DbAppend())
    if ( NetErr() )
      MsgBox("Append error 1")
      QUIT
    ENDIF
    TestTable->Dom:="            "
    TestTable->Sin:="000000000001"
    TestTable->Ele:=1
    TestTable->Pro:=1
    TestTable->Nam:="1-1"
    TestTable->(DbCommit())

    TestTable->(DbAppend())
    if ( NetErr() )
      MsgBox("Append error 2")
      QUIT
    ENDIF
    TestTable->Dom:="            "
    TestTable->Sin:="000000000001"
    TestTable->Ele:=1
    TestTable->Pro:=2
    TestTable->Nam:="1-2"
    TestTable->(DbCommit())

    TestTable->(DbAppend())
    if ( NetErr() )
      MsgBox("Append error 3")
      QUIT
    ENDIF
    TestTable->Dom:="            "
    TestTable->Sin:="000000000001"
    TestTable->Ele:=2
    TestTable->Pro:=1
    TestTable->Nam:="2-1"
    TestTable->(DbCommit())

    TestTable->(DbAppend())
    if ( NetErr() )
      MsgBox("Append error 4")
      QUIT
    ENDIF
    TestTable->Dom:="            "
    TestTable->Sin:="000000000001"
    TestTable->Ele:=2
    TestTable->Pro:=2
    TestTable->Nam:="2-2"
    TestTable->(DbCommit())

    TestTable->(DbAppend())
    if ( NetErr() )
      MsgBox("Append error 5")
      QUIT
    ENDIF
    TestTable->Dom:="            "
    TestTable->Sin:="000000000001"
    TestTable->Ele:=2
    TestTable->Pro:=3
    TestTable->Nam:="2-3"
    TestTable->(DbCommit())

    TestTable->(OrdSetFocus("idxDescend"))
    TestTable->(DbGoTop())
    do while ( ! TestTable->(Eof()) )
      MsgBox(TestTable->Nam)
      TestTable->(DbSkip(1))
    enddo

    cLookFor := "            " + ;
                "000000000001" + ;
                PadL(Var2Char(1),9,'0')
    MsgBox( "[" + cLookFor + "]" )
    if ( TestTable->(DbSeek(cLookFor,.T., "idxDescend") ) )
      MsgBox( "Found and is " + TestTable->Nam )
    else
      MsgBox( "Not found (" + Var2Char(TestTable->(RecNo())) + "), but I 
was expecting to get 1-2" )
    endif

    cLookFor := "            " + ;
                "000000000001" + ;
                PadL(Var2Char(2),9,'0')
    MsgBox( "[" + cLookFor + "]" )
    if ( TestTable->(DbSeek(cLookFor,.T., "idxDescend") ) )
      MsgBox( "Found and is " + TestTable->Nam )
    else
      MsgBox( "Not found (" + Var2Char(TestTable->(RecNo())) + "), but I 
was expecting to get 2-3" )
    endif
RETURN


Thank you in advance
Andrea
Matej JuracRe: DESCEND problem
on Thu, 12 Apr 2018 07:58:03 +0200
Sorting in Postgresql for char or text depends on collation definition of your
locale. So in this case Italian_Italy.1252 it is sorted according to grammatic
(not computer or ANSI rules) rules of which (as in my Slovenian too) strings
are probably left trimmed in process of sorting and then sorted.

On full server you can either use "COLLATE "C" ASC" when doing ORDER BY or
recreate database with ANSI ; dump, recreate and import is needed for existing
databases.



Andrea Urbani wrote in message
news:776a2e99$339bc8b5$89adb@news.alaska-software.com...
>Hello to everybody,
>I'm trying the Postgresql interface and I have a problem with the 
>DESCEND inside the indexes.
>In the following raw example if you active the FOXCDX version setting 
>lPostgres to .F. you will see the records in the right descending order 
>and the dbseek will work fine, but with lPostgres set to .T. you will 
>have wrong order and seek too.
>Can anybody teach me where I'm wrong?
>I'm using "PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit"
>
>/*
>CREATE DATABASE descendtest
>   WITH OWNER = postgres
>        ENCODING = 'UTF8'
>        TABLESPACE = pg_default
>        LC_COLLATE = 'Italian_Italy.1252'
>        LC_CTYPE = 'Italian_Italy.1252'
>        CONNECTION LIMIT = -1;
>*/
>
>#pragma library("ADAC20B.LIB")
>
>#include "Common.ch"
>#include "pgdbe.ch"
>#include "collat.ch"
>PROCEDURE Main
>    LOCAL oSession
>    LOCAL cConnStr
>    LOCAL cLookFor
>    LOCAL lPostgres := .T.
>
>    SET CHARSET TO ANSI
>    SET COLLATION TO ITALIAN
>    SET DELETED ON
>
>    if ( lPostgres )
>       Load the PostgreSQL DatabaseEngine
>      DbeLoad("pgdbe")
>      DbeSetDefault("pgdbe")
>
>       Connect to the Server
>      cConnStr := 
>"DBE=pgdbe;server=localhost;db=descendtest;uid=postgres;pwd=thesafepassword"
>      oSession := DacSession():New(cConnStr)
>      IF(!oSession:IsConnected())
>        MsgBox("connection failed, database missing")
>        QUIT
>      ENDIF
>    else
>      IF ! DbeLoad( "FOXDBE", .T.)
>         Alert( "Database engine FOXDBE not loaded" , {"OK"} )
>         QUIT
>      ENDIF
>      IF ! DbeLoad( "CDXDBE",.T.)
>         Alert( "Database-Engine CDXDBE not loaded" , {"OK"} )
>         QUIT
>      ENDIF
>      IF ! DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )
>         Alert( "FOXCDX Database-Engine;is not created" , {"OK"} )
>         QUIT
>      ENDIF
>      DbeSetDefault( "FOXCDX" )
>    endif
>
>    DbCreate("TestTable",{ {"Dom","C",12,0},   ;
>                           {"Sin","C",12,0},   ;
>                           {"Ele","N",9,0},    ;
>                           {"Pro","N",9,0},    ;
>                           {"Nam","C",5,0} } )
>
>    select 0
>    Use ("TestTable") SHARED
>    TestTable->(OrdCreate( "TestTable", ;
>               "idxPK",     ;
> 
>"Dom+Sin+PadL(Var2Char(Ele),9,'0')+PadL(Var2Char(Pro),9,'0')", ;
>               NIL,         ;
>               .T. ))
>    TestTable->(OrdCreate( "TestTable", ;
>               "idxDescend",;
> 
>"Dom+Sin+PadL(Var2Char(Ele),9,'0')+Descend(PadL(Var2Char(Pro),9,'0'))", ;
>               NIL,         ;
>               .F. ))
>    select 0
>    Use ("TestTable") index ("TestTable") SHARED
>    TestTable->(DbAppend())
>    if ( NetErr() )
>      MsgBox("Append error 1")
>      QUIT
>    ENDIF
>    TestTable->Dom:="            "
>    TestTable->Sin:="000000000001"
>    TestTable->Ele:=1
>    TestTable->Pro:=1
>    TestTable->Nam:="1-1"
>    TestTable->(DbCommit())
>
>    TestTable->(DbAppend())
>    if ( NetErr() )
>      MsgBox("Append error 2")
>      QUIT
>    ENDIF
>    TestTable->Dom:="            "
>    TestTable->Sin:="000000000001"
>    TestTable->Ele:=1
>    TestTable->Pro:=2
>    TestTable->Nam:="1-2"
>    TestTable->(DbCommit())
>
>    TestTable->(DbAppend())
>    if ( NetErr() )
>      MsgBox("Append error 3")
>      QUIT
>    ENDIF
>    TestTable->Dom:="            "
>    TestTable->Sin:="000000000001"
>    TestTable->Ele:=2
>    TestTable->Pro:=1
>    TestTable->Nam:="2-1"
>    TestTable->(DbCommit())
>
>    TestTable->(DbAppend())
>    if ( NetErr() )
>      MsgBox("Append error 4")
>      QUIT
>    ENDIF
>    TestTable->Dom:="            "
>    TestTable->Sin:="000000000001"
>    TestTable->Ele:=2
>    TestTable->Pro:=2
>    TestTable->Nam:="2-2"
>    TestTable->(DbCommit())
>
>    TestTable->(DbAppend())
>    if ( NetErr() )
>      MsgBox("Append error 5")
>      QUIT
>    ENDIF
>    TestTable->Dom:="            "
>    TestTable->Sin:="000000000001"
>    TestTable->Ele:=2
>    TestTable->Pro:=3
>    TestTable->Nam:="2-3"
>    TestTable->(DbCommit())
>
>    TestTable->(OrdSetFocus("idxDescend"))
>    TestTable->(DbGoTop())
>    do while ( ! TestTable->(Eof()) )
>      MsgBox(TestTable->Nam)
>      TestTable->(DbSkip(1))
>    enddo
>
>    cLookFor := "            " + ;
>                "000000000001" + ;
>                PadL(Var2Char(1),9,'0')
>    MsgBox( "[" + cLookFor + "]" )
>    if ( TestTable->(DbSeek(cLookFor,.T., "idxDescend") ) )
>      MsgBox( "Found and is " + TestTable->Nam )
>    else
>      MsgBox( "Not found (" + Var2Char(TestTable->(RecNo())) + "), but I 
>was expecting to get 1-2" )
>    endif
>
>    cLookFor := "            " + ;
>                "000000000001" + ;
>                PadL(Var2Char(2),9,'0')
>    MsgBox( "[" + cLookFor + "]" )
>    if ( TestTable->(DbSeek(cLookFor,.T., "idxDescend") ) )
>      MsgBox( "Found and is " + TestTable->Nam )
>    else
>      MsgBox( "Not found (" + Var2Char(TestTable->(RecNo())) + "), but I 
>was expecting to get 2-3" )
>    endif
>RETURN
>
>
>Thank you in advance
>Andrea
Andrea UrbaniRe: DESCEND problem
on Tue, 28 Aug 2018 09:03:53 +0200
With ANSI it works fine: thank you

Il 12/04/2018 07:58, Matej Jurac ha scritto:
> Sorting in Postgresql for char or text depends on collation definition of your
> locale. So in this case Italian_Italy.1252 it is sorted according to grammatic
> (not computer or ANSI rules) rules of which (as in my Slovenian too) strings
> are probably left trimmed in process of sorting and then sorted.
> 
> On full server you can either use "COLLATE "C" ASC" when doing ORDER BY or
> recreate database with ANSI ; dump, recreate and import is needed for existing
> databases.
>