Home > Archive > Clipper > June 2006 > Comparing input to database fields.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Comparing input to database fields.
|
|
| Mack Barss 2006-06-24, 6:55 pm |
| Hello All,
Hoping that someone can help me out. Its been a long time since I did any
programming in Clipper and I am banging my head off the wall trying to
figure out what I am doing wrong.
I am reading a input text file that has 1 employee record per line, but each
record has fields. I read the file 1 line at a time and compare it to
records in a database. If the record matches a record in the database, then
I'm printing it out to a "in master database" text file. If no match is
found, then I'm writing it out to a "gap file" or "not in master" file. I
have it creating the text files, but it does not produce any output in the
text file.
I have to compare all fields that I'm reading in to the corresponding fields
in the database record (except for the REVFILE field in both). I have
pasted my source code below my sig.
Mack
//*
//* privsmnu.prg source code
//*
#include "fileio.ch"
//* function load temporary privilege dbf
Function Prv_qProcess
local iEmpnum, iCcde, iUsrid, iSysid
local iPrecust, iSecpack, iUsrpriv
local prvEmpnum, prvCcde, prvUsrid, prvSysid
local prvPrecust, privSecpack, prvUsrpriv
local cntmsg, qUsr, dUsr, mycnt := 0
local mrt, mrb, mcl, mcr, msg_scrn
local rFilename, tmpFilename
tmpFileName := popupdir('C:\IAREVAL\INPUT\*.txt','D','Select Compare file',;
"w+/b,w+/r",.t.)
rFilename := "C:\IAREVAL\INPUT\" + tmpFilename
gFilename := "C:\IAREVAL\REPORTS\GAPFILE.TXT"
mFilename := "C:\IAREVAL\REPORTS\INMASTER.TXT"
if varlen(rFileName) < 20
return
end
ft_fselect(1)
rfile := ft_fuse(rFilename)
ft_fselect(2)
gFile := fcreate(gFilename,FO_READWRITE)
gFile := fopen(gFilename, FO_READWRITE + FO_SHARED)
gFile := ft_fuse(gFilename)
ft_fselect(3)
mFile := fcreate(mFilename,FO_READWRITE)
mFile := fopen(mFilename, FO_READWRITE + FO_SHARED)
mFile := ft_fuse(mFilename)
qUsr := ft_dispmsg( { { "Press [Y] to continue with", ;
"process. Press any other ", ;
"key to abort the process. " }, ;
{ "r/w", "r/w", "r/w" } } , ;
"Y" )
if qUsr = .t.
use PRIVQDBF index PRIVQDBF shared new
plswait(.t.,"Processing compare...")
ft_fselect(1)
rFile := ft_fuse(rFilename)
ft_fgotop()
do while ! ft_feof()
* read line
rline = ft_freadln(rFile)
mycnt = mycnt + 1
iEmpnum := substr(rline, 1, 6)
iCcde := substr(rline, 18, 3)
iUsrid := substr(rline, 28, 20)
iSysid := substr(rline, 49, 18)
iPrecust := substr(rline, 68, 20)
iSecpack := substr(rline, 89, 16)
iUsrpriv := substr(rline,106, 45)
iRevFile := substr(rline,152, 44)
select PRIVQDBF
dbgotop()
do while !eof()
BEGIN SEQUENCE
if iEmpnum = Empnum .and. iCcde = Ccde .and. iUsrid = Usrid ;
.and. iSysid = Sysid .and. iSecPack = Secpack .and. iUsrpriv =
Usrpriv
//* write dbf record to txt file *//
tmplne := empnum + ccde + usrid + sysid + precust + secpack +
usrpriv + revfile
fwrite(mfile,tmplne)
BREAK
else
forget()
skip
endif
End
end
//* record not found - write to gap file *//
tmplne := iEmpnum + iCcde + iUsrid + iSysid + iPrecust + iSecpack +
iUsrpriv + iRevfile
fwrite(gfile,tmplne)
tmplne := ""
ft_fselect(1)
ft_fskip()
enddo
close all
plswait(.f.)
msg_scrn = savescreen(0,0,Maxrow(),79)
msgcolor = "n/w,n/w,,,n/w"
setcolor(msgcolor)
mrt := maxrow()/2-2
mrb := maxrow()/2+1
mcl := maxcol()/2-9
mcr := maxcol()/2+9
clearbox(mrt,mcl,mrb,mcr,'Single')
@mrt+1,mcl+1 say " # Records read:"
@mrt+2,mcl+3 say " ==> " + ltrim(str(mycnt))
inkey(20)
setcolor(defColor)
restscreen(0,0,Maxrow(),79,msg_scrn)
endif
return nil
*: Eof: privsmnu.prg
| |
| Stephen Quinn 2006-06-24, 9:55 pm |
| Mack
> gFile := fopen(gFilename, FO_READWRITE + FO_SHARED)
This returns a file handle
> gFile := ft_fuse(gFilename)
What happens to it here??
Is it still a valid file handle??
Same thing with the other files you open.
> if iEmpnum = Empnum .and. iCcde = Ccde .and. iUsrid = Usrid ;
> .and. iSysid = Sysid .and. iSecPack = Secpack .and. iUsrpriv =
> Usrpriv
I have to assume that ALL your fields are CHARACTER type
- ie No Logic or Numeric
> use PRIVQDBF index PRIVQDBF shared new
You open an index but never use it.
You'd get better performance if you indexed the DBF on EmpNum and s ed it,
then test the other fields, instead of reading each record in the loop.
Eg
IF PRIVQDBF->( DBS( iEmpNum ) ) )
IF iCcde = Ccde .and. iUsrid = Usrid .and. iSysid = Sysid .and. ;
iSecPack = Secpack .and. iUsrpriv = Usrpriv
// Write to master file
ENDIF
ENDIF
// Write to gap file
Easiest way to find out where things go wrong is to put the stuff on screen.
You show no code that closes the TEXT files.
Eg
FClose( gFile ), etc...
HTH
Steve
| |
| Mack Barss 2006-06-24, 9:55 pm |
| Hi Steve,
Thanks for replying.
I actually took the gFile := ftuse and mFile := out from under the open
statements. Made a backup of the source prg
> This returns a file handle
>
> What happens to it here??
> Is it still a valid file handle??
>
> Same thing with the other files you open.
They are supposed to be valid file handles.. I was coding it based on some
of the examples I saw in the manual. I most
likely did it wrong. I correct my code so that the files get opened, then
changed my fwrite line to say
fwrite(gFile,tmplne) etc.
>
> I have to assume that ALL your fields are CHARACTER type
> - ie No Logic or Numeric
You are correct, all the fields are character type.
> You open an index but never use it.
>
> You'd get better performance if you indexed the DBF on EmpNum and sed
> it, then test the other fields, instead of reading each record in the
> loop.
> Eg
> IF PRIVQDBF->( DBS( iEmpNum ) ) )
> IF iCcde = Ccde .and. iUsrid = Usrid .and. iSysid = Sysid
> .and. ;
> iSecPack = Secpack .and. iUsrpriv = Usrpriv
> // Write to master file
> ENDIF
> ENDIF
> // Write to gap file
>
> Easiest way to find out where things go wrong is to put the stuff on
> screen.
I'll add in a couple of SAY to see what results I'm getting.
> You show no code that closes the TEXT files.
> Eg
> FClose( gFile ), etc...
Added the code to close the text files.. thanks... forgot about closing
them.
Mack
| |
| Stephen Quinn 2006-06-24, 9:55 pm |
| Mack
I just read the ng about the FT_F*() functions
Use one or the other but not both
> gFile := fopen(gFilename, FO_READWRITE + FO_SHARED)
The default opening here is FO_READ not FO_READWRITE so change the call to
gFile := ft_fuse(gFilename, FO_READWRITE + FO_SHARED)
Instead of FClose( gFile ) it should be
FT_FSELECT(1)
FT_FUSE()
HTH
Steve
| |
| Mack Barss 2006-06-25, 6:55 pm |
| Hi Steve,
Made a few changes and I can see that the dbs is working now for
comparing the text file records to the database. But I"m still not getting
any records written to the text output files.
????????????????????????????????????????????????????????????????????????????????
? Privilege Users Searhes Maintenance
?
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
??? Browse Input Files
?????????????????????????????????????????????????????????
??? Quarterly Compare
?????????????????????????????????????????????????????????
??? Quarterly Database
?????????????????????????????????????????????????????????
??? Annual Compare
?????????????????????????????????????????????????????????
??? Annual Database
?????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
?????????????????????? Identit???????????????????nt
????????????????????????????
?????????????????????? Rev? # Records read: ?
????????????????????????????
?????????????????????? ? ==> 5731 ?
????????????????????????????
?????????????????????? Co???????????????????
????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
?CV3UBV??BTUSHINGHAM ADMINISTRATORS
??????
?CV3UBV??BTUSHING *ALLOBJ
??????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
Above is a screen shot. On the of the screen shot the first cv3ubv line is
the line from the text file and the one underneth is from the database.
I'm pretty sure that I coded the write to text file correctly. I am telling
that if it finds a match to write to the inmaster text file and if it
doesn't to write to the gapfile text file. Below is what my code looks like
now:
//*
//* privsmnu.prg source code
//*
#include "fileio.ch"
//* function load temporary privilege dbf
Function Prv_qProcess
local iEmpnum, iCcde, iUsrid, iSysid
local iPrecust, iSecpack, iUsrpriv
local prvEmpnum, prvCcde, prvUsrid, prvSysid
local prvPrecust, privSecpack, prvUsrpriv
local cntmsg, qUsr, dUsr, mycnt := 0
local mrt, mrb, mcl, mcr, msg_scrn
local rFilename, tmpFilename
tmpFileName := popupdir('C:\IAREVAL\INPUT\*.txt','D','Select Compare file',;
"w+/b,w+/r",.t.)
rFilename := "C:\IAREVAL\INPUT\" + tmpFilename
gFilename := "C:\IAREVAL\REPORTS\GAPFILE.TXT"
mFilename := "C:\IAREVAL\REPORTS\INMASTER.TXT"
if varlen(rFileName) < 20
return
end
ft_fselect(1)
rfile := ft_fuse(rFilename)
fcreate(mFilename)
fcreate(gFilename)
ft_fselect(2)
mFile := ft_fuse(mFilename)
ft_fselect(3)
gFile := ft_fuse(gFilename)
qUsr := ft_dispmsg( { { "Press [Y] to continue with", ;
"process. Press any other ", ;
"key to abort the process. " }, ;
{ "r/w", "r/w", "r/w" } } , ;
"Y" )
revcnt := 0
mstcnt := 0
gapcnt := 0
if qUsr = .t.
use PRIVQDBF index EMPNUM shared new
plswait(.t.,"Processing compare...")
ft_fselect(1)
rFile := ft_fuse(rFilename)
ft_fgotop()
do while ! ft_feof()
* read line
rline = ft_freadln(rFile)
revcnt = revcnt + 1
iEmpnum := substr(rline, 1, 6)
iCcde := substr(rline, 17, 3)
iUsrid := substr(rline, 28, 20)
iSysid := substr(rline, 49, 18)
iPrecust := substr(rline, 68, 20)
iSecpack := substr(rline, 89, 16)
iUsrpriv := substr(rline,106, 45)
iRevFile := substr(rline,152, 44)
@ 20, 1 say iEmpnum
@ 20, 9 say iUsrid
@ 20, 29 say iUsrpriv
go top
if PRIVQDBF->( DBS( iEmpNum ) )
@ 21, 1 say empnum
@ 21, 9 say usrid
@ 21, 29 say usrpriv
if iCcde = Ccde .and. iUsrid = Usrid .and. iSysid = Sysid .and. ;
iSecPack = Secpack .and. iUsrpriv = Usrpriv
// Write to master file
tmplne := empnum + ccde + usrid + sysid + precust + secpack +
usrpriv + revfile
ft_fselect(2)
ft_fwriteln(tmplne,ft_feof())
mstcnt = mstcnt + 1
endif
endif
//* record not found - write to gap file *//
tmplne := iEmpnum + iCcde + iUsrid + iSysid + iPrecust + iSecpack +
iUsrpriv + iRevfile
ft_fselect(3)
ft_fwriteln(tmplne,ft_feof())
gapcnt = gapcnt + 1
tmplne := ""
ft_fselect(1)
ft_fskip()
enddo
ft_fselect(1)
ft_fuse()
ft_fselect(2)
ft_fuse()
ft_fselect(3)
ft_fuse()
plswait(.f.)
msg_scrn = savescreen(0,0,Maxrow(),79)
msgcolor = "n/w,n/w,,,n/w"
setcolor(msgcolor)
mrt := maxrow()/2-2
mrb := maxrow()/2+1
mcl := maxcol()/2-9
mcr := maxcol()/2+9
clearbox(mrt,mcl,mrb,mcr,'Single')
@mrt+1,mcl+1 say " # Records read:"
@mrt+2,mcl+3 say " ==> " + ltrim(str(revcnt))
inkey(20)
setcolor(defColor)
restscreen(0,0,Maxrow(),79,msg_scrn)
endif
return nil
*: Eof: privsmnu.prg
| |
| Markus Wiederstein 2006-06-25, 6:55 pm |
| Am Sun, 25 Jun 2006 18:34:12 +0200 hat Mack Barss <mbarss@shaw.ca> =
geschrieben:
> fcreate(mFilename)
> fcreate(gFilename)
This creates the files and opens them, so you probably cannot open them =
=
twice.
> ft_fselect(2)
> mFile :=3D ft_fuse(mFilename)
>
> ft_fselect(3)
> gFile :=3D ft_fuse(gFilename)
try this, i'm not familiar with the ft_* functions so i simply close the=
=
files.
local nCr1,nCr2
if (nCr1 :=3D fcreate(mFilename)) >=3D 0
fclose(nCr1)
endif
if (nCr2 :=3D fcreate(gFilename)) >=3D 0
fclose(nCr2)
endif
if file(mFilename)
ft_fselect(2)
mFile :=3D ft_fuse(mFilename)
endif
if file(gFilename)
ft_fselect(3)
gFile :=3D ft_fuse(gFilename)
endif
Greetings from the fifa world cup nation,
MArkus
| |
| Markus Wiederstein 2006-06-25, 6:55 pm |
|
or better like this:
(i'm not familiar with the ft_* functions so i simply close the files)
local nCr
if (nCr :=3D fcreate(mFilename)) >=3D 0
fclose(nCr)
ft_fselect(2)
mFile :=3D ft_fuse(mFilename)
endif
if (nCr :=3D fcreate(gFilename)) >=3D 0
fclose(nCr)
ft_fselect(3)
gFile :=3D ft_fuse(gFilename)
endif
Greetings from the fifa world cup nation,
Markus
| |
| John F. Eldredge 2006-06-25, 6:55 pm |
| On Sun, 25 Jun 2006 16:34:12 GMT, "Mack Barss" <mbarss@shaw.ca> wrote:
>Hi Steve,
>
>Made a few changes and I can see that the dbs is working now for
>comparing the text file records to the database. But I"m still not getting
>any records written to the text output files.
I would suggest single-stepping through in interactive debug mode to
make sure that your program flow is going where you think it is going.
Also, the use of hard-coded workarea numbers is not a good idea. Doing
so risks using the same workarea number for some other table elsewhere
in the program, and thus reading from or writing to the wrong table.
A better practice is to use an alias, or to store the work area number
assigned by Clipper into a variable, and then use that variable when
you select a work area.
--
John F. Eldredge -- john@jfeldredge.com
PGP key available from http://pgp.mit.edu
"Reserve your right to think, for even to think wrongly is better
than not to think at all." -- Hypatia of Alexandria
| |
| Stephen Quinn 2006-06-25, 6:55 pm |
| Mack
Did you miss the follow up I posted about the mode flags in the FT_F*()
functions.
The file is opened in READONLY MODE (the default) using this syntax
> gFile := ft_fuse(gFilename)
> gFile := ft_fuse(gFilename, FO_READWRITE + FO_SHARED)
Your current logic will put all your data into the GAP file - changes below.
IF PRIVQDBF->( DBS( iEmpNum ) )
> if iCcde = Ccde .and. iUsrid = Usrid .and. iSysid = Sysid .and. ;
> iSecPack = Secpack .and. iUsrpriv = Usrpriv
> // Write to master file
> tmplne := empnum + ccde + usrid + sysid + precust + secpack +
> usrpriv + revfile
> ft_fselect(2)
> ft_fwriteln(tmplne,ft_feof())
> mstcnt = mstcnt + 1
> endif
ELSE
>
> //* record not found - write to gap file *//
> tmplne := iEmpnum + iCcde + iUsrid + iSysid + iPrecust + iSecpack +
> iUsrpriv + iRevfile
> ft_fselect(3)
> ft_fwriteln(tmplne,ft_feof())
> gapcnt = gapcnt + 1
ENDIF
> tmplne := ""
> ft_fselect(1)
> ft_fskip()
> enddo
The other thing to look at is if Empnum has duplicates, if so the above code
will only ever test the first found record sed and miss any subsequent
matches.
HTH
Steve
| |
| Mack Barss 2006-06-25, 6:55 pm |
| Hi Steve,
> Did you miss the follow up I posted about the mode flags in the FT_F*()
> functions.
Got the follow up you posted about the FT_F*() functions.
> The file is opened in READONLY MODE (the default) using this syntax
>
made the changes to the ft_fuse statements.
[color=darkred]
> Your current logic will put all your data into the GAP file - changes
> below.
>
> IF PRIVQDBF->( DBS( iEmpNum ) )
> ELSE
> ENDIF
>
> The other thing to look at is if Empnum has duplicates, if so the above
> code will only ever test the first found record sed and miss any
> subsequent matches.
The Empnum field will have duplicates as an employee does have userids on
various systems with various access privileges. Because of the way AS/400
access/privilege rules work and also those on the mainframe, there will be
one line in the input file indicating each access level for a userid on a
given platform. For example, employe abc123 has a userid of abc123ab on a
AS/400 system with access levels of *ALLOBJ, *IOSYSCFG, *JOBCTL, SAVSYS.
This means the input file will have 4 records with only the Usrpriv field
being different and if the Empnum is in the database, then there is a
possibility of 4 records, 1 for each access level. What I am trying to do
is only write out the records/access levels that in the input file but don't
exist in the database. I will then use the output from the "not in master"
report to find out why some one has an access level/privilege that was not
authorized.
Is it possible to extend the DBSEEK function to handle more then 1 field?
Ie: (DBS( iEmpNum->iCcde->iUsrId ) )
And here I thought I would never look at, or even play with Clipper again.
| |
|
|
| Mack Barss 2006-06-27, 6:55 pm |
| Good Afternoon Steve,
Just wanted to let you know that I have the program working the way it
should (thanks to your help).
You assistance was greatly appreciated.
Mack
| |
| Stephen Quinn 2006-06-27, 6:55 pm |
| Hi Mack
> Just wanted to let you know that I have the program working the way it should
> (thanks to your help).
>
> You assistance was greatly appreciated.
Good to hear and glad to be of help.
CYA
Steve
|
|
|
|
|