# Step 2: Executing commands and scripts

> # Executing basic xSQL commands and scripts

> As mentioned in Step 1, xSQL can be started in server or client mode. For the next group of exercises we will start xSQL by simply typing `xsql` from directory `target\bin`:

>         xsql

>         Simple interactive configuration is started
>         To run as a server, type, for example: 'xsql -size 100m -p 5000'
>         Then to connect as a client, use 'xsql @127.0.0.1:5000'
>         Type 'xsql -h' for more details

>         xsql started
>         Runtime configuration
>          Transaction manager  : MURSIW
>          Storage (transient)  : Conventional memory, 100M
>          Storage (persistent) : Not supported
>          Runtime              : Release
>         XSQL>

> ## Executing SQL statements

> To execute SQL statements interactively simply type the statement followed by a semicolon. For example, the following select statement queries the system table `Metatable` to show that there are as yet no tables in the database:

>         XSQL>select TableName from Metatable;
>         TableName
>         ------------------------------------------------------------------------------

>         Selected records: 0
>         XSQL>

> So to create and initialize a table we might enter statements like the following:

>         XSQL>create table t(id int);
>         XSQL>insert into t values( [1,2,3] );
>         XSQL>select * from t;
>         id
>         ------------------------------------------------------------------------------
>         1
>         2
>         3

>         Selected records: 3
>         XSQL>

> ## Executing script files

> But rather than type SQL statements interactively, it is often more convenient to read SQL script files into xSQL to preform SQL operations. To facilitate this exercise, several scripts and additional files are provided in the directory `\samples\xsql\scripts`.

> For example, the script in file `compound.sql` creates the simple table `A` and index `idx`, populates the table and performs a compound query and then executes the explain command to display the execution plan:

>         create table A (a int, b int, c int, d int);
>         create index idx on A (a, b, c);
>         insert into A values (1,2,3,4);
>         select * from A a1, A a2 where a1.a=a2.a and a1.c=a2.c;
>         explain select * from A a1, A a2 where a1.a=a2.a and a1.c=a2.c;

> The script can be executed in "batch" mode by starting xSQL with the following command:

>         xsql -size 100m -b -f ..\..\samples\xsql\scripts\compound.sql

> xSQL displays the following lines of output showing the results of the `select` statement and the `explain` command:

>         a       b       c       d       a       b       c       d
>         ------------------------------------------------------------------------------
>         1       2       3       4       1       2       3       4

>         Selected records: 1
>         Plan
>         ------------------------------------------------------------------------------
>         INDEX-JOIN[3] with A using index A.idx(a, b, c) WHERE (Eq A.c A.c)
>         .SEQ-SCAN[1] of table A

>         Selected records: 1

> Note that xSQL terminates after executing the scripts in "batch" mode. To use xSQL interactively we use the `-i` command line option. For example we could run the script and enter interactive mode with the following command:

>         xsql -size 100m -i -f ..\..\samples\xsql\scripts\compound.sql

> Now we can type an SQL `select` statement like the following to display the contents of table `A`:

>         XSQL>select * from A;
>         a       b       c       d
>         ------------------------------------------------------------------------------
>         1       2       3       4

>         Selected records: 1

> ## The script command

> Script files can also be run from interactive mode using the `script` command. For example, the following command runs the script `as.sql`:

>         XSQL>script ..\..\samples\xsql\scripts\as.sql
>         bond.bond
>         ------------------------------------------------------------------------------
>         James Bond

>         Selected records: 1

> The file `as.sql` looks like this:

>         create table bond (bond string);
>         insert into bond values ('James Bond');
>         select "bond" as "bond.bond" from bond;

> The `script` command can also be used to run one script from another script file. For example suppose we have the following two script files:

> `inner.sql`:

>         XSQL>select TableName, FieldName, FieldTypeName  from Metatable;

> and `outer.sql`:

>         select 1;
>         script inner.sql
>         select 2;

> Now we could execute the `script` command in interactive mode to produce the following output:

>         XSQL>script outer.sql
>         #1
>         ------------------------------------------------------------------------------
>         1

>         Selected records: 1
>         TableName       FieldName       FieldTypeName
>         ------------------------------------------------------------------------------
>         A       a       Int4
>         A       b       Int4
>         A       c       Int4
>         A       d       Int4
>         bond    bond    String

>         Selected records: 5

> Or the script `outer.sql` could be run from the xSQL command line to produce the same result and remain in interactive mode:

>         xsql -f ..\..\samples\xsql\scripts\outer.sql

> ## Sample scripts directories

> A number of sample SQL scripts and command files (shell scripts) are provided in the directory:

>         eXtremeDB/samples/xsql/scripts

> These sample scripts demonstrate many specific uses of standard SQL and extended eXtremeSQL features.

> Because xSQL automatically loads dynamic libraries located in `eXtremeDB/target/bin.so` it is recommended to run xSQL from the `eXtremeDB/target/bin` directory. (Alternatively the `LD_LIBRARY_PATH` environment variable can be exported (set) to indicate the directory from which to load dynamic libraries.) But, to simplify launching xSQL with these sample script files the Windows command file `testxsql.bat` (`testxsql.sh` in Unix-Linux packages) is provided. It eliminates the necessity to type a long relative directory path and invokes the specified script with the following lines:

>         @echo off
>         ..\..\..\target\bin\xsql.exe -c xsql.cfg -f %1.sql

> For example, using this command file from directory `eXtremeDB/samples/xsql/scripts` (in a Windows console window), the following is a minimal command to run xSQL as a server and execute script `array.sql`:

>         xsqltest array

> or from a Linux console window:

>         ./xsqltest.sh array

> Running this command, xSQL comes up in server mode and displays the following output:

>         a       v
>         ------------------------------------------------------------------------------
>         [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]  [one, two, three]
>         [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] [abc, xyz]
>         [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] []

>         Selected records: 3
>         press Enter to stop SQL server

> We recommend experimenting with several of the samples provided.

> It is often convenient to display output from select statements in a different format. The next step in this tutorial will demonstrate different display options.