grab new data

[inteleserve]

I am tring to write a simple program that will grab all the records from filea.dbf, import them into a fileb.dbf, then go back to filea.dbf and check for new records every five seconds grabbing only new records since the last time it checked the file....


any ideas?

Thanks,

Ross

 

[inteleserve]

I guess a loop like this is probably not possible..

Thanks,

 

[nickpatel]

is Filea.dbf erased after importing to fileb.dbf? If so then you are alway going to get new record (by some process of course that is populating you filea table). If you are not deleting records from Filea.dbf after importing to fileb, then you probably could use a datetime field to compare if new record have been added since last import. You can create a timer cycling thur every five seconds. Everytime you import, capture the time to property variable so you can fire you import routine when the time arrives. Here is my routine that updates files or insert files through timer.

with thisform
local a,h,y,s,b,d,c,k,l,e,ggg,lcOldOnError
dimension a__gen1(1,3) && hold data after conversion of date and time
dimension a__gen2(1,1)

******************************************************************************************
* initialize tables *
******************************************************************************************
delete from download_table1
delete from download_table2
delete from download_table3

CREATE CURSOR download_table1 (T1_ipin C(10), ;
T1_ccdn1 C(10), T1_ccdn2 C(10), T1_ds D, T1_ts C(4), ;
T1_sn C(3), T1_aft C(1), T1_df D, T1_tf C(4), ;
T1_cen C(9), T1_drp D, T1_prc C(1), T1_dh D, ;
T1_acn C(6), T1_acsc C(1), T1_di D, T1_acc C(5), ;
T1_dti C(1), T1_ss C(9), T1_nl C(25), T1_nmf C(15), ;
T1_nmi C(15), T1_ns C(3), T1_dlr D, T1_ntr C(3), ;
T1_ar C(4), T1_sex C(1), T1_rc C(1), T1_dob D, ;
T1_h C(3), T1_w C(3), T1_deci C(1), T1_ssn C(9), ;
T1_scd C(2), T1_dln C(10), T1_ciin C(9), T1_fbi C(10), ;
T1_sno C(7), T1_snm C(20), T1_sdt C(4), T1_city C(15), ;
T1_sc C(2), T1_zc C(9), T1_aut C(3), T1_aui C(4), ;
T1_pn C(10), T1_nls C(25), T1_nfs C(15), T1_nms C(15), ;
T1_sufs C(3), T1_iin C(10), T1_inin C(2), T1_acn1 C(6), ;
T1_s1 C(3), T1_vln1 C(8), T1_vmy1 C(4), T1_vmc1 C(4), ;
T1_vmlc1 C(3), T1_vsc1 C(2), T1_vcc11 C(3), T1_vcc21 C(3), ;
T1_scv1 C(2), T1_acn2 C(6), T1_s2 C(3), T1_vln2 C(8), ;
T1_vmy2 C(4), T1_vmc2 C(4), T1_vmlc2 C(3), T1_vsc2 C(2), ;
T1_vcc12 C(3), T1_vcc22 C(3), T1_scv2 C(2), T1_acn3 C(6), ;
T1_s3 C(3), T1_vln3 C(8), T1_vmy3 C(4), T1_vmc3 C(4), ;
T1_vmlc3 C(3), T1_vsc3 C(2), T1_vcc13 C(3), T1_vcc23 C(3), ;
T1_scv3 C(2), T1_acn4 C(6), T1_s4 C(3), T1_vln4 C(8), ;
T1_vmy4 C(4), T1_vmc4 C(4), T1_vmlc4 C(3), T1_vsc4 C(2), ;
T1_vcc14 C(3), T1_vcc24 C(3), T1_scv4 C(2), T1_acn5 C(6), ;
T1_dos D, T1_tos C(4), T1_actc C(1), T1_ccdn C(10),;
T1_dadc D, T1_dpe D, T1_rsc C(1), T1_fsc C(1), ;
T1_csc C(1), T1_ca C(20), T1_dda C(20), T1_ddsc C(1), ;
T1_dsa C(20), T1_tcc C(68), T1_si C(9), T1_sdd C(9), ;
T1_cssc C(1), T1_dcm D, T1_scf C(2), T1_cfc C(15),;
T1_tsc C(1), T1_dtm D, T1_cn C(8), T1_ysd C(4), ;
T1_msd C(2), T1_dsd C(3), T1_ysj C(4), T1_msj C(2), ;
T1_dsj C(3), T1_acn6 C(6), T1_dost D, T1_tost C(4), ;
T1_ai C(1), T1_dclm D, T1_dh2 D, T1_cdn C(2), ;
T1_th C(4), T1_dbw D, T1_cri C(1), T1_pri C(1), ;
T1_npt C(1), T1_yp C(2), T1_mp C(2), T1_dp C(3), ;
T1_ic C(1), T1_hci C(1), T1_ys C(4), T1_ms C(2), ;
T1_dys C(3), T1_wij C(3), T1_ssc C(1), T1_sttc C(1), ;
T1_sri C(1), T1_nri C(1), T1_ari C(1), T1_ci C(1), ;
T1_cdi C(1), T1_ddi C(1), T1_hi C(1), T1_blank C(22) )


