Hello Group,

I am running in to a problem that has me baffled.  I am reading data
from an input file and then performing a dbseek to see if the data
exists in the master database.  If the record exists, it writes it a
matched database and if it doesn't it writes the record to a non-
matched database.

I have the dbstruct coded as:

         anArray := DBStruct()
         nPos1 := privmstr->(fieldpos("Empnum"))
         nPos2 := privmstr->(fieldpos("Ccde"))
         nPos3 := privmstr->(fieldpos("Usrid"))
         nPos4 := privmstr->(fieldpos("Sysid"))
         nPos5 := privmstr->(fieldpos("Usrpriv"))
         nPos6 := privmstr->(fieldpos("Custcode"))

         dbEmpnum  := anArray[nPos1][DBS_LEN]
         dbCcde    := anArray[nPos2][DBS_LEN]
         dbUsrid   := anArray[nPos3][DBS_LEN]
         dbSysid   := anArray[nPos4][DBS_LEN]
         dbUsrpriv := anArray[nPos5][DBS_LEN]
         dbCustcode:= anArray[nPos6][DBS_LEN]

and I have the dbseek coded as:

       cSeekThis := PAD(iEmpnum,dbEmpnum) + PAD(iCcde,dbCcde) + ;
                           PAD(iUsrid,dbUsrid) + PAD(iSysid,dbSysid)
+ ;
                           PAD(iUsrpriv,dbUsrpriv)
+PAD(iCustcode,dbCustCode)
       DBSeek(cSeekThis)

The master database is indexed in the order appearing above

             empnum+ccde+usrid+sysid+usrpriv+custcode

The problem is that dbseek appears to be ignoring the custcode field
and matches the record using only the first 5 fields.

I am using ntx as the indexing.

Are limitations on the number of fileds that can be used in a dbseek?


    Mack

Dear Mack:

On Mar 26, 8:21=A0am, Mack <mba...@shaw.ca> wrote:
> Hello Group,
>
> I am running in to a problem that has me baffled. =A0I am reading data
> from an input file and then performing a dbseek to see if the data
> exists in the master database. =A0If the record exists, it writes it a
> matched database and if it doesn't it writes the record to a non-
> matched database.
>
> I have the dbstruct coded as:
>
> =A0 =A0 =A0 =A0 =A0anArray :=3D DBStruct()
> =A0 =A0 =A0 =A0 =A0nPos1 :=3D privmstr->(fieldpos("Empnum"))
> =A0 =A0 =A0 =A0 =A0nPos2 :=3D privmstr->(fieldpos("Ccde"))
> =A0 =A0 =A0 =A0 =A0nPos3 :=3D privmstr->(fieldpos("Usrid"))
> =A0 =A0 =A0 =A0 =A0nPos4 :=3D privmstr->(fieldpos("Sysid"))
> =A0 =A0 =A0 =A0 =A0nPos5 :=3D privmstr->(fieldpos("Usrpriv"))
> =A0 =A0 =A0 =A0 =A0nPos6 :=3D privmstr->(fieldpos("Custcode"))
>
> =A0 =A0 =A0 =A0 =A0dbEmpnum =A0:=3D anArray[nPos1][DBS_LEN]
> =A0 =A0 =A0 =A0 =A0dbCcde =A0 =A0:=3D anArray[nPos2][DBS_LEN]
> =A0 =A0 =A0 =A0 =A0dbUsrid =A0 :=3D anArray[nPos3][DBS_LEN]
> =A0 =A0 =A0 =A0 =A0dbSysid =A0 :=3D anArray[nPos4][DBS_LEN]
> =A0 =A0 =A0 =A0 =A0dbUsrpriv :=3D anArray[nPos5][DBS_LEN]
> =A0 =A0 =A0 =A0 =A0dbCustcode:=3D anArray[nPos6][DBS_LEN]

See the casing on dbCustcode...

> and I have the dbseek coded as:
>
> =A0 =A0 =A0 =A0cSeekThis :=3D PAD(iEmpnum,dbEmpnum) + PAD(iCcde,dbCcde) +=
 ;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0PAD(iUsrid,dbUsrid=
) + PAD(iSysid,dbSysid)
> + ;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0PAD(iUsrpriv,dbUsr=
priv)
> +PAD(iCustcode,dbCustCode)

You have a different casing here as dbCustCode.

