• 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

Introduction

SQLSTATE and Equivalent Generic Errors