Pardon my stupidity, but this is all new to me.
Why is the following stored procedure failing to compile?

declare variable fkvalue varchar(11) character set ASCII;
declare variable x integer;

for select PERSON_ID FROM PERSONS
for update
into :fkvalue
as cursor mycursor
do
begin
/* Procedure Text */
x = 1
end;
when GDSCode transliteration_failed do
begin
update persons
set PERSON_ID = 'XXX'
where current of mycursor;
end;

It tells me that PERSON_ID doesn't exists (while it is a column in
the table). If I use PK_PERSONS I get the same error.

Thanks

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 02:40 PM 30/04/2004 +0000, you wrote:
> >When I iterate a through the PERSONS table (with IBExpert) I get at
> >the certain moment the following error:
> >
> >Arithmic overflow of division by zero has occured
> >arithmic exception, numeric overflow or string truncation
> >
> >Cannot transliterate character between character sets.
> >
> >Does this means that there is a strange character in a certain
> >record? How can I find this record and fix or delete it?
>
> If this is related to your previous Restore problem, then it seems

likely

> you have a "rogue" character occupying a column that is a foreign

key. You

> could write a stored procedure that loops through the table as a

cursor,

> writing the value from the problem column into a variable of the

correct

> character set.
>
> declare variable fkvalue varchar(n) character set ISO_8859_1;
> FOR SELECT PK_PERSONS FROM PERSONS
> FOR UPDATE
> INTO :FKVALUE
> AS CURSOR MYCURSOR
> DO
> BEGIN
> // if it's not Fb 1.5, you'll need to put some do-nothing

code in here

>
> END
>
> When the loop encounters the bad character, it will throw the

exception you

> have seen elsewhere. Use the GDSCODE to trap the faulty value:
>
> WHEN GDSCODE transliteration_failed do
> begin
> update persons
> set pk_persons = null
> where current of mycursor;
> end
> end
> If you can't set the value to null (because it is not a nullable

column)

> then you'll need to prepare a "dummy parent" beforehand, by

creating a

> master record with a pk value that is empty string. Commit it, of

course.

>
> Then your sp can do
>
> set pk_persons = ''
> where current of mycursor;
>
> /heLen