> =A0 =A0 =A0 =A0DBSeek(cSeekThis)
>
> The master database is indexed in the order appearing above
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0empnum+ccde+usrid+sysid+usrpriv+custcode
>
> The problem is that dbseek appears to be ignoring the custcode field
> and matches the record using only the first 5 fields.
>
> I am using ntx as the indexing.
>
> Are limitations on the number of fileds that can be used in a dbseek?

No.  You do not reveal how (for example) iEmpnum gets a value... if it
is not a casing problem, we'll need to see that too.

David A. Smith

On Mar 26, 10:52=A0am, dlzc <dl...@cox.net> wrote:
> No. =A0You do not reveal how (for example) iEmpnum gets a value... if it
> is not a casing problem, we'll need to see that too.

Hi David,

I fixed the casing problem so that dbCustcode is the same and
recompiled.  Still have the same problem.

The database has all the records stored in upper case.  When I read in
the input file, I uppercasee the input line and then parse the fields.
iEmpnum, iCcde, iUsrid, iSysid, iUsrpriv and iCustcode are obtained
from performing a hb_atokens on the input line as the input file uses
| as field separators.

I know that the the iCustcode is getting populated as I see the field
populated in the databases as well as the log file that I write out.

I know that it is seeing the first 5 fields indicated due to if I I
change the data in the sysid field in the input file by one character
the results are as expected.

The results are the same if I index on the first 5 fields and exclude
the custcode.

If I read in an input file with 500 lines (records), it ends up
matching 498 and not matching 2.

When including custcode, read in 500 lines, it ends up matching 498
and not matching 2.  But it shouldn't have matched any records as the
custcode is not in the master database.  The records do exist with a
different customer code.

   Mack

On Mar 26, 10:39=A0am, Mack <mba...@shaw.ca> wrote:
> On Mar 26, 10:52=A0am,dlzc<dl...@cox.net> wrote:
>
> > No. =A0You do not reveal how (for example) iEmpnum gets a value... if i=
t
> > is not a casing problem, we'll need to see that too.
>
> Hi David,
>
> I fixed the casing problem so that dbCustcode is the same and
> recompiled. =A0Still have the same problem.
>
> The database has all the records stored in upper case. =A0When I read in
> the input file, I uppercasee the input line and then parse the fields.
> iEmpnum, iCcde, iUsrid, iSysid, iUsrpriv and iCustcode are obtained
> from performing a hb_atokens on the input line as the input file uses
> | as field separators.
>
> I know that the the iCustcode is getting populated as I see the field
> populated in the databases as well as the log file that I write out.
>
> I know that it is seeing the first 5 fields indicated due to if I I
> change the data in the sysid field in the input file by one character
> the results are as expected.
>
> The results are the same if I index on the first 5 fields and exclude
> the custcode.
>
> If I read in an input file with 500 lines (records), it ends up
> matching 498 and not matching 2.
>
> When including custcode, read in 500 lines, it ends up matching 498
> and not matching 2. =A0But it shouldn't have matched any records as the
> custcode is not in the master database. =A0The records do exist with a
> different customer code.

OK, then the last black magic I can offer is:
- verify the index does in fact have all five fields *when* you want
to run this code.  ? indexkey() somewhere you can view it.
- make sure atokens is returning *something* in custcode, and that
"iCustcode" is non zero
- check the settings of EXACT and SOFTSEEK
- finally, move custcode up in the index, just so you can stop
worrying about "too many fields".

And before all that, I'd make triply sure of your logic that is
supposed to bin copies of the records into different files.  Many
times I've written what I thought was right, and read it as my
intention was, rather than how the compiler reads it.  Maybe rewrite
yours a couple of different ways, and see if it changes your results.

David A. Smith

> When including custcode, read in 500 lines, it ends up matching 498
> and not matching 2.  But it shouldn't have matched any records as the
> custcode is not in the master database.  The records do exist with a
> different customer code.

It seems to be a SOFTSEEK problem....

I have checked the index and all the fields I require are indexed.  I
have verified this using DBU and all the fields are being populated
correctly.

Its as though the DbSeek was totally ignoring the last field on the
seek.

  cSeekThis := PAD(iEmpnum,dbEmpnum) + PAD(iCcde,dbCcde) + ;
                      PAD(iUsrid,dbUsrid) + PAD(iSysid,dbSysid)  + ;
                      PAD(iUsrpriv,dbUsrpriv) +
PAD(iCustcode,dbCustcode)
  DBSeek(cSeekThis)