CREATE CURSOR download_table2 ( T2_ipin C(10), ;
T2_ccdn C(10), T2_nl C(25), T2_nf C(15), T2_nm C(15), ;
T2_dob D, T2_rolc C(5), T2_cdn C(2), T2_cd C(30), ;
T2_cil C(20), T2_blank C(10) )


CREATE CURSOR download_table3 ( T3_ipin C(10), ;
T3_ccdn C(10), T3_oc C(3), T3_os C(20), T3_ot C(1), ;
T3_dd C(30), T3_blank C(26))


******************************************************************************************
* process *
******************************************************************************************
adir(a__gen2,'C:\programming\programs\vfp7\Assign\DATA\Asgn*.*')
if vartype(a__gen2) # 'U' AND vartype(a__gen2) # 'L' && CHECK IF FILE FOUND OR NOT !!!!!!!
ggg = .text1.value
.text1.value = 'Updates in Progress'
.text1.refresh()
k = 'C:\programming\programs\vfp7\Assign\DATA\'
l = 'C:\programming\programs\vfp7\Assign\tofile\'
s = alen(a__gen2)
s = s/5
rand(-1)
for y = 1 to s
a = a__gen2(y,3)
a = dtoc(a)
a =(alltrim(strtran(a, '/',''))) && convert date by removing slashes
a = substr(a,1,4) && parse date
b = a__gen2(y,1) && get file name from adir array
f = b && name of file being moved (source)
b = substr(b,5,4) && parse file to determine type of table name to be designated
d = rand() && generate random number
d = alltrim(str(d*10000)) && random number
e = '.txt'
c = alltrim(b+a+d+e) && new file name to be moved as
a__gen1(y,1) = a__gen2(y,3)

a__gen1(y,2) = a__gen2(y,4)
a__gen1(y,3) = c
sour = k+f && source location of files defined
dest = l+c && destination location of files defined
* Save the original error handler
.continue_on = .t.
lcOldOnError = ON("ERROR")
ON ERROR .continue_on = .f.
rename (sour) to (dest)
ON ERROR &lcOldOnError
if .continue_on = .t.
do case

case alltrim(b) = 'PROB'
select download_table1 && probation table
appe from (dest) type sdf && append probation file to table

case alltrim(b) = 'CRTC'
select download_table2 && court case table
appe from (dest) type sdf

case alltrim(b) = 'CTCH'
select download_table3 && charges table
appe from (dest) type sdf

endcase
endif
if y < s
dimension a__gen1(alen(a__gen1, 1) + 1, alen(a__gen1, 2)) &&increase array by 1 row
endif
endfor
release a__gen2
release a__gen1

*****************************************************************************************************************
* Person table data massage *
* any additional fields added to the query should also reflect in alen calc process *
* where a1 = a1/(number of fields in select statement) *
*****************************************************************************************************************
local c1 && loop counter for a__gen1
local a1 && length of a__gen1 array
local q && terminates date conversion loop
local g && move .g property valued date into variable for update in person_tbl
local fd1, fd2, fd3, fd4, fd5, fd6, fd7, fd8, fd9 && field use to update person tbl move array -> variable

dimension a__gen1(1,1) && hold combined info of download_table 1 & 2

SELECT Download_table1.t1_ipin, Download_table1.t1_ccdn1,;
Download_table1.t1_dh, Download_table1.t1_acn, Download_table2.t2_ipin,;
Download_table2.t2_ccdn, Download_table2.t2_nl, Download_table2.t2_nf,;
Download_table2.t2_nm, Download_table2.t2_rolc, Download_table2.t2_cdn;
FROM assign_dbc!download_table1 INNER JOIN assign_dbc!download_table2 ;
ON Download_table1.t1_ipin == Download_table2.t2_ipin;
AND Download_table1.t1_ccdn1 == Download_table2.t2_ccdn into array a__gen1

if vartype(a__gen1) # 'U' AND vartype(a__gen1) # 'L'
a1 = alen(a__gen1)
a1 = a1/11 && make sure you change this (11) if you add more fields to select
for c1 = 1 to a1
dimension a__gen64(1,1) && calculate date for dictation due date
.g = a__gen1(c1,3)
.g = .g - 14
q = .f.
do while q <> .t.
adel(a__gen64, 1)
select count(h_holiday) from holiday_tbl ;
where h_holiday = .g into array a__gen64
.i = a__gen64(1,1)
if .i = 1
.g = .g - 1
else
q = .t.
endif
enddo
g = .g && move calculated date to variable for update
release a__gen64

