Connecting and Disconnecting

Connecting and Disconnecting

Connecting to a Database

The CONNECT command is used to connect to Altibase with a specified user ID. If the first connection attempt fails, the CONNECT command does not prompt again for the user ID or password.

CONNECT [logon][nls] [AS SYSDBA];  
logon: userID[/password]  
nls: NLS=character_set

• userID/password The user ID and password with which to establish a connection to Altibase.

• NLS=character_set The NLS option specifies the character set.

  iSQL> CONNECT sys/manager NLS=US7ASCII
  Connect success.
  

• AS SYSDBA The AS clause permits the SYS user to access the server in sysdba manager mode.

  If CONNECT is successful, the current session is terminated, and a connection is established to the server using the specified user ID and password and the information in altibase.properties. Accordingly, the session information is cleared before connecting.

  For instance, if AUTOCOMMIT mode is set to TRUE in altibase.properties and AUTOCOMMIT mode is changed to FALSE in iSQL, when the CONNECT statement is executed, AUTOCOMMIT mode will be changed to TRUE, because of the value in altibase.properties.

  If CONNECT fails, the previous session is terminated and the connection with the server is closed. In other words, the result of all SQL statements executed thereafter will be a "Not connected" message. Execute "CONNECT userID/password [AS SYSDBA]" to attempt to re-establish a connection with the server.

  $ isql
  -------------------------------------------------------
       Altibase Client Query utility.
       Release Version 7.1.0.1
       Copyright 2000, Altibase Corporation or its subsidiaries.
       All Rights Reserved.
  -------------------------------------------------------
  Write Server Name (default:127.0.0.1) :
  Write UserID : SYS
  Write Password :
  ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
  iSQL> SHOW USER;
  User : SYS
  iSQL> CREATE USER altiadmin IDENTIFIED BY altiadmin1234;
  Create success.
  iSQL> CONNECT altiadmin/altiadmin1234;
  Connect success.
  iSQL> SHOW USER;
  User : ALTIADMIN
  iSQL> CREATE TABLE altitbl(i1 INTEGER, i2 CHAR(5));
  Create success.
  iSQL> SELECT * FROM tab;
  TABLE NAME                               TYPE
  ---------------------------------------------
  ALTITBL                                  TABLE
  .
  .
  .
  33 row selected.
  iSQL> CONNECT sys/manager;
  Connect success.
  iSQL> SHOW USER;
  User : SYS
  iSQL> CREATE TABLE systbl(i1 INTEGER, i2 CHAR(5));
  Create success.
  iSQL> SELECT * FROM tab;
  USER NAME       TABLE NAME        TYPE
  -----------------------------------------------
  SYSTEM_       SYS_COLUMNS_      SYSTEM TABLE
  SYSTEM_       SYS_CONSTRAINTS_  SYSTEM TABLE
  .
  .
  .
  ALTIADMIN       ALTITBL             TABLE
  SYS             SYSTBL              TABLE
  .
  .
  .
  93 rows selected.
  

Note

Double quotation marks should be used if the name contains special characters or spaces.

iSQL> CONNECT "user name";

Connecting on SSL

Server-Exclusive Mode

When using a private certificate in server-exclusive mode (when the SSL_CLIENT_AUTHENTICATION property is set to 0), the location of the client certificate and private key file need not be specified, as the server does not authenticate the client.

Enable the -ssl_verify option and specify the location of the CA certificate file in which the server public key is incorporated, to verify the certificate received from the server.

$ export ISQL_CONNECTION=SSL
$ isql -s localhost -u sys -p MANAGER

or

$ isql -s localhost -u sys -p MANAGER -ssl_verify -ssl_ca ~/cert/ca-cert.pem

Mutual Authentication Mode

When using a private certificate in mutual authentication mode (when the SSL_CLIENT_AUTHENTICATION property is set to 1), the location of the client certificate and private key file need to be specified, as the server performs client authentication.

Enable the -ssl_verify option and specify the location of the CA certificate file in which the server public key is incorporated, to verify the certificate received from the server.

$ export ISQL_CONNECTION=SSL
$ isql -s localhost -u sys -p MANAGER \
-ssl_cert ~/cert/client-cert.pem \
-ssl_key ~/cert/client-key.pem

or

$ isql -s localhost -u sys -p MANAGER \
-ssl_verify -ssl_ca ~/cert/ca-cert.pem \
-ssl_cert ~/cert/client-cert.pem \
-ssl_key ~/cert/client-key.pem

Disconnecting from a Database

DISCONNECT is used to terminate the current session and disconnect from the server. The result of all subsequently executed SQL statements will be a "Not connected" message, and "CONNECT userID/password" must be executed in order to connect to the server again.

DISCONNECT;

iSQL> INSERT INTO systbl VALUES(1, 'A1');
1 row inserted.
iSQL> INSERT INTO systbl VALUES(2, 'A2');
1 row inserted.
iSQL> SELECT * FROM systbl;
SYSTBL.I1   SYSTBL.I2  
--------------------------
1           A1     
2           A2     
2 rows selected.
iSQL> DISCONNECT;
Disconnect success.
iSQL> INSERT INTO systbl VALUES(3, 'A3');
[ERR-91020 : No Connection State]
iSQL> SELECT * FROM systbl;
[ERR-91020 : No Connection State]
iSQL> CONNECT sys/manager;
Connect success.

Executing iSQL with the NOLOG Option

The /NOLOG option allows the user to execute iSQL without connecting to the database. The server IP address and port number must be specified to use this option.

isql -s localhost -port 20300 /NOLOG

Once iSQL is running, enter the database user ID and password with the CONNECT command to connect to the database, and then execute a SQL statement.