Can anyone offer some SQL suggestions that will make this procedure work?

The code at the bottom of this message is written in Delphi 5, using IBX
components and being applied to an InterBase 6 database.

The idea is to get rid of the "LEGAL_DESC" column that is defined as
VarChar(32767) (which I will say slows down just about anything you try to
do to this table). Before removing this column however, I want to copy it to
the newly created "BLEGAL_DESC" column defined as Blob Sub_Type Text. On the
surface this seems easy enough, but when I run this code, I get the
following error:

    Project UseBlobsFix.exe raised exception class EIBInterBaseError with
message 'arithmetic exception, numeric overflow, or string truncation'.

Thanks in advance for any help...
-Bob

procedure TConvert.BlobRecLegal;
begin
  with TIBSQL.Create(nil) do
  try
    Database := Form1.IBDatabase1;
    Transaction := Form1.IBTransaction1;

    //Add a BLOB field
    SQL.Text := 'ALTER TABLE REC_LEGAL '
              + 'ADD BLEGAL_DESC BLOB SUB_TYPE TEXT';
    ExecQuery;

    //Move the VarChar to BLOB
    SQL.Text := 'UPDATE REC_LEGAL '
              + 'SET BLEGAL_DESC = LEGAL_DESC ';
    ExecQuery;

    //Delete the VarChar field
    SQL.Text := 'ALTER TABLE REC_LEGAL '
              + 'DROP LEGAL_DESC';
    ExecQuery;

    //Rename the BLOB field
    SQL.Text := 'ALTER TABLE REC_LEGAL '
              + 'ALTER BLEGAL_DESC TO LEGAL_DESC';
    ExecQuery;
  finally
    Free;
  end;
end;

Can't do this this way.  Read the VarChar locally and then update using
parameters that value.  Blob require special processing and parameters is going
to be the easiest way to do this.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts, build
a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a computer,
cook a tasty meal, fight efficiently, die gallantly.  Specialization is for
insects.   (RAH)

"Jeff Overcash (TeamB)" <jeffoverc...@mindspring.com> wrote in message
news:3C4F25F3.20F6D761@mindspring.com...

Jeff:

I was afraid someone was going to tell me that...

I've been trying the approach you describe using IBX components but have run
into a wall with an error message of: "EIBInterBaseError with message
'internal error'". I guess I'll go back to the
borland.public.delphi.database.interbaseexpress group and try some more over
there.

Unless someone here can offer an idea of what this somewhat less than
helpful message means. <GRIN>

Thanks,
-Bob

Greetings,

    You can convert blob to varchar using an UDF.
    If you need one please e-mail me and I send my library for you.

    Cheers,
    Marcio Wesley Borges
    www.sibprovider.com

"Bob Heide" <BobHe...@NoSpam.lllow.com> escreveu na mensagem
news:3c4f1f7e_1@dnews...

news:3c4f2857_2@dnews...

The conversion won't be as fast having to read the blobs into the
application and write them back out, but it will work and my understanding
is this is a one time conversion.

Reading the varchars into a Delphi app shouldn't be a problem, to get them
back out to the blob you should be able to use an IBSQL component with a
query as:

update rec_legal set blegal_desc = :blobfld

Then in a loop on another IBSQL component that selects the key field and
blob from all records  in rec_legal:
Here's a tested example on a  table I created:

procedure TForm1.Button1Click(Sender: TObject);
begin
  IBTransaction1.StartTransaction;
  IBSQL1.ExecQuery; // select * from rec_legal
  while not IBSQL1.Eof do begin
    IBSQL2.Params[0].AsString := IBSQL1.FieldByName('ntext').AsString;
    IBSQL2.Params[1].AsInteger := IBSQL1.FieldByName('refid').AsInteger;
    IBSQL2.ExecQuery; // update rec_legal set blegal_desc = :desc where
yourkey = :key
    IBSQL1.Next;
  end;
  IBTransaction1.Commit;
end;

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
In a tornado, even turkeys can fly. - unknown