--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
Thanx Helen,
I think that'll doit!
I know its a potential bomb, but when the real count of the item is
less than 10 million, I figured 100mm would be safe.

E. D. Epperson jr.

> DECLARE EXTERNAL FUNCTION lpad
> CSTRING(255), INTEGER, CSTRING(1)
> RETURNS CSTRING(255) FREE_IT
> ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';
>
> You are running the risk of an overflow by allowing only 8

characters for

> this conversion, since integer-to-string is potentially 10

characters and

> BigInt-to-string potentially 20. Still, it's up to you whether you

want to

> build in that "bomb"...let's assume you're comfortable that this number
> will never reach 100 million, and stick with CHAR(8) :-))
>
> You can declare a UDF with any function name you like, and string
> parameters can be adapted to suit the specific need. I'd want to

customise

> this declaration to make it simpler for others coming along behind

and to

> optimise the sizes of the strings. You can do this, for example:
>
> DECLARE EXTERNAL FUNCTION MAKE_CUSTID
> CSTRING(8), INTEGER, CSTRING(1)
> RETURNS CSTRING(8) FREE_IT
> ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';
>
> Now, write a BEFORE INSERT trigger that invokes this function:
>
> CREATE TRIGGER BI_CUSTINFO FOR CUSTINFO
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF (new.oID is null or or new.oID = '' or new.oID starting with

'new') then

> new.oID = MAKE_CUSTID (CAST(GEN_ID(GEN_CU_ID, 1) AS

VARCHAR(8))), 8, '0');

> END
>
> Alternatively, if oID is actually the primary key, then for Delphi, you
> will possibly want to get that new oID value *before* you post the
> insert. In that case, do the following instead:
>
> CREATE PROCEDURE MAKE_CUST_ID
> RETURNS NEW_OID CHAR(8) AS
> BEGIN
> NEW_OID = MAKE_CUSTID (CAST(GEN_ID(GEN_CU_ID, 1) AS

VARCHAR(8))), 8, '0');

> END
>
> and make the trigger:
>
> CREATE TRIGGER BI_CUSTINFO FOR CUSTINFO
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> declare new_oid char(8);
> IF (new.oID is null or or new.oID = '' or new.oID starting with

'new') then

> BEGIN
> EXECUTE PROCEDURE MAKE_CUSTID
> RETURNING_VALUES (:new_oid);
> new.oID = new_oid;
> END
> END
>
> Including a test for a string starting with 'new' is just an option

I threw

> in there, in case you might want to default the field to a dummy

value in

> any of your apps. The second OR test is a pair of single-quotes (empty
> string), not a double-quote. Testing for empty string is a valid

thing to

> do with some Delphi data access interfaces, which are enslaved to the
> Paradox-inherited notion of null and insist on passing an empty string
> instead of a string that is null. Even if you are using a smarter data
> access interface, the test for empty string doesn't eat anything.
>
> Note, don't try to cast the generator value as CHAR(8), since the value
> passed to the function will then be '88 ', i.e. the new

sequence value

> right-padded with blanks - so this would come right back from the

function

> unchanged.
>
> Another thing to notice: PSQL isn't Delphi, so your END statements
> shouldn't terminate with a semi-colon. The last END statement should
> terminate with whatever terminator you set in SET TERM.
>
> ./hb