|for InterBase and FireBird|
| There are various limits for Interbase and for
Firebird, as well as between their versions.
The data size of columns in a SQL query cannot exceed the
column limit AND the row limit. The full row size of the SQL cannot exceed the maximum row size limit.
The size of a query is the sum of each defined column sizes. Not the sum of the real field content:
- a column with ist defined size - for example VARCHAR(100), 2 bytes for SMALLINT etc.
- a UDF with the defined output-value - for example 100 bytes for ...RETURN VARCHAR(100)...
- a StroredProcedure s.o.
column limit row limit
InterBase 6.02 32 kB 32 kB
InterBase 7.x 32 kB 64 kB
FireBird 1.x 32 kB 32 kB
FireBird 2.x 32 kB 40 kB
In order that the following construction works:
select F_REPLACE(F_REPLACE(F_REPLACE(F_REPLACE(‘abcdefg’, ‘a’, ‘x‘), ‘b’, ‘y’), ‘c’, ‘z’), ‘d’, ‘-‘) from TABLE...
The definition of the F_REPLACE must looks like:
DECLARE EXTERNAL FUNCTION F_REPLACE
RETURNS CSTRING(8190) FREE_IT
ENTRY_POINT 'replace' MODULE_NAME 'FreeAdhocUDF';
The return string cannot be larger than 8190 bytes, because the function is used 4 times in ONE SQL
-> 4 * 8190 = 32760 bytes
Should the SQL contain also table-columns like
SELECT COLUMN, F_REPLACE(F_REPLACE(F_REPLACE(....
may the F_REPLACE appear just 3 times for don’t exceeding the limit of 32kB.
Due to the fact that you want to use the UDF in many ways for each project, it’s possible to define one UDF multiple :
- F_REPLACE mit RETURN CSTRING(254)
- F_REPLACE4 mit RETURN CSTRING(4095)
- F_REPLACE8 mit RETURN CSTRING(8190)
- F_BIGREPLACE mit RETURN CSTRING(32760)
So you have the right UDF for all circumstances.