4.              c-tree isql utility

Interactive SQL (often referred to throughout this manual as ISQL) is a utility supplied with ctreeSQL that lets you issue SQL statements directly from a terminal and see results displayed at the terminal. You can use interactive SQL to:

l         Learn how SQL statements work

l         Test and prototype SQL statements to be embedded in programs

l         Modify an existing database with data definition statements

l         Perform ad-hoc queries and generate formatted reports with special ISQL formatting statements

 

4.1.    SQL-92 Standard support

4.1.1.  iSQL supports SQL-92 standard

1)     iSQL command Options:

Graph 4.1.1.1

 

2)     Create a table

Type the following commands to create a table:

CREATE TABLE EMPLOYEE (   EMPID        INT,   FIRSTNAME VARCHAR(30),   LASTNAME  VARCHAR(30),   MALE          VARCHAR(10),   AGE            SMALLINT,   BIRTH         TIMESTAMP,   NOTE          VARCHAR(200),   DEPARTMENTID INT,   PRIMARY KEY(EMPID) );   CREATE TABLE DEPARTMENT (   DEPTID       INT,   NAME         VARCHAR(50),   DESCRIPTION    VARCHAR(200),   PRIMARY KEY(DEPTID) );

 

3)     Insert records

Type the following commands to insert records to a table:

INSERT INTO EMPLOYEE(EMPID, FIRSTNAME, LASTNAME, MALE, AGE, BIRTH, NOTE, DEPARTMENTID) VALUES (1, 'Clark', 'Jin', 'Male', 26, '1999-05-11 13:17:11', '', 1); INSERT INTO EMPLOYEE(EMPID, FIRSTNAME, LASTNAME, MALE, AGE, BIRTH, NOTE, DEPARTMENTID) VALUES (2, 'Johnson', 'He', 'Male', 28, '1989-05-11 13:17:11', '', 1);   INSERT INTO DEPARTMENT VALUES (1, 'STB', 'STB development team');

 

4)     Get records

Select * from employee;   Select (firstname + lastname) as name from employee, department where employee.departmentid = department.deptid;

 

5)     Create a index

CREATE INDEX IDX_1 ON EMPLOYEE (AGE);

 

 

4.1.2.  Transaction Processing

Use command “COMMIT WORK” and “ROLLBACK WORK” to do the transaction processing.

 

4.1.3.  System Tables

c-tree server has many system tables which are similar as SQL Server.

E.g.

l         SysIndexes

l         SysColumns

 

For detail information, please view the iSQL help document.

 

4.1.4.  iSQL statements

iSQL has many other functionalities. E.g.

l         Format outputs

l         Spool outputs to file

l         Execute the script file by “@” statement

 

For detail information, please go to the iSQL help document.

 

5.2.    Other utilities

5.2.1.  dbload utility

The dbload utility allows loading of variable- or fixed-length records, and lets the load operation specify the set of fields and records to be stored from an input file. Data files can use multiple-character record delimiters. dbload also allows control of other characteristics, such as error handling and logging, in its command line. dbload generates a badfile that contains records from the input file that failed to load in the database.

Graph 4.2.1.1

 

 

5.2.2.  dbdump utility

dbdump writes the data in a database to a file. The format of the exported data is specified by

the record description given in an input command file to dbdump.

Graph 4.2.2.1

 

 

5.              c-tree server SQL Reference

Because c-tree server SQL grammar supports SQL-92 standard, most commands are same as standard SQL.

 

5.1.    Data Types

There are several categories of SQL data types:

l         Character

l         Exact numeric

l         Approximate numeric

l         Date-time

l         Bit String

 

All the types can be transferred to SQL Server data types.

 

5.2.    Database objects

There are several categories of c-tree server database objects:

l         Column

l         Index

l         Table

l         View

l         Trigger

l         Stored Procedure

l         Synonym

 

A stored procedure example:

CREATE PROCEDURE new_sal (        IN deptnum INTEGER,        IN pct_incr INTEGER, ) RESULT (        empname CHAR(20),        oldsal NUMERIC,        newsal NUMERIC ) BEGIN        StringBuffer ename = new StringBuffer (20) ;        BigDecimal osal = new BigDecimal () ;        BigDecimal nsal = new BigDecimal () ;        SQLCursor empcursor = new SQLCursor (        "SELECT empname, sal, (sal * ( ? /100) + NVL (comm, 0)) total FROM emp WHERE deptnum = ? " ) ;        empcursor.setParam (1, pct_incr);        empcursor.setParam (2, deptnum);        empcursor.open () ;        do        {               empcursor.fetch ();               if (empcursor.found ())               {                      empcursor.getValue (1, ename);                      empcursor.getValue (2, osal);                      empcursor.getValue (3, nsal) ;                      SQLResultSet.set (1, ename);                      SQLResultSet.set (2, osal);                      SQLResultSet.set (3, nsal) ;                      SQLResultSet.insert ();               }        } while (empcursor.sound ()) ;        empcursor.close () ; END

 

 

5.3.    SQL Functions

 

5.4.    Unicode support

From version 7.11(current Oberon systems use c-tree server 6.10) Unicode was supported by c-tree server.

         For detail information, please view the product documents.