Master Contents

Documentation

SQL Reference

Introduction
About this Manual
Reading SQL Syntax Diagrams
Key to Syntax Diagrams
Reading Standard Compliance Tables
Introduction to SQL Standards
History of Standards
SQL-99
The Unicode Standard and ISO/IEC 10646
Mimer SQL Database Objects
The Data Dictionary
Databanks
Idents
Schemas
Tables
Primary Keys and Indexes
Stored Procedures
Synonyms
Shadows
Triggers
Sequences
Mimer SQL Character Sets
Collations and Linguistic Sorting
Data Integrity
Privileges
SQL Syntax Elements
Characters
Identifiers
Data Types in SQL Statements
Literals
Operators and Values
Operators
Value Specifications
Default Values
Assignments
Comparisons
Result Data Types
Functions
About Set Functions
About Scalar Functions
Scalar Functions
Standard Compliance
Expressions and Predicates
Expressions
CASE Expression
CAST Specification
Predicates
Search Conditions and Joins
Search Conditions
Joined Tables
INNER JOINs
OUTER JOINs
Standard Compliance
The SELECT Specification
SELECT Specification Syntax
Vendor-specific SQL
Escape Clauses
Processing Escape Clauses
Standard Compliance
SQL Statement Descriptions
Usage Modes
ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
ALTER DATABANK
ALTER DATABANK RESTORE
ALTER IDENT
ALTER SHADOW
ALTER STATEMENT
ALTER TABLE
CALL
CASE
CLOSE
COMMENT
COMMIT
COMPOUND STATEMENT
CONNECT
CREATE BACKUP
CREATE COLLATION
CREATE DATABANK
CREATE DOMAIN
CREATE FUNCTION
CREATE IDENT
CREATE INDEX
CREATE MODULE
CREATE PROCEDURE
CREATE SCHEMA
CREATE SEQUENCE
CREATE SHADOW
CREATE STATEMENT
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DEALLOCATE DESCRIPTOR
DEALLOCATE PREPARE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE SECTION
DECLARE VARIABLE
DELETE
DELETE CURRENT
DESCRIBE
DISCONNECT
DROP
ENTER
EXECUTE
EXECUTE IMMEDIATE
EXECUTE STATEMENT
FETCH
GET DESCRIPTOR
GET DIAGNOSTICS
GRANT ACCESS PRIVILEGE
GRANT OBJECT PRIVILEGE
GRANT SYSTEM PRIVILEGE
IF
INSERT
ITERATE
LEAVE
LEAVE (PROGRAM ident)
LOOP
OPEN
PREPARE
REPEAT
RESIGNAL
RETURN
REVOKE ACCESS PRIVILEGE
REVOKE OBJECT PRIVILEGE
REVOKE SYSTEM PRIVILEGE
ROLLBACK
SELECT
SELECT INTO
SET
SET CONNECTION
SET DATABANK
SET DATABASE
SET DESCRIPTOR
SET SESSION
SET SHADOW
SET TRANSACTION
SIGNAL
START
UPDATE
UPDATE CURRENT
UPDATE STATISTICS
WHENEVER
WHILE
Data Dictionary Views
INFORMATION_SCHEMA
INFO_SCHEM
FIPS_DOCUMENTATION
Appendix A Reserved Words
Appendix B Character Sets
Character Data
National Character Data - Unicode
Appendix C Limits
Appendix D Deprecated Features
Indicator Variables
Operators
Statements
Communication Areas
Program Idents
Datetime Scalar Functions
Data Dictionary Views
Host Variable Types
Appendix E Return Status and Conditions
SQLSTATE Return Codes
Appendix F SQL-99 Compliance
SQL-99 Core Features
Features Outside Core Supported by Mimer SQL
Appendix G Languages

Programmer's

Introduction
About this Manual
Database APIs
Mimer SQL and the ODBC API
The Mimer ODBC Driver
Required Files
Unicode and ANSI Interfaces
Examples in this Chapter
Operating Systems
Declarations
Initializing the ODBC Environment
Error Handling
Transaction Processing
Executing a Command
Repeating - Prepared Execution
Result-set Processing
Updating Data
Mimer SQL and the JDBC API
The Mimer JDBC Driver
Embedded SQL
General Principles for Embedding SQL Statements
Processing ESQL
Essential Program Structure
Linking Applications
Idents and Privileges
Mimer SQL Idents
Database Privileges
Connecting to a Database
The CONNECT Statement
Communicating with the Application Program
Using Host Variables
The SQLSTATE Variable
The Diagnostics Area
The SQL Descriptor Area
Accessing Data
Retrieving Data Using Cursors
Retrieving Single Rows
Retrieving Data from Multiple Tables
Entering Data into Tables
Transaction Handling and Database Security
Transaction Principles
Transactions and Logging
Protecting Against Data Loss
Transaction Control Statements
Distributed Transactions
Terms and Abbreviations
How Does it Work?
Handling failures
Mimer SQL Support For Microsoft DTC on Windows
Dynamic SQL
Principles of Dynamic SQL
General Summary of Dynamic SQL Processing
SQL Descriptor Area
Preparing Statements
Extended Dynamic Cursors
Describing Prepared Statements
Handling Prepared Statements
Example Framework for Dynamic SQL Programs
Mimer SQL Stored Procedures
About Routines
Syntactic Components of a Routine Definition
Modules
SQL Constructs in Routines
Manipulating Data
Result Set Procedures
Managing Exception Conditions
Access Rights
Using DROP and REVOKE
The Mimer SQL PSM Debugger
Triggers
Creating a Trigger
Trigger Time
Trigger Event
Trigger Action
Comments on Triggers
Using DROP and REVOKE
Handling Errors and Exceptions
Syntax Errors
Semantic Errors
Run-time Errors
Appendix A Host Language Dependent Aspects
ESQL in C/C++ Programs
ESQL in COBOL Programs
ESQL in FORTRAN Programs
Appendix B Return Codes
SQLSTATE Return Codes
Internal Mimer SQL Return Codes
Appendix C Deprecated Features
INCLUDE SQLCA
SQLCODE
SQLDA
Parameter Marker Representation
VARCHAR(size)
SET TRANSACTION
DBERM4

