Summary of the content on the page No. 1
ISQL and Tools Reference Guide
For use with c-treeSQL Server
This manual provides reference material for the ISQL interactive SQL utility
and other administrative tools provided in the c-treeSQL environment. It also
includes a tutorial describing how to use the ISQL utility.
Summary of the content on the page No. 2
Copyright © 1992-2004 FairCom Corporation All rights reserved. Portions © 1987-2004 Dharma Systems, Inc. All rights reserved. Eleventh Edition, First printing: September 2003 Information in this document is subject to change without notice. No part of this publication may be stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise without the prior written permission of FairCom Corporation. Printed in the United Stat
Summary of the content on the page No. 3
Table of Contents Documentation Overview Purpose of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Syntax Diagram Conventions . . . .
Summary of the content on the page No. 4
3.5 The HELP and TABLE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.6 Transaction Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.7 ISQL Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3-12 3.7.1 @ (Execute) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Summary of the content on the page No. 5
5.4 Data File Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 5.5 The Commands File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 5.5.1 The DEFINE RECORD Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3 5.5.2 The FOR RECORD Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4 5.6 Ex
Summary of the content on the page No. 6
iv FairCom Corporation
Summary of the content on the page No. 7
Documentation Overview PURPOSE OF THIS MANUAL This manual provides reference material for the ISQL interactive SQL utility as well as the dbload, dbdump, and dbschema administrative tools provided in the c-treeSQL environment. It also includes a tutorial describing how to use the ISQL utility. AUDIENCE The reader of this manual should be familiar with general SQL concepts. STRUCTURE The manual contains the following chapters: Chapter 1 Describes the use of Interactive SQL (ISQL) for perfor
Summary of the content on the page No. 8
ISQL and Tools lowercase Lowercase type denotes either user-supplied elements or names of other syntax diagrams. User-supplied elements include names of tables, host- language variables, expressions, and literals. Syntax diagrams can refer to each other by name. If a diagram is named, the name appears in lower- case type above and to the left of the diagram, followed by a double-colon (for example, privilege ::). The name of that diagram appears in lowercase in diagrams that refer to it. { }
Summary of the content on the page No. 9
Chapter 1 Introduction 1.1 OVERVIEW Interactive SQL (often referred to throughout this manual as ISQL) is a utility supplied with c- treeSQL that lets you issue SQL statements directly from a terminal and see results displayed at the terminal. You can use interactive SQL to: • Learn how SQL statements work • Test and prototype SQL statements to be embedded in programs • Modify an existing database with data definition statements • Perform ad-hoc queries and generate formatted reports with speci
Summary of the content on the page No. 10
ISQL and Tools 1-2 FairCom Corporation
Summary of the content on the page No. 11
Chapter 2 Quick Tour 2.1 INTRODUCTORY TUTORIAL iSQL_Tutorial1.sql This introductory tutorial will rapidly take you through the basic use of the powerful interactive SQL (iSQL) database interface. iSQL is a full featured command line client side query tool useful for submitting ad hoc SQL statements to a Server. Likewise the tool provides ample output formatting capabilities. By no means does this introduction cover the full scope, detail, or flexibility that iSQL offers. It does however
Summary of the content on the page No. 12
ISQL and Tools 2.1.2 Define In this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement in which specific fields are defined. Upon successful creation of the table, the changes made to the database by this transaction are made permanent by executing the COMMIT WORK statement. The following SQL syntax provides the functionality for the define phase: • CREATE TABLE — Create a table. • COMMIT WORK — Make changes permanent. Below is the interactive SQL
Summary of the content on the page No. 13
Quick Tour VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'); ISQL> INSERT INTO CUSTMAST VALUES ('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta'); ISQL> INSERT INTO CUSTMAST VALUES ('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia'); ISQL> COMMIT WORK; Display Records ISQL> SELECT * FROM CUSTMAST; Delete Records ISQL> DELETE FROM CUSTMAST; ISQL> COMMIT WORK; 2.1.4 Done When a client application has completed
Summary of the content on the page No. 14
ISQL and Tools 2.2 RELATIONAL MODEL AND INDEXING TUTORIAL iSQL_Tutorial2.sql This intermediate tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables and building a relational model. This tutorial will walk you through defining an index for each table, demonstrating the power of indexes in a rela- tional model using a few simple API calls. This tutorial operates on the assumption that the database named 'myDatabase', already exists. Please ref
Summary of the content on the page No. 15
Quick Tour OrderList - A table of records consisting of a list of orders. OrderItem - A table of records consisting of specific items associated with an order. ItemMaster - A table of records consisting of information about items. CustomerMaster - A table of records consisting of specific info related to each customer. Each order (ordernum) in the orderlist table will contain 1 or more items (itemnum) in the orderitem table. Each item will have a corresponding definition (weight, price, desc
Summary of the content on the page No. 16
ISQL and Tools ISQL> CREATE INDEX itemnum ON itemmast (im_itemnum); ISQL> CREATE TABLE custmast ( cm_custnum VARCHAR(5), cm_zip VARCHAR(10), cm_state VARCHAR(3), cm_rating VARCHAR(2), cm_name VARCHAR(48), cm_address VARCHAR(48), cm_city VARCHAR(48)); ISQL> CREATE INDEX custnum ON custmast (cm_custnum); ISQL> COMMIT WORK; 2.2.3 Manage This step provides data management functionality for the applica- tion. In this example we will simply add records to
Summary of the content on the page No. 17
Quick Tour INSERT INTO custmast VALUES ('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange'); INSERT INTO custmast VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'); INSERT INTO custmast VALUES ('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta'); INSERT INTO custmast VALUES ('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia'); COMMIT WORK; The following SQL statement performs the query and
Summary of the content on the page No. 18
ISQL and Tools 2.2.5 Complete Relational Model and Indexing Tutorial Source Code Complete source code for the relational model and indexing tuto- rial can be found in Appendix A "Tutorial Source Code". 2-8 FairCom Corporation
Summary of the content on the page No. 19
Quick Tour 2.3 LOCKING TUTORIAL iSQL_Tutorial3.sql This tutorial will introduce the concept of locking. The function- ality for this tutorial focuses on adding records, then updating a single record to the customer master table. From the iSQL utility the script will be parsed and passed to the server. The script com- pletes without "committing" the update. This leaves the updated record locked. The user will launch another instance of the Inter- active SQL utility in another window which
Summary of the content on the page No. 20
ISQL and Tools 2.3.2 Define In this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement in which specific fields are defined. Upon successful creation of the table, the changes made to the database by this transaction are made permanent by executing the COMMIT WORK statement. The following SQL syntax provides the functionality for the define phase: • CREATE TABLE - Create a table. • COMMIT WORK - Make changes permanent. Below is the interactive SQL
