On Tuesday 2002-02-26 at 01:19:42 +0100, Duilio Foschi wrote:

> var
> i:integer;
> v,Part_New:string;
> begin
>
> ...
> with ATable do
>
> while not eof do
> begin
> i:=FieldByName('singolo').AsInteger;
> v:=Part_New+Format('%8.8d',[i]);
> Edit;
> FieldByName('partita').AsString:=v;
> Post;
> Next;
> end;
> ...
>
> end;
>
> I can write this:
>
> update atable set partita=:part_new || cast (singolo as char(8) )

Perhaps you could write a stored procedure that uses the strlen
function in ib_udf. So, you would need to declare:

DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';


and you would need some sort of procedure along the lines of the
following (totally untested, of course):



SET TERM ##;
CREATE PROCEDURE UPDATE_IT (PART_NEW VARCHAR)
AS
DECLARE VARIABLE old_num INTEGER;
DECLARE VARIABLE counter INTEGER;
DECLARE VARIABLE pk NUMERIC(18,0);
DECLARE VARIABLE new_num VARCHAR;

BEGIN

FOR SELECT
id,
singolo
FROM
atable
INTO
:pk,
:old_num
DO

BEGIN

new_num = CAST(old_num AS VARCHAR);
counter = 8 - strlen(new_num);

WHILE (counter > 0) DO
new_num = '0' || new_num;

UPDATE
atable
SET
partita = :new_num
WHERE
id = :pk;

counter = counter - 1;

END

END ##
SET TERM ;##



That's one way to do it anyway. There are obviously other ways; for
example, find or write a UDF that does what C's "sprintf" does. In the
above example, you might be able to use an updateable cursor instead
of issuing a separate update statement for each row (but someone else
will have to show you that trick...).


David Trudgett