Examples with NuoDB SQL

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.

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>