Using Caché SQL
- About This Book
- Introduction to Caché SQL
- Caché SQL Basics
- Language Elements
- Commands and Keywords
- Functions: Intrinsic and Extrinsic
- Literals
- NULL and the Empty String
- Arithmetic Operators and Functions
- Relational Operators
- Logical Operators
- Comments
- Identifiers
- Defining Tables
- Table Names and Schema Names
- RowID Field
- Primary Key
- RowVersion and Serial Counter Fields
- Defining a Table by Creating a Persistent Class
- Defining a Table by Using DDL
- Defining a Table by Querying an Existing Table
- External Tables
- Listing Tables
- Listing Column Names and Numbers
- Defining and Using Views
- Creating a View
- Updateable Views
- Read-only Views
- View ID: %VID
- Listing View Properties
- Listing View Dependencies
- Relationships Between Tables
- Modifying the Database
- Inserting Data
- UPDATE Statements
- Computed Field Values on INSERT or UPDATE
- DELETE Statements
- Transaction Processing
- Querying the Database
- Types of Queries
- Using a SELECT Statement
- Defining and Executing Named Queries
- Queries Invoking User-defined Functions
- Querying Serial Object Properties
- Querying Collections
- Queries Invoking Free-text Search
- Pseudo-Field Variables
- Query Metadata
- Queries and ECP
- Collation
- Collation Types
- Namespace-wide Default Collation
- Table Field/Property Definition Collation
- Index Definition Collation
- Query Collation
- Legacy Collation Types
- SQL and NLS Collations
- Implicit Joins (Arrow Syntax)
- Using Embedded SQL
- Compiling Embedded SQL and the Macro Preprocessor
- Embedded SQL Syntax
- Embedded SQL Code
- Host Variables
- SQL Cursors
- Embedded SQL Variables
- Auditing Embedded SQL
- Using Dynamic SQL
- Introduction to Dynamic SQL
- The %SQL.Statement Class
- Creating an Object Instance
- Preparing an SQL Statement
- Executing an SQL Statement
- Returning the Full Result Set
- Returning Specific Values from the Result Set
- Returning Multiple Result Sets
- SQL Metadata
- Auditing Dynamic SQL
- Dynamic SQL Using Older Result Set Classes
- Dynamic SQL Using %ResultSet.SQL
- Dynamic SQL Using %Library.ResultSet
- Input Parameters
- Closing a Query
- %Library.ResultSet Metadata
- %ResultSet.SQL Metadata
- Using the SQL Shell Interface
- Other Ways of Executing SQL
- Invoking the SQL Shell
- Storing and Recalling SQL Statements
- SQL Shell Parameters
- SQL Metadata and Performance Metrics
- Transact-SQL Support
- Using the Management Portal SQL Interface
- Management Portal SQL Facilities
- Executing SQL Statements
- Filtering Schema Contents
- Catalog Details
- Open Table
- Actions
- Wizards
- Importing SQL Code
- Using Triggers
- Defining Triggers
- Types of Triggers
- Trigger Code
- Pulling Triggers
- Triggers and Object Access
- Triggers and Transactions
- Listing Triggers
- Defining and Using Stored Procedures
- Storing and Using Stream Data (BLOBs and CLOBs)
- Stream Fields and SQL
- Stream Field Concurrency Locking
- Using Stream Fields within Caché Methods
- Using Stream Fields from ODBC
- Using Stream Fields from JDBC
- Users, Roles, and Privileges
- Using the Caché SQL Gateway
- Architecture of the Caché SQL Gateway
- Creating Gateway Connections for External Sources
- The Link Table Wizard: Linking to a Table or View
- The Link Procedure Wizard: Linking to a Stored Procedure
- Controlling Gateway Connections
- The Data Migration Wizard: Migrating Data from an ODBC or JDBC Source
- Importing and Exporting SQL Data