Synchronizing MySQL cusror adaptor with VFP table 2

[bluesw3]

Im searching for a procedure to keep a MySQL table up to date with changes made to my local vfp database. I need something that checks for changes in existing records, updates them, then adds any new records. This seems difficult without indexing. Any suggestions?

 

[Mike Gagnon]

bluesw3

Here is a portion of code I use as a scheduled task on a Windows 2000 server. Don't forget you need the MySql ODBC driver to be able to accomplish this.

Local GetConnHandle,;
	GetQuery,;
	lnaddRec
Close Databases All
lnaddRec = .F.
Set Exclusive Off
Cd Sys(5)+Curdir()
Open Database suntel
Use viewanirec1 In 0 Shared
Set Multilocks On  && Must be on for table buffering
= CursorSetProp('Buffering', 5, 'viewanirec1' )
GetConnHandle = SQLConnect('65.39.231.152','root','test')
If GetConnHandle > 0
	Select viewanirec1
	Scan
		GetQuery = SQLEXEC(GetConnHandle,'Select cAni from Customer where cAni='+viewanirec1.cani)
		If GetQuery> 0
			Select * From Sqlresult Into Cursor isthere
			If _Tally > 0
				lnaddRec = .F.
			Else
				lnaddRec = .T.
			Endif

			uppStr = Str(viewanirec1.lnActive)

			If lnaddRec = .T.
				GetQuery = SQLEXEC(GetConnHandle,'Insert into Customer (cAni,nActive,nLang) values ('+viewanirec1.cani+','+uppStr+','+Str(viewanirec1.nlang)+')')
			Else

				GetQuery = SQLEXEC(GetConnHandle,'Update Customer set nActive ='+uppStr+',nlang = '+Str(viewanirec1.nlang) +'  where cAni='+viewanirec1.cani)
			Endif
			If GetQuery> 0
				Select viewanirec1
				Replace viewanirec1.lisup With .T.,;
					viewanirec1.tUpdate With Datetime()

			Endif
		Endif

	Endscan

	SQLDISCONNECT(GetConnHandle)
	Select viewanirec1
	Scan
		If lisup
			Select viewanirec1
			Replace lndone With .T.
			Tableupdate()
		Endif
	Endscan

Endif
Close Databases All

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[bluesw3]

Thanks, that will certainly get me in the right direction!