User's

Introduction
About this Manual
Basic Concepts of Mimer SQL
Tables
Synonyms
Primary Keys and Indexes
Data Integrity
Sequences
Databanks
Shadows
Mimer SQL Character Sets
Collations and Linguistic Sorting
Stored Procedures
Idents
Schemas
Access Rights and Privileges
The Data Dictionary
Mimer SQL Statements
Managing Database Connections
Connecting to a Database
PROGRAM Idents - ENTER and LEAVE
Retrieving Data
Simple Retrieval
Result Order
Table Names
Setting Column Labels
Eliminating Duplicate Values
Selecting Specific Rows
Retrieving Computed Values
Using Scalar Functions
Using the CASE Expression
Using the CAST Specification
Datetime Arithmetic and Functions
Using Set Functions
Grouped Set Functions - the GROUP BY Clause
Selecting Groups - the HAVING Clause
Ordering the Result Table
Retrieving Data from More than One Table
Handling NULL Values
Conceptual Description of the Selection Process
Collations
Character Sets and Collations
Using Collations
Using Collations - Examples
Working with Data
Inserting Data
Updating Tables
Deleting Rows from Tables
Calling Procedures
Updatable Views
Managing Transactions
Transaction Principles
Logging Transactions
Handling Transactions
Creating a Database
Creating Idents and Schemas
Creating Databanks
Creating Tables
Creating Sequences
Creating Domains
Creating Functions, Procedures, Triggers and Modules
Creating Views
Creating Secondary Indexes
Creating Synonyms
Commenting Objects
Altering Databanks, Tables and Idents
Dropping Objects from the Database
Defining Privileges
Ident Structure
Granting Privileges
Revoking Privileges
Mimer BSQL
Running BSQL
BSQL Commands
Variables in BSQL
Transaction Handling in Mimer BSQL
LOBs in BSQL
Errors in BSQL
Error Messages
Appendix A The Example Environment
The EXLOAD program
The MIMER_STORE Schema
Procedures
Views
Triggers
Idents
The MIMER_STORE_MUSIC Schema
The MIMER_STORE_BOOK Schema
The MIMER_STORE Schema Revisited
The MIMER_STORE_WEB Schema
Synonyms
Appendix B Deprecated Features
BSQL Commands

System Management

Introduction
About this Manual
System Management Responsibilities
The Database Environment
The Data Dictionary
Idents
Schemas
Databanks
Databank Options
Locating Databank Files
Organizing Databank Files
Altering Databank Locations
Accessing Databank Files
Databank File Deletion
Transaction Control
Database Security
Data Integrity
Creating a Mimer SQL Database
Registering the Database
The Local Database
Accessing a Database Remotely
Mimer SQL License Key
Generating the Mimer SQL System Databanks
Managing Database Connections
Running Mimer BSQL
Managing a Database Server
Database Server Memory Areas
Threads
Database Server System Requirements
Controlling the Database Server - MIMCONTROL
System Information - MIMINFO
Database Server Log
Other Log Files
Several Installations on One Machine
Backing-up and Restoring Data
Database Consistency
LOGDB and TRANSDB Importance
Updates Recorded in LOGDB
TRANSDB Considerations
SQLDB Considerations
Databank Backups
System vs. Online Backups
SQL Statements for Backing-up Databanks
Backing-up and Restoring Databanks
Restoring a Databank
Restoring SYSDB
Re-creating TRANSDB, LOGDB and SQLDB
Audit trail with READLOG
Databank Check Functionality
DBC Syntax
Authorization
Result File Contents
DBOPEN
DBOPEN Syntax
Functions
Authorization
DBOPEN Output Example
Loading and Unloading Data and Definitions
Loading Data
Unloading Data
Data Description Headers and Files
File Format Specifications
MIMLOAD
Using STDIN/STDOUT/STDERR
EXPTOLOAD
Mimer SQL Shadowing
About Databank Shadowing
Levels of Data Protection
Creating and Managing Shadows
SQL Shadowing Commands - an Example Session
Shadowing System Databanks
Data Protection Strategy
Configuring Your System
Performance Aspects of Shadowing
Troubleshooting
Database Statistics
Authorization
The SQL Statistics Statements
When to Use the SQL Statistics Statements
Appendix A Executing in Single-user Mode
File Protection in Single- and Multi-user Mode
Specifying Single-user Mode Access
Accessing in Single-user Mode
Appendix B Data Dictionary Tables
Summary of Data Dictionary Tables
Appendix C Deprecated Features
Export/Import
Load/Unload
Readlog from UTIL
Backup/Restore from UTIL
Statistics from UTIL
Shadowing Management from UTIL

Working

Introduction
About Mimer SQL Mobile
Database APIs
About this Manual
Developing Applications with Mimer SQL Mobile
Embedded SQL
ODBC
JDBC
Packaging Mimer SQL Mobile Applications on Symbian