How to convert carriage return to another char string in Informix SQL or 4GL

[Wayne T]

I am post this question for my co-worker, here is his questions. Any
help would be appreciated.

Hi,

In a databse table there is a field of type character or string - or
whatever informix calls it. This string contains new line characters
or carriage returns.
In a 4gl program this field is queried and printed.

Just before this field is printed the line number internal variable
"lineno" is , say, 10. Then this field is printed. Lets say the field
has one new line character
( IN other words the field contains"

Hello,
How are you."
)

As you can see the field has one new line character after "Hello,"
since there are two lines.

Immediately after printing the above field value, the line number
internal variable "lineno" is , 12.
It should be 13 since two lines have been used by the field.

This causes the "skip to top of page" command to not really skip to
the top of the page and report format is all messed up.
We need this fixed.

But for now, as a work around, if I can replace any occurence of "new
line" or "carriage return" with a space or a dash, I can live with
that
but unfortunately I can't find a way to type in the "new line"
character in the function call.

For example, in order to replace any occurence of "y" with a "z" in
the string "xyz"
I will use this sql function:

replace("xyz", "y", "z")
the result will be "xzz".

Here I specified the character "y" to be replaced.
How do I specify that I need to replace a "carriage return" or a
"newline" character.
I tried '^M' but this did not work.

[Jonathan Leffler]

Wayne T wrote:

'^M' is just a 2-character string. You probably wanted ASCII(13).

The line number computation isn't immediately obvious. You print
lines 10 and 11 to contain the data with a single new line, and expect
the line number to jump to 13 instead of 12? Did someone write the
question once with 2 newlines in the data, and then only partially
amend the question? Or what? The data sample appears to have a blank
line and then 'Hello,' and then 'How are you.'. If the data really
has a single new line in it, then it seems to me that ISQL/ACE and
I4GL are behaving magnificently. If the data really has several new
lines in it, then there is room to think about 'bug'.

Oh, and you didn't mention version information. Make a New Year's
resolution - "I will always include version and platform information
in questions posted to comp.databases.informix". Thanks!

--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[Dragi Raos]

Have you tried using C escapes from 4GL (\r or \015 for <CR>, \n or \012
for <LF>)? Mind you, I am not at all sure that will work, haven't used
4GL in more than a decade, don't remember whether strings are ordinary C
char arrays and don't have docs :-)

Alternatively, you could write your own little C function to do that and
link it into your 4GL program (or even massage ESQL/C files - those with
.ec extension - that are result of the first phase of 4GL compilation -
dirty but quick ;-)

Cheers!

Dragi "Bonzi" Raos
Zagreb, Croatia

sending to informix-list

[Ronald Cole]

Jonathan Leffler <jlef...@earthlink.net> writes:
> line and then 'Hello,' and then 'How are you.'. If the data really
> has a single new line in it, then it seems to me that ISQL/ACE and
> I4GL are behaving magnificently. If the data really has several new
> lines in it, then there is room to think about 'bug'.

Not too surprising. I've had to pull some heinous tricks in my time
to get Informix report writers to do things that their developers
obviously never considered.

One "line" is probably exactly defined as the output of one "print"
statement. You'll probably have to write some code to extract each
line from the variable and print them individually to get what you
want.

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ron...@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

[Dragi Raos]

Have you tried using C escapes from 4GL (\r or \015 for <CR>, \n or \012
for <LF>)? Mind you, I am not at all sure that will work, haven't used
4GL in more than a decade, don't remember whether strings are ordinary C
char arrays and don't have docs :-)

Alternatively, you could write your own little C function to do that and
link it into your 4GL program (or even massage ESQL/C files - those with

Cheers!

sending to informix-list
sending to informix-list

[Art S. Kagel]

On Fri, 02 Jan 2004 09:55:31 -0500, Wayne T wrote:

One idea that may solve the problem depending... The 4GL report writer
normally pads pages to their defined length with newline characters. So
having an extra newline printed by your embedded newline string which is not
counted messes up the line count and misalligns the following pages. A
solution is to tell the 4GL report module to use FORMFEED (^L) to paginate
instead. This is done by placing the following line at the top of the REPORT
module function in the OUTPUT section:

OUTPUT
TOP OF PAGE "^L"

You may still have a problem with the report writer missing that a page has
filled and so outputting a page with a single line on it. You can fix that
also by keeping your own line count, adjusted for the embedded newlines, and
forcing a page break (with SKIP TO TOP OF PAGE) when your own count indicates
that a page boundary has been reached, then clear the counter in the PAGE
HEADER section (so it also clears when the report writer pages for a
subsection boundary that specifies a new page). Been doing this for years in
combination with setting the 4GL page length to 99999 in order to get variable
length paging.

Art S. Kagel

[Doug Lawry]

Your function could be used as follows in any 4GL version:

replace("xyz", ASCII(10), "-")

It's a little harder in SPL, but in version 9 you can use

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T')

to allow linefeeds in quoted strings.

See Art's answer if you would prefer to fix using form feeds.

Regards,
Doug Lawry
www.douglawry.webhop.org

"Wayne T" <ntr...@yahoo.com> wrote in message
news:e6623a40.04010...@posting.google.com...