MySql to Paradox

Hi experts

I am using Delphi 7 and Direct Mysql Objects v.1.1 to access MySql database from Delphi application. I downloaded these commponents from http://www.webclass.ru/eng/Tutorials/Delphi/MySQL_and_Delphi.html.

I can access succesfully to my database on REMOTE server from Delphi.
-------------------------------------------------------------------------------------------------------------------------------------------
Now, I expand my needs...
-------------------------------------------------------------------------------------------------------------------------------------------
- I have two (clients) same applications which, in same time (one client in same time write to two diferent databases), write to local PARADOX (*.DB) database throught BDE and to REMOTE MySql database throught MySql Objects 1.1.
- E.G.
  First client write: "INSERT INTO sites VALUES(5,"Google","http://www.google.com/" and
  Second write    : "INSERT INTO sites VALUES(6,"Yahoo","http://www.yahoo.com/"

Now I want> When user of any CLIENT click on button "Synchronize" to synchronize local PARADOX with REMOTE MySql database. In other words> Application need to check if MySql database (table) have more records then PARADOX table, and if,to copy (first probaby convert)  records to LOCAL PARADOX table.

P.S.  Post Your comments ASAP!
Thanks in advance

Marko
LVL 2
prevarantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BlackTigerXCommented:
a common practice would be to have an extra field in both tables, say called Sync tinyint, with values 0 or 1

when you insert a record, or update a record, you put a value of 0 in that record

so then when is time to synchronize the databases you simply query both tables
where Sync = 0
and you get all the records that need to be synchronized to the other table

if both tables definitions match you can even construct the insert statement in the fly directly from the fields names in the other query, something like:

var
  theQuery:string;
  X:Integer;
  theFields:TStringList;
begin
...
Query1.SQL.Text:='select Field1, Field2, Field2, Sync from Table1 where Sync = 0';
Query1.Open;

  //grab the list of fields that will help us construct the query on the fly for the insert (sync to the other table)
  theFields:=TStringList.Create;
  for X:=0 to Query1.Fields.Count-1 do
    theFields.Add(Query1.Fields[X].Name); //you could use the same method as this one, to gather the full list of fields for the initial select statement, excluding the primary key field simply by doing a 'select top 1 * from table1'

while not (Query1.EOF) do
begin
  //create the query string
  theQuery:='insert into table2 values(';
  for X:=0 to theFields.Count-1 do
    theQuery:=theQuery+Query1.FieldValues[theFields[X]]+',';
 
  Delete(theQuery, Length(theQuery), 1); //delete the last char, wich should be a comma

  Query2.SQL.Text:=theQuery; //assign it to the query object

  Query2.ExecSQL; //and execute it
  //then another query here to update the Sync field to 1
end

one important thing to remember is that you cannot keep primary keys synchronized, so you might want to have another field that makes each record unique and that allows you to identify that record in both tables

didn't actually test the code, but I hope you get the idea

best regards
BlackTigerXCommented:
I can already see a bug :-S...
kretzschmarCommented:
>I can already see a bug :-S...
query1.next is missed ;-))

btw. if using paradox  as to sync,
it would be better to use a ttable like
....
while not (Query1.EOF) do
begin
  //in case to decide if update or insert
  if pdxTable.locate(fieldlist,[ValueList],[]) then  //pdxtable is the paradoxtable
  begin
     pdxTable.Edit;
     pdxTabel.FieldByName('FieldName').Value := query1.FieldByName('FieldName').Value;
     //more fields, in case of needed conversion use the as... methods (like asString)
     //for complex converions you have to care yourself
     pdxTable.post;
  else  //insert
     pdxTable.Insert;  //or append
     pdxTabel.FieldByName('FieldName').Value := query1.FieldByName('FieldName').Value;
     //more fields, in case of needed conversion use the as... methods (like asString)
     //for complex converions you have to care yourself
     pdxTable.post;
   end;
   query1.next;  //the missed one ;-)
end;  

btw. if the connection to both databases is though bde,
then the btachtmove-method could be used also or
the TBatchMove-Component (but only if there are no complex conversions)

meikl ;-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

prevarantAuthor Commented:
:::Black TigerX:::

I have TWO clients!
If FIRST write 1 to field SECOND will not synchronize with THAT field because "Sync" is 1. NOT 0...

Regards
Marko
BlackTigerXCommented:
doesn't matter how many clients you have, as long as evey time you add a record, or update a record, you change the field "Sync" to a 0, you will know at any time which records need to be synched to the other table

do you mean you have two clients writing records to the tables?
you can even have a third client just to take care of the synching (is this a word?)

"query1.next is missed ;-))"
...that too :-|

and I wouldn't recommend using a TTable, specially if the tables grow in considerable size
kretzschmarCommented:
:-))
i recommend a ttable only because its paradox and if used the bde
(which is at least the native driver for paradox) and queries do then
create temporary tables (which costs time)

meikl ;-)
kretzschmarCommented:
btw. if updates are issued, then best would be a unique index
on the Paradoxtable and unsing the findkey-method rather then the locate-method

meikl ;-)
BlackTigerXCommented:
in that case, you're right ;-), or at least I agree with you =o), for Paradox/BDE probably won't be any better to use a query, for the  mySQL definetely use a query
RadikalQ3Commented:
kretzschmar: Locate method use indexes when available... so is fast like findkey.

Is better use a timestamp field instead a integer field in the update indicator field, so... this lets make the update without 'reset' the indicator field in the MySql table when update is made, and... lets make update for more than 1 client.
Or... instead of a timestamp, use a shortint in the Sync field but, increase the Sync field, not just set 0 or 1 value... like a 'version indicator' for each record...

prevarantAuthor Commented:
I have two (or more) clients writing records to the tables.
E.G. First client is located in Berlin and Second in Paris.

Is there another way? I mean without "Sync" field.
Because I will need "Sync" fields as manu as Clients.
Am I wrong?

Regards
Marko
RadikalQ3Commented:
You can use just one Sync field for several clients.

When you write a record in the MySql remote table, put in the Sync field (a TDateTime field, by example) the actual time (Now;)

Each client must be store (localy, in a INI file, by example or in the registry) the DateTime of the last update made, and for capture the new or modified recors, simply make a select in the remote table, filtering the records with a value in the Sync Field greater than the LastUpdate local variable of the client.
Later, you have a query with the new or modified records... compare them with the records in the local table, creating the new records, updating the existing record (and deleting the deleted records)...

illusion_chaserCommented:
First of all, You need to identify every update with an ID.

You can implement TCPConnection between server and clients. Each time Server DB is updated it will send "Sync" message to all other clients (except for the one that updated the Server DB). "Sync" msg will include the new UpdateID. Clients, when received the "Sync" message will retrieve new data from the server (using the UpdateID, also see my second comment) update their own DBs. I would also advise to use XML for the "Sync" message and store in it all the data needed for a sync notification/request.

Consider using TClientDataSet (with its XML capabilities). Meaning that when Client needs to retrieve data from Server it sends a requet and Server replies with XML buit by TClientDataSet (giving the client only the new data).

ic
RadikalQ3Commented:
illusion_chaser: That method just works if all statios are 'on-line' in the same instant when database is updated...
illusion_chaserCommented:
Right, then Clients should ask the Server "if there were any updates?" every once in a while.
illusion_chaserCommented:
When Client becomes On-Line, for example.
RadikalQ3Commented:
I think is better using a datetimestamp... so permit no-dependent working between clients
illusion_chaserCommented:
I don't think "datetimestamp" is reliable.
Sometimes ppl tend to play with the date/time settings of their computer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.