SQL

Description

SQL expression allows one to express the processing of one or more SQL statements.

For connection setup, visit Database Connection Setup for more detail.

Execution Result of Queries

The resultName parameter used in the operations below refers to the Execution Result of the SQL queries. It is the variable associated with the query result that we can assign using the syntax --nexial:resultName as shown in the examples below. In cases where there is no variable specified corresponding to the query, Nexial, by default uses 0-based index to refer to execution outcome of that particular query.

For Example

Our student table looks like this

We write multiple queries into a single variable and assign only one of them a variable.

As stated before, where there is no variable assigned to the query, nexial by default used a 0-based index. We use the index 0 to address the outcome of this query.

Similarly, we use the index 1 for the second query

We assigned a variable to the third query, so we’ll use that. Alternatively, we can use the index 2.

Using index 3 for the last query

The resulting student table looks like this after running the queries.

If the query contains errors

Normally, the execution outcome of a query would contain various pieces of information as shown in the pictures below.

However, in case of inaccurate or erroneous queries.

The execution outcome will return an error upon running the execute and running other operations on the outcome will return either 0 or null.

Output:

However, some meaningful information can be extracted the execution outcome of an erroneous query. For example

Output:

Referencing Queries in External File

Nexial built-in functions, data variables and expressions can also be used in the SQL queries. For Example:

select * from ${table}; where table is:

SQL queries can either be written directly into the script or can be stored in a file and run. However, writing queries in a file has the following advantages:

Ease of readability compared to queries written directly into the script
Better flexibility in terms of modification since the queries are separate from the script.
Externalized queries provide for ease of maintenance and reusabilty.
Queries written in a file also can make use of built-in functions, data variables and expressions.

As a best practice, the file(s) containing the queries should be stored under the data or the script directory as it helps with portablity.

Operations

`cell(resultName,row,column)`

Return the value of a specific cell (row + column) from an execution result named resultName. Must be invoked only after execute().

The resultName can either be the name given to the corresponding result or its index (the nth resultset).
The row parameter refers to the row index of the target resultset. Note that row is zero-based.
The column parameter refers to the column name of the target resultset.

As for result:

Note that if row or column is not valid, Nexial will throw an exception.
If the resolved value is a number, then a NUMBER expression will be returned.
If the resolved value is a binary or byte array in nature, then a BINARY expression will be returned.
All else, a TEXT expression will be returned.

`cells(resultName,column)`

Return a list of data matching column.

Example

Existing Database:

Script: Fetching the entries in the first_name column

Output:

`columnCount(resultName)`

Returns the number of columns for a given execution result named resultName. Must be invoked after execute().

Example

Database:

Script:

Output:

`columns(resultName)`

Returns the column names of an execution result named resultName. Must be invoked after execute().

Example

Database:

Script:

Output:

`csv(resultName)`

Transform the content of execution result named resultName to CSV. Must be invoked after execute().

Example

Database:

Script:

Output:

`elapsedTime(resultName)`

Returns amount of milliseconds to execute and retrieve an execution result named resultName. Must be invoked after execute().

Example

Database:

Queries:
insert2.sql

update.sql

Script:

Output:

Resulting Database:

`error(resultName)`

Returns any error, as TEXT, incurred during the execution and retrieval of an execution result named resultName. Must be invoked after execute().

Example

Script:

Output:

`execute(db)`

Execute the associated SQL statement(s) using db as the database connection. Most of the operations mentioned below must be preceded by execute(db) as a requirement. `

Example

Database:

Script: Inserting new tuples into the table

Output: Insertion successful

Output: Insertion failed. Nexial prints the query as the output.

`resultCount`

Find out the number of execution results rendered through the most recent execute() operation. Must be invoked after execute().

Example

Database:

Script: Running a single select query.

Output:

SQL File: operation.sql

Script: Running multiple queries

Output:

Resulting Database

`rolledBack(resultName)`

Returns true/false value if the execution and retrieval of an execution result named resultName has caused a roll back event. Must be invoked after execute().

Example

Database:

SQL file: insertRollback.sql

Script:

Output:

Resulting Database:

`row(resultName,rowIndex)`

Transform one row (denoted by rowIndex) from the specific result (denoted by resultName) as a LIST expression. Must be invoked after execute().

Note that any invalid row reference (less than zero or greater than available row) will result in an empty LIST.,