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

>
> 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
>

Hi Helen

Thanks for your valuable comments. Indeed, the NULL problem is what
came to my mind first thing this morning.

The correction to the cast works well
cast(coalesce(substring(RF.RDB$DESCRIPTION from 1 for 20), '') as
varchar(255)).
You suggested not to use the cast at all but to provide a char
directly into the variable. It's probably quite sraightforward but I
would very much appreciate if you could tell me how to do that as i've
never done such a thing!

cheers
daniele