SQL Errorcodes
lindsay smith
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: lin...@giaeb.cc.monash.edu.au
Switchback Rd, Churchill, 3842 Phone : +61 51 226 669
Victoria AUSTRALIA Fax : +61 51 226 738
Ricky Leung
>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.
There are two places that you can look at:
(1) with Ingres 6.4, file ~ingres/files/generr.h. Here are the
relevant lines:
#define GE_DATA_EXCEPTION 40200 /* Data Exception, with subcode.
The subcode is identified by
SQLCODE - GE_DATA_EXCEPTION */
/* The following codes are a combination of the above error with the */
/* subcodes added in (see generic error subcodes later in this file) */
#define E_GE9D08_DATAEX_NOSUB 40200
#define E_GE9D09_DATAEX_TRUNC 40201
#define E_GE9D0A_DATAEX_NEED_IND 40202
#define E_GE9D0B_DATAEX_NUMOVR 40203
#define E_GE9D0C_DATAEX_ASSGN 40204 <------
#define E_GE9D0D_DATAEX_FETCH0 40205
...
/* Generic error subcodes */
#define GESC_NO_SC 00 /* No subcode */
#define GESC_TRUNC 01 /* Character data, right truncation */
#define GESC_NEED_IND 02 /* Null value, no indicator parameter */
#define GESC_NUMOVR 03 /* Exact numeric data, loss of significance
** (decimal overflow) */
#define GESC_ASSGN 04 /* Error in assignment */ <-----
(2) Appendix F of Ingres/SQL Reference Manual (page F-5).
In addition, Ingres also produces an Ingres Error Message Dictionary.
(I have Release 6 published in Feb. 1989).
--
Ricky Leung
Centre for Information Technology Research Phone: +61 7 365-4321
The University of Queensland Fax: +61 7 365-4399
Queensland 4072, Australia Email: ri...@citr.uq.oz.au
Steve Caswell
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 | s...@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 | s...@tpghq.com | own. They may not be perfect,
The Palmer Group | uunet!tpghq!sfc | but they're all I've got."
Cathy Savino
|> lin...@giaeb.cc.monash.edu.au (lindsay smith) writes:
[ Stuff Deleted ...]
|>
|> In addition, Ingres also produces an Ingres Error Message Dictionary.
|> (I have Release 6 published in Feb. 1989).
|>
|> --
|> Ricky Leung
|> Centre for Information Technology Research Phone: +61 7 365-4321
|> The University of Queensland Fax: +61 7 365-4399
|> Queensland 4072, Australia Email: ri...@citr.uq.oz.au
Ingred no longer produces this document, the last printing was in '89, and it is
out of date. There is an online utility that is much easier to use. It is in
(Unix version) $II_SYSTEM/ingres/sig/errhelp. There is documentation in this
directory to explain how to use it, also some information about how ingres
error messages are built.
Opinions: Mine, Mine, Mine!
--
Cathy Savino
___ _____ __ __ Ingres, An ASK Company
/ _ \ / ____\ | |/ / 1080 Marina Village Parkway
/ /_\ \ \____ \ | |\ \ Alameda, Ca 94501
/_/ \_\ \_____/ |_| \_\ (510) 748-3524
cat...@ingres.com
Cathy Savino
-402xx, with x being a sub code. Subcode 04 is an error in assignment.
All of the generic error code are documented in the back of the SQL Reference
Manual, in Appendix F.
FYI: The subcodes are not documented, so here they are: 00 no subcode, 01 Character
data, right truncation, 02 Null value, no indicator parameter, 03 Exact numeric
data, loss of significance (decimal overflow), 04 Error in assaignment, 05 Fetch
orientation has value zero, 06 Invalid datetime format, 07 Datetime field overflow,
08 Reserved, 09 Invalid indicator parameter value, 10 Invalid cursor name,
15 Invalid data type, 20 Fixed Point Overflow, 21 Exponent Overflow,
22 Fixed Point Underflow, 26 Exponent Underflow, 27 Decimal Underflow, 28 Other
unspecified math exception.
Opinions (& typo's): Mine, Mine, Mine!
Mark Jaeger
writes:>> ...Recently a student while running an ESQL
>> program written in C received an SQLCODE error of -40204...
>> a. what is this particular error triggered by?
>
> 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)
This has the unfortunate side effect of causing code to break if it was
written to expect only one type of error code (generic or
dbms-specific). Luckily, the two sets of codes don't overlap.
Thus, we have the generic code for serialization error:
#define GE_SERIALIZATION 49900 /* Serialization failure (deadlock) */
and the dbms-specific cases (these are our home-brewed definitions):
#define SQL_DEADLOCK 4700
#define SQL_LOG_FULL 4706
#define SQL_XXX 4708 /* Does anyone know what this is? */
No dbms-specific error has the code 49900, and no generic errors are in
the range 4700-4799.
When writing code to test the SQLCODE, you can check everything:
SQLCODE = sqlca.sqlcode ;
if ( SQLCODE == -GE_SERIALIZATION
|| SQLCODE == -SQL_DEADLOCK
|| SQLCODE == -SQL_LOG_FULL
|| SQLCODE == -SQL_XXX )
...retry once or twice...
else if ( SQLCODE < 0 )
...rollback and quit...
else
...okay...
This code will continue to work regardless of the ii_embed_set setting.
We prefer to leave the SQLCODE with the ANSI-standard generic
definition, and use inquire_ingres if we want to get the dbms-specific
error as well. We automatically trap errors and log them to an error
log for later analysis.
> Confusing, isn't it?
Amen.
--Mark Jaeger internet: cs...@gsbvax.uchicago.edu
Graduate School of Business yellnet: (312) 702-0328
University of Chicago faxnet: (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus. Mach' mit und kopiere mich in Deine .signature.