""Fred"" <info@...> wrote in message
news:C987CF2B3D51864A8A65FE02CD96145E09D309@......

> Hi, I want to known if anyone has a Udf in Interbase for give Upper in

blob.

>
> for example:
>
> Select upper(field blob) from table.

A UDF won't be reliable in this context because they generally don't handle
charsets and don't have any way to know the charset of the column.
Furthermore, BLOBs only have charsets, not collations. So you cannot apply
too much IQ to a blob field.

What you could try is converting the blob to a varchar field and then apply
the collation. You won't lose nothing, since you can't output more than 32K
in a row, so the conversion to string already limited to 32K is not a
problem.

Here's an example that should have worked:

H:\ibdev\fbbuild\interbase\ib_debug\bin>ibserver -a
H:\ibdev\fbbuild\interbase\ib_debug\bin>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> set names iso8859_1;
SQL> connect h:/proy/g;
Database: h:/proy/g
SQL> create table tblob(a blob sub_type text character set iso8859_1);

These direct insertions only work in Firebird:

SQL> insert into tblob(a) values(_iso8859_1 'ala');
SQL> insert into tblob(a) values(_iso8859_1 'aló');
SQL> insert into tblob(a) values(_iso8859_1 'alá');
SQL> select upper(a) from tblob;

==============================
Statement failed, SQLCODE = -413

conversion error from string "BLOB"

The above is expected to fail. This was your problem.

SQL> select upper(substring(a from 1 for 50)) from tblob;

==============================
ALA
ALó
ALá

Well, but we are missing the collate, let's try to put it:

SQL> select upper(substring(a from 1 for 50) collate es_es) from tblob;
Statement failed, SQLCODE = -204

Dynamic SQL Error
-SQL error code = -204
-Data type unknown
-COLLATION ES_ES is not valid for specified CHARACTER SET

And that's a bug, dear friends. It seems that I didn't see the need to write
a case for substring inside a function called pass1_node in DSQL and maybe
this missing step is taking revenge against me now. The other possibility is
that JRD didn't obey the charset forcing line that I put when a literal is
pushed into a blob, but in this case, it means that a specially crafted UDF
using descriptors can or cannot do the trick, too. I don't have the time to
hit the wall with the head for a full day, so the bug will live rampant in
FB1. Substring wasn't meant to be charset-aware in the original
implementation that come from prehistoric days.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing