Informix 9.52C1 - replace carriage return and line feed

Question

I search google and couldn't find anything that really helped me IBM website show Informix 11.5 and up functions, like if they have stop supporting 9.52C1 or something. Hence the reason I am here.

As the topic stated I'm using Informix 9.52C1. I got this information using the SQL:

SELECT owner FROM systables WHERE TABNAME= ' VERSION';

I'm not sure if the replace function is even supported because when I execute the statement:

SELECT col1, REPLACE('r','p','poster') as col2 FROM table;

The column col2 contains just the letter 'r' however no error was thrown although I used the REPLACE() function.

I know the escape characters (CRLF) can be found using the "\r\n" escape format because this SQL worked accordingly:

SELECT *  FROM table WHERE col1 LIKE '%\r\n%';

Note: I used the above SQL to retrieve the version number as I only had access via a client DB application. If however you have access to the actual host running the informix DB server I'm sure there is some command arguement to resolve the version maybe the defualt linux --version or -v arguement with the db server application.

And this indeed returned only the records that contained CRLF.

My main job is to place a backslash in front of CR and LF as I am migrating data from Informix 9.52C1 to a PostgreSQL database. I'm planning to use the COPY function to load the data to the PostgreSQL database and the COPY function works in this manner as I have done a test record. My dilemma is extracting the data from Informix in the correct format. Can anyone assist with this issue?

I have tried:

SELECT REPLACE('\\\r\\\n','\r\n',col1) as description FROM table;

However this didn't work I believe due to the replace function, as I have mention I am not sure if the replace function is available in this Informix version.

Thanks in advance,

jerg

P.S. None of the functions ASCII(),CHAR() and CHR() worked either. Some sites suggested these function. But as far as I can see the functions CHR() & ASCII() was implemented in verison 11.5+. This post suggested the functions.

Answer 1

To migrate data between databases you can use JDBC drivers (I use Jython, but Java or other language which can use JDBC driver will be ok) and then SELECT ... from source database (Informix) and INSERT ... just read data into destination (PostgreSQL). With BatchInsert or PreparedStatement it is really fast. In my code it looks like:

insert_str = 'INSERT INTO ' + table_name + ' (' + column_names + ') VALUES (' + question_marks + ')'
insert_stmt = db_to.prepareStatement(insert_str)
...
pstm2 = db_from.createStatement()
rs_in = pstm2.executeQuery('SELECT %s FROM %s %s' % (column_names, table_name, order_str))
...
while (rs_in.next()):
    for i in range(1, col_count + 1):
        insert_stmt.setObject(i, rs_in.getObject(i))

Of course you must take care of errors, auto commit, fetch size etc. but I think it is worth your effort.

Your idea with COPY ... is also good. Do you save output from Informix in text file? If it is in text file you can simply correct text file before importing it to PostgreSQL.

Answer 2

I don't recognize the version 9.52C1. An Informix version might be 9.52.UC1 — with a choice from U, F, W, H, T for the first letter. But then again, the main version numbers were 9.00..9.03, 9.10..9.16, 9.20, 9.21, 9.30, 9.40, 10.00, 11.10, 11.50, 11.70, 12.10 — a sequence which doesn't include a 9 and a 5 in a single version. Well, in many ways, it doesn't matter; any version 9.x has been out of support for most of a decade, if not longer, and shouldn't still be in use.

Assuming that an upgrade to a modern version of Informix is not in the cards, you are stuck with implementing your own functions. To do the job, you'd do best to implement them in C and install them as a package of UDRs (user-defined routines) in a shared library. That would give you the best performance. Assuming that's not feasible (I don't have a set of such functions on hand, but it shouldn't be hard to write them), then you have to fall back on SPL (stored procedure language) routines. These are not fast because of the limitations of SPL w.r.t substring operations — specifically, the built-in substring operations with [] notation do not accept variables as subscripts. That's really painful, to be polite about it.

Here's a version of char_at which fetches a single character at a given position in a string of up to 255 characters:

