Batch insert/update
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2007-07-12 21:02

Hello.
Could someone point me to direction how to insert many records in one
operation (batch insert) to PostgreSQL, using Lazarus and Zeos?
I would appreciate an example.
Thanks.

In fact, I was trying to batch insert data from a .csv file to a
PostgreSQL table, by looping through both datasets and appending rows
from .csv file to PostgreSQL table.
The following code works:

procedure TForm1.ButtonImportOrdersClick(Sender: TObject);
begin
//Start transaction?
//ZConnection1.AutoCommit:=False;
//ZConnection1.StartTransaction;

//Delete all from table "orders".
//ZTableOrdersPostgreSQL.Edit;
ZTableOrdersPostgreSQL.EmptyDataSet;
//ZTableOrdersPostgreSQL.Post;

ZTableOrdersPostgreSQL.Refresh;

//Enable cached updates.
ZTableOrdersPostgreSQL.CachedUpdates:=True;

//Go to the first row in both tables.
ZTableOrdersPostgreSQL.First;
SdfDataSetOrdersCsv.First;

//Disable controls.
ZTableOrdersPostgreSQL.DisableControls;
//Loop through the .csv dataser
  while not SdfDataSetOrdersCsv.EOF do
  begin
        //Enable appending new data.
        ZTableOrdersPostgreSQL.Append;
       
        //Set field values.
        //If SdfDataSetOrdersCsv.FieldByName('Sys.status') like
'%CNF%' THEN
        if pos(' CNF ',
SdfDataSetOrdersCsv.FieldByName('Sys.status').AsString)<>0 then
           ZTableOrdersPostgreSQL.FieldByName('order_confirmed_finally
').AsBoolean:=True
        else
           ZTableOrdersPostgreSQL.FieldByName('order_confirmed_finally
').AsBoolean:=False;
        ZTableOrdersPostgreSQL.FieldByName('order').AsString:=
SdfDataSetOrdersCsv.FieldByName('Order').Value;
        ZTableOrdersPostgreSQL.FieldByName('product').AsString:=
SdfDataSetOrdersCsv.FieldByName('Material').Value;
       
ZTableOrdersPostgreSQL.FieldByName('product_description').AsString:=
SdfDataSetOrdersCsv.FieldByName('Mat.descr.').Value;
        ZTableOrdersPostgreSQL.FieldByName('order_type').AsString:=
SdfDataSetOrdersCsv.FieldByName('Order type').Value;
       
ZTableOrdersPostgreSQL.FieldByName('product_target_qty').AsVariant:=
SdfDataSetOrdersCsv.FieldByName('Target qty').Value;
        ZTableOrdersPostgreSQL.FieldByName('product_target_qty_units')
.AsString:= SdfDataSetOrdersCsv.FieldByName('Target qty1').Value;
       
ZTableOrdersPostgreSQL.FieldByName('order_planned_start').AsDateTime:=
SdfDataSetOrdersCsv.FieldByName('Bas. start').Value;
        ZTableOrdersPostgreSQL.FieldByName('order_planned_finish').AsD
ateTime:= SdfDataSetOrdersCsv.FieldByName('Basic fin.').Value;
        ZTableOrdersPostgreSQL.FieldByName('product_confirmed_qty').As
Variant:= SdfDataSetOrdersCsv.FieldByName('Conf. qty').AsVariant;
        ZTableOrdersPostgreSQL.FieldByName('product_delivered_qty').As
Variant:= SdfDataSetOrdersCsv.FieldByName('Del. qty').AsVariant;
        if SdfDataSetOrdersCsv.FieldByName('Created on').AsString <>''
then
         
 ZTableOrdersPostgreSQL.FieldByName('order_created_date').AsDateTime:=
SdfDataSetOrdersCsv.FieldByName('Created on').Value;
        if SdfDataSetOrdersCsv.FieldByName('Release').AsString<>''
then
           ZTableOrdersPostgreSQL.FieldByName('order_released_date').A
sDateTime:= SdfDataSetOrdersCsv.FieldByName('Release').Value;
        if SdfDataSetOrdersCsv.FieldByName('Act. start').AsString <>''
then
         
 ZTableOrdersPostgreSQL.FieldByName('order_actual_start').AsDateTime:=
SdfDataSetOrdersCsv.FieldByName('Act. start').Value;
        if SdfDataSetOrdersCsv.FieldByName('Act.finish').AsString<>''
then
           ZTableOrdersPostgreSQL.FieldByName('order_actual_finish').A
sDateTime:= SdfDataSetOrdersCsv.FieldByName('Act.finish').Value;
        ZTableOrdersPostgreSQL.FieldByName('order_status').AsString:=
SdfDataSetOrdersCsv.FieldByName('Sys.status').AsString;
        ZTableOrdersPostgreSQL.FieldByName('plant').AsString:= 'NE05';
        if SdfDataSetOrdersCsv.FieldByName('Order
type').AsString='PI01' THEN
         
 ZTableOrdersPostgreSQL.FieldByName('order_active').AsBoolean:=True
        else
           
ZTableOrdersPostgreSQL.FieldByName('order_active').AsBoolean:=False;

        //Post appended row.
        ZTableOrdersPostgreSQL.Post;
       
       //Move to next row in both tables.
       SdfDataSetOrdersCsv.Next;
       ZTableOrdersPostgreSQL.Next;
  end;
 
//Apply appended row (cached update).
ZTableOrdersPostgreSQL.ApplyUpdates;
ZTableOrdersPostgreSQL.CommitUpdates;

//Commit transaction?
//ZConnection1.Commit;

//Enable controls.
ZTableOrdersPostgreSQL.EnableControls;
end;

But,

1. I'm not shure whether CachedUpdates insert value row by row, or the
whole statement (all rows at once). Why do I need CommitUpdates after
ApplyUpdates?
2. If I try to uncomment "...StartTransaction....Commit" I get error
that operation is not allowed for NonAutoCommit...". What operation?
3. Should I use string concatenation  that would create SQL string for
INSERT INTO, from the .csv file, as btrewern suggests, instead?

Can someone explain me what is CachedUpdate and whether that is what I
need, or I need StartTransaction/Commit methods of connection object.
I need something that will behave like TBatchMove in Delphi...

Regards,

Zlatko