Examples with NuoDB SQL

Example 1

Use a config file that contains options that can also be passed on the nuosql command line.

Using a file named /mypath/my_nuosql.config as an example, the following would be entries in the /mypath/my_nuosql.config file:

user dba
password goalie
schema hockey

Run the nuosql command, passing the name of the config file to connect to the test database on localhost. The config file tells the nuosql command to connect with user_name dba, password goalie, and use schema hockey as the default schema.

$ nuosql test --config /mypath/my_nuosql.config

Example 2

Use a file that contains SQL statements to be executed.

Using a file named /my_path/my_batch_file.sql as an example, the following would be entries in the my_batch_file.sql file:

USE hockey
CREATE TABLE hockey_temp (id INTEGER, name STRING);
INSERT INTO hockey_temp SELECT id, name FROM hockey WHERE position = 'Forward';
SELECT * FROM hockey_temp;

Run the nuosql command, passing the name of the file that contains the SQL statements to be executed. Once executed, nuosql will exit. Any errors are ignored and processing continues with the next SQL statement.

The commands will display to the screen, but can be redirected to a file.

$ nuosql test@localhost --user dba --password goalie --file /my_path/my_batch_file.sql
USE hockey
DROP TABLE IF EXISTS hockey_temp;
CREATE TABLE hockey_temp (id INTEGER, name STRING);
INSERT INTO hockey_temp SELECT id, name FROM hockey WHERE position = 'Goalie';
SELECT * FROM hockey_temp;
 ID      NAME
 --- --------------
 22  ANTON KHUDOBIN
 23  TUUKKA RASK

Example 3

Use the --connection-property option to set the TimeZone JDBC property. This property will be in effect for the duration of the nuosql session.

$ nuosql test --user dba --password goalie --connection-property TimeZone=America/New_York

Example 4

Use a nuosql initialize file that contains options to run when nuosql starts up.

Using a file named /mypath/my_nuosql.init as an example, the following would be entries in the /mypath/my_nuosql.init file:

SET OUTPUT VERTICAL
USE SCHEMA1

Run the nuosql command, passing the name of the init file. The init file tells the nuosql command to display output vertically and to USE SCHEMA schema1 as the default schema.

$ nuosql test --user dba --password goalie --init /mypath/my_nuosql.init

SQL> CREATE TABLE testtab(column1 INTEGER, column2 STRING);
SQL> SHOW TABLES;

Tables in schema SCHEMA1
   TESTTAB

SQL> INSERT INTO testtab VALUES(1,'string1'),(2,'string2'),(3,'string3');
SQL> SELECT * FROM testtab;
==================================== Row #1 ====================================
COLUMN1: 1
COLUMN2: string1
==================================== Row #2 ====================================
COLUMN1: 2
COLUMN2: string2
==================================== Row #3 ====================================
COLUMN1: 3
COLUMN2: string3

Example 5

Set --timer to full. This will cause timing statistics to be reported for both the nuosql client and the Transaction Engine (TE).

$ nuosql test --user dba --password goalie --timer full
SQL> select * from hockey.scoring limit 3000;

 PLAYERID  YEAR  STINT  TEAMID  POSITION  GAMESPLAYED  GOALS  ASSISTS  PENALTYMINUTES
 --------- ----- ------ ------- --------- ------------ ------ -------- ---------------

 aaltoan01 1997    1      ANA      C            3         0       0            0
 aaltoan01 1998    1      ANA      C           73         3       5           24
...
 bladoto01 1972    1      PHI      D           78        11      31           26
 bladoto01 1973    1      PHI      D           70        12      22           37

Elapsed time 36ms
Server execution time 7ms
SQL>

Example 6

Use --direct in conjunction with database@host:port to connect directly to a TE instead of via a connection provided by an Admin Process (AP). The port is the port number of the TE. It can be ascertained, for example, by running nuocmd show domain, using the nuosql command SELECT * FROM system.nodes;, or from AP log files.

$ nuocmd show domain
server version: 6.0-1-fc6a857de9, server license: Enterprise
server time: 2023-09-01T17:43:14.697, client token: 9b2af420d9a0ba4186770207e383755b655c61c3
Servers:
  [nuoadmin-0] localhost:48005 [last_ack = 2.58] [member = ADDED] [raft_state = ACTIVE] (LEADER, Leader=nuoadmin-0, log=5/258/258) Connected *
Databases:
  hockey [state = RUNNING]
    [SM] parallels-Parallels-Virtual-Platform/localhost:48006 [start_id = 6] [server_id = nuoadmin-0] [pid = 21829] [node_id = 1] [last_ack =  4.25] MONITORED:RUNNING
    [TE] parallels-Parallels-Virtual-Platform/localhost:48007 [start_id = 7] [server_id = nuoadmin-0] [pid = 23722] [node_id = 2] [last_ack =  4.25] MONITORED:RUNNING
$ nuosql hockey@localhost:48007 --schema hockey --user dba --password goalie --direct
SQL>