I have an index that uses the descend() function as follows:

descend(iff(empty(date),'99999999',dtos(date))

This puts rows with empty dates first in the index.

I am using Advantage 12 and the latest version of xBase.

When browsing the table the index order of the rows are correct -
empty dates appear first.

However, when seeking with descending value such as:

dbseek(descend('99999999'))

or 

dbseek(descend('99999999',.t.)

the seek fails.

The index key is proper as returned from indexkey() and also in the
Advantage database (using Advantage Data Architect to view.)

Thanks,
Tim

Also meant to add that seeks such as:

dbseek(descend('20180501'))

Also fail when there are indeed rows with that date.

On Thu, 17 May 2018 17:13:19 -0700, Tim Callahan wrote:

>I have an index that uses the descend() function as follows:
>
>descend(iff(empty(date),'99999999',dtos(date))
>
>This puts rows with empty dates first in the index.
>
>I am using Advantage 12 and the latest version of xBase.
>
>When browsing the table the index order of the rows are correct -
>empty dates appear first.
>
>However, when seeking with descending value such as:
>
>dbseek(descend('99999999'))
>
>or 
>
>dbseek(descend('99999999',.t.)
>
>the seek fails.
>
>The index key is proper as returned from indexkey() and also in the
>Advantage database (using Advantage Data Architect to view.)
>
>Thanks,
>Tim

On 5/18/2018 11:44 AM, Tim Callahan wrote:
> Also meant to add that seeks such as:
> 
> dbseek(descend('20180501'))
> 
> Also fail when there are indeed rows with that date.
> 
> On Thu, 17 May 2018 17:13:19 -0700, Tim Callahan wrote:
> 
>> I have an index that uses the descend() function as follows:
>>
>> descend(iff(empty(date),'99999999',dtos(date))
>>
>> This puts rows with empty dates first in the index.
>>
>> I am using Advantage 12 and the latest version of xBase.
>>
>> When browsing the table the index order of the rows are correct -
>> empty dates appear first.
>>
>> However, when seeking with descending value such as:
>>
>> dbseek(descend('99999999'))
>>
>> or
>>
>> dbseek(descend('99999999',.t.)
>>
>> the seek fails.
>>
>> The index key is proper as returned from indexkey() and also in the
>> Advantage database (using Advantage Data Architect to view.)
>>
>> Thanks,
>> Tim

Hi Tim,

Based on some prior postings I'm guessing that you are tables from 
within Advantage's Dictionary as I tried to replicate your situation 
except I use ADS with free tables...

DbeSetDefault( "ADSDBE" )
DbeInfo( COMPONENT_DATA, ADSDBE_TBL_MODE, ADSDBE_CDX )
DbeInfo( COMPONENT_ORDER, ADSDBE_INDEX_EXT, "CDX" )

I have heard from others that Advantage 12 has problems with Xbase++ but 
have no direct experience on this so it may happen with ADS 11 and 
dictionaries too.

As a work around is there any way you could adjust your logic and try 
using DbSetDescend( <lDescend> )?

Regards... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada

Hi Jonathon,

Thanks for the reply.

I am indeed opening the files from within an advantage data
dictionary. My dbe is setup as shown below.

The indexes are also used/created by Clipper 5.2 apps.

Also I have tried setting COLLATION to nothing (recommended by Alaska
PDR for clipper comatability and to ASCII (-1) which seems to be the
clipper default. Setting to either value seems to make no difference
in the apps behavior as I have tried both for lengths of time.

   Dbeload( "ADSDBE", .F. )
 
   DbeSetDefault( "ADSDBE" )

    FOXCDX compatability - .CDX/.FPT
   DbeInfo( COMPONENT_DATA , ADSDBE_TBL_MODE, ADSDBE_CDX )
   DbeInfo( COMPONENT_ORDER, ADSDBE_TBL_MODE, ADSDBE_CDX )

     Clipper compatible locking
   DbeInfo(COMPONENT_DATA, ADSDBE_LOCK_MODE,ADSDBE_COMPATIBLE_LOCKING
)


I've been working on an app with ADS 12 for a while and this is the
first issue I've run into.

Re using dbsetdescend() I'm not sure if I can. The index actually has
ascending values too as in:

descend(if(empty(date),'99999999',dtos(date)))+id

which puts the rows in ascending ID order within the descending dates
with empty dates coming first.

Using dbsetdescend() means I'd have to use descend(ID) I think e.g.
descend this key:

if(empty(date),'99999999',dtos(date))+descend(ID)

Do I have that right?

Thanks again,
Tim 

On Fri, 18 May 2018 15:20:22 -0600, Jonathan Leeming wrote:

>
>Hi Tim,
>
>Based on some prior postings I'm guessing that you are tables from 
>within Advantage's Dictionary as I tried to replicate your situation 
>except I use ADS with free tables...
>
>DbeSetDefault( "ADSDBE" )
>DbeInfo( COMPONENT_DATA, ADSDBE_TBL_MODE, ADSDBE_CDX )
>DbeInfo( COMPONENT_ORDER, ADSDBE_INDEX_EXT, "CDX" )
>
>I have heard from others that Advantage 12 has problems with Xbase++ but 
>have no direct experience on this so it may happen with ADS 11 and 
>dictionaries too.
>
>As a work around is there any way you could adjust your logic and try 
>using DbSetDescend( <lDescend> )?
>
>Regards... Jonathan

On 5/22/2018 2:50 PM, Tim Callahan wrote:
> Hi Jonathon,
> 
> Thanks for the reply.
> 
> I am indeed opening the files from within an advantage data
> dictionary. My dbe is setup as shown below.
> 
> The indexes are also used/created by Clipper 5.2 apps.
> 
> Also I have tried setting COLLATION to nothing (recommended by Alaska
> PDR for clipper comatability and to ASCII (-1) which seems to be the
> clipper default. Setting to either value seems to make no difference
> in the apps behavior as I have tried both for lengths of time.
> 
>     Dbeload( "ADSDBE", .F. )
>   
>     DbeSetDefault( "ADSDBE" )
> 
>      FOXCDX compatability - .CDX/.FPT
>     DbeInfo( COMPONENT_DATA , ADSDBE_TBL_MODE, ADSDBE_CDX )
>     DbeInfo( COMPONENT_ORDER, ADSDBE_TBL_MODE, ADSDBE_CDX )
> 
>       Clipper compatible locking
>     DbeInfo(COMPONENT_DATA, ADSDBE_LOCK_MODE,ADSDBE_COMPATIBLE_LOCKING
> )
> 
> 
> I've been working on an app with ADS 12 for a while and this is the
> first issue I've run into.
> 
> Re using dbsetdescend() I'm not sure if I can. The index actually has
> ascending values too as in:
> 
> descend(if(empty(date),'99999999',dtos(date)))+id
> 
> which puts the rows in ascending ID order within the descending dates
> with empty dates coming first.
> 
> Using dbsetdescend() means I'd have to use descend(ID) I think e.g.
> descend this key:
> 
> if(empty(date),'99999999',dtos(date))+descend(ID)
> 
> Do I have that right?
> 
> Thanks again,
> Tim
> 
> On Fri, 18 May 2018 15:20:22 -0600, Jonathan Leeming wrote:
> 
>>
>> Hi Tim,
>>
>> Based on some prior postings I'm guessing that you are tables from
>> within Advantage's Dictionary as I tried to replicate your situation
>> except I use ADS with free tables...
>>
>> DbeSetDefault( "ADSDBE" )
>> DbeInfo( COMPONENT_DATA, ADSDBE_TBL_MODE, ADSDBE_CDX )
>> DbeInfo( COMPONENT_ORDER, ADSDBE_INDEX_EXT, "CDX" )
>>
>> I have heard from others that Advantage 12 has problems with Xbase++ but
>> have no direct experience on this so it may happen with ADS 11 and
>> dictionaries too.
>>
>> As a work around is there any way you could adjust your logic and try
>> using DbSetDescend( <lDescend> )?
>>
>> Regards... Jonathan

Your if(empty(date),'99999999',dtos(date))+descend(ID) is how what I 
would try as well but it starts getting "silly" descending and descending!

Do you have access to ADS 11 instead of 12 to try?  I use a two user 
developer version of 11 that does not need a license.

Regards... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada

>On 5/22/2018 2:50 PM, Tim Callahan wrote:
>> Hi Jonathon,
>> 
>> Thanks for the reply.
>> 
>> I am indeed opening the files from within an advantage data
>> dictionary. My dbe is setup as shown below.
>> 
>> The indexes are also used/created by Clipper 5.2 apps.
>> 
>> Also I have tried setting COLLATION to nothing (recommended by Alaska
>> PDR for clipper comatability and to ASCII (-1) which seems to be the
>> clipper default. Setting to either value seems to make no difference
>> in the apps behavior as I have tried both for lengths of time.
>> 
>>     Dbeload( "ADSDBE", .F. )
>>   
>>     DbeSetDefault( "ADSDBE" )
>> 
>>      FOXCDX compatability - .CDX/.FPT
>>     DbeInfo( COMPONENT_DATA , ADSDBE_TBL_MODE, ADSDBE_CDX )
>>     DbeInfo( COMPONENT_ORDER, ADSDBE_TBL_MODE, ADSDBE_CDX )
>> 
>>       Clipper compatible locking
>>     DbeInfo(COMPONENT_DATA, ADSDBE_LOCK_MODE,ADSDBE_COMPATIBLE_LOCKING
>> )
>> 
>> 
>> I've been working on an app with ADS 12 for a while and this is the
>> first issue I've run into.
>> 
>> Re using dbsetdescend() I'm not sure if I can. The index actually has
>> ascending values too as in:
>> 
>> descend(if(empty(date),'99999999',dtos(date)))+id
>> 
>> which puts the rows in ascending ID order within the descending dates
>> with empty dates coming first.
>> 
>> Using dbsetdescend() means I'd have to use descend(ID) I think e.g.
>> descend this key:
>> 
>> if(empty(date),'99999999',dtos(date))+descend(ID)
>> 
>> Do I have that right?
>> 
>> Thanks again,
>> Tim
>> 
>> On Fri, 18 May 2018 15:20:22 -0600, Jonathan Leeming wrote:
>> 
>>>
>>> Hi Tim,
>>>
>>> Based on some prior postings I'm guessing that you are tables from
>>> within Advantage's Dictionary as I tried to replicate your situation
>>> except I use ADS with free tables...
>>>
>>> DbeSetDefault( "ADSDBE" )
>>> DbeInfo( COMPONENT_DATA, ADSDBE_TBL_MODE, ADSDBE_CDX )
>>> DbeInfo( COMPONENT_ORDER, ADSDBE_INDEX_EXT, "CDX" )
>>>
>>> I have heard from others that Advantage 12 has problems with Xbase++ but
>>> have no direct experience on this so it may happen with ADS 11 and
>>> dictionaries too.
>>>
>>> As a work around is there any way you could adjust your logic and try
>>> using DbSetDescend( <lDescend> )?
>>>
>>> Regards... Jonathan
>
>Your if(empty(date),'99999999',dtos(date))+descend(ID) is how what I 
>would try as well but it starts getting "silly" descending and descending!
>
>Do you have access to ADS 11 instead of 12 to try?  I use a two user 
>developer version of 11 that does not need a license.
>
>Regards... Jonathan

I could try ADS 11 but that's not a practical solution as we are
already committed to ADS 12 on other fronts.

Trying to get some help from the ADS support forums as well. I'll post
any results I get.

Tim

On Tue, 22 May 2018 15:50:24 -0700, Tim Callahan wrote:

According to Alaska support there is no gaurantee of the value
computed by DESCEND() in ADS will be the same as that which is
computed by xBase++.

Based on extensive testing there is no way I can find to configure ADS
to have these values matched (mainly testing collations, languages and
table drivers.)

My solution is to modify my code (84 instances) to either add extra
indexes where needed or to implement short dbskip() loops to position
to the proper record after getting close with a non-descending seek.

I can't use the index descend property as our keys are a mix of
descending and ascending values - there would always be something that
needed to be descended.

Pretty big gotcha,
>
>>On 5/22/2018 2:50 PM, Tim Callahan wrote:
>>> Hi Jonathon,
>>> 
>>> Thanks for the reply.
>>> 
>>> I am indeed opening the files from within an advantage data
>>> dictionary. My dbe is setup as shown below.
>>> 
>>> The indexes are also used/created by Clipper 5.2 apps.
>>> 
>>> Also I have tried setting COLLATION to nothing (recommended by Alaska
>>> PDR for clipper comatability and to ASCII (-1) which seems to be the
>>> clipper default. Setting to either value seems to make no difference
>>> in the apps behavior as I have tried both for lengths of time.
>>> 
>>>     Dbeload( "ADSDBE", .F. )
>>>   
>>>     DbeSetDefault( "ADSDBE" )
>>> 
>>>      FOXCDX compatability - .CDX/.FPT
>>>     DbeInfo( COMPONENT_DATA , ADSDBE_TBL_MODE, ADSDBE_CDX )
>>>     DbeInfo( COMPONENT_ORDER, ADSDBE_TBL_MODE, ADSDBE_CDX )
>>> 
>>>       Clipper compatible locking
>>>     DbeInfo(COMPONENT_DATA, ADSDBE_LOCK_MODE,ADSDBE_COMPATIBLE_LOCKING
>>> )
>>> 
>>> 
>>> I've been working on an app with ADS 12 for a while and this is the
>>> first issue I've run into.
>>> 
>>> Re using dbsetdescend() I'm not sure if I can. The index actually has
>>> ascending values too as in:
>>> 
>>> descend(if(empty(date),'99999999',dtos(date)))+id
>>> 
>>> which puts the rows in ascending ID order within the descending dates
>>> with empty dates coming first.
>>> 
>>> Using dbsetdescend() means I'd have to use descend(ID) I think e.g.
>>> descend this key:
>>> 
>>> if(empty(date),'99999999',dtos(date))+descend(ID)
>>> 
>>> Do I have that right?
>>> 
>>> Thanks again,
>>> Tim
>>> 
>>> On Fri, 18 May 2018 15:20:22 -0600, Jonathan Leeming wrote:
>>> 
>>>>
>>>> Hi Tim,
>>>>
>>>> Based on some prior postings I'm guessing that you are tables from
>>>> within Advantage's Dictionary as I tried to replicate your situation
>>>> except I use ADS with free tables...
>>>>
>>>> DbeSetDefault( "ADSDBE" )
>>>> DbeInfo( COMPONENT_DATA, ADSDBE_TBL_MODE, ADSDBE_CDX )
>>>> DbeInfo( COMPONENT_ORDER, ADSDBE_INDEX_EXT, "CDX" )
>>>>
>>>> I have heard from others that Advantage 12 has problems with Xbase++ but
>>>> have no direct experience on this so it may happen with ADS 11 and
>>>> dictionaries too.
>>>>
>>>> As a work around is there any way you could adjust your logic and try
>>>> using DbSetDescend( <lDescend> )?
>>>>
>>>> Regards... Jonathan
>>
>>Your if(empty(date),'99999999',dtos(date))+descend(ID) is how what I 
>>would try as well but it starts getting "silly" descending and descending!
>>
>>Do you have access to ADS 11 instead of 12 to try?  I use a two user 
>>developer version of 11 that does not need a license.
>>
>>Regards... Jonathan
>
>I could try ADS 11 but that's not a practical solution as we are
>already committed to ADS 12 on other fronts.
>
>Trying to get some help from the ADS support forums as well. I'll post
>any results I get.
>
>Tim