Hi Mikcaau,

Thanks for the information. Unfortunately, I'm still missing some
details that I need to continue.

Do you know the best way to step through each of 1.5 million records
without using up a massive ammount of resources? If I do a "SELECT *
FROM TABLE", then step through each record in turn, I'm going to run
into problems with memory (I think!).

If I use FIRST and SKIP, am I right in assuming that the queries will
get slower the further into the table I get (I'm assuming that "SELECT
FIRST 100 SKIP 1000000 * FROM TABLE" will still step through the first
1000000 records, but only return the next 100)?

BTW, I am using Delphi 7 with either IBX or FIBPlus.

Cheers,
Paul

--- In firebird-support@yahoogroups.com, "mikcaau" <mikcaau@y...> wrote:
>
> --- 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
> }