At 08:50 PM 6/08/2003 +0000, you wrote:

>Thanks, I feel a bit better now: only have 438 hours, still some to
>go.
>
>But I still can't make t work.
>
>Executing:
>"UPDATE Table
>set newFld = cast (_NONE oldFld as VARCHAR(70) CHARACTER SET
>_ISO8859_1)"
>
>returns "Token Unknown" on "oldFld"
>
>And executing :
>"set newFld = _ISO8859_1 oldFld"
>
>returns "Token Unknown" on "oldFld" again.

Yup. Unfortunately, this syntax only works with string constants. So

set newFld = _ISO8859_1 oldFld

won't work, but

set newFld = _ISO8859_1 'Some string'

will work.

>Does anyone know how to write the syntax of this ??
>Should I write a Store Procedure keeping each value to update in a
>variable, and updating one at a time ??

Not in Firebird 1.0.x, because it has no way to construct a DSQL statement
"on the fly" that would give you the string constant. The only way I could
see to do this with straight DSQL would be from a client application that
was looping through the source rows and passing the complete DSQL
statement, assembled each time with the string constants. It's not very
nice as a client/server technique but it's doable.

In Firebird 1.5, there is the EXECUTE STATEMENT construct. You *can* do it
in a stored procedure using this, although you MUST get the quoting exactly
right. Here's one I've just done and run to prove the pudding (this being
my first venture with EXECUTE STATEMENT.)

I worked on an existing table named PERSON, which includes two columns
FIRST_NAMES varchar(120) and SURNAME varchar(70). I created a table to
store the converted data:

create table dropme (
first_names varchar(120) character set ISO8859_1,
surname varchar(70) character set ISO8859_1) ;

Then I wrote this SP:
set term ^;
create procedure changeme as
declare variable infirst varchar(120);
declare variable exestring varchar(2000);
declare variable inlast varchar(70);
begin
for select first_names, surname from person
into :infirst, :inlast do
begin
exestring = 'insert into dropme (first_names, surname)
values (_ISO8859_1 '''||infirst||''', _ISO8859_1 '''||inlast||''')';
execute statement exestring;
end
end ^

(Watch all those quotemarks - they are all single-quotes. Also note that
the interactive SQL tool I use doesn't know about EXECUTE STATEMENT so I
needed to create the procedure in a script. It worked fine interactively
in isql, though, and I found the new "readline" feature very handy as I
wrestled with those quotemarks!)

It worked, EXCEPT for one gotcha ---

My data (members of the FirebirdSQL Foundation!) included the name
"O'Donohue". The procedure barfed on the apostrophe in "O'Donohue". The
reason of course is that EXECUTE PROCEDURE, although a great asset in some
ways, is completely dumb at run-time. It's literally brute force - if the
exestring has any possibility of resolving to bad syntax under any
condition, it's broke. The PSQL compiler can't anticipate your run-time
conditions so you absolutely MUST anticipate them yourself.

In a real-life situation, if I had to convert data that potentially
contained apostrophes, I'd need to write another procedure (using
substring() and the StrLen and/or SubStrLen UDFs) that gets called inside
the FOR loop BEFORE the exestring is assembled, to check the raw input and
double any apostrophes it finds.

heLen