c-treeACE Embedded SQL

 
Copyright Notice
FairCom Typographical Conventions
Introduction
Overview
The Relational View of Data
Using c-treeSQL in a Host Language
Advantages of Using ESQL
Components of an ESQL Application
Developing an Embedded SQL Application
Quick Tour
Introductory Tutorial
Init
Define
Manage
Done
Additional Resources
Relationships
Init
Define
Manage
Done
Additional Resources
Record/Row Locking
Init
Define
Manage
Done
Additional Resources
Transaction Processing
Init
Define
Manage
Done
Additional Resources
Using the esqlc Command
Introduction
esqlc Command Line Reference
esqlc Command Examples
Precompiling, Compiling, and Linking Multiple Files with esqlc
Issuing Separate Commands for Precompiling, Compiling, and Linking
Mixing Precompiler/Compiler Options in the esqlc Command
ESQL Program Structure
Introduction
ESQL Declare Statements
Host Variables and their Usage
Indicator Variables and their Usage
Referring to Indicator Variables
Inserting Null Values With Indicator Variables
Checking Indicator Variables to Test for Returned Null Values
Limitations of the Declare Section
ESQL Executable Statements
Types of Executable Statements
The c-treeSQL Communications Area
The COMMIT WORK and ROLLBACK WORK Statements
ESQL Application Development
Introduction
Guidelines
Using c-treeSQL for Computation and Conversion
Using c-treeSQL for Condition Evaluation
Using Indicator Variables
Using Scalar Functions
Using Static and Dynamic Statements
Connection Management in ESQL
Introduction
The CONNECT Statement
Connection Using a Connection Name
Connection by Default
Connection to a Remote Database
The SET CONNECTION Statement
The DISCONNECT Statement
c-treeSQL Data Definition Statements
Introduction
Creating/Dropping Tables
Creating Tables
Dropping Tables
Creating/Dropping Indices
Creating Indices
Dropping Indices
Creating/Dropping Views
Creating Views
Dropping Views
Integrity Constraints
Need for Integrity Constraints
Types of Integrity Constraints
Check Constraints
Column-Level Check Constraint
Table-Level Check Constraint
Primary Keys
Column-Level Primary Key Constraint
Table-Level Primary Key Constraint
Candidate Keys
Column-Level Candidate Key Constraint
Table-Level Candidate Key Constraint
Referential Constraints
Column-Level Foreign Key Constraint
Table-Level Foreign Key Constraint
Handling Cycles in Referential Integrity
Creation of Tables in Cycles
Insertion of Rows in Cycles
Dropping the Tables in Cycles
DDL Statements in Long Running Transactions
c-treeSQL Data Manipulation Statements
Introduction
Using DML Statements
Inserting Rows into a Table
Deleting Rows from a Table
Updating Rows in a Table
Input Host Variables in DML Statements
Query Statements
Introduction
Elements of a Query
Input Host Variables in Query Statements
Output Host Variables in Query Statements
Queries Returning a Single Row
Queries Returning Multiple Rows
Introduction to Cursors
Associating a Cursor with a Query
Opening a Cursor
Retrieving Rows Using a Cursor
Closing a Cursor
Deleting or Updating the Current Row
Array Fetches: Retrieving Multiple Rows with One FETCH Statement
NULL Value Handling in ESQL
Introduction
Inserting NULL Values
Inserting NULL Values by Default
Using the NULL Keyword to Insert NULL Values
Using Indicator Variables to Insert NULL Values
Updating with NULL Values
Retrieving NULL Values
Using Indicator Variables to Retrieve NULL Values
Using the Scalar Function NVL to Retrieve NULL values
Using NULL Values in Expressions
Using NULL Values in the WHERE Clause
Using NULL Values in GROUP BY Clause
Using NULL Values in ORDER BY Clause
Using NULL Values in Scalar Functions
Using NULL Values in Aggregate Functions
Error Handling in ESQL
Introduction
Using SQLCA for Error Handling
Fields of the SQLCA
Fields of the SQLCA
Using SQLCA for Checking Errors
Using SQLCA for Checking Warnings
Using the WHENEVER Statement for Error Handling
Handling the SQL_NOT_FOUND Condition with WHENEVER
Using WHENEVER Along With Explicit Error Checking
Using Indicator Variables for Error Handling
Dynamic SQL Management in ESQL
Introduction
Overview
Preparing Statements
Non-SELECT Statements
Executing a Non-SELECT Statement
The EXECUTE IMMEDIATE Statement
SELECT Statements
Opening a Cursor
Fetching Rows Using a Cursor
Closing a Cursor
ESQL Descriptor Statement
ALLOCATE DESCRIPTOR
DEALLOCATE DESCRIPTOR
GET DESCRIPTOR
SET DESCRIPTOR
The c-treeSQL Descriptor Area - SQLDA
The Components of SQLDA
Allocating an SQLDA
Setting the SQLDA Types and Lengths
Allocating for SQLDA Data Buffers and Indicator Variables
Using SQLDA for Array Fetches
Freeing an SQLDA
The DESCRIBE Statement
Using SQLDA for Input Variables
Using SQLDA for Output Variables
Transaction Management in ESQL
Introduction
Transaction Overview
Starting a Transaction
The COMMIT WORK Statement
The ROLLBACK WORK Statement
Transaction Isolation Levels
Locking and Transactions
Abnormal Termination of an ESQL Application Program
Forced Rollback of a Transaction
Interrupting the Execution of an SQL Statement
Data Type Handling in ESQL
Introduction
Data Type Descriptions
CHARACTER Data Type
SMALLINT Data Type
INTEGER Data Type
BIGINT
REAL Data Type
FLOAT Data Type
DOUBLE PRECISION
NUMERIC or DECIMAL Data Type
Internal Storage Format for NUMERIC Data
Interpreting the Sign/Exponent Byte of dec_digits
Interpreting the Data Values Bytes of dec_digits
Complete Examples: Interpreting Sign/Exponent and Data Bytes of dec_digits
DATE Data Type
TIME Data Type
TIMESTAMP
BIT
BINARY [(length)]
LVARBINARY | LONG VARBINARY
Data Conversion
Implicit Data Type Conversion
Using the SQL API Function tpe_conv_data for Data Type Conversion
Using SQL Scalar Functions for Data Type Conversion
Data Comparison
Using the SQL API Function tpe_compare_data for Data Comparison
Using SQL Arithmetic Expressions for Data Comparison
ESQL Reference
Introduction
BEGIN-END DECLARE SECTION
CLOSE
DECLARE CURSOR
DESCRIBE
END DECLARE SECTION
EXEC SQL
EXECUTE
EXECUTE IMMEDIATE
FETCH
OPEN
PREPARE
Query Expressions
Search Conditions
LIKE Predicate
IN Predicate
Single-Row SELECT Statement
Type Specifications for Variable and Type Declarations
Database Types
Host Language Types
Table-Column Types
Static Array Types
Record Types
Table Record Types
WHENEVER
Long Data Type Support
DECLARE Section
INSERT Operation
SELECT Operation
Programmatic Interfaces
Introduction
Programmatic Interfaces
sqld_alloc
sqld_free
dh_alloc_sqlenv
dh_compare_data
dh_conv_data
dh_dayofweek
dh_free_sqlenv
dh_get_curdbhdl
dh_get_curtmhdl
dh_num_add
dh_set_cursor
dh_set_ptrs
dh_set_sqlda
dh_sqlclose
dh_sqlconnect
dh_sqldeclare
dh_sqldescribe
dh_sqldescribe_param
dh_sqldisconnect
dh_sqlexecute
dh_sqlfetch
dh_sqlgetdata
dh_sqlopen
dh_sqlprepare
dh_sqlselect
dh_tm_alloc_handle
dh_tm_begin_trans
dh_tm_end_trans
dh_tm_mark_abort
dh_sqlputdata
dh_sqlallocdesc
dh_sqlgetsqldaptr
dh_sqldeallocdesc
dh_sqlgetdesc
dh_sqlsetdesc
dh_sqlgetdiag
dh_tm_set_level
dh_sqltables
dh_sqlcolumns
dh_sqlstatistics
dh_sqlprimarykeys
dh_sqlforeignkeys
Sample ESQL Programs
Static Non-Select Statements
Compiling and Running
Program Source Code
Static SELECT Statements
Compiling and Running
Program Source Code
Dynamic Non-SELECT Statements
Compiling and Running
Program Source Code
Dynamic SELECT Statements
Compiling and Running
Program Source code
Long Data Type Support
Sample ESQLC Program for lvarchar Data Type
Sample ESQLC Program for lvarbinary Data Type
Glossary
Index