At 12:04 AM 21/02/2005 +0000, you wrote:

>I was browsing through the UDFs and saw a function I would like to
>use, but don't know how.
>
>example of use
>
>CREATE PROCEDURE NEWCUST(sCU_NAME VARCHAR(40))
>RETURNS (oID CHAR(8)) AS
>BEGIN
> oID = GEN_ID(GEN_CU_ID, 1);
>
> INSERT INTO CUSTINFO(CU_ID, CU_NAME) VALUES (:oID, :sCU_NAME);
>
>END;
>
>Now assuming the generator was started at 1,
>how would I get oID set to 00000001
>
>Add this line below the Gen_ID line?
>oID = lpad(oID, 8, '0');
>
>Will that work ok in a stored Procedure and is there anything I need
>to do to free the resources?

You could do this in a trigger, and use the UDF lpad() from the ib_udf
library. Here's the declaration, "as is":

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