From: sfc@tpghq.com (Steve Caswell)
Subject: Re: SQL Errorcodes
Organization: Palmer & Associates, Inc., Norcross, GA
Date: Wed, 14 Oct 1992 12:23:02 GMT

In article <lindsay.718941123@giaeb> lindsay@giaeb.cc.monash.edu.au (lindsay smith) writes:
>
>My organisation has recently switched over to using INGRES as a teaching
>platform for our DataBase units. Recently a student while running an ESQL
>program written in C received an SQLCODE error of -40204. The inquire_sql call
>apparently returned a blank message. I have two questions which someone might
>be able to help me with
>
>a. what is this particular error triggered by?, and
>
>b. is there anywhere in the INGRES documentation where these codes are listed?
>I have already done a fairly extensive search - but to no avail.
>
>Lindsay.
>-- 
>Lindsay Smith, Computing Lecturer
>Monash University College Gippsland    Internet: lindsay@giaeb.cc.monash.edu.au
>Switchback Rd, Churchill, 3842         Phone   : +61 51 226 669
>Victoria AUSTRALIA                     Fax     : +61 51 226 738

This is a generic error that means "A data handling error occurred.  The subcode
'04' defines the type of error."  This is documented in appendix F of the 
INGRES/SQL Reference Manual, Release 6.4, December 1991.  The problem is that
the manual does not define what "subcode 04" means.

Generic errors are defined by the ANSI SQL standard and are intended for use
by clients that may access different SQL servers.  As such, the generic error
is not very specific in providing details as to what went wrong.  A better way
to determine what is the real problem is to re-run the application or query
and ask for the INGRES-specific error.  The INGRES-specific error will typically
give much better details.

To get the INGRES-specific error, you can do one of two things.  The first is 
to set up a logical name (VMS) or environment variable (UNIX) before running 
the application or query.  This will tell INGRES to put the INGRES-specific 
error number into SQLCODE, rather than the generic error. To create this 
logical name/environment variable, do the following:

   (On VMS)  $ DEFINE II_EMBED_SET "DBMSERROR"
   (On UNIX) % setenv II_EMBED_SET DBMSERROR     (C Shell)
             $ II_EMBED_SET=DBMSERROR            (Bourne Shell)

If you encounter an error running the application with this setting, SQLCODE 
will contain the INGRES-specific error number.  You can get the error text for
this number from a file that contains the INGRES errors.  This file is in

   (VMS)  II_SYSTEM:[INGRES.FILES.ENGLISH.MESSAGES]MESSAGES.TXT
   (UNIX) $II_SYSTEM/ingres/files/english/messages/messages.txt

This is a text file that you can search or grep.

If you decide you would like to make this feature permanent in your application,
you can use the set_sql(errortype) command from within your application.  Doing
so would eliminate the need to set up II_EMBED_SET.

The other method for getting the INGRES-specific error is to change where you
look for the error in your code.  Rather than looking at sqlca.sqlcode, look
in sqlca.sqlerrd[0].  The first array element of sqlerrd contains the INGRES-
specific error number.

If you have access to the INGRES/SQL Reference Manual, there is a discussion
of these topics in chapter 6, beginning on page 6-38.
Confusing, isn't it?


Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."