• OpenSQL Reference Guide
  • Introduction
    • In This Guide
    • Audience
    • Syntax Conventions Used in This Guide
    • Conventions for Embedded OpenSQL Examples
    • Terminology Used in This Guide
  • Overview of OpenSQL
    • What Is OpenSQL?
      • Enterprise Access Products
      • Ingres Star
      • Interactive OpenSQL
      • Embedded OpenSQL
        • How Embedded OpenSQL Programs Are Built
      • Dynamic OpenSQL—Specifying Parameters at Runtime
      • Differences Between Embedded and Interactive OpenSQL
    • OpenSQL Features
      • Rules for Naming Objects
      • Regular and Delimited Identifiers
      • Restrictions on Identifiers
        • Case Sensitivity of Identifiers
      • Comment Delimiters
      • Statement Terminators
      • Correlation Names
  • OpenSQL Data Types
    • OpenSQL Data Types
    • Character Data Types
      • Character Data Type
      • Varchar Data Type
      • Long Varchar Data Type
        • Restrictions on Long Varchar Columns
    • Unicode Data Types
      • Nchar Data Type
      • Nvarchar Data Type
      • Long Nvarchar Data Type
        • Restrictions on Long Nvarchar Columns
    • Numeric Data Types
      • Integer Data Type
      • Decimal Data Type
      • Floating-point Data Type
    • Abstract Data Types
      • Date Data Type
        • Absolute Date Input Formats
      • Money Data Type
    • Binary Data Types
      • Long Byte Data Type
    • Storage Formats of Data Types
    • Literals
      • String Literals
        • Quotes in Strings
      • Numeric Literals
        • Integer Literals
      • Floating-point Literals
    • OpenSQL Constants
    • Nulls
      • Nulls and Comparisons
      • Nulls and Aggregate Functions
  • Elements of OpenSQL Statements
    • Operators
      • Arithmetic Operators
      • Comparison Operators
      • Logical Operators
    • Operations
      • Assignment Operations
        • Character String Assignment
        • Numeric Assignment
        • Date Assignment
        • Null Assignment
      • Arithmetic Operations
        • Default Type Conversion
        • Arithmetic Operations on Decimal Data Types
        • Comparison of Decimal Handling Settings
      • Functions
        • Function Support for Enterprise Access Products
      • Scalar Functions
        • Data Type Conversion Functions
        • Numeric Functions
        • String Functions
        • String Concatenation Results
        • Date Functions
        • Date_trunc Function
        • Date_part Function
        • Bitwise Functions
        • Hash Function
        • Random Number Functions
      • rand() and rand(integer) Functions
      • Aggregate Functions
        • Unary Aggregate Functions
        • Binary Aggregate Functions
        • Count(*) Function
        • Aggregate Functions and Decimal Data
        • Using Group By Clause with Aggregate Functions
        • Restrictions on the Use of Aggregate Functions
      • Ifnull Function
        • Ifnull Result Data Type
        • IFNULL and Decimal Data
      • Universal Unique Identifier (UUID)
        • UUID Format
        • UUID Functions
    • Expressions
    • Predicates
      • Like Predicate
      • Between Predicate
      • In Operator
      • Any-or-All Predicate
      • Exists Predicate
      • IS BOOLEAN Predicate
      • Is Null Predicate
    • Search Conditions
    • Subqueries
      • Subqueries in the FROM Clause (Derived Tables)
      • Derived Table Syntax
  • Embedded OpenSQL
    • Embedded OpenSQL
    • How Embedded OpenSQL Programs Are Processed
    • Syntax of an Embedded OpenSQL Statement
    • Structure of Embedded OpenSQL Programs
    • Host Language Variables
      • Variable Declarations
      • The Include Statement
      • Variable Usage
      • Variable Structures
      • The Dclgen Utility—Generate Structure
      • Indicator Variables
      • Null Indicators and Data Retrieval
      • Using Null Indicators to Assign Nulls
      • Indicator Variables and Character Data Retrieval
      • Null Indicator Arrays and Host Structures
    • Data Manipulation with Cursors
      • An Example of Cursor Processing
      • Cursor Declaration
      • Opening a Cursor
      • Open Cursors and Transaction Processing
      • Fetch Statement—Fetch the Data
      • Fetching Rows Inserted by Other Queries
      • Using Cursors to Update Data
      • Using Cursors to Delete Data
        • Example of Updating and Deleting with Cursors
      • Closing Cursors
      • Summary of Cursor Positioning
        • Example of Dynamically Specified Cursor Names
    • Data Handlers for Large Objects
      • Errors in Data Handlers
      • Restrictions on Data Handlers
      • Large Objects in Dynamic SQL
        • Length Considerations
        • Data Handlers in Dynamic SQL
      • Example: PUT DATA Handler
      • Example: GET DATA Handler
      • Example: Dynamic SQL Data Handler
  • Dynamic OpenSQL
    • Dynamic Programming
    • The SQL Descriptor Area (SQLDA)
      • Structure of the SQLDA
      • Including the SQLDA in a Program
      • Describe Statement and the SQLDA
      • Data Type Codes
      • The Using Clause
    • Dynamic OpenSQL Statements
      • Execute Immediate Statement
      • Prepare and Execute Statements
      • Describe Statement
    • How to Execute a Dynamic Nonselect Statement
      • Preparing and Executing a Non-select Statement
      • Executing a Non-select Statement Using Execute Immediate
    • How to Execute a Dynamic Select Statement
      • When the Result Column Data Types Are Known
      • When the Result Column Data Types Are Unknown
      • Preparing and Describing the Select Statement
      • Analyzing the Sqlvar Elements
      • Executing the Select with Execute Immediate
      • Retrieve the Results Using a Cursor
  • OpenSQL Features
    • Transactions
      • How Transactions Work
      • How Consistency Is Maintained During Transactions
      • How Transactions Are Controlled
      • How Transactions Are Committed
      • Abort Policy for Statements and Transactions
      • Effects of Aborting Transactions
      • Interrupting Transactions
    • Status Information
      • The Dbmsinfo Function—Retrieve Information on Current Session
      • The INQUIRE_SQL Statement—Retrieve Runtime Information
      • The SQL Communications Area (SQLCA)
    • Error Handling
      • The SQLSTATE Variable
      • Local and Generic Errors
      • Error Message Format
      • Display of Error Messages
      • Error Handling in Embedded Applications
      • Error Information from SQLCA
      • Error Trapping Using Whenever Statement
        • Scope of the Whenever Statement
      • How You Define an Error Handler
      • Error Checking Using Inquire Statements
      • Error Message Suppression
      • Program Termination When Errors Occur
      • Handling Deadlock
        • Noncursor Template for Handling Deadlock
        • Single Cursor Template for Handling Deadlock
        • Master/Detail Template for Handling Deadlock
    • Multiple Session Connections
      • Session Identifier—Connect to Multiple Sessions
      • Session Switching
      • Session Termination
      • Multiple Sessions and the SQLCA
      • Multiple Sessions and the DBMS
      • Multiple Session Examples
    • Database Procedures
      • How Database Procedures Are Created
      • Register Procedure Statement—Register Database Procedure
        • Remove Procedure Statement—Delete a Procedure Registration
      • Guidelines for Executing Database Procedures
    • DBMS Extensions
      • Enterprise Access and EDBC With Clause
      • With Clause Syntax
    • Database Events
      • Database Event Statements
        • Creating a Database Event
        • Raising a Database Event
        • Registering to Receive a Database Event
        • How a Database Event is Received
        • Methods for Processing Database Events
        • Using GET DBEVENT
        • Using WHENEVER DBEVENT
        • Using User-defined Database Event Handlers
        • Removing a Database Event Registration
        • Dropping a Database Event
  • OpenSQL Statements
    • OpenSQL Version
    • Context for SQL Statements
    • Extended Statements
    • BEGIN DECLARE
      • BEGIN DECLARE Example
    • CALL
      • CALL Examples
    • CLOSE
      • Embedded Usage
      • Usage in OpenAPI, ODBC, JDBC, .NET
      • CLOSE Example
    • COMMIT
      • Embedded Usage
      • Usage in OpenAPI, ODBC, JDBC, .NET
      • COMMIT Example
    • CONNECT
      • Permissions
      • CONNECT Examples
    • CREATE DBEVENT
      • Embedded Usage
      • Usage in OpenAPI, ODBC, JDBC, .NET
    • CREATE INDEX
      • Embedded Usage
      • CREATE INDEX Example
    • CREATE TABLE
      • Column Specification--Describe Column Characteristics
      • Using CREATE TABLE...AS SELECT
      • CREATE TABLE Examples
    • CREATE VIEW
      • Embedded Usage
      • CREATE VIEW Example
    • DECLARE CURSOR
      • Usage in OpenAPI
      • DECLARE CURSOR Examples
    • DECLARE GLOBAL TEMPORARY TABLE
      • Embedded Usage
      • Restrictions
      • Related Statements
      • DECLARE GLOBAL TEMPORARY TABLE Examples
    • DELCARE Statement
      • DECLARE STATEMENT Example
    • DECLARE TABLE
      • DECLARE TABLE Example
    • DELETE
      • Embedded Usage
      • Non-Cursor Delete
      • Cursor Delete
      • DELETE Example
    • DESCRIBE
    • DIRECT EXECUTE IMMEDIATE
    • DISCONNECT
      • DISCONNECT Examples
    • DROP
      • Embedded Usage
      • DROP Examples
    • DROP DBEVENT
      • Embedded Usage
    • END DECLARE SECTION
    • ENDSELECT
      • ENDSELECT Example
    • EXECUTE
      • EXECUTE Examples
    • EXECUTE IMMEDIATE
      • EXECUTE IMMEDIATE Example
    • EXECUTE PROCEDURE
      • Passing Parameters - Non-dynamic Version
      • Passing Parameters - Dynamic Version
      • Execute Procedure Loops
        • Permissions
        • Locking
        • Performance
        • EXECUTE PROCEDURE Examples
    • FETCH
      • FETCH Examples
    • GET DBEVENT
    • HELP
      • HELP Examples
    • INCLUDE
      • INCLUDE Examples
    • INQUIRE_SQL
      • Inquiring About Database Events
      • Types of Inquiries
      • INQUIRE_SQL Example
    • INSERT
      • Embedded Usage
      • INSERT Examples
    • OPEN
      • OPEN Examples
    • PREPARE
      • PREPARE Example
    • RAISE DBEVENT
      • Embedded Usage
    • REGISTER DBEVENT
      • Embedded Usage
    • REMOVE DBEVENT
    • ROLLBACK
      • Embedded Usage
      • Performance
    • SELECT (interactive)
      • SELECT Statement Clauses
      • SELECT Clause
      • FROM Clause
        • Specifying Tables and Views
      • WHERE Clause
        • Joins
        • ANSI/ISO Join Syntax
        • Outer Joins
        • Join Relationships
        • Subselects
      • ORDER BY Clause
      • GROUP BY Clause
      • HAVING Clause
      • UNION Clause
      • Query Evaluation
      • SELECT (interactive) Examples
    • SELECT (embedded)
      • Non-Cursor Select
      • Select Loops
      • Retrieving Values into Host Language Variables
      • Use of Host Language Variables in a Union
      • REPEATED Queries
      • Cursor Select
      • Error Handling for Embedded SELECT
      • Embedded Usage
      • SELECT (embedded) Examples
    • SET
    • SET_SQL
    • UPDATE
      • Embedded Usage
      • Permissions
      • Cursor Updates
      • Locking
      • Related Statements
      • UPDATE Examples
    • WHENEVER
      • WHENEVER Examples
  • Extended Statements
    • CREATE SCHEMA
      • Restrictions
      • Embedded Usage
      • Permissions
      • CREATE SCHEMA Example
    • CREATE TABLE (extended)
      • Column Specifications
        • DEFAULT Clause
        • NULL Clause
      • Constraints
        • UNIQUE Constraints
        • CHECK Constraints
        • Referential Constraints
        • Primary Key Constraint
      • Column-Level Constraints and Table-Level Constraints
      • Constraint Index Options
        • NO INDEX Option
        • INDEX = BASE TABLE STRUCTURE Option
        • INDEX = Index_Name Option
      • Using CREATE TABLE...AS SELECT
      • Embedded Usage
      • Permissions
      • CREATE TABLE (extended) Examples
    • GRANT
      • The Grant All Privileges Option
      • The Grant Option
      • Embedded Usage
      • Permissions
      • GRANT Examples
    • REVOKE
      • Revoking the Grant Option
      • Restrict versus Cascade
      • Embedded Usage
      • Permissions
      • REVOKE Example
    • SELECT
  • OpenSQL Limits
    • OpenSQL Limits
  • OpenSQL Standard Catalogs
    • Standard Catalog Level
    • System Catalog Characteristics
    • Standard Catalog Interface
      • iialt_columns Catalog
      • iiaudittables Catalog
      • iicolumns Catalog
      • iiconstraint_indexes Catalog
      • iiconstraints Catalog
      • iidb_subcomments Catalog
      • iidb_comments Catalog
      • iidbcapabilities Catalog
        • cap_capability Column
      • iidbconstants Catalog
      • iievents Catalog
      • iigwscalars Catalog
      • iihistograms Catalog
      • iiindex_columns Catalog
      • iiindexes Catalog
      • iikeys Catalog
      • iiphysical_tables Catalog
      • iiprocedures Catalog
      • iiref_constraints Catalog
      • iiregistrations Catalog
      • iisecurity_alarms Catalog
      • iistats Catalog
      • iisynonyms Catalog
      • iitables Catalog
      • iiviews Catalog
    • Mandatory and Ingres-Only Standard Catalogs
      • Mandatory Catalogs with Entries Required
      • Mandatory Catalogs Without Entries Required
      • Ingres-Only Catalogs
  • A. Keywords
    • Reserved Keywords and Identifiers
    • Abbreviations Used in Keyword Lists
    • Reserved Single Keywords
    • Reserved Double Keywords
    • ANSI/ISO SQL Keywords
  • B. SQLSTATE Values and Generic Error Codes
    • How Error Code Mapping Works
    • SQLSTATE Values
    • Generic Error Codes
      • Generic Error Data Exception Subcodes
    • SQLSTATE and Equivalent Generic Errors

OpenSQL Reference Guide

SQLSTATE and Equivalent Generic Errors