[Info-ingres] Convert hex string to an integer

[Martin Bowes]

Hi All,

 

There has got to be an easier  way….

 

Given a string which represents a hex value, convert it to an integer of a given size.

 

The only solution I’ve come up with is by making an OME function. See attached.

 

Here is how I would describe that function…

hex2int(hexString, integerSize)

Convert the indicated hex string into a signed integer of the given size. Case is ignored.

 

Two’s complement is respected and hence when converting to integer1 the string ‘FF’ becomes -1. However the same string converted to integer2 becomes 255.

 

The hex string may be prefixed with ‘0x’, ‘+0x’, or ‘-0x’. But in the case of ‘-0x’ there is the possibility of a double negative if the string converts to a negative integer.

 

And here is some sample output…

select hex2int('FFFD', 8)\g

┌──────────────────────┐

│col1                  │

├──────────────────────┤

│                 65533│

└──────────────────────┘

(1 row)

 

select hex2int('FFFD', 22)\g

E_OME001 hex2int(): Invalid size '22' specified. It must be one of 1, 2,

    4, 8

    (Tue Aug 10 14:47:58 2021)

 

select hex2int('FFFD', 2)\g

 

┌──────┐

│col1  │

├──────┤

│    -3│

└──────┘

(1 row)

 

select hex2int('garbage', 8)\g

Executing . . .

 

E_OME001 hex2int(): Non hex character 'g' found in string.

    (Wed Aug 11 14:08:29 2021)

 

So the questions are:

1.       Can anyone do this in simple SQL? If so, how?

2.       Could people have a quick look over the C code and give me some feedback please. I’m not a C coder and after compiling with no errors I’m not too sure what more I have to do.

 

Thanks,

 

Marty

[Martin Bowes]

Hi Adrian,

 

I like the suggestion on ship and allow the users to debug.

 

The new (11.2) UDFs available with CREATE FUNCTION are sweet but very limited compared to OME functions. Specifically:

1.       They cannot use C, javaScript, Perl.

I’ve asked for Perl and C to be included in any future development.

2.       They are not installation wide.

They are only available to the database in which they are defined.

3.       Furthermore, they are not available to all users, only the user who created them.

There is no grant execute on function statement available.

 

Where the new UDFs come into their own is for little things.

Eg. cat hyperbolic.sql

\r

set autocommit on;

\p\g

CREATE OR REPLACE FUNCTION cosh(x FLOAT8)

RETURN(FLOAT8) AS

BEGIN

    RETURN (EXP(x) + EXP(-x)) / 2;

END;

\p\g

CREATE OR REPLACE FUNCTION sinh(x FLOAT8)

RETURN(FLOAT8) AS

BEGIN

    RETURN (EXP(x) - EXP(-x)) / 2;

END;

\p\g

CREATE OR REPLACE FUNCTION tanh(x FLOAT8)

RETURN(FLOAT8) AS

BEGIN

    RETURN sinh(x) / cosh(x);

END;

\p\g

 

Marty

 

From: Adrian Williamson <adrian.w...@rationalcommerce.com>
Sent: 11 August 2021 15:11
To: Martin Bowes <martin...@ndph.ox.ac.uk>; roy....@rationalcommerce.com
Subject: RE: [Info-ingres] Convert hex string to an integer

 

https://docs.actian.com/actianx/11.2/index.html#page/RelSum%2FScalar_User-defined_Functions_(UDFs).htm%23wwconnect_header

 

UDF not OME…

 

From: Adrian Williamson <adrian.w...@rationalcommerce.com>
Sent: 11 August 2021 15:07
To: 'Martin Bowes' <martin...@ndph.ox.ac.uk>; 'roy....@rationalcommerce.com' <roy....@rationalcommerce.com>
Subject: RE: [Info-ingres] Convert hex string to an integer

 

Ø  after compiling with no errors I’m not too sure what more I have to do.

 

Ship it and let the customers find the bugs?

 

Te He.

 

Good work Marty.

 

Did I read somewhere you can extend Ingres using Perl or Java or basically something else other than ‘C’?

1. Can anyone do this in simple SQL? If so, how?

1. Could people have a quick look over the C code and give me some feedback please. I’m not a C coder and after compiling with no errors I’m not too sure what more I have to do.

 

Thanks,

 

Marty