--- In firebird-support@yahoogroups.com, "norgepaul"
<paul.thornton@c...> wrote:

>
> Hi,
>
> I am trying to figure out the best way to do the following:
>
> I have a database with around 1.5 million records. One of the tables
> has two BLOB fields that contain text. I would like to merge these
> BLOB fields into a third BLOB field then remove the original fields.
>
> Can this be done using only SQL or must I write a little program?
>
> The program would be simple apart from the fact that I would have to
> select 1.5 million records! What would be the best way to do this. I'm
> thinking select the first 200 records, process them, commit the
> transaction, move to the next 200, process them etc. Is this possible
> using just the FIRST and SKIP commands? Is this the way a datapump
> application would work?
>
> Thanks,
> Paul

Paul,
You are, unfortunatly, unable to merge blobs with SQL. You will need
to use a client program to do the work.
I would be inclined to to do the job as follows - this is a bit like
Delphi

1) Kick all other users off the server becuase you don't want new
stuff coming in until you are finished and you will probably change
the DB structure - if you are going to drop Blobs1 and 2.

Use two queries and two transactions.
The read query has a read transaction
the write query has a write transaction.

Read blobs1 and 2 and pass them to another method to post like
var
updCounter : integer;

TForm1.FormCreate(Sender : TObject);
begin
updCounter := 0;
end;

TForm1.ConcatBlobs(Blob1, Blob2 : string);
begin
with updQuery do begin
//always the next two
//because commit will close the transaction
//and we need it open
Transaction.Active := true;
if not prepared then prepare;
inc(updCounter);
paramByName("blob1').Clear;
paramByName("blob2').Clear;
paramByName("blob3').asString := Blob1 + Blob2;
ExecQuery;

if updCounter mod 200 = 0 then begin
//commit every 200 so db isn't locked up
if Transaction.InTransaction then Transaction.Commit;
end;
end;
end;

{your updQuery looks a bit like this
update MyTable
set Blob1 = :Blob1,
Blob2 = :Blob2;
Blob3 = :Blob3
}