• OpenSQL Reference Guide
•  1. Introduction
  • In This Guide
  • Audience
  • Syntax Conventions Used in This Guide
  • Conventions for Embedded OpenSQL Examples
  • Terminology Used in This Guide
•  2. 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
•  3. 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
•  4. 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
  •  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
    •  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 Null Predicate
  • Search Conditions
  •  Subqueries
    • Subqueries in the FROM Clause (Derived Tables)
    • Derived Table Syntax
•  5. 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
•  6. 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
•  7. 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
•  8. OpenSQL Statements
  • OpenSQL Version
  • Context for SQL Statements
  • Extended Statements
  •  BEGIN DECLARE
    • Syntax
    • Description
    • BEGIN DECLARE Example
  •  CALL
    • Syntax
    • Call Description
    • CALL Examples
  •  CLOSE
    • Syntax
    • Description
    • Embedded Usage
    • Usage in OpenAPI, ODBC, JDBC, .NET
    • CLOSE Example
  •  COMMIT
    • Syntax
    • Description
    • Embedded Usage
    • Usage in OpenAPI, ODBC, JDBC, .NET
    • Example: Commit
  •  CONNECT
    • Syntax
    • Description
    • Permissions
    • Examples: Connect
  •  CREATE DBEVENT
    • Syntax
    • Description
    • Embedded Usage
    • Usage in OpenAPI, ODBC, JDBC, .NET
  •  CREATE INDEX
    • Syntax
    • Description
    • Embedded Usage
    • Example: Create Index
  •  CREATE TABLE
    • Syntax
    • Description
    • Column Specification—Describe Column Characteristics
    • Using Create Table...As Select
    • Examples: Create Table
  •  CREATE VIEW
    • Syntax
    • Description
    • Embedded Usage
    • Example: Create View
  •  DECLARE CURSOR
    • Syntax
    • Description
    • Usage in OpenAPI
    • DECLARE CURSOR Examples
  •  DECLARE GLOBAL TEMPORARY TABLE
    • Syntax
    • Description
    • Embedded Usage
    • Restrictions
    • Related Statements
    • Examples: Declare Global Temporary Table
  •  Declare Statement
    • Syntax
    • DECLARE STATEMENT Example
  •  DECLARE TABLE
    • Syntax
    • Description
    • DECLARE TABLE Example
  •  Delete
    • Syntax
    • Embedded Usage
    • Non-Cursor Delete
    • Cursor Delete
    • DELETE Example
  •  DESCRIBE
    • Syntax
    • Description
  •  Direct Execute Immediate
    • Syntax
    • Description
  •  Disconnect
    • Syntax
    • Description
    • Examples: Disconnect
  •  Drop
    • Syntax
    • Description
    • Embedded Usage
    • Examples: Drop
  •  DROP DBEVENT
    • Syntax
    • Description
    • Embedded Usage
  •  END DECLARE SECTION
    • Syntax
    • Description
  •  ENDSELECT
    • Syntax
    • Description
    • ENDSELECT Example
  •  Execute
    • Syntax
    • Description
    • Examples: Execute
  •  EXECUTE IMMEDIATE
    • Syntax
    • Description
    • Example: Execute Immediate
  •  EXECUTE PROCEDURE
    • Syntax
    • Description
    • Passing Parameters - Non-Dynamic Version
    • Passing Parameters - Dynamic Version
    •  Execute Procedure Loops
      • Permissions
      • Locking
      • Performance
      • EXECUTE PROCEDURE Examples
  •  FETCH
    • Syntax
    • Description
    • Examples: Fetch
  •  GET DBEVENT
    • Syntax
  •  Help
    • Syntax
    • Examples: Help
  •  INCLUDE
    • Syntax
    • Description
    • Examples: Include
  •  INQUIRE_SQL
    • Syntax
    • Description
    • Inquiring About Database Events
    • Types of Inquiries
    • Example: Inquire_sql
  •  INSERT
    • Syntax
    • Description
    • Embedded Usage
    • INSERT Examples
  •  OPEN
    • Syntax
    • Description
    • OPEN Examples
  •  Prepare
    • Syntax
    • Description
    • PREPARE Example
  •  Raise Dbevent
    • Syntax
    • Embedded Usage
  •  Register Dbevent
    • Syntax
    • Description
    • Embedded Usage
  •  Remove Dbevent
    • Syntax
    • Description
  •  Rollback
    • Syntax
    • Embedded Usage
    • Performance
  •  Select (interactive)
    •  Syntax
      • Description
    • 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
    • Examples: Select (interactive)
  •  Select (embedded)
    • Syntax
    • 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
    • Syntax
  •  Set_sql
    • Syntax
  •  Update
    • Syntax
    • Description
    • Embedded Usage
    • Permissions
    • Cursor Updates
    • Locking
    • Related Statements
    • UPDATE Examples
  •  Whenever
    • Syntax
    • Examples: Whenever
•  9. Extended Statements
  •  CREATE SCHEMA
    • Syntax
    • Description
    • Restrictions
    • Embedded Usage
    • Permissions
    • Example: Create Schema
  •  Create Table (extended)
    • Syntax
    •  Column Specifications
      • Default Clause
      • Null Clause
      • With|Not Null and With|Not Default Combinations
    •  Constraints
      • Unique Constraint
      • Check Constraint
      • Referential Constraint
      • 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
    • Examples: Create Table (extended)
  •  Grant
    • Syntax
    • Description
    • The Grant All Privileges Option
    • The Grant Option
    • Embedded Usage
    • Permissions
    • Examples: Grant
  •  Revoke
    • Syntax
    • Revoking the Grant Option
    • Restrict versus Cascade
    • Embedded Usage
    • Permissions
    • Example: Revoke
  •  Select
    • Select Syntax
•  10. OpenSQL Limits
  • OpenSQL Limits
•  11. OpenSQL Standard Catalogs
  • Standard Catalog Level
  • System Catalog Characteristics
  •  Standard Catalog Interface
    • The iialt_columns Catalog
    • The iiaudittables Catalog
    • The iicolumns Catalog
    • The iiconstraint_indexes Catalog
    • The iiconstraints Catalog
    • The iidb_subcomments Catalog
    • The iidb_comments Catalog
    •  The iidbcapabilities Catalog
      • The cap_capability Column
    • The iidbconstants Catalog
    • The iievents Catalog
    • The iigwscalars Catalog
    • The iihistograms Catalog
    • The iiindex_columns Catalog
    • The iiindexes Catalog
    • The iikeys Catalog
    • The iiphysical_tables Catalog
    • The iiprocedures Catalog
    • The iiref_constraints Catalog
    • The iiregistrations Catalog
    • The iisecurity_alarms Catalog
    • The iistats Catalog
    • The iisynonyms Catalog
    • The iitables Catalog
    • The 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

Ingres 10S

SQLSTATE and Equivalent Generic Errors