*!* This program is example of using ODBC to update tables and uses
timestamp to take care of record lock when updating and
*!* inserting. It uses set prop to make sure tables can be reverted
in case of error. It is very important to have .001 time
*!* delay in inserting and updating records because FoxPro does not
give time in thousand of a second. In putting .001 it
*!* makes sure that you get all unique records.
*****************************************************************************************************************************
*!* Make table with four fields Date --- date() *
*!* Sec --- 10,5 numeric use SECOUND() function *
*!* Name --- Character 10 *
*!* Lname--- Character 10 *
*********************************************************************************************
clear
ERROR1 =.f.
UPDATELOCK = 0
set safety off
sqldis(0)
connection = sqlconnect('VfpTables'," "," ")
?connection
nprop= SqlSetpro(connection,"transactions",2)
if nprop = 1
for i= 1 to 100
wait window('wait..') timeout .001 && important delay for time stamp
wait window("Updating Tabel " + str(i)) nowait
Query1 ="Select * from datetime "
NRESULT = SQLEXEC(connection,Query1,'temp')
if NRESULT !=1
=aerror(x)
=messagebox(x(2)+ chr(13)+Query1)
=SQLDISCONNECT(connection)
ERROR1 = .t.
endif
select * from temp into table counter
use in counter
do while UPDATELOCK = 0 && to make sure record is not changed
Query1 = "Select *,"+str(second(),10,4)+" AS NEW_TIME,"+; && update
current time to timestamp in temp table
str(i)+" AS CREATSTAMP"+; && this line is not needed
" From counter "+;
" WHERE counter.lastname = 'KHAN'"
NRESULT = SQLEXEC(connection,Query1,'temp')
if NRESULT !=1
=aerror(x)
=messagebox(x(2)+ chr(13)+Query1)
=SQLDISCONNECT(connection)
ERROR1 = .t.
endif
Query1= "update datetime set name ='AAA',Sec
="+str(temp.New_Time,10,4)+" where LastName ='KHAN'"&& update real
table with temp timestamp
NRESULT= SQLEXEC(connection,Query1)
if NRESULT != 1
=aerror(x)
ERROR1 = .t.
=messagebox(x(2)+ chr(13)+Query1)
wait window " Insert failed."
ERROR1= .t.
endif
Query1 = " select * from datetime "+;
" WHERE Lastname= '"+alltrim(temp.Lastname)+"' AND Sec
="+str(temp.New_Time,10,4)&& if time in real table =time in temp table
you can update
NRESULT = SQLEXEC(connection,Query1,'Temp1')
if NRESULT != 1
=aerror(x)
ERROR1 = .t.
=messagebox(x(2)+ chr(13)+Query1)
wait window " Query failed."
ERROR1= .t.
else
if reccount()> 0 && as long as record is zero you keep trying to
update.
UPDATELOCK =1&& as soon as u get record which means you have
unique timestamp and u get out of the loop
endif
endif
enddo
if ERROR1 = .t.
=sqlrollback(connection)
ERROR1 = .f.
messagebox ('Some Error Happened Nothing Saved',32,'Error')
exit
else
=sqlcommit(connection)
endif
endfor
wait clear
else
messagebox('Set ProP Error ',32,'Error')
endif