At 03:32 23/08/2008, you wrote:

>Hallo All,
>
>I'm trying to write a procedure creating a trigger for change history.
>The procedure has to be in plase because fields to a table may be
>added at runtime. I let users create fiels where the field name is
>given by the system and the apparent title is put in the field
>description. this works fine; however, i would like to have the
>correct mapping on the change history table (users can see that table).
>
>I defined the procedure as follows - i my eyes the correct way:
>
>...
> declare variable TABLEN varchar(255);
> declare variable TRIGRN varchar(255);
> declare variable FName varchar(255);
> declare variable FType varchar(255);
> declare variable Fcapt varchar(255);
>...
> for select
> trim(RF.RDB$FIELD_NAME),
> trim(T.RDB$TYPE_NAME),
> cast(substring(RF.RDB$DESCRIPTION from 1 for 20) as varchar(255))
> from
> RDB$RELATION_FIELDS RF inner join
> RDB$FIELDS F on F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE inner join
> RDB$TYPES T on T.RDB$TYPE = F.RDB$FIELD_TYPE and T.RDB$FIELD_NAME =
>'RDB$FIELD_TYPE'
> where
> RF.RDB$RELATION_NAME = :TABLEN
> into
> :FName,
> :FType,
> :Fcapt
> do
>...
>
>The error says "cannot convert to string", but the line:
>cast(substring(RF.RDB$DESCRIPTION from 1 for 20) as varchar(255))
>returns very well a field varchar as some test have shown. but as soon
>as i want to use the variable :Fcapt i get the error.
>Can someone explain that ?

You would get this exception if the procedure encounters NULL in rdb$description. A NULL can be written to a varchar but a NULL cannot be cast as anything. Try altering the procedure so that you're not asking the engine to cast any nulls:

cast(coalesce(substring(RF.RDB$DESCRIPTION from 1 for 20, '')) as varchar(255))

... you shouldn't need the cast, though. You can plug a char(n) (your substring or null) straight into the varchar variable and avoid the cast error entirely. (The reverse isn't true: you can't push a varchar into a char.)

If this sample is similar to your actual code then I strongly recommend, at each turn of the loop including the first, initialising all variables that are being fed from nullable columns!

./heLen