#1  
Old 25th April 2012, 11:49 PM
jprenou jprenou is offline
Senior Member
 
Join Date: Oct 2008
Posts: 265
Default Text2Hexa function

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

Code:
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;
  #2  
Old 26th April 2012, 12:54 AM
Ad Franse
 
Posts: n/a
Default Re: Text2Hexa function

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;
  #3  
Old 26th April 2012, 03:41 AM
jprenou jprenou is offline
Senior Member
 
Join Date: Oct 2008
Posts: 265
Default

Not sure if I understand why !

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

Regards,

JP
  #4  
Old 26th April 2012, 04:14 AM
Ad Franse
 
Posts: n/a
Default Re: Text2Hexa function

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;
  #5  
Old 26th April 2012, 06:25 PM
jprenou jprenou is offline
Senior Member
 
Join Date: Oct 2008
Posts: 265
Default

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
  #6  
Old 26th April 2012, 06:32 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Text2Hexa function

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'
  #7  
Old 26th April 2012, 07:20 PM
jprenou jprenou is offline
Senior Member
 
Join Date: Oct 2008
Posts: 265
Default

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
Code:
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
Code:
UPDATE "MyTable" SET "MyBlobField"="MyBlobField"||Text2Blob('test');
but not that
Code:
UPDATE "MyTable" SET "MyBlobField"="MyBlobField"+Text2Blob('test');
Regards,

JP
  #8  
Old 27th April 2012, 04:19 PM
Brian Evans [NDX]
 
Posts: n/a
Default Re: Text2Hexa function

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


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
function Adoniram nexusdb.public.support.sql 5 1st January 2014 01:50 PM
SQL Function Wolfgang nexusdb.public.support.sql 1 26th August 2011 05:33 AM
DOW Function Dexter McKenzie nexusdb.public.support.sql 4 16th May 2011 03:55 PM
SQL Function Help Ken Randall nexusdb.public.support.sql 1 25th January 2011 04:54 PM
OT- SQL Function to convert currency to words, and function to aligncurrency. John Turner nexusdb.public.support.sql 6 2nd June 2005 06:56 AM


All times are GMT +11. The time now is 01:16 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.