-- @(#)$Id: char_at.spl,v 1.1 1999/05/17 23:59:59 jleffler Exp $
--
-- CHAR_AT stored procedure, to return character at given position in string
--
-- Author: J Leffler
-- Date: 1999-05-17

CREATE PROCEDURE char_at(str VARCHAR(255), pos SMALLINT) RETURNING CHAR(1);

    DEFINE c CHAR(1);

    IF pos > LENGTH(str) OR pos <= 0 THEN
        LET c = NULL;
    ELIF pos <= 16 THEN
        IF   pos =  1 THEN LET c = str[ 1];
        ELIF pos =  2 THEN LET c = str[ 2];
        ELIF pos =  3 THEN LET c = str[ 3];
        ELIF pos =  4 THEN LET c = str[ 4];
        ELIF pos =  5 THEN LET c = str[ 5];
        ELIF pos =  6 THEN LET c = str[ 6];
        ELIF pos =  7 THEN LET c = str[ 7];
        ELIF pos =  8 THEN LET c = str[ 8];
        ELIF pos =  9 THEN LET c = str[ 9];
        ELIF pos = 10 THEN LET c = str[10];
        ELIF pos = 11 THEN LET c = str[11];
        ELIF pos = 12 THEN LET c = str[12];
        ELIF pos = 13 THEN LET c = str[13];
        ELIF pos = 14 THEN LET c = str[14];
        ELIF pos = 15 THEN LET c = str[15];
        ELIF pos = 16 THEN LET c = str[16];
        END IF;
    ELIF pos <=  32 THEN LET c = char_at(str[ 17, 32], pos -  1 * 16);
    ELIF pos <=  48 THEN LET c = char_at(str[ 33, 48], pos -  2 * 16);
    ELIF pos <=  64 THEN LET c = char_at(str[ 49, 64], pos -  3 * 16);
    ELIF pos <=  80 THEN LET c = char_at(str[ 65, 80], pos -  4 * 16);
    ELIF pos <=  96 THEN LET c = char_at(str[ 81, 96], pos -  5 * 16);
    ELIF pos <= 112 THEN LET c = char_at(str[ 97,112], pos -  6 * 16);
    ELIF pos <= 128 THEN LET c = char_at(str[113,128], pos -  7 * 16);
    ELIF pos <= 144 THEN LET c = char_at(str[129,144], pos -  8 * 16);
    ELIF pos <= 160 THEN LET c = char_at(str[145,160], pos -  9 * 16);
    ELIF pos <= 176 THEN LET c = char_at(str[161,176], pos - 10 * 16);
    ELIF pos <= 192 THEN LET c = char_at(str[177,192], pos - 11 * 16);
    ELIF pos <= 208 THEN LET c = char_at(str[193,208], pos - 12 * 16);
    ELIF pos <= 224 THEN LET c = char_at(str[209,224], pos - 13 * 16);
    ELIF pos <= 240 THEN LET c = char_at(str[225,240], pos - 14 * 16);
    ELIF pos <= 255 THEN LET c = char_at(str[241,255], pos - 15 * 16);  -- Note asymmetry in upper bound!
    ELSE                 LET c = NULL;  -- Not reached!
    END IF;

    RETURN c;

END PROCEDURE;

Handling general substrings and the like is similarly painful. I've never gone to the effort of building even a semi-performant SPL implementation of SUBSTR. It is far, far better to upgrade to a version of Informix that has the support built-in.

CHR() and ASCII()

The CHR(). ASCII() functions can be simulated with:

-- @(#)$Id: chr.sql,v 1.2 2008/09/19 18:48:37 jleffler Exp $
--
-- @(#)Procedure CHR() - return character corresponding to integer

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 val = i;
    END IF;
    RETURN c;
END PROCEDURE;

-- @(#)$Id: ascii.sql,v 1.2 2008/09/19 18:40:19 jleffler Exp $
--
-- Procedure ASCII - returning integer corresponding to character.
-- Misnomer: it works on any single-byte character.

CREATE PROCEDURE jl_ascii(C CHAR) RETURNING INT AS result;
    DEFINE i INTEGER;
    IF c IS NULL THEN
        LET i = 0;
    ELSE
        SELECT val INTO i FROM ascii WHERE chr = c;
    END IF;
    RETURN i;
END PROCEDURE;

which requires a table:

-- @(#)$Id: asciitbl.sql,v 1.2 2005/03/30 17:51:12 jleffler Exp $
--
-- @(#)Create ASCII Table (for ASCII and CHR functions).

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;

and the data to go in it — 255 lines such as:

…
32| 
33|!
34|"
35|#
36|$
37|%
38|&
39|'
40|(
…
64|@
65|A
66|B
67|C
68|D
69|E
…

You should be able to find a copy of this code at the IIUG under the name ascii.