Delphi ZEOSlib: read from a table and write it to another database

Question

Is it possible to read a table from a database with delphi zeoslib and write it back to another server?

I think it, to read the table and write back the resultset, but i don't know how, is it possible?

I thought for the following: I made an zquery: Select * from table1, with zconnection1 and I want to insert the results back to another database, to zconnection2.

I use mysql databases.

Answer 1

I don't know if MySQL (or Zeos) supports cross-server queries; the MySQL documentation should answer that portion. Whether Zeos allows that to get through to the DBMS is also a question I can't answer; one of the drawbacks to using open-source products is that they usually lack documentation that would tell you these things.

One way that I know for sure would work (although it would be a little slow for very large tables) is to do it row by row:

var
  i: Integer;
begin
  ZQuery1.SQL.Text := 'SELECT * FROM MyDataOnServer1';
  while not ZQuery1.Eof do
  begin
    ZTable1.Append;
    for i := 0 to ZQuery1.FieldCount - 1 do
      ZTable1.Fields[i].Value := ZQuery1.Fields[i].Value;
  end;
  ZTable1.Post;
end;

The other two possiible alternatives that come to mind (neither of which I know whether MySQL or Zeos supports) are

• Export the table from server1 as a backup, and restore it to server2
• If SELECT...INTO is supported, you could select into a temporary disk file on server1, and then do an INSERT FROM that temporary disk table into server2