.cr1 = a__gen1(c1,1) && t1_ipin
.cr1 = .cr1 + a__gen1(c1,2) && t1_ccdn1

fd1 = a__gen1(c1,4) && p_file_no
fd2 = alltrim(a__gen1(c1,7)) && p_lname
fd3 = alltrim(a__gen1(c1,8)) && p_fname
fd4 = alltrim(a__gen1(c1,9)) && p_mi
fd5 = alltrim(a__gen1(c1,10)) && p_crt_loc
fd6 = alltrim(a__gen1(c1,11)) && P_crt_deptno
cr1 = alltrim(.cr1) && move involved person Id & courtcase to variable
fd7 = alltrim(a__gen1(c1,1)) && involved person Id number
fd8 = alltrim(a__gen1(c1,2)) && courtcase number
fd9 = a__gen1(c1,3) && date sentenced (hearing date)

select p_id from person_tbl where p_id == alltrim(.cr1) into array a__gen2
if vartype(a__gen2) # 'U' AND vartype(a__gen2) # 'L' && means p_id record does exists &&if1
release a__gen2 && record found
update person_tbl set person_tbl.p_inv_pid = fd7, ; && if record found do update
person_tbl.p_crtcase = fd8, ;
person_tbl.p_dte_due = g, ;
person_tbl.p_lname = fd2, ;
person_tbl.p_fname = fd3, ;
person_tbl.p_mi = fd4, ;
person_tbl.p_dte_sent = fd9, ;
person_tbl.p_crt_loc = fd5, ;
person_tbl.p_crt_deptno = fd6, ;
person_tbl.p_fileno = fd1 ;
where p_id == cr1
else
insert into person_tbl (p_id, ;
p_inv_pid, ;
p_crtcase, ;
p_dte_due, ;
p_lname, ;
p_fname, ;
p_mi, ;
p_dte_sent, ;
p_crt_loc, ;
p_crt_deptno, ;
p_fileno) ;
values (cr1, fd7, fd8, g, fd2, fd3, fd4, fd9, fd5, fd6, fd1)
endif &&if1
endfor
endif

release a__gen1
release a__gen2
*************************************************************************************************************
* charges table data massage *
*************************************************************************************************************
local c2 && loop counter for a__gen1
local a2 && length of a__gen1 array
local fdA, fdB, fdC, fdD, fdE, fdF, fdG, fdH, fdI && field use to update charges tbl move array -> variable

&& searches string and deletes
delete for !('PLED' $ t3_dd) and !('CERTIFIED' $ t3_dd) and !('FOUND' $ t3_dd) in download_table3
dimension a__gen1(1,1) && hold combined info of download_table 1 & 2

SELECT Download_table3.t3_ipin, Download_table3.t3_ccdn, Download_table3.t3_oc, ;
Download_table3.t3_os, Download_table3.t3_ot, Download_table3.t3_dd ;
FROM assign_dbc!download_table3 into array a__gen1


if vartype(a__gen1) # 'U' AND vartype(a__gen1) # 'L'
a2 = alen(a__gen1)
a2 = a2/6 && make sure you change this (11) if you add more fields to select
for c2 = 1 to a2


.cr2 = a__gen1(c2,1) && t3_ipin
.cr2 = .cr2 + a__gen1(c2,2) && t3_ccdn1


fdA = alltrim(.cr2) && c_uniqueid
fdB = alltrim(a__gen1(c2,1)) && c_inv_pid
fdC = alltrim(a__gen1(c2,2)) && c_crt_case
fdD = alltrim(a__gen1(c2,3)) && c_offcode
fdE = alltrim(a__gen1(c2,4)) && c_offsec
fdF = alltrim(a__gen1(c2,5)) && c_offtype
fdG = alltrim(a__gen1(c2,6)) && c_dispn
insert into charges_tbl (c_uniqueid, ;
c_inv_pid, ;
c_crt_case, ;
c_offcode, ;
c_offsec, ;
c_offtype, ;
c_dispn) ;
values (fdA, fdB, fdC, fdD, fdE, fdF, fdG)
endfor
endif

release a__gen1
release a__gen2
*************************************************************************************************************
* move file to savefile folder *
*************************************************************************************************************
adir(a__gen2, 'C:\programming\programs\vfp7\Assign\tofile\*.*')
k = 'C:\programming\programs\vfp7\Assign\tofile\'
l = 'C:\programming\programs\vfp7\Assign\SAVEFILE\'
if vartype(a__gen2) # 'U' AND vartype(a__gen2) # 'L' && CHECK IF FILE FOUND OR NOT !!!!!!!
s = alen(a__gen2)
s = s/5
for y = 1 to s
fil = a__gen2(y,1)
sour = k+fil && source location of files defined
dest = l+fil && destination location of files defined

