Using Caché SQL
Contents
 
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

 
Contents

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 Types
  • 2.5.1 Collation Fundamentals
  • 2.5.2 Namespace Default Collation
  • 2.5.3 Collation of Indexed Values
  • 2.5.4 Legacy Collation Types
  • 2.5.5 SQL and NLS Collations
  • 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 Caché ObjectScript and SQL
  • 3.4 Arithmetic Operators and 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.5 Defining a Table by Using DDL
  • 5.5.1 Using DDL in Embedded SQL
  • 5.5.2 Executing DDL Using Dynamic SQL
  • 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 Caché 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 Executing Named 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 Property Reference
  • 10.2 Child Table Reference
  • 10.3 Arrow Syntax Privileges
  • Chapter 11: 
     
  • 11.1 Compiling Embedded SQL and the Macro Preprocessor
  • 11.1.1 Recompilation Required following Change to Dependent Class
  • 11.2 Embedded SQL Syntax
  • 11.2.1 The &sql Directive
  • 11.2.2 &sql Alternative Syntax
  • 11.2.3 &sql Marker Syntax
  • 11.2.4 Embedded SQL and Line Offsets
  • 11.3 Embedded SQL Code
  • 11.3.1 Simple SQL Statements
  • 11.3.2 Schema Name Resolution
  • 11.3.3 Literal Values
  • 11.3.4 Data Format
  • 11.3.5 Privilege Checking
  • 11.4 Host Variables
  • 11.4.1 Host Variable Examples
  • 11.4.2 Host Variable Subscripted by Column Number
  • 11.4.3 NULL and Undefined Host Variables
  • 11.4.4 Validity of Host Variables
  • 11.4.5 Host Variables and Procedure Blocks
  • 11.5 SQL Cursors
  • 11.5.1 The DECLARE Cursor Statement
  • 11.5.2 The OPEN Cursor Statement
  • 11.5.3 The FETCH Cursor Statement
  • 11.5.4 The CLOSE Cursor Statement
  • 11.6 Embedded SQL Variables
  • 11.6.1 %msg
  • 11.6.2 %ok
  • 11.6.3 %ROWCOUNT
  • 11.6.4 %ROWID
  • 11.6.5 SQLCODE
  • 11.6.6 $TLEVEL
  • 11.6.7 $USERNAME
  • Chapter 12: 
     
  • 12.1 Introduction to Dynamic SQL
  • 12.1.1 Dynamic SQL versus Embedded SQL
  • 12.2 The %SQL.Statement Class
  • 12.3 Creating an Object Instance
  • 12.3.1 %SelectMode Property
  • 12.3.2 %SchemaPath Property
  • 12.3.3 %Dialect Property
  • 12.3.4 %ObjectSelectMode Property
  • 12.4 Preparing an SQL Statement
  • 12.4.1 %Prepare()
  • 12.4.2 %PrepareClassQuery()
  • 12.5 Executing an SQL Statement
  • 12.5.1 %Execute()
  • 12.5.2 %ExecDirect()
  • 12.6 Returning the Full Result Set
  • 12.6.1 %Display() Method
  • 12.6.2 %DisplayFormatted() Method
  • 12.7 Returning Specific Values from the Result Set
  • 12.7.1 %Print() Method
  • 12.7.2 rset.name Property
  • 12.7.3 %Get("fieldname") Method
  • 12.7.4 %GetData(n) Method
  • 12.8 Returning Multiple Result Sets
  • 12.9 SQL Metadata
  • 12.9.1 SQL Statement Metadata
  • 12.9.2 SQL Metadata for Columns
  • 12.9.3 Metadata for Query Stored Procedures
  • Chapter 13: 
     
  • 13.1 Dynamic SQL Using %ResultSet.SQL
  • 13.2 Dynamic SQL Using %Library.ResultSet
  • 13.2.1 %Library.ResultSet Supports SQL Result Properties
  • 13.2.2 %Library.ResultSet Does Not Support CALL
  • 13.3 Input Parameters
  • 13.4 Closing a Query
  • 13.5 %Library.ResultSet Metadata
  • 13.6 %ResultSet.SQL Metadata
  • Chapter 14: 
     
  • 14.1 Other Ways of Executing SQL
  • 14.2 Invoking the SQL Shell
  • 14.2.1 GO Command
  • 14.2.2 Input Parameters
  • 14.2.3 Executing Caché ObjectScript Commands
  • 14.2.4 CALL Command
  • 14.2.5 Executing an SQL Script File
  • 14.3 Storing and Recalling SQL Statements
  • 14.3.1 Recall by Number
  • 14.3.2 Recall by Name
  • 14.4 SQL Shell Parameters
  • 14.4.1 Displaying, Setting, and Saving SQL Shell Parameters
  • 14.4.2 Setting DISPLAYMODE and DISPLAYTRANSLATE
  • 14.4.3 Setting EXECUTEMODE
  • 14.4.4 Setting LOG
  • 14.4.5 Setting PATH
  • 14.4.6 Setting SELECTMODE
  • 14.5 SQL Metadata and Performance Metrics
  • 14.5.1 Displaying Metadata, Show Plan, and Show Statement
  • 14.5.2 SQL Shell Performance
  • 14.6 Transact-SQL Support
  • 14.6.1 Setting DIALECT
  • 14.6.2 Setting COMMANDPREFIX
  • 14.6.3 RUN Command
  • 14.6.4 TSQL Examples
  • Chapter 15: 
     
  • 15.1 Management Portal SQL Facilities
  • 15.1.1 Selecting a Namespace
  • 15.2 Executing SQL Statements
  • 15.2.1 Writing SQL Statements
  • 15.2.2 Table Drag and Drop
  • 15.2.3 Execute Query Options
  • 15.2.4 SQL Statement Results
  • 15.2.5 Show History
  • 15.2.6 Other SQL Interfaces
  • 15.3 Filtering Schema Contents
  • 15.3.1 Browse Tab
  • 15.3.2 Catalog Details Tab
  • 15.3.3 Open Table
  • 15.4 Actions
  • 15.5 Wizards
  • Chapter 16: 
     
  • 16.1 Importing Caché SQL
  • 16.1.1 Import File Format
  • 16.1.2 Supported SQL Statements
  • 16.2 Code Migration: Importing non-Caché SQL
  • Chapter 17: 
     
  • 17.1 Defining Triggers
  • 17.2 Types of Triggers
  • 17.2.1 AFTER Triggers
  • 17.2.2 Recursive Triggers
  • 17.3 How Trigger Code Works
  • 17.3.1 Macros within Trigger Code
  • 17.3.2 {name*O}, {name*N}, and {name*C} Trigger Code Syntax
  • 17.3.3 Additional Trigger Code Syntax
  • 17.4 Triggers and Object Access
  • 17.5 Triggers and Transactions
  • Chapter 18: 
     
  • 18.1 Overview
  • 18.2 Defining Stored Procedures
  • 18.2.1 Defining a Stored Procedure Using DDL
  • 18.2.2 SQL to Class Name Transformations
  • 18.2.3 Defining a Method Stored Procedure using Classes
  • 18.2.4 Defining a Query Stored Procedure using Classes
  • 18.2.5 Customized Class Queries
  • 18.3 Using Stored Procedures
  • 18.3.1 Stored Functions
  • 18.3.2 Privileges
  • Chapter 19: 
     
  • 19.1 Stream Fields and SQL
  • 19.1.1 BLOBs and CLOBs
  • 19.2 Defining Stream Fields Using DDL
  • 19.2.1 Empty BLOBs
  • 19.3 Stream Field Concurrency Locking
  • 19.4 Using Stream Fields within Caché Methods
  • 19.5 Using Stream Fields from ODBC
  • 19.6 Using Stream Fields from JDBC
  • Chapter 20: 
     
  • 20.1 Users
  • 20.2 Roles
  • 20.3 Privileges
  • Chapter 21: 
     
  • 21.1 Architecture of the Caché SQL Gateway
  • 21.1.1 Persisting External Tables in Caché
  • 21.1.2 Restrictions on SQL Gateway Queries
  • 21.2 Creating Gateway Connections for External Sources
  • 21.3 The Link Table Wizard: Linking to a Table or View
  • 21.3.1 Using the Link Table Wizard
  • 21.3.2 Limitations When Using the Linked Table
  • 21.4 The Link Procedure Wizard: Linking to a Stored Procedure
  • 21.5 Controlling Gateway Connections
  • 21.6 The Data Migration Wizard: Migrating Data from an ODBC Source
  • 21.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