Subject Re: Merging two textual blob fields
Author mikcaau
--- In, "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

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

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
updCounter : integer;

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

TForm1.ConcatBlobs(Blob1, Blob2 : string);
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;
paramByName("blob3').asString := Blob1 + Blob2;

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

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