dbServer:seek failure in 2.8 -CDX

Pedro

>> I suggest you set a reltionship between the Ingredients and RecDetails
>> table, that way you wouldn't have to seek into the child table at all.
>>
How would it work in a one to many relationship?
<<
That's what a relationship is each child has a link to it's parent
Eg
ParentID = 1234 // Parent DBF
ChildID = 9874 // Child DBF
Child_ParentID = 1234
ChildID = 9875
Child_ParentID = 1234
ChildID = 9876
Child_ParentID = 1234

> Why are you using a string index on the child server or for that matter
> why
> a numeric on the master??
>>
In the back of my mind I remember that numeric indexes were faster
(don't know if it was dBase, Clipper, or VO)
The reason for a string index on the child server is because is
concatenated with another field i.e. index on
Str(Field2,7)+Str(Field1,5)
<<

As I said
> If your using CDX then it's easy to add temp orders (in a temp file) as
> required.
Creating temp index orders on the fly is trivial
Eg
oServer:CreateOrder( '_TEMP', SELF:cTmpIdxName, Str( Field2, 7 ),
etc...)

>>
You're right! all FC names are defines. This was implemented as a way
to make the reverse engineering of some of my tables more difficult. I
live in a country that has piracy as a national sport... (as an
example the iTunes store cannot be reached from here)
<<
If they have your tables it makes no matter how you reference the fields
within the app., just makes it more work for the developer to understand the
code.

<<
A final comment "> IMO string indices are more reliable than numeric
ones." MAYBE THIS IS THE SOURCE OF MY PROBLEM.
>>
Possibly - I (have) never use(d) numeric indices in production code.

CYA
Steve

Pedro,

> A final comment "> IMO string indices are more reliable than numeric
> ones." MAYBE THIS IS THE SOURCE OF MY PROBLEM.

No, absolutely NOT. There is no difference in reliability whatsoever. In
fact, numeric indexes (which obviously includes dates) are far faster
than string indexes. Numeric indexes are much more efficient and
reliable for numeric comparisons but are less flexible if you need to
combine multiple columns into the expression. If you have a single
column index and it is inherently numeric then it is a bad idea to
convert it to a string. You will need to StrZero() the value to get
proper comparisons. This is a nuisance and only involves overhead.

Horses for courses. Choose the index based on its merit and whether or
not it needs to be combined but there are no issues of reliability here.

Geoff

Pedro M. Espinosa wrote:
> On Aug 28, 3:37 am, "Stephen Quinn" <stevej...@SPbigpond.AMnet.au>
> wrote:
>> Pedro
>>
>> A better construct is
>>
>> if self:mydbServer:Seek(nKey)
>> do while ( self:mydbServer:Fieldget( #Whatever ) = nKey )
>> self:mydbServer:LockCurrentRecord()
>> self:mydbServer:FieldPut(72, new value)
>> self:mydbServer:skip()
>> enddo
>> self:mydbServer:Commit()
>> self:mydbServer:Unlock()
>> else
>> tone(300,3)
>> endif
>>
>> CYA
>> Steve
>
> Steve and Paul
> This was a simplification of the problem.
> the Do loop is for another server who gives me the value of nKey and
> obviously can change or stay the same (and there I have the skip!).
> Let me try to explain a little of the bussiness logic. This is a
> Recipes database, and in the previous code self:mydbServer is the
> Master table.
> The code belongs to the pricing of the ingredients, and when an
> ingridient price changes I want to update the price of all the Recipes
> that contain that ingredient. In order not to update every time a
> single price is modified, I just marked the ingridients which prices
> changed, and do all the update just before the user finishes updating
> the prices.
> In my test case I updated the price of two ingridients of the same
> recipe, and that's the reason why I seek twice for the same record.
>
> I hope that this explanation answers the "what's the use of a do-loop
> when you don't skip?" and the need of the Do while.
> As for the other suggestions, I don't need the
> do while ( self:mydbServer:Fieldget( #Whatever ) = nKey )
> as the Master table has unique keys
>
> The Geoff comment. I also tried a Gotop() before the if
> self:mydbServer:Seek(nKey)
> statement but that apparently didn't refresh the server, and besides I
> thought that the seek would allways find the record no matter where
> the pointer is.
>
> The Gunter comment. The reason I do the commit inside the loop is that
> I use the field that I uptdate (the price) to recalc the new value
> (price). I'm not certain if without the commit I get the updated
> value. I know it takes a little longer but I wanted to be sure the
> data is actually written.
>
> The Oscar comment. As I explained above there is only one 40001 record
> and the keyfield is never updated, once written.
>
> Finally, here is the full code: - You can skip to the line
> // Actualizar Recetas **** HERE COMES THE FAULTY (?) CODE
> dbRecDetail is the detail table
> dbRecetas is the master table (one to many relashionship)
>
> method Guardar() class Precios
> // gIngreds is the Ingridients dbServer
> /* Every ingridient can have up to 5 differnt suppliers and one is
> chosen by the user as the preferred one
> */
> LOCAL nClave as dword // Ingredient number
> LOCAL j as dword
> LOCAL nNumPrv as dword // Suppiler nbr
> LOCAL nReceta as dword // Recipe nbr
> LOCAL nDifPrecio as Float // Price difference
> LOCAL nPrecio as Float // price
> LOCAL nRecno as dword
> nRecno := selfDCBrIngredientes:Server:Recno // bBrowser with
> bArrayServer
> selfDCBrIngredientes:Server:SetFilter({|Server|
> Server:Modificad}) // updated price
> selfDCBrIngredientes:Server:GoTop()
> do while .not. selfDCBrIngredientes:Server:Eof
> nClave := selfDCBrIngredientes:Server:FIELDGET(#Clave)
> // Actualizar Ingredientes
> if gIngreds:Seek(nClave)
> gIngreds:LockCurrentRecord()
> gIngreds:FIELDPUT(FINumProvdr,
> selfDCBrIngredientes:Server:FIELDGET(#Proveedor) )
> gIngreds:FIELDPUT(FIPrecio,
> selfDCBrIngredientes:Server:FIELDGET(#Precio))
> gIngreds:FIELDPUT(FIUnidad_cmp,
> selfDCBrIngredientes:Server:FIELDGET(#UnidadCmp) )
> gIngreds:FIELDPUT(FICantd_cmp,
> selfDCBrIngredientes:Server:FIELDGET(#CantidadC) )
> gIngreds:FIELDPUT(FIPrecio_100,
> selfDCBrIngredientes:Server:FIELDGET(#Precio100) )
> gIngreds:Commit()
> gIngreds:Unlock()
> endif
> // Actualizar Recetas **** HERE COMES THE FAULTY (?) CODE
> self:dbRecDetail:Seek(Str(nClave,7),true)
> do while self:dbRecDetail:FIELDGET(#RiCveIng) == nClave
> nReceta := self:dbRecDetail:FIELDGET(#RiReceta)
> nDifPrecio := Round(;
> (selfDCBrIngredientes:Server:FIELDGET(#Precio100 ) - ;
> selfDCBrIngredientes:Server:FIELDGET(#PrOrig100) ) * ;
> self:dbRecDetail:FIELDGET(#PesoNeto) / 100,2)
> // self:dbRecetas:GoTop() DIDN'T WORK
> if self:dbRecetas:Seek(nReceta)
> self:dbRecetas:LockCurrentRecord()
> nPrecio := self:dbRecetas:FIELDGET(FCPRECIO) + ;
> Round(nDifPrecio/self:dbRecetas:FIELDGET(FCRDPORCION),2)
> self:dbRecetas:FIELDPUT(FCPRECIO, nPrecio)
> self:dbRecetas:Commit()
> self:dbRecetas:Unlock()
> self:dbRecetas:Skip()
> else
> Tone(100, 3) /* THIS OF COURSE IS JUST TO CHECK IF SOMETHING IS
> WRONG */
> endif
> self:dbRecDetail:Skip()
> enddo
> // Cambia de Registro
> selfDCBrIngredientes:Server:FIELDPUT(#Modificad, false)
> selfDCBrIngredientes:Server:Skip()
> enddo
> Return nil

Hi Pedro,

well I agree that the "faulty" code looks OK to me.

You've said you had a workaround, namely putting a skip() after the
unlock?
I suppose it is the SELF:dbRecetas:Skip() ?
You could try with a skip(0).

Anyway, you have solved your problem if I understand you well?

--
Paul

On Aug 29, 11:31*pm, "Geoff Schaller"
<geo...@softxwareobjectives.com.au> wrote:
> Pedro,
>
> > A final comment "> IMO string indices are more reliable than numeric
> > ones." MAYBE THIS IS THE SOURCE OF MY PROBLEM.
>
> No, absolutely NOT. There is no difference in reliability whatsoever. In
> fact, numeric indexes (which obviously includes dates) are far faster
> than string indexes. Numeric indexes are much more efficient and
> reliable for numeric comparisons but are less flexible if you need to
> combine multiple columns into the expression. If you have a single
> column index and it is inherently numeric then it is a bad idea to
> convert it to a string. You will need to StrZero() the value to get
> proper comparisons. This is a nuisance and only involves overhead.
>
> Horses for courses. Choose the index based on its merit and whether or
> not it needs to be combined but there are no issues of reliability here.
>
> Geoff

Geoff
Thanks for refreshing my memory regarding numeric indexes
Pedro

On Sep 1, 2:32*am, "Paul D B" <poll...@NOMORESPAMhnt.be> wrote:
> Pedro M. Espinosa wrote:
> > On Aug 28, 3:37 am, "Stephen Quinn" <stevej...@SPbigpond.AMnet.au>
> > wrote:
> >> Pedro
>
> >> A better construct is
>
> >> if self:mydbServer:Seek(nKey)
> >> do while ( self:mydbServer:Fieldget( #Whatever ) = nKey )
> >> self:mydbServer:LockCurrentRecord()
> >> self:mydbServer:FieldPut(72, new value)
> >> self:mydbServer:skip()
> >> enddo
> >> self:mydbServer:Commit()
> >> self:mydbServer:Unlock()
> >> else
> >> tone(300,3)
> >> endif
>
> >> CYA
> >> Steve
>
> > Steve and Paul
> > This was a simplification of the problem.
> > the Do loop is for another server who gives me the value of nKey and
> > obviously can change or stay the same (and there I have the skip!).
> > Let me try to explain a little of the bussiness logic. This is a
> > Recipes database, and in the previous code self:mydbServer is the
> > Master table.
> > The code belongs to the pricing of the ingredients, and when an
> > ingridient price changes I want to update the price of all the Recipes
> > that contain that ingredient. In order not to update every time a
> > single price is modified, I just marked the ingridients which prices
> > changed, and do all the update just before the user finishes updating
> > the prices.
> > In my test case I updated the price of two ingridients of the same
> > recipe, and that's the reason why I seek twice for the same record.
>
> > I hope that this explanation answers the "what's the use of a do-loop
> > when you don't skip?" and the need of the Do while.
> > As for the other suggestions, I don't need the
> > * * * * do while ( self:mydbServer:Fieldget( #Whatever ) = nKey )
> > as the Master table has unique keys
>
> > The Geoff comment. I also tried a Gotop() before the if
> > self:mydbServer:Seek(nKey)
> > statement but that apparently didn't refresh the server, and besides I
> > thought that the seek would allways find the record no matter where
> > the pointer is.
>
> > The Gunter comment. The reason I do the commit inside the loop is that
> > I use the field that I uptdate (the price) to recalc the new value
> > (price). I'm not certain if without the commit I get the updated
> > value. I know it takes a little longer but I wanted to be sure the
> > data is actually written.
>
> > The Oscar comment. As I explained above there is only one 40001 record
> > and the keyfield is never updated, once written.
>
> > Finally, here is the full code: - You can skip to the line
> > // Actualizar Recetas **** HERE COMES THE FAULTY (?) CODE
> > dbRecDetail is the detail table
> > dbRecetas is the master table (one to many relashionship)
>
> > method Guardar() class Precios
> > // gIngreds is the Ingridients dbServer
> > /* Every ingridient can have up to 5 differnt suppliers and one is
> > chosen by the user as the preferred one
> > */
> > LOCAL nClave as dword // Ingredient number
> > LOCAL j as dword
> > LOCAL nNumPrv as dword // Suppiler nbr
> > LOCAL nReceta as dword // Recipe nbr
> > LOCAL nDifPrecio as Float // Price difference
> > LOCAL nPrecio as Float // price
> > LOCAL nRecno as dword
> > nRecno := selfDCBrIngredientes:Server:Recno // bBrowser with
> > bArrayServer
> > selfDCBrIngredientes:Server:SetFilter({|Server|
> > Server:Modificad}) // updated price
> > selfDCBrIngredientes:Server:GoTop()
> > do while .not. selfDCBrIngredientes:Server:Eof
> > * *nClave := selfDCBrIngredientes:Server:FIELDGET(#Clave)
> > * * *// Actualizar Ingredientes
> > * *if gIngreds:Seek(nClave)
> > * * * gIngreds:LockCurrentRecord()
> > * * * gIngreds:FIELDPUT(FINumProvdr,
> > selfDCBrIngredientes:Server:FIELDGET(#Proveedor) )
> > * * * gIngreds:FIELDPUT(FIPrecio,
> > selfDCBrIngredientes:Server:FIELDGET(#Precio))
> > * * * gIngreds:FIELDPUT(FIUnidad_cmp,
> > selfDCBrIngredientes:Server:FIELDGET(#UnidadCmp) )
> > * * * gIngreds:FIELDPUT(FICantd_cmp,
> > selfDCBrIngredientes:Server:FIELDGET(#CantidadC) )
> > * * * gIngreds:FIELDPUT(FIPrecio_100,
> > selfDCBrIngredientes:Server:FIELDGET(#Precio100) )
> > * * * gIngreds:Commit()
> > * * * gIngreds:Unlock()
> > * *endif
> > * *// Actualizar Recetas **** HERE COMES THE FAULTY (?) CODE
> > * *self:dbRecDetail:Seek(Str(nClave,7),true)
> > * *do while self:dbRecDetail:FIELDGET(#RiCveIng) == nClave
> > * * * nReceta := self:dbRecDetail:FIELDGET(#RiReceta)
> > * * *nDifPrecio *:= Round(;
> > (selfDCBrIngredientes:Server:FIELDGET(#Precio100 ) - ;
> > selfDCBrIngredientes:Server:FIELDGET(#PrOrig100) ) * ;
> > self:dbRecDetail:FIELDGET(#PesoNeto) / 100,2)
> > // * * *self:dbRecetas:GoTop() DIDN'T WORK
> > * * *if self:dbRecetas:Seek(nReceta)
> > self:dbRecetas:LockCurrentRecord()
> > nPrecio := self:dbRecetas:FIELDGET(FCPRECIO) + ;
> > Round(nDifPrecio/self:dbRecetas:FIELDGET(FCRDPORCION),2)
> > self:dbRecetas:FIELDPUT(FCPRECIO, nPrecio)
> > self:dbRecetas:Commit()
> > self:dbRecetas:Unlock()
> > self:dbRecetas:Skip()
> > * * * else
> > Tone(100, 3) /* THIS OF COURSE IS JUST TO CHECK IF SOMETHING IS
> > WRONG */
> > * * * endif
> > * * * self:dbRecDetail:Skip()
> > enddo
> > // Cambia de Registro
> > selfDCBrIngredientes:Server:FIELDPUT(#Modificad, false)
> > selfDCBrIngredientes:Server:Skip()
> > * * *enddo
> > Return nil
>
> Hi Pedro,
>
> well I agree that the "faulty" code looks OK to me.
>
> You've said you had a workaround, namely putting a skip() after the
> unlock?
> I suppose it is the SELF:dbRecetas:Skip() *?
> You could try with a skip(0).
>
> Anyway, you have solved your problem if I understand you well?
>
> --
> Paul- Hide quoted text -
>
> - Show quoted text -

Paul:
Thanks for agreeing with me on the code.
Is Skip(0) faster? otherwise it woudn't make any difference as I will
seek each time arround.

> Anyway, you have solved your problem if I understand you well?
I think so, at least with my test data.

Pedro

On Aug 29, 8:19*pm, "Stephen Quinn" <stevej...@SPbigpond.AMnet.au>
wrote:
> Pedro
>
> >> I suggest you set a reltionship between the Ingredients and RecDetails
> >> table, that way you wouldn't have to seek into the child table at all.
>
> How would it work in a one to many relationship?
> <<
> That's what a relationship is each child has a link to it's parent
> Eg
> ParentID = 1234 * * *// Parent DBF
> * * ChildID = 9874 * *// Child DBF
> * * Child_ParentID = 1234
> * * ChildID = 9875
> * * Child_ParentID = 1234
> * * ChildID = 9876
> * * Child_ParentID = 1234
>
> > Why are you using a string index on the child server or for that matter
> > why
> > a numeric on the master??
>
> In the back of my mind I remember that numeric indexes were faster
> (don't know if it was dBase, Clipper, or VO)
> The reason for a string index on the child server is because is
> concatenated with another field i.e. index on
> Str(Field2,7)+Str(Field1,5)
> <<
>
> As I said> If your using CDX then it's easy to add temp orders (in a tempfile) as
> > required.
>
> Creating temp index orders on the fly is trivial
> Eg
> * * oServer:CreateOrder( '_TEMP', SELF:cTmpIdxName, Str( Field2, 7 ),
> etc...)
>
>
>
> You're right! all FC names are defines. This was implemented as a way
> to make the reverse engineering of some of my tables more difficult. I
> live in a country that has piracy as a national sport... (as an
> example the iTunes store cannot be reached from here)
> <<
> If they have your tables it makes no matter how you reference the fields
> within the app., just makes it more work for the developer to understand the
> code.
>
> <<
> A final comment "> IMO string indices are more reliable than numeric
> ones." MAYBE THIS IS THE SOURCE OF MY PROBLEM.
>
> Possibly - I (have) never use(d) numeric indices in production code.
>
> CYA
> Steve

Steve:
I do understand the SetRelation concept but in my case wouldn't work
as Ingredients is actually two DBF's, the first is the one I
distribute and the second one is the one that the user updates and
both are used concurrently depending on the ingredient number (I
stripped that code to make it more readable). I was trying to point
out that you suggested the inverse relation....

If you see Geoff comment, he agrees with me on the numeric indexes

I agree with you in that creating a temp index is trivial, but time
consuming when you're dealing with 5,000 + records...

> If they have your tables it makes no matter how you reference the fields
> within the app., just makes it more work for the developer to understand the
> code.

Here I disagree with you completely. The tables have meaningless names
i.e. Field37 or Field 73, and the code makes much more sense if it
says FieldGet(Name) or FieldGet(Price). I only distribute the exe file
along with the tables, not the source code.
(maybe the problem is that I didn't explained myself)

Pedro

Pedro

> If you see Geoff comment, he agrees with me on the numeric indexes
I read it and will only say that Geoff and I don't agree on everything<bg>

Outside of a test environment I never use numeric indices (and that is to
make sure that I shipped no products with a $0.00 price tag) but that's the
way I do things.

>>
Here I disagree with you completely. The tables have meaningless names
i.e. Field37 or Field 73, and the code makes much more sense if it
says FieldGet(Name) or FieldGet(Price). I only distribute the exe file
along with the tables, not the source code.
(maybe the problem is that I didn't explained myself)
<<
I see now, still it means you have to look up a list to find out what
'Field37' is when looking the DBF or do you have them all memorised<g>

>>
I agree with you in that creating a temp index is trivial, but time
consuming when you're dealing with 5,000 + records...
<<
Nothing is stopping you from making the extra tag permanent (disk space is
cheeeeep<bg>).

CYA
Steve

Pedro M. Espinosa wrote:
>
> Paul:
> Thanks for agreeing with me on the code.
> Is Skip(0) faster? otherwise it woudn't make any difference as I will
> seek each time arround.
>
Well just peeked into the SDK and no it will not make a difference
because:
IF IsNil(nRecordCount)
iRecords := 1

so it is the same as skip(1)
funny, I always thought skip(0) meant to stay on the current record


--

Paul

Paul D B ha usato la sua tastiera per scrivere :
> Well just peeked into the SDK and no it will not make a difference because:
> IF IsNil(nRecordCount)
> iRecords := 1
>
> so it is the same as skip(1)
> funny, I always thought skip(0) meant to stay on the current record

Paul,
Skip() is the same as Skip(1), not Skip(0)


Ciao
Stefano

On Sep 1, 11:19*pm, "Stephen Quinn" <stevej...@SPbigpond.AMnet.au>
wrote:
> Pedro
>
> > If you see Geoff comment, he agrees with me on the numeric indexes
>
> I read it and will only say that Geoff and I don't agree on everything<bg>
>
> Outside of a test environment I never use numeric indices (and that is to
> make sure that I shipped no products with a $0.00 price tag) but that's the
> way I do things.
>
>
>
> Here I disagree with you completely. The tables have meaningless names
> i.e. Field37 or Field 73, and the code makes much more sense if it
> says FieldGet(Name) or FieldGet(Price). I only distribute the exe file
> along with the tables, not the source code.
> (maybe the problem is that I didn't explained myself)
> <<
> I see now, still it means you have to look up a list to find out what
> 'Field37' is when looking the DBF or do you have them all memorised<g>
>
>
>
> I agree with you in that creating a temp index is trivial, but time
> consuming when you're dealing with 5,000 + records...
> <<
> Nothing is stopping you from making the extra tag permanent (disk space is
> cheeeeep<bg>).
>
> CYA
> Steve

Steve:

> I see now, still it means you have to look up a list to find out what
> 'Field37' is when looking the DBF or do you have them all memorised<g>

Actually I have two tables, the development one has all the names,
i.e. Name, Price, etc. and the distribution table has the Field37 and
I have a conversion routine to make things simpler.
Best regards
Pedro