lcOldOnError = ON("ERROR")
* Issue ON ERROR with the name of a procedure
ON ERROR no_move = .f.
rename (sour) to (dest)
ON ERROR &lcOldOnError
endfor
endif
release a__gen2

*************************************************************************************************************
* delete 2 weeks old file from save folder *
*************************************************************************************************************
adir(a__gen2, 'C:\programming\programs\vfp7\Assign\SAVEFILE\*.*')
l = 'C:\programming\programs\vfp7\Assign\SAVEFILE\' && destination directory


if vartype(a__gen2) # 'U' AND vartype(a__gen2) # 'L' && CHECK IF FILE FOUND OR NOT !!!!!!!
s = alen(a__gen2)
s = s/5
for y = 1 to s
filename = a__gen2(y,1)
filedate = a__gen2(y,3)
olddate = date() - 14
if filedate <= olddate && if file is more that 2 weeks old, then delete the file
sour = l+filename && source location of files defined
.continue_on = .t.
cerrhandler = on('error') && catch error if file does not exist in location
on error no_delete = .f.
ofs = createobject('scripting.filesystemobject') && move files to destionation
result =ofs.deletefile(sour) && allow a delete
on error &cerrhandler
endif
endfor
endif
release a__gen2
.text1.value = ggg
.text1.refresh()
endif
endwith

Nick Patel

 

[inteleserve]

Are you serious?

My eyeballs just popped out of my head!!

 

[nickpatel]

oops, sorry I accidently posted wrong code. I was thinking that maybe you can create a table where only new records are populated and after you import, you delete those records. Then using a run a timer routine that checks that table for new record.

again sorry about the code.

Nick Patel

 

[nickpatel]

here is a sample code. t1 = timer

*init
with thisform
.t1.interval = set to whatever time
.t1.enabled = .t.
.refresh()
endwith

*t1 timer
with thisform
local a
.poptable
.cleanup
.text2.value = 'Timer On'
.text2.refresh()
endwith



*poptable
select fileb
append from filea


*cleanup
use filea exclusive
set safety off
zap
set safety on

hope this helps
nick Patel

 

[DSummZZZ]

If you are able, modify the structure of tablea to include a logical field named something like, "copied".
Then, you can SCAN the table and only get the un-copied records for tableb. Like this:

SELECT tablea
SCAN FOR !copied
   SCATTER MEMVAR MEMO
   INSERT INTO tableb FROM MEMVAR
   REPLACE copied WITH .T.
ENDSCAN

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[darrellblackhawk]

I'm assuming this is a multi-user app.

If so, maybe adding a time field would be helpful.

Then it's just a matter of performing a select statement
with the where clause set to next interval.

Darrell

Here's an example:

Run 2 instances of VFP.


Place this code in instance#1 and run it.
[tt]
Create table (addbs(sys(2023))+"FileA") free (datafld1 c(20), lastupdate t)
Create table (addbs(sys(2023))+"FileB") free (datafld1 c(20))
close data all
use (addbs(sys(2023))+"FileA") in 0 shared

Local i
Public oTimer
For i = 1 to 100
Insert into FileA (datafld1,lastupdate) values ("Data"+transform(i),datetime())
Next

oTimer = createobject("clsTimer")

Define class clsTimer as timer
Interval = 5100
Enabled = .t.

Procedure timer
This.enabled = .f.
Local i
For i = 1 to 10
Insert into FileA (dataFld1,lastupdate) values ;
("Data"+transform(reccount("FileA")),datetime())
Next
This.enabled = .t.
Endproc
Enddefine
[/tt]



In instance#2 place the following code and run it:
(Note: This code may be susceptible to "Race-Conditions", so you'll need to play with it)
[tt]
Local oForm
oForm = createobject("clsForm")
oForm.show()
Read events

Define class clsForm as form
DataSession = 2

Add object grid1 as grid with ;
top = 10, ;
left = 10, ;
width = this.width - 20, ;
height = this.height - 20, ;
recordsource = "tmpData"

Add object tmrGetData as clsTimer

Procedure load
Use (addbs(sys(2023))+"FileB") in 0 shared
Endproc

Procedure destroy
Clear events
Endproc
Enddefine

Define class clsTimer as timer
dLastTime = DATETIME(year(date()), month(date()), day(date()), 0, 0)


Interval = 5000
Enabled = .t.

Procedure timer
This.enabled = .f.
Local dCurTime
dCurTime = Datetime()

Select * from (addbs(sys(2023))+"FileA") a ;
where between(a.lastupdate,this.dLastTime,dCurTime) ;
into cursor tmpData

Thisform.grid1.recordsource = "tmpData"
This.dLastTime = dCurTime+1

* Perform update to FileB here...

This.enabled = .t.
Endproc
Enddefine
[/tt]