Create chr function in informix 11.50
A user on IDS 11.50 (which lacks the built-in CHR()) installed the IIUG 'ascii' package to strip CR/LF from text columns before unloading to a file for Excel, but got error 217 "Column (num) not found in any table". Fernando Nunes and Jonathan Leffler identified a bug in the shipped script: the CHR() procedure selects WHERE num = i, while the ascii table's column is actually named val; recreating the procedure with 'val' fixes it, and Leffler uploaded a corrected package to the IIUG site. Others noted the unload file's escaped newlines reload fine, suggested sed to strip them for Excel, and pointed to native CHR() in v12.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
I think that what you are seeing in the unload file is likely the newlines which will be escaped - you'll see a "\\\\" at the end of the line? This just indicates that the next character (the newline) is to be treated as a literal newline, and not treated as the end of the record. If this is the case, then even though the file may look out of alignment, with the records split across lines, you can just load the file in the same format. Are you just trying to remove the newlines from the character fields? Are they causing a problem? Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CAUE CANDELORO Sent: Tuesday, October 18, 2016 2:19 PM To: ids@iiug.org Subject: Create chr function in informix 11.50 [38003] 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.
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
Uppppppsss.... it's a bit late in my latitude...
The change would have to be from "num" to "val"....
Regards.
On Tue, Oct 18, 2016 at 11: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...
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--94eb2c04428e1a973a053f2b393b
Caue:
How are you unloading the data from the table and how are you trying to
load it back? The dbaccess UNLOAD verb should be quoting embedded newlines
with backslashes which the dbaccess LOAD verb understands as well. If you
are using EXTERNAL TABLES then you have to include the ESCAPE clause in the
external table definition for both the export table and the import table.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Oct 18, 2016 at 4: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.
>
>
--001a114a1080f1277d053f2ca9af
From: Mike Walker [mailto:mike@advancedatatools.com] Sent: Tuesday, October 18, 2016 4:22 PM To: 'Caue Candeloro' Subject: RE: Create chr function in informix 11.50 [38004] Instead of removing the newlines from the data, consider removing the newlines from the unload file before loading into Excel. When I receive a data file from other source with newlines, I use âsedâ to strip out the newlines. The following script will remove the escaped newlines: INFILE=test_unl.unl sed -ne ' :loopy /\\\\\\\\$/ { N s/\\\\\\\\\\\\ //g t loopy } p ' $INFILE For example, the following file: 0|here \\\\ is \\\\ some \\\\ text|And \\\\ some \\\\ more| 0|here \\\\ is \\\\ some \\\\ text|And \\\\ some \\\\ more| Will be turned into: 0|here is some text|And some more| 0|here is some text|And some more| From: Caue Candeloro [mailto:cauecandeloro@gmail.com] Sent: Tuesday, October 18, 2016 2:47 PM To: mike@advancedatatools.com Subject: Re: Create chr function in informix 11.50 [38004] Hi Mike, Yes, this is the case. But, after unload the file i have to import this in excel. When i do that, the result is a confusing report. Caue. Em ter, 18 de out de 2016 Ã s 18:38, Mike Walker <mike@advancedatatools.com> escreveu: I think that what you are seeing in the unload file is likely the newlines which will be escaped - you'll see a "\\\\" at the end of the line? This just indicates that the next character (the newline) is to be treated as a literal newline, and not treated as the end of the record. If this is the case, then even though the file may look out of alignment, with the records split across lines, you can just load the file in the same format. Are you just trying to remove the newlines from the character fields? Are they causing a problem? Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CAUE CANDELORO Sent: Tuesday, October 18, 2016 2:19 PM To: ids@iiug.org Subject: Create chr function in informix 11.50 [38003] 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. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
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
I've uploaded a new version of the package to the IIUG web site. It
includes the fix I made in 2008 for this problem. I'm sorry I didn't get
the release to the IIUG sooner.
If you need the fix urgently, send me email directly.
On Tue, Oct 18, 2016 at 11:35 PM, Jonathan Leffler <
jonathan.leffler@gmail.com> wrote:
> 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
>
>
> ************************************************************
> *******************
> 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."
--94eb2c1a1ee096c9a1053f325cee