三
三代坦克
Unregistered / Unconfirmed
GUEST, unregistred user!
Useful pieces of code
Some things from this document can easily be implemented by UDF, however in some situations you can't/don't want to use UDF. Among reasons to avoid UDFs are e.g.
UDFs are not supported on NetWare
UDFs are platform dependent
missing UDF can prevent doing database restore
UDF has no info about character set of its parameters
UDFs can't return <null>
incorrectly written UDF can crash server
Some other tips from this document allow you to do things that are not directly supported in stored procedures (like SET GENERATOR command).
Of course, all tips that refer stored procedures are applicable to triggers too.
String manipulating functions:
TrimRight in SP
Truncating string in SP
Length of string function in SP
Position of substring function in SP
Substring function in SP
Using generators in stored procedures:
How to create generator in SP
How to read generator's value in SP
How to set generator in SP
How to drop generator
--------------------------------------------------------------------------------
TrimRight in SP
InterBase does not have built-in function for trimming trailing spaces. Such function can be useful e.g. to convert CHAR string to VARCHAR when importing data from external files. rtrim function is e.g. part of the standard UDF library ib_udf.dll, but sometimes you want to avoid using UDFs. Unless you need to use it on very long strings (e.g. CHAR(30000)), it is possible to implement trim function as stored procedure.
When you CAST string to shorter one, the statement will succeed if removed characters are spaces, or it will fail (raise exception) if you try to remove non-blank characters. So just trying to CAST the string to shorter and shorter string and trapping errors will do the trick. It can be called either as a stored procedure (EXECUTE PROCEDURE TrimRight 'abc') or as a select procedure (SELECT ... FROM TrimRight('abc') ).
CREATE PROCEDURE TrimRight (str VARCHAR(10))
RETURNS (ret VARCHAR(10)) AS
BEGIN
ret = str;
IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
IF (str = '') THEN BEGIN ret = ''; SUSPEND; EXIT; END
BEGIN
ret = CAST (str AS char(9));
ret = CAST (str AS char(8));
ret = CAST (str AS char(7));
ret = CAST (str AS char(6));
ret = CAST (str AS char(5));
ret = CAST (str AS char(4));
ret = CAST (str AS char(3));
ret = CAST (str AS char(2));
ret = CAST (str AS char(1));
SUSPEND;
WHEN ANY DO SUSPEND;
END
END
Examples:
SELECT '>' || ret || '<'
FROM TrimRight (null)
============
<null>
SELECT '>' || ret || '<'
FROM TrimRight (' 1234 ')
============
> 1234<
EXECUTE PROCEDURE TrimRight '1234 '
==========
1234
Example of calling from another stored procedure:
EXECUTE PROCEDURE TrimRight str_in
RETURNING_VALUES str_out;
Notes:
It is not possible to use WHILE (...) DO loop to simplify the code because CAST can't contain variable in place of char length (i.e. CAST(str AS CHAR
len))).
There is a bug in IB5.1/5.6 (corrected in IB6) - if you remove inner BEGIN/END parenthesis, then SELECT ... FROM TrimRight(null); and SELECT ... FROM TrimRight(''); will return two rows instead of one.
--------------------------------------------------------------------------------
Truncating string in SP
Unlike trimming, truncating is a function that will shorten string regardless of its contents, i.e. it will remove even non-blank characters. There is no such built-in function in Interbase (except external UDF). When CASTing longer string as shorter one InterBase will raise exception "... string truncation". When assigning longer string directly into shorter variable, InterBase will raise exception too, but the truncated value will be assigned anyway ! All we need to do is trap the exception by WHEN ANY DO statement. Here is example procedure that truncate string down to 5 characters:
CREATE PROCEDURE Trunc10To5 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = '';
ret = a;
WHEN ANY DO EXIT;
END
Command
EXECUTE PROCEDURE Trunc10To5 '1234567890'
will return '12345'.
Note that you must not use CAST, and that variable you are assigning to must not contain <null>, so these two procedures will not work:
CREATE PROCEDURE test1 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = null;
ret = a;
WHEN ANY DO EXIT;
END
CREATE PROCEDURE test2 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = CAST(a AS VARCHAR(5));
WHEN ANY DO EXIT;
END
Also note that it is probably a bug that value is assigned even if exception is raised; however it is the way how IB4, IB5 and IB6 work.
--------------------------------------------------------------------------------
Length of string function in SP
Because Length function does not modify input string (like trimming and truncating), the implementation using WHILE loop and LIKE test is straightforward:
CREATE PROCEDURE Len (str VARCHAR(100))
RETURNS (len INTEGER) AS
DECLARE VARIABLE pat VARCHAR(100);
BEGIN
len = null;
IF (str IS NULL) THEN EXIT;
pat = '';
len = 0;
WHILE (NOT str LIKE pat) DO BEGIN
pat = pat || '_';
len = len + 1;
END
END
You can omit "len = null;" because variables are initialized to null automatically. The length will be counted including trailing spaces.
EXECUTE PROCEDURE Len null
LEN
=======
<null>
EXECUTE PROCEDURE Len ''
LEN
=======
0
EXECUTE PROCEDURE Len 'abc'
LEN
=======
3
EXECUTE PROCEDURE Len 'xyz '
LEN
=======
6
--------------------------------------------------------------------------------
Position of substring function in SP
This function returns index of the first character in a specified substring (SubStr parameter) that occurs in a given string (Str).
CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
IF (SubStr IS NULL OR Str IS NULL)
THEN BEGIN Pos = NULL; EXIT; END
SubStr2 = SubStr || '%';
Tmp = '';
Pos = 1;
WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
SubStr2 = '_' || SubStr2;
Tmp = Tmp || '_';
Pos = Pos + 1;
END
IF (Str LIKE Tmp) THEN Pos = 0;
END
Tmp variable is used to stop the loop if number of iterations is equal to Str length. Because SubStr is used on right side of LIKE operator, it should not contain SQL wildcards, i.e. '_' and '%' (unless you use ESCAPE clause). If substring is not found, return value is zero.
EXECUTE PROCEDURE Pos 'ab', 'abcdefghij'
POS
=======
1
EXECUTE PROCEDURE Pos 'cd', 'abcdefghij'
POS
=======
3
EXECUTE PROCEDURE Pos 'x', 'abcdefghij'
POS
=======
0
--------------------------------------------------------------------------------
Substring function in SP
I will leave this as homework for esteemed readers.
--------------------------------------------------------------------------------
How to create generator in SP
Of course InterBase has command CREATE GENERATOR, but because it is DDL command, you can't use it in stored procedure. But thanks to system of active tables, you can create generator by inserting it's name directly into system table:
INSERT INTO RDB$GENERATORS (RDB$GENERATOR_NAME)
VALUES (UPPER('MYGEN'));
The new generator is available immediately, even before commit (but if you rollback, the generator-name/generator-id will be lost). Do not forget that most IB versions have bug that prevents you from creating and using too many generators.
--------------------------------------------------------------------------------
How to read generator's value in SP
Many people use 1-row table (rdb$database) to retrieve generator's value in SP (SELECT GEN_ID(g,1) FROM rdb$database INTO :x
. While using rdb$database table can be useful if client program needs to read generator (because you can't "execute" standalone GEN_ID() expression from client), it is absolutely unnecessary in stored procedure. Expressions can be assigned to SP variables/parameters directly by assignment statement. E.g. procedure to retrieve next value from generator can look like:
CREATE PROCEDURE ReadGen RETURNS (ret INTEGER) AS
BEGIN
ret = GEN_ID(MyGen,1);
END;
--------------------------------------------------------------------------------
How to set generator in SP
Because command SET GENERATOR ... TO ...; is not supported inside stored procedures, the only way is to read current value in one GEN_ID call and use it to adjust value by another one. (But be careful in multiuser environment because these two GEN_ID calls are not guaranteed to be executed in one atomic operation !)
CREATE PROCEDURE SetGen (val INTEGER) AS
BEGIN
val = GEN_ID (MyGen, val - GEN_ID (MyGen,0) );
END
--------------------------------------------------------------------------------
How to drop generator
There is no DROP GENERATOR ...; command in InterBase. The only possibility is to delete definition of the generator from system table:
DELETE FROM RDB$GENERATORS
WHERE RDB$GENERATOR_NAME='MYGEN';
But this method has some disadvantages:
InterBase does not properly track dependencies for generators, which means that it will allow you to delete generator even if it is used by stored procedure or trigger. Fortunately, simple new creating of missing generator (and setting its initial value) will remedy this problem.
Another problem is that each generator consists of two parts:
record in RDB$GENERATORS table
generator variable/counter itself, that is stored on special database page
RDB$GENERATOR_ID field in RDB$GENERATORS table is in fact index to array of generators on generator page. When you delete generator from system table, then the space assigned to it on generator page (and its generator_id) will remain unused; it means that newly created generator will always have new rdb$generator_id value instead of reusing free one. So simple deleting generator is worth only if you plan to backup/restore the database, or if you want to get rid of the generator and do not plan to create new one. In case you need the generator of different name, just rename old, unused one:
UPDATE RDB$GENERATORS
SET RDB$GENERATOR_NAME='NEW_GEN'
WHERE RDB$GENERATOR_NAME='OLD_GEN';
Some things from this document can easily be implemented by UDF, however in some situations you can't/don't want to use UDF. Among reasons to avoid UDFs are e.g.
UDFs are not supported on NetWare
UDFs are platform dependent
missing UDF can prevent doing database restore
UDF has no info about character set of its parameters
UDFs can't return <null>
incorrectly written UDF can crash server
Some other tips from this document allow you to do things that are not directly supported in stored procedures (like SET GENERATOR command).
Of course, all tips that refer stored procedures are applicable to triggers too.
String manipulating functions:
TrimRight in SP
Truncating string in SP
Length of string function in SP
Position of substring function in SP
Substring function in SP
Using generators in stored procedures:
How to create generator in SP
How to read generator's value in SP
How to set generator in SP
How to drop generator
--------------------------------------------------------------------------------
TrimRight in SP
InterBase does not have built-in function for trimming trailing spaces. Such function can be useful e.g. to convert CHAR string to VARCHAR when importing data from external files. rtrim function is e.g. part of the standard UDF library ib_udf.dll, but sometimes you want to avoid using UDFs. Unless you need to use it on very long strings (e.g. CHAR(30000)), it is possible to implement trim function as stored procedure.
When you CAST string to shorter one, the statement will succeed if removed characters are spaces, or it will fail (raise exception) if you try to remove non-blank characters. So just trying to CAST the string to shorter and shorter string and trapping errors will do the trick. It can be called either as a stored procedure (EXECUTE PROCEDURE TrimRight 'abc') or as a select procedure (SELECT ... FROM TrimRight('abc') ).
CREATE PROCEDURE TrimRight (str VARCHAR(10))
RETURNS (ret VARCHAR(10)) AS
BEGIN
ret = str;
IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
IF (str = '') THEN BEGIN ret = ''; SUSPEND; EXIT; END
BEGIN
ret = CAST (str AS char(9));
ret = CAST (str AS char(8));
ret = CAST (str AS char(7));
ret = CAST (str AS char(6));
ret = CAST (str AS char(5));
ret = CAST (str AS char(4));
ret = CAST (str AS char(3));
ret = CAST (str AS char(2));
ret = CAST (str AS char(1));
SUSPEND;
WHEN ANY DO SUSPEND;
END
END
Examples:
SELECT '>' || ret || '<'
FROM TrimRight (null)
============
<null>
SELECT '>' || ret || '<'
FROM TrimRight (' 1234 ')
============
> 1234<
EXECUTE PROCEDURE TrimRight '1234 '
==========
1234
Example of calling from another stored procedure:
EXECUTE PROCEDURE TrimRight str_in
RETURNING_VALUES str_out;
Notes:
It is not possible to use WHILE (...) DO loop to simplify the code because CAST can't contain variable in place of char length (i.e. CAST(str AS CHAR
len))). There is a bug in IB5.1/5.6 (corrected in IB6) - if you remove inner BEGIN/END parenthesis, then SELECT ... FROM TrimRight(null); and SELECT ... FROM TrimRight(''); will return two rows instead of one.
--------------------------------------------------------------------------------
Truncating string in SP
Unlike trimming, truncating is a function that will shorten string regardless of its contents, i.e. it will remove even non-blank characters. There is no such built-in function in Interbase (except external UDF). When CASTing longer string as shorter one InterBase will raise exception "... string truncation". When assigning longer string directly into shorter variable, InterBase will raise exception too, but the truncated value will be assigned anyway ! All we need to do is trap the exception by WHEN ANY DO statement. Here is example procedure that truncate string down to 5 characters:
CREATE PROCEDURE Trunc10To5 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = '';
ret = a;
WHEN ANY DO EXIT;
END
Command
EXECUTE PROCEDURE Trunc10To5 '1234567890'
will return '12345'.
Note that you must not use CAST, and that variable you are assigning to must not contain <null>, so these two procedures will not work:
CREATE PROCEDURE test1 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = null;
ret = a;
WHEN ANY DO EXIT;
END
CREATE PROCEDURE test2 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = CAST(a AS VARCHAR(5));
WHEN ANY DO EXIT;
END
Also note that it is probably a bug that value is assigned even if exception is raised; however it is the way how IB4, IB5 and IB6 work.
--------------------------------------------------------------------------------
Length of string function in SP
Because Length function does not modify input string (like trimming and truncating), the implementation using WHILE loop and LIKE test is straightforward:
CREATE PROCEDURE Len (str VARCHAR(100))
RETURNS (len INTEGER) AS
DECLARE VARIABLE pat VARCHAR(100);
BEGIN
len = null;
IF (str IS NULL) THEN EXIT;
pat = '';
len = 0;
WHILE (NOT str LIKE pat) DO BEGIN
pat = pat || '_';
len = len + 1;
END
END
You can omit "len = null;" because variables are initialized to null automatically. The length will be counted including trailing spaces.
EXECUTE PROCEDURE Len null
LEN
=======
<null>
EXECUTE PROCEDURE Len ''
LEN
=======
0
EXECUTE PROCEDURE Len 'abc'
LEN
=======
3
EXECUTE PROCEDURE Len 'xyz '
LEN
=======
6
--------------------------------------------------------------------------------
Position of substring function in SP
This function returns index of the first character in a specified substring (SubStr parameter) that occurs in a given string (Str).
CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
IF (SubStr IS NULL OR Str IS NULL)
THEN BEGIN Pos = NULL; EXIT; END
SubStr2 = SubStr || '%';
Tmp = '';
Pos = 1;
WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
SubStr2 = '_' || SubStr2;
Tmp = Tmp || '_';
Pos = Pos + 1;
END
IF (Str LIKE Tmp) THEN Pos = 0;
END
Tmp variable is used to stop the loop if number of iterations is equal to Str length. Because SubStr is used on right side of LIKE operator, it should not contain SQL wildcards, i.e. '_' and '%' (unless you use ESCAPE clause). If substring is not found, return value is zero.
EXECUTE PROCEDURE Pos 'ab', 'abcdefghij'
POS
=======
1
EXECUTE PROCEDURE Pos 'cd', 'abcdefghij'
POS
=======
3
EXECUTE PROCEDURE Pos 'x', 'abcdefghij'
POS
=======
0
--------------------------------------------------------------------------------
Substring function in SP
I will leave this as homework for esteemed readers.
--------------------------------------------------------------------------------
How to create generator in SP
Of course InterBase has command CREATE GENERATOR, but because it is DDL command, you can't use it in stored procedure. But thanks to system of active tables, you can create generator by inserting it's name directly into system table:
INSERT INTO RDB$GENERATORS (RDB$GENERATOR_NAME)
VALUES (UPPER('MYGEN'));
The new generator is available immediately, even before commit (but if you rollback, the generator-name/generator-id will be lost). Do not forget that most IB versions have bug that prevents you from creating and using too many generators.
--------------------------------------------------------------------------------
How to read generator's value in SP
Many people use 1-row table (rdb$database) to retrieve generator's value in SP (SELECT GEN_ID(g,1) FROM rdb$database INTO :x
. While using rdb$database table can be useful if client program needs to read generator (because you can't "execute" standalone GEN_ID() expression from client), it is absolutely unnecessary in stored procedure. Expressions can be assigned to SP variables/parameters directly by assignment statement. E.g. procedure to retrieve next value from generator can look like: CREATE PROCEDURE ReadGen RETURNS (ret INTEGER) AS
BEGIN
ret = GEN_ID(MyGen,1);
END;
--------------------------------------------------------------------------------
How to set generator in SP
Because command SET GENERATOR ... TO ...; is not supported inside stored procedures, the only way is to read current value in one GEN_ID call and use it to adjust value by another one. (But be careful in multiuser environment because these two GEN_ID calls are not guaranteed to be executed in one atomic operation !)
CREATE PROCEDURE SetGen (val INTEGER) AS
BEGIN
val = GEN_ID (MyGen, val - GEN_ID (MyGen,0) );
END
--------------------------------------------------------------------------------
How to drop generator
There is no DROP GENERATOR ...; command in InterBase. The only possibility is to delete definition of the generator from system table:
DELETE FROM RDB$GENERATORS
WHERE RDB$GENERATOR_NAME='MYGEN';
But this method has some disadvantages:
InterBase does not properly track dependencies for generators, which means that it will allow you to delete generator even if it is used by stored procedure or trigger. Fortunately, simple new creating of missing generator (and setting its initial value) will remedy this problem.
Another problem is that each generator consists of two parts:
record in RDB$GENERATORS table
generator variable/counter itself, that is stored on special database page
RDB$GENERATOR_ID field in RDB$GENERATORS table is in fact index to array of generators on generator page. When you delete generator from system table, then the space assigned to it on generator page (and its generator_id) will remain unused; it means that newly created generator will always have new rdb$generator_id value instead of reusing free one. So simple deleting generator is worth only if you plan to backup/restore the database, or if you want to get rid of the generator and do not plan to create new one. In case you need the generator of different name, just rename old, unused one:
UPDATE RDB$GENERATORS
SET RDB$GENERATOR_NAME='NEW_GEN'
WHERE RDB$GENERATOR_NAME='OLD_GEN';
