Scripting the splice> Command Line Interface

You can use two simple and different methods to script the splice> command line interpreter; both of described here:

Running a File of splice> Commands

You can create a simple text file of command lines and use the splice> run command to run the commands in that file. Follow these steps:

  1. Create a file of SQL commands:

    First, create a file that contains any SQL commands you want to run against your Splice Machine database.

    For this example, we’ll create a file named mySQLScript.sql that connects to a database, creates a table, inserts records into that table, and then displays the records in the table.

    connect 'jdbc:splice://localhost:1527/splicedb;user=YourUserId;password=YourPassword';
    
    create table players (
     ID SMALLINT NOT NULL PRIMARY KEY,
     Team VARCHAR(64) NOT NULL,
     Name VARCHAR(64) NOT NULL,
     Position CHAR(2),
     DisplayName VARCHAR(24),
     BirthDate DATE );
    
    INSERT INTO Players
       VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
              (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
              (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
    
    SELECT * FROM Players;
    
  2. Start splice>

    If you’ve not yet done so, start Splice Machine and the splice> command line interface. If you don’t know how to do so, please see our Introduction to the splice> Command Line Interface.

  3. Run the SQL Script

    Now, in splice>, run your script with the  run command:

    run 'mySQLScript.sql';
    

    You’ll notice that splice> displays exactly the same results as you would see if you typed each command line into the interface:

    splice> connect 'jdbc:splice://localhost:1527/splicedb;user=YourUserId;password=YourPassword';
    splice> create table players (
     ID SMALLINT NOT NULL PRIMARY KEY,
     Team VARCHAR(64) NOT NULL,
     Name VARCHAR(64) NOT NULL,
     Position CHAR(2),
     DisplayName VARCHAR(24),
     BirthDate DATE );
    0 rows inserted/updated/deleted
    splice> INSERT INTO Players
       VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
              (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
              (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
    3 rows inserted/updated/deleted
    splice> SELECT * FROM Players;
    ID    |TEAM      |NAME                   |POS&|DISPLAYNAME             |BIRTHDATE
    ----------------------------------------------------------------------------------
    27    |Cards     |Earl Hastings          |OF  |Speedy Earl             |1982-04-22
    73    |Giants    |Lester Johns           |P   |Big John                |1984-06-09
    99    |Giants    |Joe Bojangles          |C   |Little Joey             |1991-07-11
    
    3 rows selected
    splice>
    

Running Splice Machine From a Shell Script

You can also use a shell script to start the splice> command line interpreter and run command lines with Unix heredoc (<<) input redirection. For example, we can easily rework the SQL script we used in the previous section into a shell script that starts splice>, runs several commands/statements, and then exits splice>.

  1. Create a shell script

    For this example, we’ll create a file named myShellScript.sql that uses the same commands as we did in the previous example:

    #!/bin/bashecho "Running splice> commands from a shell script"./bin/sqlshell.sh << EOFconnect 'jdbc:splice://localhost:1527/splicedb;user=YourUserId;password=YourPassword';
    
    create table players (
     ID SMALLINT NOT NULL PRIMARY KEY,
     Team VARCHAR(64) NOT NULL,
     Name VARCHAR(64) NOT NULL,
     Position CHAR(2),
     DisplayName VARCHAR(24),
     BirthDate DATE );
    
    INSERT INTO Players
       VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
              (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
              (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
    
    SELECT * FROM Players;exit;EOF
    

    If you’re not familiar with this kind of input redirection: the << specifies that an interactive program (./bin/sqlshell.sh) will receive its input from the lines in the file until it encounters EOF. The program responds exactly as it would had a user directly typed in those commands.

  2. Make your script executable

    Be sure to update permissions on your script file to allow it to run:

    chmod +x myShellScript.sh
    
  3. Run the script

    In your terminal window, invoke the script:

    ./myShellScript.sh
    

    You’ll notice that splice> starts and runs exactly as it did in the SQL script example above, then exits.

    Running Splice Machine Commands from a Shell Script...
    
     ========= rlwrap detected and enabled.  Use up and down arrow keys to scroll through command line history. ========
    
    Running Splice Machine SQL shell
    For help: "splice> help;"splice> connect 'jdbc:splice://srv55:1527/splicedb;user=YourUserId;password=YourPassword';
    splice> create table players (
     ID SMALLINT NOT NULL PRIMARY KEY,
     Team VARCHAR(64) NOT NULL,
     Name VARCHAR(64) NOT NULL,
     Position CHAR(2),
     DisplayName VARCHAR(24),
     BirthDate DATE );
    0 rows inserted/updated/deleted
    splice> INSERT INTO Players
       VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
              (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
              (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
    3 rows inserted/updated/deleted
    splice> SELECT * FROM Players;
    ID    |TEAM      |NAME                   |POS&|DISPLAYNAME             |BIRTHDATE
    ----------------------------------------------------------------------------------
    27    |Cards     |Earl Hastings          |OF  |Speedy Earl             |1982-04-22
    73    |Giants    |Lester Johns           |P   |Big John                |1984-06-09
    99    |Giants    |Joe Bojangles          |C   |Little Joey             |1991-07-11
    
    3 rows selected
    

Using nohup for Long-Running Scripts

If you want to run an unattended shell script that may take a long time, you can: use the Unix nohup utility, which allows you to start a script in the background and redirect its output. This means that you can start the script, log out, and view the output at a later time. For example:

nohup ./myShellScript.sh > ./myShellScript.out 2>&1 &

Once you’ve issued this command, you can log out, and subsequently view the output of your script in the myShellScript.out file.