IDS Forum

Re: Create chr function in informix 11.50

Posted By: Jonathan Leffler
Date: Wednesday, 19 October 2016, at 2:35 a.m.

In Response To: Re: Create chr function in informix 11.50 (Fernando Nunes)

The two columns in the table ASCII are chr and val; the SELECT in function
CHR() should be:

SELECT chr INTO c FROM ascii WHERE val = i;

This is symmetric with the SELECT in function ASCII():

SELECT val INTO i FROM ascii WHERE chr = c;

It's difficult to test this in Informix 12.10 because the functions already
exist in the DB (and there's no need for the table any more, in the
ordinary course of events). So, the procedures have to be renamed to be
creatable in 12.10. By renaming them with a prefix 'old_' (so the
procedures are 'old_chr()' and 'old_ascii()', the fix with num mapped to
val works.

I'll package up a new version and ship it to the IIUG web site.

On Tue, Oct 18, 2016 at 3:27 PM, Fernando Nunes <domusonline@gmail.com>
wrote:

> Somehow the scripts in IIUG are not correct. Jonathan may want to fix this,
> but you can do it yourself:
>
> CREATE TABLE ascii
> (
>
> val INTEGER NOT NULL UNIQUE CONSTRAINT u1_ascii,
>
> chr CHAR(1) NOT NULL UNIQUE CONSTRAINT u2_ascii
> );
> REVOKE ALL ON ascii FROM PUBLIC;
> GRANT SELECT ON ascii TO PUBLIC;
>
> CREATE PROCEDURE chr(i INTEGER) RETURNING CHAR(1) AS result;
>
> DEFINE c CHAR;
>
> IF i < 0 OR i > 255 THEN
>
> RAISE EXCEPTION -746, 0, 'CHR(): integer value out of range 0..255';
>
> END IF;
>
> IF i = 0 OR i IS NULL THEN
>
> LET c = NULL;
>
> ELSE
>
> SELECT chr INTO c FROM ascii WHERE num = i;
>
> END IF;
>
> RETURN c;
> END PROCEDURE;
>
> The column name is "chr" but on the SELECT inside the procedure about the
> query uses "num".
> The easier fix would be to recreate the procedure changing "num" with "chr"
>
> Apart from this, my suggestion would be to use and Informix version from
> this decade (V12) before the decade ends :)
> Then you'd have the native CHR() and many other improvements. Also take a
> look at IFX_ALLOW_NEWLINE()
>
> Regards.
>
> On Tue, Oct 18, 2016 at 9:18 PM, CAUE CANDELORO <cauecandeloro@gmail.com>
> wrote:
>
> > Hi everyone.
> > I'm new in the forum and also with informix.
> > I am facing a problem with text fields that have line break. When I
> unload
> > the
> > information for a .txt file, everything is out of position.
> >
> > Looking on the internet i found the chr () function, but I'm using
> informix
> > version 11:50 and this function does not exist. Then I found this link
> > (ftp://ftp.iiug.org/pub/informix/pub/ascii.tgz) showing how to create
> this
> > function.
> >
> > When load the ascii.unl file, the message say: 255 lines load. But if I
> do
> > a
> > select on the table, the results are all mixed and apparently missing
> > information.
> >
> > I'm trying to run the command:
> > trim (replace (replace (abcd, chr (10), ''), chr (13), '')) as abcd
> > and return this error 217 - Column (num) not found in any table.
> >
> > Does anyone know what I'm doing wrong?
> > Thanks a lot for the help !
> >
> > Caue.
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a113f97723da960053f2b34b1
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--001a11411a3e782215053f32039b

Messages In This Thread

• Create chr function in informix 11.50
  CAUE CANDELORO -- Tuesday, 18 October 2016, at 4:18 p.m.
  • RE: Create chr function in informix 11.50
    Mike Walker -- Tuesday, 18 October 2016, at 4:37 p.m.
    • RE: Create chr function in informix 11.50
      Mike Walker -- Tuesday, 18 October 2016, at 8:24 p.m.
  • Re: Create chr function in informix 11.50
    Fernando Nunes -- Tuesday, 18 October 2016, at 6:27 p.m.
    • Re: Create chr function in informix 11.50
      Jonathan Leffler -- Wednesday, 19 October 2016, at 2:35 a.m.
      • Re: Create chr function in informix 11.50
        Jonathan Leffler -- Wednesday, 19 October 2016, at 3:02 a.m.
  • Re: Create chr function in informix 11.50
    Fernando Nunes -- Tuesday, 18 October 2016, at 6:30 p.m.
  • Re: Create chr function in informix 11.50
    Art Kagel -- Tuesday, 18 October 2016, at 8:12 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.