Text2Hexa function

[jprenou]

Hi,

I wrote this function below.

If I do this

SELECT Text2Hexa('test') FROM #DUMMY

I've got x'74657374' and it's fine.
If I do this

UPDATE "MyTable" SET "MyBlobField"=Text2Hexa('test')

It's not working. Can you tell me what I'm doing here

Regards,

JP

DROP FUNCTION IF EXISTS TEXT2HEXA;
CREATE FUNCTION TEXT2HEXA(aText NULLSTRING(255)) RETURNS NULLSTRING(514)
BEGIN
  DECLARE i INT;
  DECLARE sResult NULLSTRING(514);
  DECLARE iCalc INT;
  DECLARE iHex INT;
  DECLARE sHex NULLSTRING(2);
  SET i = 1;
  SET sResult = '';
  WHILE i <= CHAR_LENGTH(aText) DO
    SET iHex =  ORD(SUBSTRING(aText FROM i FOR 1));
    SET sHex = '';
    WHILE iHex > 0 DO
      IF iHex >= 16 THEN
        SET iCalc = iHex/16;
        SET iHex = iHex-iCalc*16;
      ELSE
        SET iCalc = iHex;
        SET iHex = 0;
      END IF;
      IF iCalc < 9 THEN
        SET sHex = sHex + CHR(iCalc+48);
      ELSE
        SET sHex = sHex + CHR(iCalc+55);
      END IF;
    END WHILE;
    SET sResult = sResult + sHex;
    SET i = i + 1;
  END WHILE;
  IF sResult = '' THEN
    SET sResult = 'NULL';
  ELSE
    SET sResult = 'x ' + CHR(39) + sResult + CHR(39);
  END IF;
  RETURN sResult;
END;

Op 25-4-2012 14:49, jprenou schreef:

you get an error message

blobstr Type mismatch: The source value type (CHARACTER VARYING) and the
target site type (column: blobstr, [BINARY LARGE OBJECT]) are not
assignment compatible.

use NClob or clob. (see script below)

Ad Franse



drop table if exists test;
create table test
(
test_id autoinc primary key
, str varchar( 255)
, blobstr blob
, clobstr clob
, imagestr image
, nclobstr nclob
);

DROP FUNCTION IF EXISTS TEXT2HEXA;

CREATE FUNCTION TEXT2HEXA(aText NULLSTRING(255)) RETURNS NULLSTRING(514)
BEGIN
DECLARE i INT;
DECLARE sResult NULLSTRING(514);
DECLARE iCalc INT;
DECLARE iHex INT;
DECLARE sHex NULLSTRING(2);

SET i = 1;
SET sResult = '';
WHILE i <= CHAR_LENGTH(aText) DO
SET iHex = ORD(SUBSTRING(aText FROM i FOR 1));
SET sHex = '';
WHILE iHex > 0 DO
IF iHex >= 16 THEN
SET iCalc = iHex/16;
SET iHex = iHex-iCalc*16;
ELSE
SET iCalc = iHex;
SET iHex = 0;
END IF;
IF iCalc < 9 THEN
SET sHex = sHex + CHR(iCalc+48);
ELSE
SET sHex = sHex + CHR(iCalc+55);
END IF;
END WHILE;
SET sResult = sResult + sHex;
SET i = i + 1;
END WHILE;
IF sResult = '' THEN
SET sResult = 'NULL';
ELSE
SET sResult = 'x ' + CHR(39) + sResult + CHR(39);
END IF;
RETURN sResult;
END;




try
insert into test (blobstr) values ( TEXT2HEXA( 'test' )); //type
mismatch
catch position( 'mismatch' in lower( ERROR_MESSAGE )) <> 0 // type
mismatch
insert into test (str) values ( 'blobstr ' || ERROR_MESSAGE );
end;

try
insert into test (imagestr) values ( TEXT2HEXA( 'test' )); //type
mismatch
catch position( 'mismatch' in lower( ERROR_MESSAGE )) <> 0 // type
mismatch
insert into test (str) values ( 'imagestr ' || ERROR_MESSAGE );
end;

