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 Function
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
language
dbname
tablename
filename
structurename
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
procedure_name
procedure_name
return_type_list
Remove Procedure Statement--Delete a Procedure Registration
Guidelines for Executing Database Procedures
DBMS Extensions
Enterprise Access and EDBC With Clause
With Clause Syntax
db_id_
option_name
option_value
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
command_string
subsystem
dbname
parameter
value
Call Description
CALL Examples
CLOSE
Syntax
cursor_name
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
dbname
Description
Permissions
Examples: Connect
CREATE DBEVENT
Syntax
event_name
Description
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, .NET
CREATE INDEX
Syntax
index_name
table_name
column_name
Description
Embedded Usage
Example: Create Index
CREATE TABLE
Syntax
table_name
subselect
Description
Column Specification--Describe Column Characteristics
column_name
column_name
datatype
Using Create Table...As Select
Examples: Create Table
CREATE VIEW
Syntax
view_name
subselect
Description
Embedded Usage
Example: Create View
DECLARE CURSOR
Syntax
cursor_name
Description
Usage in OpenAPI
DECLARE CURSOR Examples
DECLARE GLOBAL TEMPORARY TABLE
Syntax
table_name
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
table_name
Embedded Usage
Non-Cursor Delete
Cursor Delete
DELETE Example
DESCRIBE
Syntax
statement_name
descriptor name
Description
Direct Execute Immediate
Syntax
Description
Disconnect
Syntax
session_identifier
Description
Examples: Disconnect
Drop
Syntax
objecttype
objectname
Description
Embedded Usage
Examples: Drop
DROP DBEVENT
Syntax
Description
Embedded Usage
END DECLARE SECTION
Syntax
Description
ENDSELECT
Syntax
Description
ENDSELECT Example
Execute
Syntax
statement_name
Description
Examples: Execute
EXECUTE IMMEDIATE
Syntax
Description
Example: Execute Immediate
EXECUTE PROCEDURE
Syntax
proc_name
param_spec
Description
Passing Parameters - Non-Dynamic Version
Passing Parameters - Dynamic Version
Execute Procedure Loops
Permissions
Locking
Performance
EXECUTE PROCEDURE Examples
FETCH
Syntax
cursor_name
Description
Examples: Fetch
GET DBEVENT
Syntax
Help
Syntax
Examples: Help
INCLUDE
Syntax
Description
Examples: Include
INQUIRE_SQL
Syntax
variable
object
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
event_name
event_text
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
source
join_type
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
condition
action
Examples: Whenever
9. Extended Statements
CREATE SCHEMA
Syntax
schema_name
object_definition
Description
Restrictions
Embedded Usage
Permissions
Example: Create Schema
Create Table (extended)
Syntax
table_name
column_specification
table_constraint
Column Specifications
column_name
column_name
datatype
Default Clause
Null Clause
With|Not Null and With|Not Default Combinations
Constraints
Unique Constraint
Check Constraint
Referential Constraint
referential actions
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
privilege
table_name
auth_id
Description
The Grant All Privileges Option
The Grant Option
Embedded Usage
Permissions
Examples: Grant
Revoke
Syntax
privilege
table_name
auth_id
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 10.2
SQLSTATE and Equivalent Generic Errors