I found a solution that works now but it took a damn lot of sweat to
get it to work, since a '+' instead of a '||' (yep, im use to c-
syntax) apparently gave the same type of error as when you have a
variable in your stmt-string to 'execute statement', but i got this
solution, albeit not that nice looking :

--Start Code

create procedure sp_prefix_blob_aux (
prefixlength integer,
someid integer)
returns (dynsql varchar(200))
as
begin
dynsql = 'SELECT substring(myBlobColumnName from 1 for ' || cast
(prefixlen as varchar(20)) || ' ) from myTable ';
dynsql = dynsql || 'WHERE mySomethingID = ' || cast(someid as
varchar(20));
suspend;
end

--Fetches the first 'prefixlength' bytes from
myTable.myBlobColumnName, which is of type blob(subtype 1, aka.text
blob)
CREATE PROCEDURE sp_prefix_blob
(someid int,prefixlength int) returns (preBlob varchar(1003))
AS
declare dynsql varchar(200);
declare dynres varchar(1000);
begin
execute procedure
sp_prefix_blob_aux :prefixlength, :someid returning_values :dynsql;
for execute statement dynsql into :dynres do
if (:dynres is not null) then
preBlob = dynres || '...';
suspend;
end

--EOCode

There are some issues, that the varaible prefix-int can't be more
than a 1000 and will have to fixed manually for now to inc. the
limit, because i dont think that :

(aString varchar(:myPreviouslyDefinedNumber))

..is legal (yet..??)

Also the old(FB1.0, according to release notes for 1.5) substring()
construct doesn't take variables in the ..FROM..FOR.. part so that
made me use an Aux procedure...but on the other side it WORKS on
BLOBs a little as well(see the release notes again), which took me a
looong time to find out, so i'll just spend a line here mentioning
that to other poor souls, who didn't see that...

Please, if anyone has other ways of solving this problem, please
post a reply...tia.

/Rasmus

--- In firebird-support@yahoogroups.com, "Rasmus Olesen"
<u981101@d...> wrote:

> Hi
>
> I need to extract the first 1000 chars or so from a BLOB(1) (ie.

subtype 1)

> and put them in a varchar(1000). How do one do that the smartest

way...??

>
> Oh, yeah, and i need to do in a stored procedure, so can't use any

cursors

> :-(
>
> If CAST() ate BLOBs, i think it would look like this :
>
> select CAST(myBlobColumnName AS varchar(1000))....
>
> ..if i assume it would cut the blob i half and keep the LEFT part
>
> or if substring or the likes, worked :
>
> select subblob(myBlobColumnName, 0, 1000)....
>
> But i really can't think of a way to do this without doing it with

a

> hardcore UDF trick-function or so.....
>
> regards.
> /Rasmus Olesen
> Denmark
>
>
>
> [Non-text portions of this message have been removed]