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. ----------------------------------------------------------------------------