I have two questions

I have a list of products that I want insert into a table but if there are already a product with a matching index I want to change it.

I have done like below now (I try insert and if that fails I update). It works but it seems like a workaround to me, something that could and should be done more automatically by a FB feature.

begin
INSERT INTO T_MOVIES
("INDEX", NAME, DESCRIPTION)
VALUES (:IID, :SNAME, :SDESCRIPTION);
when sqlcode -803 do --errorcode "no duplicate value"
UPDATE T_MOVIES
set name = :SNAME,
description=:SDESCRIPTION
where "INDEX" = :IID;
end

Anyway, the above seems to work I just want to check I if it could be done in a better way. The more interesting question is about global constants. Are there any built in constants available for things like the error codes etc? It is ugly to write -803 I'd rather write NO_DUP.

Also constants like MAX_INTEGER would be useful from time to time.
select first (COALESCE (:ICOUNT, 999999)) skip (COALESCE (:ISTART, 0))
99999 should rather be some MAX_INT constant or similar.

When I come to think about it I have a two more questions with presumed negative answers.

Is there some kind of trick to give domain like capabilities to types of parameters to stored procedures? All I want is really a named type so I can change it on one place instead of several. I don't need trigger functionalities etc.
Something like "Varchar(50) as type titleName" would be desired.

Another thing that exist in some DB's that I haven't found in FB are "infinite" length varchars (where I don't have to give a max size). But maybe it is better to just use a large lenght to the varchar.
What is the cost of using a much too large varchar?

Sorry for the long mail, I am not sure if it is liked or thought of as bad to post several questions in one mail but they were all some small I thought so they didn't warrant a mail of their own.


Tim