Problem with bulk-insert

[MaxMara]

Hi!
I have one ADOConnection and one TZConnection in my project.
The ADOConnections opens an Excelfile. I want this data to be inserted into my MySQL database.
Here's my code:

Code: Select all

  ADOQuery1.Open;
  ZQuery1.Open;   // MySQL-Tabelle 
  while not ADOQuery1.Eof do 
  begin 
    ZQuery1.Append;  // neuer, leerer Datensatz 
    ZQuery1.FieldByName('content_nr').AsString := ADOQuery1.Fields[0].Value;  // Feld kopieren 
    ZQuery1.FieldByName('content_sprache').AsString := ADOQuery1.Fields[1].Value; 
    ZQuery1.FieldByName('content_text').AsString := ADOQuery1.Fields[2].Value; 
    ZQuery1.FieldByName('content_datetime').AsString := 'now()'; 
    ZQuery1.Post;  // Daten abschicken 
    ADOQuery1.Next;   // nächste Excel Reihe 
  end;

When i execute the code it says "SQL Query is empty".
Can anybody tell me please, what I am doing wrong.

Thanks,
Christian

[mdaems]

This information is quite limited. How are the Zquery1 properties set? At what line of your code do you get this error?
Did you set the SQL property of ZQuery1? To what value?

BTW : Some trick I learned from Ben (btrewern) : if you want to add rows to a table using this method you should write your query using a where clause that never returns data like 'select * from table where 0=1'. This way you don't retrieve any data but the right data structures are set.

Mark

[MaxMara]

Hi Mark,
the error occurs at 'ZQuery.Open'. The SQL property of ZQuery is empty. I tried my code with a ZTable instead of the ZQuery and everythings is working.
Very strange...

Christian

[mdaems]

That explains everything. A TZQuery needs a sql statement to execute in order to know the right datastructure it has to create. The ZTable internally works the same way. However there the sql statement is generated automatically based on the table name you provide.

So : just set ZQuery.SQL.Text := 'select * from content where 0=1'. This generates an empty dataset with a column for every table field.

In case you always start with an empty table a TZTable will do exactly the same. (As a TZTable is a TZQuery descendant with sql = 'select * from content') In case you add data to an already filled table, use a TZQuery to avoid that all data is loaded every time at ZQuery.open by limiting the query output using a where clause. (TZTable isn't smart enough to add a where clause to the generated query)

Mark

[MaxMara]

OK. And this explains everything to me.
Thanks for your help Mark.

Regards
Christian