Using the same index and coding either

  cSeekThis := PAD(iEmpnum,dbEmpnum) + PAD(iCcde,dbCcde) + ;
                      PAD(iUsrid,dbUsrid) + PAD(iSysid,dbSysid)  + ;
                      PAD(iUsrpriv,dbUsrpriv) +
PAD(iCustcode,dbCustcode)

or this

  cSeekThis := PAD(iEmpnum,dbEmpnum) + PAD(iCcde,dbCcde) + ;
                      PAD(iUsrid,dbUsrid) + PAD(iSysid,dbSysid)  + ;
                      PAD(iUsrpriv,dbUsrpriv)

produced the same results, when you would expect that if custcode
didn't match on the first example, that the results would be
different.

I then decided to use the same fields for indexing, but indexed in
this order:
        custcode+empnum+ccde+usrid+sysid+usrpriv
instead of
        empnum+ccde+usrid+sysid+usrpriv+custcode

and changed the dbseek order to
        cSeekThis := PAD(iCustcode,dbCustcode) + PAD(iEmpnum,dbEmpnum)
+ ;
                            PAD(iCcde,dbCcde) + PAD(iUsrid,dbUsrid)
+ ;
                            PAD(iSysid,dbSysid) +
PAD(iUsrpriv,dbUsrpriv)

I now get the correct results.  That is all I changed.  I did not
change any other coding in the routine.

Now I'm trying to figure why just by switching the index order that
routine works.

   Mack

Dear Mack:

On Mar 27, 2:52=A0pm, Mack <mba...@shaw.ca> wrote:
....
What is the maximum length of each field?
>         custcode+empnum+ccde+usrid+sysid+usrpriv

You said you were using NTX... it has a maximum key length of 256
characters.

David A. Smith

On Mar 27, 6:27=A0pm, dlzc <dl...@cox.net> wrote:
> What is the maximum length of each field?
>
> > =A0 =A0 =A0 =A0 custcode+empnum+ccde+usrid+sysid+usrpriv
>
> You said you were using NTX... it has a maximum key length of 256
> characters.

Hi David,

Yes, I am using ntx .... the field lengths are

      empnum - 7
      ccde - 3
      usrid - 80
      sysid - 80
      usrpriv - 150
      custcode - 50

which would give a total of 370.  All fields are character fields

Now I am really confused as the results should have been the same no
matter what order I used for the index and seek as I have exact turned
on.

I have only ever used ntx for doing indexing.  I have never coded
using cdx.

   Mack

Dear Mack:

On Mar 27, 6:20=A0pm, Mack <mba...@shaw.ca> wrote:
> On Mar 27, 6:27=A0pm,dlzc<dl...@cox.net> wrote:
>
> > What is the maximum length of each field?
>
> > > =A0 =A0 =A0 =A0 custcode+empnum+ccde+usrid+sysid+usrpriv
>
> > You said you were using NTX... it has a maximum
> > key length of 256 characters.
>
> Yes, I am using ntx .... the field lengths are
>
> =A0 =A0 =A0 empnum - 7
> =A0 =A0 =A0 ccde - 3
> =A0 =A0 =A0 usrid - 80
> =A0 =A0 =A0 sysid - 80
> =A0 =A0 =A0 usrpriv - 150
> =A0 =A0 =A0 custcode - 50
>
> which would give a total of 370. =A0All fields are
> character fields
>
> Now I am really confused as the results should
> have been the same no matter what order I used
> for the index and seek as I have exact turned on.

It seems that both the index key and the compared expression are
truncated.  This would yield the results you saw.

> I have only ever used ntx for doing indexing. =A0I
> have never coded using cdx.

No big deal.
REQUEST DBFCDX
.... executable code
RddSetDefault( "DBFCDX" )
.... or ...
USE <cFileName> ... via "DBFCDX"

Or just eliminate usrpriv from the index...

David A. Smith

