Using DB-Access Interactively in Non-Menu Mode

If you do not want to use the menus and do not have a prepared SQL file, use your keyboard or standard input device to enter SQL statements.

Reading from the Keyboard or Standard Input Device

When you invoke DB–Access without a menu argument (such as -q) and with a hyphen as the final argument, DB–Access processes commands from the standard input device (on UNIX platforms) or the keyboard (on Windows platforms). DB–Access reads what you type until you indicate that the input is complete. Then DB–Access processes your input and writes the results to the standard output device (on UNIX platforms), or the command window (on Windows).

Interactive Input

DB–Access reads and executes SQL statements from the terminal keyboard interactively. While DB–Access runs interactively, the greater than (>) prompt marks the line where you type your next SQL statement.

When you type a semicolon (;) to end a single SQL statement, DB–Access processes that statement. When you press CTRL-D to end the interactive session, DB–Access stops running. The following example shows user input and results in an interactive session:

dbaccess - -
>database stores_demo;

Database selected.

>select count(*) from systables;

(count(*))

     21

1 row(s) retrieved.

>^D

dbaccess - -
>database stores_demo;

Database selected.

>select count(*) from systables;

(count(*))

     21

1 row(s) retrieved.

>^D

Batch Command Input on UNIX Platforms

You can use an in-line shell script to supply one or more SQL statements. For example, you can use the UNIX C, Bourne, or Korn shell with in-line standard input files:

dbaccess mystores- <<EOT!
select avg(customer_num) from customer
where fname matches '[A-G]*';
EOT!

You can use a pipe to supply SQL statements:

echo 'select count(*) from systables' | dbaccess mystores

DB–Access interprets any line that begins with an exclamation mark (!) as a shell command. You can mix shell escape lines with SQL statements and put them in SQL statements, as follows:

dbaccess mystores -
>select
!echo hello
>hello
count(*) from systables;
>
(count(*))

     21

1 row(s) retrieved.
>

Connecting to a Database Environment in Non-Menu Mode

You can use the CONNECT ... USER syntax in SQL statements that you issue in interactive mode. However, DB–Access does not support the USER clause of the CONNECT statement when you connect to a default database server.

Connecting in Interactive Non-Menu Mode

When you include the USER 'user identifier' clause in a CONNECT statement in interactive mode, DB–Access prompts you to enter a password.

The following two command examples show how to connect to a database server in interactive mode. The first example uses the CONNECT statement without specifying a user identifier.

dbaccess -nohistory- -

> connect to '@starfish';

Connected.

If you include the USER clause in a CONNECT statement, as the second example shows, DB–Access uses echo suppression to prompt you for a password:

> connect to '@starfish' user 'marae';

ENTER PASSWORD:

Connected.

Connecting with a File or Shell File in Background Mode

You can execute the USER clause of a CONNECT statement in a DB–Access file that includes the USER clause. The following example uses a command file that contains a CONNECT statement with a USING clause to connect to a database server:

dbaccess - connfile.sql

CONNECT USER user_id USING password 

dbaccess - - <<\!
connect to '@starfish' user 'marae';
!

ENTER PASSWORD:

Using the DB-Access History in Command Mode (XPS)

DB-Access provides command history logging which makes it easier to re-execute commands. The following example shows how the command history function is used.

% dbaccess - -
1> create database sales_demo;

Database created.

2> create table customer (
2>        customer_code integer,
2>        customer_name char(31),
2>        company_name char(20));

Table created.

3> insert into customer values (102, "Carole Sadler", "Sports Spot");

1 row(s) inserted.

4> history;

1   create database sales_demo
2   create table customer (
           customer_code integer,
           customer_name char(31),
           company_name char(20))
3   insert into customer values (102, "Carole Sadler", "Sports Spot")

4> run 3;

1 row(s) inserted.

5> history;

1   create database sales_demo
2   create table customer (
           customer_code integer,
           customer_name char(31),
           company_name char(20))
3   insert into customer values (102, "Carole Sadler", "Sports Spot")
4   insert into customer values (102, "Carole Sadler", "Sports Spot")

5> 

The history command lists the SQL statements used in the current session. The number of commands that are logged is determined by the IFMX_HISTORY_SIZE environment variable. For more information see Environment Variables.

The run statement_number command executes the statement corresponding to the number in the command history. The run command logs the original command in the history. The history and run commands are not logged.