Usage Guidelines

Prerequisites

The user has the permission to access the database.

Background

Use the gsql command to connect to the remote database service. When connecting to the remote database service, enable remote connection on the server. For details, see "Database Usage > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database" in the Developer Guide.

Procedure

1. Connect to the MogDB server using the gsql tool.

   The gsql tool uses the -d parameter to specify the target database name, the -U parameter to specify the database username, the -h parameter to specify the host name, and the -p parameter to specify the port number.

   NOTE: If the database name is not specified, the default database name generated during initialization will be used. If the database username is not specified, the current OS username will be used by default. If a variable does not belong to any parameter (such as -d and -U), and -d is not specified, the variable will be used as the database name. If -d is specified but -U is not specified, the variable will be used as the database username.

   Example 1: Connect to port 15400 of the mogdb database on the local PC as user omm:

   gsql -d mogdb -p 15400

   Example 2: Connect to the port 15400 of the remote mogdb database as user jack.

   gsql -h 10.180.123.163 -d mogdb -U jack -p 15400

   Example 3: mogdb and omm do not belong to any parameter, and they are used as the database name and the username, respectively.

   gsql mogdb omm -p 15400

   Equals

   gsql -d mogdb -U omm -p 15400

   For details about the gsql parameters, see Command Reference.

2. Run a SQL statement.

   The following takes creating database human_staff as an example:

   CREATE DATABASE human_staff;

Ordinarily, input lines end when a command-terminating semicolon is reached. If the command is sent and executed without any error, the command output is displayed on the screen.

1. Execute gsql meta-commands.

   The following takes the listing of all MogDB databases and description information as an example.

   mogdb=#  \l
                                   List of databases
         Name      |  Owner   | Encoding  | Collate | Ctype |   Access privileges
   ----------------+----------+-----------+---------+-------+-----------------------
    human_resource | omm | SQL_ASCII | C       | C     |
    mogdb       | omm | SQL_ASCII | C       | C     |
    template0      | omm | SQL_ASCII | C       | C     | =c/omm         +
                   |          |           |         |       | omm=CTc/omm
    template1      | omm | SQL_ASCII | C       | C     | =c/omm          +
                   |          |           |         |       | omm=CTc/omm
    human_staff    | omm | SQL_ASCII | C       | C     |
   (5 rows)

   For details about gsql meta-commands, see Meta-Command Reference .

Example

Run the following command to create a tablespace EXAMPLE:

mogdb=# CREATE TABLESPACE EXAMPLE RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE

After the tablespace is created, run the following command to create a schema HR:

mogdb=# CREATE schema HR;
CREATE SCHEMA

The example shows how to spread a command over several lines of input. Note the prompt change:

mogdb=# CREATE TABLE HR.areaS(
mogdb(# area_ID   NUMBER,
mogdb(# area_NAME VARCHAR2(25)
mogdb(# )tablespace EXAMPLE;
CREATE TABLE

Query the table definition:

mogdb=# \d HR.areaS
               Table "hr.areas"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
 area_id   | numeric               |
 area_name | character varying(25) |
Tablespace: "example"

Insert four lines of data into HR.areaS.

mogdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (1, 'Europe');
INSERT 0 1
mogdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (2, 'Americas');
INSERT 0 1
mogdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (3, 'Asia');
INSERT 0 1
mogdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (4, 'Middle East and Africa');
INSERT 0 1

Change the prompt.

mogdb=# \set PROMPT1 '%n@%m %~%R%#'
omm@[local] mogdb=#

Query the table:

omm@[local] mogdb=#SELECT * FROM HR.areaS;
 area_id |       area_name
---------+------------------------
       1 | Europe
       4 | Middle East and Africa
       2 | Americas
       3 | Asia
(4 rows)

Use the \pset command to display the table in different ways:

omm@[local] mogdb=#\pset border 2
Border style is 2.
omm@[local] mogdb=#SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)
omm@[local] mogdb=#\pset border 0
Border style is 0.
omm@[local] mogdb=#SELECT * FROM HR.areaS;
area_id       area_name
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)

Use the meta-command:

omm@[local] mogdb=#\a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.
omm@[local] mogdb=#SELECT * FROM HR.areaS;
area_id|2
area_name|Americas

area_id|1
area_name|Europe

area_id|4
area_name|Middle East and Africa

area_id|3
area_name|Asia
omm@[local] mogdb=#