On Mar 27, 10:28=A0pm, dlzc <dl...@cox.net> wrote:
> Dear Mack:
>
> On Mar 27, 6:20=A0pm, Mack <mba...@shaw.ca> wrote:
>
>
>
>
>
> > On Mar 27, 6:27=A0pm,dlzc<dl...@cox.net> wrote:
>
> > > What is the maximum length of each field?
>
> > > > =A0 =A0 =A0 =A0 custcode+empnum+ccde+usrid+sysid+usrpriv
>
> > > You said you were using NTX... it has a maximum
> > > key length of 256 characters.
>
> > Yes, I am using ntx .... the field lengths are
>
> > =A0 =A0 =A0 empnum - 7
> > =A0 =A0 =A0 ccde - 3
> > =A0 =A0 =A0 usrid - 80
> > =A0 =A0 =A0 sysid - 80
> > =A0 =A0 =A0 usrpriv - 150
> > =A0 =A0 =A0 custcode - 50
>
> > which would give a total of 370. =A0All fields are
> > character fields
>
> > Now I am really confused as the results should
> > have been the same no matter what order I used
> > for the index and seek as I have exact turned on.
>
> It seems that both the index key and the compared expression are
> truncated. =A0This would yield the results you saw.
>
> > I have only ever used ntx for doing indexing. =A0I
> > have never coded using cdx.
>
> No big deal.
> REQUEST DBFCDX
> ... executable code
> RddSetDefault( "DBFCDX" )
> ... or ...
> USE <cFileName> ... via "DBFCDX"
>
> Or just eliminate usrpriv from the index...
>
> David A. Smith- Hide quoted text -
>
> - Show quoted text -

Ok.. so I don't have to change my index commands .. just the rdd.

Unfornately, I can't eliminate the usrpriv.  All the fields I use in
the index are critical for the compare.

I thought I read somewhere that the key length for cdx was 220.  If I
am wrong, what is the key length for cdx?

   Mack

While, I changed my code from:

REQUEST DBFNTX

to

REQUEST DBFCDX

and deleted all the index files and recreated the indexes for the
databases.

Unfornately, it looks dbfcdx is also truncating as I got the same
results with the orginal dbseek coding.

Although I did notice that dbfcdx is alot faster at indexing.


   Mack

Dear Mack:

On Mar 27, 9:46=A0pm, Mack <mba...@shaw.ca> wrote:
> On Mar 27, 10:28=A0pm, dlzc <dl...@cox.net> wrote:
> > On Mar 27, 6:20=A0pm, Mack <mba...@shaw.ca> wrote:
> > > On Mar 27, 6:27=A0pm,dlzc<dl...@cox.net> wrote:
>
> > > > What is the maximum length of each field?
>
> > > > > =A0 =A0 =A0 =A0 custcode+empnum+ccde+usrid+sysid+usrpriv
>
> > > > You said you were using NTX... it has a maximum
> > > > key length of 256 characters.
>
> > > Yes, I am using ntx .... the field lengths are
>
> > > =A0 =A0 =A0 empnum - 7
> > > =A0 =A0 =A0 ccde - 3
> > > =A0 =A0 =A0 usrid - 80
> > > =A0 =A0 =A0 sysid - 80
> > > =A0 =A0 =A0 usrpriv - 150
> > > =A0 =A0 =A0 custcode - 50
>
> > > which would give a total of 370. =A0All fields are
> > > character fields
>
> > > Now I am really confused as the results should
> > > have been the same no matter what order I used
> > > for the index and seek as I have exact turned on.
>
> > It seems that both the index key and the compared
> > expression are truncated. =A0This would yield the
> > results you saw.
>
> > > I have only ever used ntx for doing indexing. =A0I
> > > have never coded using cdx.
>
> > No big deal.
> > REQUEST DBFCDX
> > ... executable code
> > RddSetDefault( "DBFCDX" )
> > ... or ...
> > USE <cFileName> ... via "DBFCDX"
>
> > Or just eliminate usrpriv from the index...
>
> Ok.. so I don't have to change my index
> commands .. just the rdd.
>
> Unfornately, I can't eliminate the usrpriv.

You must, at least from the index.

>=A0All the fields I use in the index are critical
> for the compare.

Set a filter on usrpriv then.

> I thought I read somewhere that the key length
> for cdx was 220. =A0If I am wrong, what is the key
> length for cdx?

I think it is 240 (upon further results).  SQL has limits at / near
900.

So you know what you wanted isn't going to work unless you:
- move to SQL,
- change field lengths *drastically*,
- change one or more of the indexed fields to
HB_Compress( <FieldName> ),
   [probably add a new field to the table, fill it with the compressed
value, and index on that]
- change one or more of the indexed fields to HB_MD5( <FieldName> )*,
or
   [probably add a new field to the table, fill it with the MD5 value,
and index on that]
- add a field that is the compressed result of your six fields, and
index on that
- manually walk through the data (then you don't need the index).

David A. Smith

* you'd need to make sure that you check and make sure that you get a
unique HB_MD5() result for each entry in <FieldName>.  Very unlikely
you wouldn't but...