Using InterSystems SQL
- About This Book
- Introduction to InterSystems SQL
- InterSystems 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
- Schema Name
- Table Name
- RowID Field
- Primary Key
- RowVersion, AutoIncrement, and Serial Counter Fields
- Defining a Table by Creating a Persistent Class
- Defining a Table by Using DDL
- Defining a Sharded Table
- Defining a Table by Querying an Existing Table
- External Tables
- Listing Tables
- Listing Column Names and Numbers
- Defining and Using Views
- Creating a View
- Altering 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
- Validating Data
- 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
- Fast Select
- Queries and Enterprise Cache Protocol (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
- Embedded SQL Syntax
- Embedded SQL Code
- Host Variables
- SQL Cursors
- Embedded SQL Variables
- Embedded SQL in Methods of a Persistent Class
- Validating Embedded SQL Code
- 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
- Using the SQL Shell Interface
- Other Ways of Executing SQL
- Invoking the SQL Shell
- Storing and Recalling SQL Statements
- Purging Cached Queries
- Configuring the SQL Shell
- SQL Metadata, Query Plan, and Performance Metrics
- Transact-SQL Support
- Using the Management Portal SQL Interface
- Management Portal SQL Facilities
- Executing SQL Query
- Filtering Schema Contents
- Catalog Details
- Wizards
- Actions
- Open Table
- Tools
- 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 InterSystems IRIS Methods
- Using Stream Fields from ODBC
- Using Stream Fields from JDBC
- Users, Roles, and Privileges
- Importing and Exporting SQL Data