Page 1 of 1
Problem with bulk-insert
Posted: 24.05.2008, 11:45
by 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
Posted: 25.05.2008, 19:41
by 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
Posted: 26.05.2008, 07:14
by 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
Posted: 26.05.2008, 09:46
by 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
Posted: 26.05.2008, 10:36
by MaxMara
OK. And this explains everything to me.
Thanks for your help Mark.
Regards
Christian