Using INGRES on Meena for the first time

May 1994

Dr. Yang Xiang

Kwei Aryeetey

Attention

This document is not a replacement for the Ingres Users' Manual. It is intended to supplement the manual with information on how to use Ingres, on Meena. It is also intended to guide new users so that they can get their hands on Ingres quickly without having to searching through several volumes of manuals.

This documentation assumes that its readers have a general knowledge of DBMS and SQL.

General Information

To access INGRES v6.0 on Meena, users need to do the following:

1. If you are registered in a class that uses INGRES, please have your class instructor request authorization for you to access INGRES, from Computing Services .

If you are not in a class that requires INGRES, please apply for authorization to access INGRES from Computing Services.

2. Include the following command line in your LOGIN.COM file to recognize INGRES commands:

$ setup INGRES

$ define term_ingres "vt100"

vt100 is the terminal type, so make sure that you have yours set to the appropriate type.

INGRES release notes are in the file INGDEV:[INGRES]RELEASE.DOC.

Extra INGRES reference manuals are also available, for perusal only, in room LY139 of Computing Services.

Creating Databases

A new database is an empty ( i.e. has no contents ) database frame. To create one, owned by you at anytime, use the following command:

$ createdb dbname -cdisk -ddisk -jdisk

where

* createdb is the command key word, and

* dbname is the name of the database.

* 'disk' is the logical name of your home device ( for example D12, M2 etc. )

To determine the name of your home device type the following command:

Show logical Sys$Login_Device

A database name must be unique and can contain up to nine (9) alphanumeric characters. The first characters must be a letter. The following are all legal commands:

createdb mydb

createdb empData

createdb Michael

createdb cs230

Listing Databases that you own

To browse through data on your own account and databases, use the following command:

$ catalogdb

CATALOGDB is a forms-based interface to the list of your databases and the databases that you may access. When you invoke the Catalogdb command, the main menu appears, offering the following options:

Catalog Database User Help Quit

In order to select these options, you must type each option, in its entirety followed by RETURN. These options do the following:

COMMAND FUNCTION

Catalog Display a menu of operations to print summary system information

Database Display detailed information about a database you own

User Display your user information

Help Print this Message

Quit Exit the CATALOGDB program

Each command invokes a form for you to browse. Depress ESCAPE before selecting a menu option from a form. This causes a colon to appear after the menu, after which you indicate your choice. When the "Database" command prompts you for the name of one of the databases that you own, simply enter the full name of the database, followed by RETURN, to invoke the form with information about that database.

Structured Query Language (SQL)

SQL is a high-level, nonprocedural language. With SQL statements, you can manipulate an INGRES database data by :

* Inserting (adding) data

* Updating data

* Deleting data

* Retrieving data

To start the SQL interface, use the following command:

$ sql dbname

where

* sql invokes the INGRES Terminal Monitor which is the primary user interface to SQL.

* dbname is your database's name

The following are a few examples of SQL queries:

* To CREATE a new table called emp :

create table emp ( empid smallint,

name varchar(20),

age smallint ) \g

Empid, name and age are data fields while smallint and varchar are some of the data types that can be chosen. Under the Terminal Monitor, SQL queries can be entered on multiple lines. '\g' or '\go' must be added at the end (see above example) of the query.

* To INSERT a new row into a table :

insert into emp ( name, salary, bDate)

values ( 'Jones Bill', 10000, 1994); \g

* To see how many tables you have :

help \g

* To see all the fields of a table :

select * from emp \g

* To project ( one way to do this is by using CREATE VIEW, since INGRES does not have a PROJECT command) :

create view empName ( name, age)

as select emp.name, emp.age

from emp \g

* To SELECT ( display ) data from a table :

select * from emp where age > 30 \g

* To JOIN tables ( one way of doing this, since INGRES does not have a JOIN, is by using a combination of the CREATE VIEW and SELECT commands) :

Example 1 :

create view older ( empname, empage, famage, famname )

as select emp.name, emp.age, family.age, family.name from emp, family; \g

select * from older \g

Example 2 :

create view new ( dname, dnum, dloc)

as select department.dname, department.number, dept_locations.dlocations from department, dept_locations

where department.dnumber = dept_locations.dnumber

select * from new \g

To find out more about INGRES SQL commands, see the INGRES SQL Reference Manual. This is available, for perusal only, at Computing Services.

In addition to the above, you could also use SQL statements to do the following (consult your INGRES reference for more information on this) :

* In INGRES applications via INGRES/4GL

* In programs via Embedded SQL ( described in the next section )

* In report specifications for the INGRES Report-Writer

* In a form-based SQL (ISQL)

Embedded SQL/C

SQL can be embedded into procedural languages such as C, PASCAL or FORTRAN, and this section describes how to embed SQL in a C program. For more information on Embedded Language Programming, see the INGRES Manual, Embedded SQL for C section.

To turn an embedded SQL/C (ESQL/C) source program into an executable program do the following :

* Preprocess source program {filename}.sc in C

esqlc file.sc

* Compile file

cc fil

* Link file

link file, ii_system:[ingres.files]esql.opt/opt,

sys$library:vaxcrt1.obj/library

* Execute file

run file.exe

For example :

* The following embedded SQL/C program receives a social insurance number from the user, extracts the information about the corresponding employee from a relation 'emp' in a database called 'dbxy', and prints out the information.

main( ) {

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;

struct emp_ {

char fname[10];

char lname[10];

long ssn;

char bdate[10];

char address[30];

char sex[1];

int salary;

long superssn;

int dno;

} emp;

long ssno;

EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR STOP;

EXEC SQL CONNECT dbxy;

printf("Enter an SSN number (0 to stop) : \n");

scanf("%d", &ssno);

while (ssno != 0) {

EXEC SQL select fname, lname, ssn, bdate, address, sex, salary

into :emp.fname, :emp.lname, :emp.ssn, :emp.bdate,

:emp.address, :emp.sex, :emp.salary

from employee where ssn = :ssno;

printf("Name: %s %s SSN: %10d\n", emp.lname, emp.fname,

emp.ssn);

printf("BirthDate: %s Address: %s \n", emp.bdate, emp.address);

printf("Sex: %.1s Salary: %d \n", emp.sex, emp.salary);

printf("Enter a SSN number (0 to Stop): \n");

scanf("%d ", &ssno);

}

EXEC SQL DISCONNECT;

}