At 02:10 PM 10-12-02 +0100, Gregor Ernst wrote:

>Thank you Martijn.
>My Question is: How do I Change it?
>I am getting an ASCII-File containing SQL Statments Like Insert into Artikel
>(ARTno,Price) Values ('12345','123,22')
>The Result is, that this Article now has Price of 12322 instead of 123.22.
>The Systems I work with runs in Germany and the OS uses the ',' as the
>decimal separator.
>Is there a possability to change the separator Character? I don't want to
>program a Stringconversion for the hole Insertscript.

You could convert the string using a Before Insert trigger. You might also
like to add a numeric(15,2) column to Artikel and cast the string value to
a currency-compatible numeric.

alter table Artikel
add price1 numeric(15,2);
commit;

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

commit;
set term ^ ;
create trigger bi_Artikel
active before insert position 0
as
declare variable pos smallint;
declare variable comma char;
declare newstring varchar(16);
begin
comma = 'F';
newstring = ''; /* two single quotes */
if (new.Price is not null and new.Price <> '') then
begin
pos = 1;
while (pos <= strlen(new.Price) do
begin
if (substring(new.Price pos for 1) = ',') then
begin
comma = 'T';
newstring = newstring || '.';
end
else
begin
newstring = newstring || substring(new.Price pos for 1);
end
pos = pos + 1;
end;
if (comma = 'F') then
newstring = newstring || '.00';
new.Price = newstring;
new.Price1 = cast(newstring as numeric (15,2));
end
end ^

commit ^

set term ; ^

heLen