HOME
PROJECTS
DOWNLOADS
FORUMS
COMMUNITY
DOCUMENTATION
CONTACTS
Add Ingres Documentation Search to your Firefox or IE7 Searchbar
Show ToC
SQL Reference Guide
- Copyright Notice
Chapter 1: Introducing the SQL Reference Guide
- Audience
- Enterprise Access Compatibility
- System-specific Text in This Guide
- Terminology Used in This Guide
- Syntax Conventions Used in This Guide
Chapter 2: Introducing SQL
- SQL Functionality
- Types of SQL Statements
SQL Releases
Interactive SQL
- Line-Based Terminal Monitors
- Forms Based Terminal Monitor
Embedded SQL
- Embedded SQL Support
- How Embedded SQL Differs From Interactive SQL
SQL Naming and Statement Rules
- Object Naming Rules
Regular and Delimited Identifiers
- Restrictions on Identifiers
- Case Sensitivity of Identifiers
- Comment Delimiters
- Statement Terminators
Correlation Names
- Correlation Names Rules
Database Procedures
- Database Procedure Creation
- Determine Settings for a Database
- Object Management Extension
- ANSI Compliance
- OpenSQL
- Security Levels
Understanding SQL Data Types
SQL Data Types
Character Data Types
- C Data Types
- Char Data Types
- Text Data Types
- Varchar Data Types
Long Varchar Data Types
- Restrictions on Long Varchar Columns
- Unicode Data Types
Numeric Data Types
- Integer Data Types
Decimal Data Types
- Decimal Data Type Syntax
Floating Point Data Types
- Float Point Limitations
Date/Time Data Types
- Date/Time Input Formats
- Date Data Type
- Time Data Types
- Timestamp Data Types
- Interval Data Types
- Summary of ANSI Date/Type Data Types
Ingresdate Data Types
Absolute Date Input
- II_DATE_FORMAT for Ingresdate
- II_DATE_CENTURY_BOUNDARY
- Absolute Time Input
- Combined Date and Time Input
- Date Interval
- Time Interval
- Date and Time Display
- Coercion Between Date/Time Data Types
Abstract Data Types
- Money Data Types
Logical Key Data Types
- Types of Logical Keys
- Restrictions on Logical Keys
Binary Data Types
- Byte Data Types
- Byte Varying Data Types
Long Byte Data Types
- Restrictions to Long Byte Data Types
- Storage Formats of Data Types
Literals
String Literals
- Hexadecimal Representation
- Quotes within Strings
- Unicode Literals
Numeric Literals
- Integer Literals
- Decimal Literals
- Floating Point Literals
Date/Time Literals
- Date Literals
- Time Literals
- Timestamp Literals
- Interval Literals
- SQL Constants
Nulls
- Nulls and Comparisons
- Nulls and Aggregate Functions
- Nulls and Integrity Constraints
Understanding the Elements of SQL Statements
SQL Operators
- Arithmetic Operators
- Comparison Operators
- Logical Operators
SQL Operations
- String Concatenation Operations
Assignment Operations
- Character String Assignment
String Truncation
- -string_truncation Flag—Specify Error Handling for String Truncation
- Numeric Assignments
- ANSI Date/Time
- Ingresdate
- Types of Logical Keys
- Null Value Assignment
Arithmetic Operations
- Default Type Conversion
- Arithmetic Operations on Decimal Data Types
- Specify Error Handling for Arithmetic Errors
- Date/Time Arithmetic
- ANSI Date/Time Arithmetic
- ANSI Date/Time Comparisons
- Ingresdate Arithmetic
- Comparing Ingresdates
SQL Functions
Scalar Functions
- Data Type Conversion Functions
- Numeric Functions
String Functions
- String Functions Supported in SQL
- String Concatenation Results
- Date Functions
Date Functions for Ingresdate Data Type
- Truncate Dates using date_trunc Function
- Using Date_part
- Extract Function—Extract a Field from a Date/Time Value
- Bit-wise Functions
- Hash Functions
- Random Number Functions
Aggregate Functions
Unary Aggregate Functions
- SQL Aggregate Functions
- Binary Aggregate Functions
- Count(*) Function
- Aggregate Functions and Decimal Data
- Group By Clause with Aggregate Functions
- Restrictions on the Use of Aggregate Functions
Ifnull Function
- Ifnull Result Data Types
- Ifnull and Decimal Data
Universal Unique Identifier (UUID)
- Benefits of Using a UUID
- UUID Format
SQL Functions for UUID Implementation
- uuid_compare(uuid1, uuid2) Function
- uuid_from_char(c) Function
- uuid_create ( ) Function
- UUID Usage
Expressions in SQL
Case Expressions
- NULLIF, COALESCE Functions
- Cast Expressions
Sequence Expressions
- Locking and Sequences
Predicates in SQL
- Comparison Predicate
- Like Predicate
- Between Predicate
- In Predicate
- Any-or-All Predicate
- Exists Predicate
- Is Null Predicate
- Search Conditions in SQL Statements
Subqueries
- Subqueries in the Where Clause
Subqueries in the From Clause (Derived Tables)
- Derived Table Syntax
Chapter 5: Working with Embedded SQL
- Embedded SQL Statements
- How Embedded SQL Statements are Processed
- General Syntax and Rules of an Embedded SQL Statement
- Structure of an Embedded SQL Program
Host Language Variables in Embedded SQL
- Variable Declarations
- Include Statement
- Variable Usage
- Variable Structures
- Dclgen Utility
Indicator Variables
- Indicator Variable Declaration
- 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
- Example: Cursor Processing
- Cursor Declarations
- Open Cursors
- Readonly Cursors
- Open Cursors and Transaction Processing
- Fetch Data From Cursor
- Fetch Rows Inserted by Other Queries
Using Cursors to Update Data
Cursor Modes
- Direct Mode for Update
- Deferred Mode for Update
- Cursor Position for Updates
Delete Data Using Cursors
- Example: Updating and Deleting with Cursors
- Closing Cursors
Summary of Cursor Positioning
- Dynamically Specifying Cursor Names
- Cursors versus Select Loops
Dynamic Programming
SQLDA
- Structure of the SQLDA
- Including the SQLDA in a Program
- Describe Statement and SQLDA
- Data Type Codes
- Using Clause
Dynamic SQL Statements
- Execute Immediate Statement
- Prepare and Execute Statements
- Describe Statement
Execute a Dynamic Non-select Statement
- Using Execute Immediate to Execute a Non-select Statement
- Preparing and Executing a Non-select Statement
Execute a Dynamic Select Statement
- Unknown Result Column Data Types
- How Unknown Result Column Data Types are Handled
- Prepare and Describe Select Statements
- Sqlvar Elements
- Select Statement with Execute Immediate
- Retrieve Results Using Cursors
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
- Ingres 4GL Interface
Chapter 6: Working with Transactions and Handling Errors
Transactions
- How Transactions Work
- How Consistency is Maintained During Transactions
How Commit and Rollback Process Works
- Individual Commits
- How to Determine if You Are in a Transaction
- Statements Used to Control Transactions
- How Transaction Control Works
- Savepoints on Multi Statement Transactions
- Interrupt Handling
Abort Policy for Transactions and Statements
- How to Direct the DBMS to Rollback an Entire Transaction or Statement
- Effects of Aborted Transactions
Two Phase Commit
- Statements that Support Two Phase Commit
- Coordinator Applications for a Two Phase Commit
- Manual Termination of a Distributed Transaction
- Example: Using Two-Phase Commit
Status Information
- session_priv Function
dbmsinfo Function
- Valid Request Names for dbmsinfo Function
inquire_sql Function
- Information Provided by the inquire_sql Function
SQL Communications Area (SQLCA)
- Variables that Compose SQLCA
SQLCODE and SQLSTATE
SQLCODE Variable
- Values Returned by SQLCODE
- SQLSTATE Variable
Error Handling
- Types of Error Codes
- Error Message Format
- Display of Error Messages
Error Handling in Embedded Applications
- Error Information from SQLCA
- SQLSTATE
- Error Trapping Using Whenever Statement
- Define Error Handling Functions
- Other Types of Handlers
- Error Information from Inquire Statements
- How to Specify Program Termination When Errors Occur
Deadlocks
- Example: Handling Deadlocks When Transactions Do Not Contain Cursors
- Example: Handling Deadlocks with One Cursor
- Example: Handling Deadlocks with Two Cursors
Chapter 7: Understanding Database Procedures, Sessions, and Events
How Database Procedures Are Created, Invoked, and Executed
- Benefits of Database Procedures
- Contents of Database Procedures
- Permissions on Database Procedures
- Methods of Executing Procedures
- How Parameters Are Passed in Database Procedures
Row Producing Procedures
- Format of Row Producing Procedures
Effects of Errors in Database Procedures
- iierrornumber and iirowcount Variables
- Raise Error Statement
Messages from Database Procedures
- Message Handling Using the Whenever Statement
- Message Handling Using User-Defined Handler Routines
- Rules
Multiple Session Connections
- Multiple Sessions
- Session Identification
- Session Switching
- Disconnection of Sessions
- Status Information in Multiple Sessions
- Multiple Sessions and the DBMS Server
- Example: Two Open Sessions
- Examples: Session Switching
Database Events
- Example: Database Events in Conjunction with Rules
Database Event Statements
- Create a Database Event
- Raise a Database Event
- Register Applications to Receive a Database Event
- Receive a Database Event
- Process Database Events
- Get Dbevent Statement
- Whenever Dbevent Statement
- User-Defined Database Event Handlers
- Remove a Database Event Registration
- Drop a Database Event
- Privileges and Database Events
- Trace Database Events
Using SQL Statements
- Context for SQL Statements
- Statements for Ingres Star Support
Alter Group
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter Group
Alter Location
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter Location
Alter Profile
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter Profile
Alter Role
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter Role
Alter Security_Audit
- Syntax
- Embedded Usage
- Permissions
- Related Statements
- Examples: Alter Security_Audit
Alter Sequence
- Syntax
- Permissions
- Locking and Sequences
- Related Statements
- Examples: Alter Sequence
Alter Table
- Syntax
Constraint Specifications
- Named Constraints
- Restrict and Cascade
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter Table
Alter User
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Alter User
Begin Declare
- Syntax
- Permissions
- Related Statements
- Example: Begin Declare
Call
- Syntax
- Permissions
- Examples: Call
Close
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Close
Comment On
- Syntax
- Description
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Comment On
Commit
- Syntax
- Embedded Usage
- Permissions
- Locking
- Performance
- Related Statements
- Example: Commit
Connect
- Syntax
- Connecting with Distributed Transactions
Creating Multiple Sessions
- Using Session Identifiers
- Using Connection Names
- Permissions
- Locking
- Related Statements
- Examples: Connect
Copy
- Syntax
Binary Copying
- Bulk Copying
- row_estimate Option
- Data File Format versus Table Format
Column Formats
- Storage Format
- Delimiters
- With Null Clause
Filename Specification
- VMS File Types
With Clause Options
-
-
-
-
- Permissions
- Locking
- Restrictions and Considerations
- Related Statements
- Example: Copy
Create Dbevent
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
Create Group
- Syntax
- Permissions
- Embedded Usage
- Locking
- Related Statements
- Examples: Create Group
Create Index
- Syntax
- Description
- Index Storage Structure
- Unique Indexes
- Effect of the Unique_Scope Option on Updates
- Index Location
- Parallel Index Building
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Index
Create Integrity
- Syntax
- Locking
- Performance
- Embedded Usage
- Permissions
- Related Statements
- Examples: Create Integrity
Create Location
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Location
Create Procedure
- Syntax
- Description
- Parameter Modes
- Nullability and Default Values for Parameters
- Set Of Parameters
- Embedded Usage
- Permissions
- Related Statements
- Examples: Create Procedure
Create Profile
- Syntax
- Description
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Profile
Create Role
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Role
Create Rule
- Syntax
Row and Statement Level Rules
- Database Procedures
- Table_Condition
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Rule
Create Schema
- Syntax
- Description
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Create Schema
Create Security_Alarm
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Security_Alarm
Create Sequence
- Syntax
- Permissions
- Locking and Sequences
- Related Statements
- Examples: Create Sequence
Create Synonym
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Synonym
Create Table
- Syntax
- Description
Column Specification—Describe Column Characteristics
- Default Clause
- Null Clause
- System_Maintained Logical Keys
- Sequence Defaults
Constraints
- Unique Constraint
- Check Constraint
- Referential Constraint
- Primary Key Constraints
- Column-Level Constraints and Table-Level Constraints
- Using Create Table...As Select
Constraint With_Clause—Define Constraint Index Options
- No Index Option
- Index = Base Table Structure Option
- Index = Index_Name Option
- Constraints and Integrities
Partitioning Schemes
- Partitioning Syntax
With_Clause for Create Table
- Page_size Option
- Security_audit Option
- With_Clause for Create Table...as Select
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create Table
Create User
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create User
Create View
- Syntax
- With Check Option Clause
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Create View
Declare
- Syntax
- Permissions
- Related Statements
- Example: Declare
Declare Cursor
- Syntax
- Cursor Updates
- Cursor Modes
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Declare Cursor
Declare Global Temporary Table
- Syntax
- Description
- SESSION Schema Qualifier
- Embedded Usage
- Permissions
- Restrictions
- Related Statements
- Examples: Declare Global Temporary Table
Declare Statement
- Syntax
- Related Statements
- Example: Declare
Declare Table
- Syntax
- Permissions
- Example: Declare Table
Delete
- Syntax
Embedded Usage
- Non-Cursor Delete
- Cursor Delete
- Permissions
- Locking
- Related Statements
- Example: Delete
Describe
- Syntax
- Permissions
- Related Statements
Describe Input
- Syntax
Disable Security_Audit
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Disable Security_Audit
Disconnect
- Syntax
- Permissions
- Locking
- Related Statements
- Examples: Disconnect
Drop
- Syntax
- Description
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Drop
Drop Dbevent
- Syntax
- Embedded Usage
- Permissions
- Related Statements
- Example: Drop Location
Drop Group
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Drop Group
Drop Integrity
- Syntax
- Embedded Usage
- Permissions
- Related Statements
- Examples: Drop Integrity
Drop Location
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements: Drop Location
Drop Procedure
- Syntax
- Embedded Usage
- Permissions
- Related Statements
- Example: Drop Procedure
Drop Profile
- Syntax
- Permissions
- Locking
- Related Statements
- Example: Drop Profile
Drop Role
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Drop Role
Drop Rule
- Syntax
- Embedded Usage
- Permissions
- Related Statements
- Example: Drop Rule
Drop Security_Alarm
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Drop Security_Alarm
Drop Sequence
- Syntax
- Permissions
- Locking and Sequences
- Related Statements
- Examples: Drop Sequence
Drop Synonym
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Drop Synonym
Drop User
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Drop User
Enable Security_Audit
- Syntax
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Example: Enable Security_Audit
Enddata
- Syntax
- Permissions
- Examples: Enddata
End Declare Section
- Syntax
- Permissions
- Related Statements
Endselect
- Syntax
- Permissions
- Locking
- Related Statements
- Example: Endselect
Execute
- Syntax
- Description
- Permissions
- Locking
- Related Statements
- Examples: Execute
Execute Immediate
- Syntax
- Description
- Permissions
- Locking
- Related Statements
- Examples: Execute Immediate
Execute Procedure
- Syntax
- Description
- Passing Parameters - Non-Dynamic Version
- Passing Parameters - Dynamic Version
Temporary Table Parameter
- Limitations of Temporary Table Parameter
- Execute Procedure Loops
- Permissions
- Locking
- Related Statements
- Examples: Execute Procedure
Fetch
- Syntax
- Readonly Cursors and Performance
- Permissions
- Related Statements
- Examples: Fetch
For-EndFor
- Syntax
- Description
- Permissions
- Example: For-EndFor
Get Data
- Syntax
- Permissions
- Related Statements
Get Dbevent
- Syntax
- Permissions
- Related Statements
Grant (privilege)
- Syntax
Types of Privileges
- Table Privileges
- Table Privileges for Views
- Database Privileges
- Database Procedure Privileges
- Database Event Privileges
- Database Sequence Privileges
- Privilege Defaults
Grant All Privileges Option
- Installation and Database Privileges
- Other Privileges
- Granting All Privileges on Views
- Grant Option Clause
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Grant (privilege)
Grant (role)
- Syntax
- Permissions
- Related Statements
- Example: Grant (role)
Help
- Syntax
- Wildcards and Help
- Permissions
- Locking
- Related Statements
- Examples: Help
If-Then-Else
- Syntax
Description
- If Statement
- If...Then Statement
- If...Then...Elseif Statement
- Nesting IF Statements
- Permissions
- Example: If-Then-Else
Include
- Syntax
- Description
- Permissions
- Related Statements
- Examples: Include
Inquire_sql
- Syntax
- Description
- Inquiring About Logical Keys
- Inquiring About Database Events
- Types of Inquiries
- Permissions
- Related Statements
- Examples: Inquire_sql
Insert
- Syntax
- Description
- Embedded Usage
- Permissions
- Repeated Queries
- Error Handling
- Locking
- Related Statements
- Examples: Insert
Message
- Syntax
- Permissions
- Related Statements
- Examples: Message
Modify
- Syntax
- Description
- Storage Structure Specification
- Modify...to Reconstruct
- Modify...to Merge
- Modify...to Relocate
- Modify...to Reorganize
- Modify...to Truncated
- Modify...to Add_extend
- Modify...with Blob_extend
- Modify...to Phys_consistent|Phys_inconsistent
- Modify...to Log_consistent|Log_inconsistent
- Modify...to Table_recovery_allowed|Table_recovery_disallowed
- Modify…to Unique_scope = Statement|Row
- Modify…to [No]Readonly
- Modify…to Priority=n
With Clause Options
- Fillfactor, Minpages, and Maxpages
- Leaffill and Nonleaffill
- Allocation Option
- Extend
- Compression
- Location
- Unique_scope
- (No)persistence Option
- Page_size
- NopartitionPartition=
- Concurrent_updates
- Nodependency_check
- Embedded Usage
- Permissions
- Locking
- Related Statements
- Examples: Modify
Open
- Syntax
- Description
- Permissions
- Locking
- Related Statements
- Examples: Open
Prepare
- Syntax
- Description
- Permissions
- Related Statements
- Example: Prepare
Prepare to Commit
- Syntax
- Permissions
- Related Statements
- Example: Prepare to Commit
Put Data
- Syntax
- Permissions
- Related Statements
Raise Dbevent
