Using Caché SQL
Contents
[Home]  [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Preface : 
 
 
Chapter 1: 
  1.1 Architecture
1.2 Features
1.2.1 SQL-92 Compliance
1.2.2 Extensions
1.3 Interoperability
1.3.1 JDBC
1.3.2 ODBC
1.3.3 Embedded SQL
1.3.4 Dynamic SQL
1.4 Limitations
Chapter 2: 
  2.1 Tables
2.1.1 Schemas
2.2 Queries
2.3 Privileges
2.4 Data Display Options
2.5 Data Collation
2.6 Executing SQL
Chapter 3: 
  3.1 Commands and Keywords
3.2 Literals
3.2.1 String Delimiters
3.2.2 Concatenation
3.3 NULL and the Empty String
3.3.1 NULL Processing
3.3.2 NULL Arithmetic
3.3.3 The Length of NULL
3.3.4 ObjectScript and SQL
3.4 Arithmetic Operators and Functions
3.4.1 Operator Precedence
3.4.2 Precision and Scale
3.4.3 Arithmetic and Trigonometric Functions
3.5 Relational Operators
3.6 Logical Operators
3.6.1 NOT Unary Operator
3.6.2 AND and OR Operators
3.7 Comments
3.7.1 Single Line Comments
3.7.2 Multiple Line Comments
3.7.3 SQL Code Retained as Comments
Chapter 4: 
  4.1 Simple Identifiers
4.1.1 Naming Conventions
4.1.2 Case of Letters
4.1.3 Testing Valid Identifiers
4.1.4 Identifiers and Class Entities
4.1.5 Identifier Length Considerations
4.2 Delimited Identifiers
4.2.1 Configuring Delimited Identifier Support
4.3 SQL Reserved Words
Chapter 5: 
  5.1 Table Names and Schema Names
5.1.1 System-wide Default Schema
5.1.2 Schema Naming Considerations
5.1.3 Platform-Specific Schema Names
5.1.4 Table Naming Considerations
5.2 RowID Field
5.3 RowVersion Field
5.4 Defining a Table by Creating a Persistent Class
5.4.1 Unique Values
5.4.2 Computed Values
5.4.3 Class Methods
5.5 Defining a Table by Using DDL
5.5.1 Using DDL in Embedded SQL
5.5.2 Using a Class Method to Execute DDL
5.5.3 Running DDL Scripts from the Command Line
5.6 External Tables
5.7 Column Names and Numbers
5.7.1 The GetColumns() Method
Chapter 6: 
  6.1 Creating a View
6.1.1 Management Portal Create View Interface
6.2 Updateable Views
6.2.1 The WITH CHECK Option
6.3 Read-only Views
6.4 View ID: %VID
6.5 Listing View Dependencies
Chapter 7: 
  7.1 Defining a Foreign Key
7.2 Foreign Key Referential Integrity Checking
7.3 Identifying Parent and Child Tables
Chapter 8: 
  8.1 INSERT Statements
8.2 UPDATE Statements
8.3 DELETE Statements
8.4 Transaction Processing
8.4.1 Transactions and Savepoints
8.4.2 Non-transaction Operations
8.4.3 Transaction Locks
8.4.4 Transaction Size Limitations
8.4.5 Reading Uncommitted Data
8.4.6 ObjectScript Transaction Commands
Chapter 9: 
  9.1 Types of Queries
9.2 Using a SELECT Statement
9.2.1 Selecting Fields
9.2.2 The JOIN Operation
9.2.3 Queries Selecting Large Numbers of Fields
9.3 Defining and Executing Named Queries
9.3.1 CREATE QUERY and CALL
9.3.2 Class Queries
9.4 Queries Invoking User-defined Functions
9.5 Collection Properties
9.6 Queries Invoking Free-text Search
9.6.1 Full Text Indexing and Text Retrieval through SQL
9.6.2 Collection Indexing and Querying Collections through SQL
9.6.3 Usage Notes and Restrictions
9.7 Pseudo-Field Variables
9.8 Query Metadata
9.9 Queries and ECP
Chapter 10: 
  10.1 Collation Types
10.2 Namespace-wide Default Collation
10.3 Table Field/Property Definition Collation
10.4 Index Definition Collation
10.5 Query Collation
10.5.1 select-item Collation
10.5.2 DISTINCT and GROUP BY Collation
10.6 Legacy Collation Types
10.7 SQL and NLS Collations
Chapter 11: 
  11.1 Property Reference
11.2 Child Table Reference
11.3 Arrow Syntax Privileges
Chapter 12: 
  12.1 Compiling Embedded SQL and the Macro Preprocessor
12.1.1 Recompilation Required following Change to Dependent Class
12.2 Embedded SQL Syntax
12.2.1 The &sql Directive
12.2.2 &sql Alternative Syntax
12.2.3 &sql Marker Syntax
12.2.4 Embedded SQL and Line Offsets
12.3 Embedded SQL Code
12.3.1 Simple SQL Statements
12.3.2 Schema Name Resolution
12.3.3 Literal Values
12.3.4 Data Format
12.3.5 Privilege Checking
12.4 Host Variables
12.4.1 Host Variable Examples
12.4.2 Host Variable Subscripted by Column Number
12.4.3 NULL and Undefined Host Variables
12.4.4 Validity of Host Variables
12.4.5 Host Variables and Procedure Blocks
12.5 SQL Cursors
12.5.1 The DECLARE Cursor Statement
12.5.2 The OPEN Cursor Statement
12.5.3 The FETCH Cursor Statement
12.5.4 The CLOSE Cursor Statement
12.6 Embedded SQL Variables
12.6.1 %msg
12.6.2 %ok
12.6.3 %ROWCOUNT
12.6.4 %ROWID
12.6.5 SQLCODE
12.6.6 $TLEVEL
12.6.7 $USERNAME
12.7 Auditing Embedded SQL
Chapter 13: 
  13.1 Introduction to Dynamic SQL
13.1.1 Dynamic SQL versus Embedded SQL
13.2 The %SQL.Statement Class
13.3 Creating an Object Instance
13.3.1 %SelectMode Property
13.3.2 %SchemaPath Property
13.3.3 %Dialect Property
13.3.4 %ObjectSelectMode Property
13.4 Preparing an SQL Statement
13.4.1 %Prepare()
13.4.2 %PrepareClassQuery()
13.5 Executing an SQL Statement
13.5.1 %Execute()
13.5.2 %ExecDirect()
13.6 Returning the Full Result Set
13.6.1 %Display() Method
13.6.2 %DisplayFormatted() Method
13.6.3 Paginating a Result Set
13.7 Returning Specific Values from the Result Set
13.7.1 %Print() Method
13.7.2 rset.name Property
13.7.3 %Get("fieldname") Method
13.7.4 %GetData(n) Method
13.8 Returning Multiple Result Sets
13.9 SQL Metadata
13.9.1 SQL Statement Metadata
13.9.2 SQL Metadata for Columns
13.9.3 Metadata for Query Stored Procedures
13.10 Auditing Dynamic SQL
Chapter 14: 
  14.1 Dynamic SQL Using %ResultSet.SQL
14.2 Dynamic SQL Using %Library.ResultSet
14.2.1 %Library.ResultSet Supports SQL Result Properties
14.2.2 %Library.ResultSet Does Not Support CALL
14.3 Input Parameters
14.4 Closing a Query
14.5 %Library.ResultSet Metadata
14.6 %ResultSet.SQL Metadata
Chapter 15: 
  15.1 Other Ways of Executing SQL
15.2 Invoking the SQL Shell
15.2.1 GO Command
15.2.2 Input Parameters
15.2.3 Executing ObjectScript Commands
15.2.4 CALL Command
15.2.5 Executing an SQL Script File
15.3 Storing and Recalling SQL Statements
15.3.1 Recall by Number
15.3.2 Recall by Name
15.4 SQL Shell Parameters
15.4.1 Displaying, Setting, and Saving SQL Shell Parameters
15.4.2 Setting DISPLAYMODE and DISPLAYTRANSLATE
15.4.3 Setting EXECUTEMODE
15.4.4 Setting LOG
15.4.5 Setting PATH
15.4.6 Setting SELECTMODE
15.5 SQL Metadata and Performance Metrics
15.5.1 Displaying Metadata, Show Plan, and Show Statement
15.5.2 SQL Shell Performance
15.6 Transact-SQL Support
15.6.1 Setting DIALECT
15.6.2 Setting COMMANDPREFIX
15.6.3 RUN Command
15.6.4 TSQL Examples
Chapter 16: 
  16.1 Management Portal SQL Facilities
16.1.1 Selecting a Namespace
16.2 Executing SQL Statements
16.2.1 Writing SQL Statements
16.2.2 Table Drag and Drop
16.2.3 Execute Query Options
16.2.4 SQL Statement Results
16.2.5 Show History
16.2.6 Other SQL Interfaces
16.3 Filtering Schema Contents
16.3.1 Browse Tab
16.3.2 Catalog Details Tab
16.3.3 Open Table
16.4 Actions
16.5 Wizards
Chapter 17: 
  17.1 Importing Caché SQL
17.1.1 Import File Format
17.1.2 Supported SQL Statements
17.2 Code Migration: Importing non-Caché SQL
Chapter 18: 
  18.1 Defining Triggers
18.2 Types of Triggers
18.2.1 AFTER Triggers
18.2.2 Recursive Triggers
18.3 How Trigger Code Works
18.3.1 Macros within Trigger Code
18.3.2 {name*O}, {name*N}, and {name*C} Trigger Code Syntax
18.3.3 Additional Trigger Code Syntax
18.4 Triggers and Object Access
18.5 Triggers and Transactions
18.6 Listing Triggers
Chapter 19: 
  19.1 Overview
19.2 Defining Stored Procedures
19.2.1 Defining a Stored Procedure Using DDL
19.2.2 SQL to Class Name Transformations
19.2.3 Defining a Method Stored Procedure using Classes
19.2.4 Defining a Query Stored Procedure using Classes
19.2.5 Customized Class Queries
19.3 Using Stored Procedures
19.3.1 Stored Functions
19.3.2 Privileges
Chapter 20: 
  20.1 Stream Fields and SQL
20.1.1 BLOBs and CLOBs
20.2 Defining Stream Fields Using DDL
20.2.1 Empty BLOBs
20.3 Stream Field Concurrency Locking
20.4 Using Stream Fields within Caché Methods
20.5 Using Stream Fields from ODBC
20.6 Using Stream Fields from JDBC
Chapter 21: 
  21.1 Users
21.2 Roles
21.3 Privileges
Chapter 22: 
  22.1 Architecture of the Caché SQL Gateway
22.1.1 Persisting External Tables in Caché
22.1.2 Restrictions on SQL Gateway Queries
22.2 Creating Gateway Connections for External Sources
22.3 The Link Table Wizard: Linking to a Table or View
22.3.1 Using the Link Table Wizard
22.3.2 Limitations When Using the Linked Table
22.4 The Link Procedure Wizard: Linking to a Stored Procedure
22.5 Controlling Gateway Connections
22.6 The Data Migration Wizard: Migrating Data from an ODBC Source
22.6.1 Microsoft Access and Foreign Key Constraints
 
Appendix A: 
  A.1 Importing Data from a Text File
A.2 Exporting Data to a Text File