At 10:52 PM 19/11/2007, you wrote:

>Hi
>
>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>> Alright. As you are handling strings, your main limitation is that
>a varchar must not exceed 32,765 *bytes* in length. If you are using
>MBCS, the limit would be proportionately shorter in *characters*.
>
>OK. I tried to CAST(... AS VARCHAR(32000)) and I got back a memo
>field. I can live with 32000 bytes no problem.
>
>
>> I don't know what sort of "template" you had in mind. You can't
>format strings, other than to include tab and CRLF characters. But it
>would be feasible to construct the string by concatenating varchars in
>a stored procedure and return it directly to the caller, without need
>to store a blob.
>
>Something contained in a FireBird table field like:
>'Invoice no. ' + TO_STRING(InvTable.NoFld) + ' dtd. ' +
>TO_STRING(InvTable.DtFld) + ' Party ' + InvTable.PartyNameFld
>
>Assuming there is either a STRING() or TO_STRING() function / UDF that
>can be used in FireBird at SELECT time.

The only function you absolutely need is CAST(). Some UDF's (or, with Fb 2.1, internal functions) might be called upon to make things tidy. There are UDFs out there to do pretty things with dates which, in general, is a good thing, since Firebird is a bit "USA-centric" about how it looks at dates.

The plus-sign is a mathematic operator in SQL. Concatenation is done with a pair of pipe-characters ||.

The above example (assuming NoFld is a BIGINT) would go something like:

select
cast(
('Invoice no. ' ||
RTRIM(CAST (NoFld as char(19))) ||
' dtd. ' ||
CAST (EXTRACT (DAY from DtFld) as char(2)) || '/' ||
CAST (EXTRACT (MONTH from DtFld) as char(2)) || '/' ||
CAST (EXTRACT (YEAR from DtFld) as char(2)) ||
' Party ' ||
PartyNameFld) as varchar(32765))
as mNarration
from InvTable
where....

>Can such a string in a field
>textmerge into a usable string, eg. 'Invoice no. 123 dtd. 19/11/20007
>Party A.B.C. and Co.'?

See above.

>If not, can this above be done in a Stored Procedure? If I can do
>something like:
>
>SELECT MySP() AS mNarration FROM InvTable WHERE ...

Not like that. You can't execute the SP by selecting it from the table. ;-)

>Though I don't want to send the BLOB nor the updated one back to
>FireBird for storage.

Then forget about blobs. They're not what you want, if you can't strain them through a client application.

./heLen