Subject: finderr stored procedure.
Date: Fri, 27 Apr 2001 10:57:08 -0700

This is a simple Korn shell script that takes the first line from all errors
available on the Informix engine and placed them in a table for use by a
stored procedure. You choose the database you would like to use.

The script itself runs in a few hours, after that, it is quick enough.

The idea is really harder than the implementation.

----- Korn shell script -----
#!/bin/ksh
#
# mkfinderr - Creates an unload file of the first line of all error messages
# in the system (run time is several hours). Once completed, create a database
# If the engine is upgraded, it would be wise to re-run the script.
#
# If the engine is upgraded, it would be wise to re-run the script.
#
# Jay Konigsberg - 4/2001
#
ErrorFile="infx_errors.731.uc7"
MaxErrorNo=-99999
ErrorNo=-1
rm -f $ErrorFile

while [ $ErrorNo -ge $MaxErrorNo ]
do
    Rtn=$(finderr $ErrorNo | head -1)
    if [ "$Rtn" = "Message number $ErrorNo not found." ]
    then
        print -n ""
    elif [ "$Rtn" = "$ErrorNo is outside the range of documented messages." ]
    then
        print Completed
        exit
    else
        Code=${Rtn%%    *}
        Text=${Rtn##*   }
        if [ "${Text##*\|}" != "$Text" ]
        then
            Text=${Text%%\|*}\\\|${Text##*\|}
        fi
        print - "${Code}|${Text}|" | tee -a $ErrorFile
    fi
    ((ErrorNo=ErrorNo-1))
done

----- table Schema and load -----
create table "informix".infx_errors
  (
    error_no integer,
    error_text varchar(254),
    primary key (error_no)
  ) in datadbs_s3 extent size 16 next size 16 lock mode page;
--*-->^^^^^^^^^^^^^ -- Choose dbspace
<-------******************
load from "infx_errors.731.uc7" insert into infx_errors;

----- Stored Procedure ------
CREATE PROCEDURE "informix".finderr(p_errorno INTEGER)
RETURNING VARCHAR(254);
DEFINE v_error_text VARCHAR(254);
IF p_errorno > 0
THEN
    RETURN "Error numbers are negitive, please pass a value less than 0.";
END IF;

LET v_error_text = NULL;

SELECT error_text
INTO v_error_text
FROM infx_errors
WHERE error_no = p_errorno;

IF v_error_text IS NULL
THEN
    RETURN "Error Number Not Found.";
ELSE
    RETURN v_error_text;
END IF;

END PROCEDURE;

----------------------------------------------------------------------------
Jay Konigsberg
Sr. Database Administrator
TowerRecords.com

If something is worth doing it's worth doing correctly.
----------------------------------------------------------------------------