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
CSTRING(8190),
CSTRING(254),
CSTRING(254)
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. |