insert into test (clobstr) values (TEXT2HEXA( 'test' ));

insert into test (str) values ( text2hexa( 'test' ));

insert into test (nclobstr) values ( TEXT2HEXA( 'test' ));
select * from test;

[jprenou]

Not sure if I understand why !

Maybe I can set my return value as a blob and not as a nullstring ?

Regards,

JP

Op 25-4-2012 18:41, jprenou schreef:
> Not sure if I understand why !
>
> Maybe I can set my return value as a blob and not as a nullstring ?th

the modificatied version below does the trick !

Ad Franse

CREATE FUNCTION TEXT2HEXA(aText NULLSTRING(255)) RETURNS blob
BEGIN
DECLARE i INT;
DECLARE sResult NULLSTRING(514);
DECLARE iCalc INT;
DECLARE iHex INT;
DECLARE sHex NULLSTRING(2);

SET i = 1;
SET sResult = '';
WHILE i <= CHAR_LENGTH(aText) DO
SET iHex = ORD(SUBSTRING(aText FROM i FOR 1));
SET sHex = '';
WHILE iHex > 0 DO
IF iHex >= 16 THEN
SET iCalc = iHex/16;
SET iHex = iHex-iCalc*16;
ELSE
SET iCalc = iHex;
SET iHex = 0;
END IF;
IF iCalc < 9 THEN
SET sHex = sHex + CHR(iCalc+48);
ELSE
SET sHex = sHex + CHR(iCalc+55);
END IF;
END WHILE;
SET sResult = sResult + sHex;
SET i = i + 1;
END WHILE;
IF sResult = '' THEN
SET sResult = NULL;
ELSE
SET sResult = CHR(39) + sResult + CHR(39);
END IF;
RETURN sResult;
END;

[jprenou]

Hi,

No it's not working. If I'm doing that I've got in my blob field '74657374' and not the text test.

NexusDB team, can you give us a tip here ?

Regards,

JP

jprenou wrote:

>
> Hi,
>
> No it's not working. If I'm doing that I've got in my blob field
> '74657374' and not the text test.
>
> NexusDB team, can you give us a tip here ?

You are not making much sense here.

Blobs store bytes.
Memos (CLOBs) store characters.

If you want to store the character sequence 'test' then you should be using a
Memo, in which case you can store the value like this:

UPDATE "MyTable" SET "MyBlobField"='test'

[jprenou]

Hi Thorsten,

I was not my choice to replace my ftMemo by ftBlob in my database structure.
Because I'm using Report Builder and because they changed their way to manage ftMemo I moved my memo fields into blob fields and not clob fields.

This function is working fine (with BLOB field) and I don't understand why

drop function if exists Text2Blob; 
create function Text2Blob(aText nullstring(255)) returns blob 
begin 
    declare i int;
    declare sBlob nullstring(510);
    set i = 1; 
    set sBlob = '';
    while i <= char_length(aText) do 
      set sBlob = sBlob + substring(aText from i for 1);
      set i = i + 1; 
    end while; 
    return sBlob; 
    end;

One more question, why the sql engine understand that

UPDATE "MyTable" SET "MyBlobField"="MyBlobField"||Text2Blob('test');

but not that

UPDATE "MyTable" SET "MyBlobField"="MyBlobField"+Text2Blob('test');

Regards,

JP

On 25/04/2012 8:49 AM, jprenou wrote:
> Hi,
>
> I wrote this function below.
>
> If I do this
>
> Code:
> --------------------
> SELECT Text2Hexa('test') FROM #DUMMY
> --------------------
>
> I've got x'74657374' and it's fine.
> If I do this
>
> Code:
> --------------------
> UPDATE "MyTable" SET "MyBlobField"=Text2Hexa('test')
> --------------------
>
> It's not working. Can you tell me what I'm doing here
>
> Regards,
>
> JP

A literal is a text representation of a value in source code.
It gets converted to a value when the source is parsed.

An expression returns a value.

Having an expression return the string equivalent of a literal
does just that: return a string equivalent to a literal. It isn't
source code and will not get parsed like a literal that appeared
in the source.

--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5