SQL Built-in

SQL Built-ins provide functionality to access SQL configured databases, execute queries and retrieve results.

Important! Functions that open an SQL Connection with the newConnection method should call the closeConnection within the same function.

Configuration

Syntax

Sql {
     <name Identifier> {
             className       = <String>
             jarUrl          = <String>
             connectionUrl   = <String>
             username        = <String>
             password        = <String>
     }
     ...
}

Parameters

• name - The name assigned to the SQL database

• className - The Java driver's class name

• jarUrl - Points to the SQL driver library (a .jar file)

  • It must start with jar:

• connectionUrl - The SQL database URL

• username and password (Optional) - These are used to access a protected database

Example

Sql {
 test {
    jarUrl          = "jar:file:/Users/QiTASC/Downloads/db-derby-10.11.1.1-bin/lib/derby.jar!/"
    className       = "org.apache.derby.jdbc.EmbeddedDriver"
    connectionUrl   = "jdbc:derby:/Users/QiTASC/database.db"
    username        = "user1"
    password        = "user1password"
 }
}

Open an SQL Connection

The newConnection function must be called to establish a connection to a preconfigured database. Following this, SQL commands can be sent. However, if the database is not accessible or there are configuration errors, an exception will be thrown.

Syntax

<connection SqlConnection> := Sql.newConnection(<configurationName String>)

Returns
The SQL connection.

Parameter

• configurationName - The SQL database's name, as defined in the configuration

Example

SqlConnection := Sql.newConnection("testdb")

Close an SQL Connection

When finished issuing commands, closeConnection closes the SQL connection and frees allocated resources.

Syntax

Sql.closeConnection(<configurationName String>)

Parameter

• configurationName - The SQL database's name, as defined in the configuration

Example

SqlConnection := Sql.newConnection("testdb")
...
//SQL commands
...
Sql.closeConnection("testdb")

SQL Commands

After opening an SQL Connection, find and update functions can be used to run queries, update values and retrieve results.

Find All

Searches for the query and returns all results in the form of an SqlResult, which can then be validated.

Syntax

<result SqlResult> := <connection SqlConnection>.findAll(<query String>)

Returns
All gathered results.

Parameters

• connection - The connection object that was returned by the newConnection function

• query - The SQL query

Example

SqlConnection := Sql.newConnection("testdb")
results := sqlConnection.findAll("SELECT * FROM mytable")
Sql.closeConnection("testdb")
assert results != null and results.isValid()
end

Find Unique

Syntax

<result SqlResult> := <connection SqlConnection>.findUnique(<query String>)

Returns
The unique result. The step will fail if there were either no results or more than one.

Parameters

• connection - The connection object that was returned by the newConnection function

• query - The SQL query

Example

sqlConnection := Sql.newConnection("myDatabase")
uniqueResult := sqlConnection.findUnique("SELECT * FROM APP.TABLETEST WHERE ID = 1")
Sql.closeConnection("myDatabase")

if (not uniqueResult.isValid()) then
    println("The SQL results are invalid")
    assert false
end
name := uniqueResult.getStringOrNull(0, "NAME")
if ((name = null) or (not name.equals("John"))) then
    println("The SQL check did not pass")
    assert false
end

SQL Result Methods

The following methods can be called on an SQL result to validate the specified column's data type and retrieve their respective values. These methods must be prepended by the assigned variable name. When addressing columns and rows, the starting position is always 0.

Method	Description	Return Data Type
isValid()	Returns an indicator of whether the result was valid (true) or invalid (false).	Boolean
getRowsCount()	Returns the number of rows retrieved by the query.	Number
getColumnsCount	Returns the number of columns retrieved by the query.	Number
isColumnBoolean(<column NumberOrString>)	Checks if the column is of type boolean.	Boolean
isColumnDate(<column NumberOrString>)	Checks if the column is of type date.	Boolean
isColumnString(<column NumberOrString>)	Checks if the column is of type string.	Boolean
isColumnNumber(<column NumberOrString>)	Checks if the column is of type number.	Boolean
getBoolean(<row Number>, <column NumberOrString>)	Retrieves the boolean value of the cell specified by the row and column indices.	Boolean
getDateOrNull(<row Number>, <column NumberOrString>)	Retrieves the date value of the cell specified by the row and column indices.	Date
getStringOrNull(<row Number>, <column NumberOrString>)	Retrieves the string value of the cell specified by the row and column indices.	String
getNumberOrNull(<row Number>, <column NumberOrString>)	Retrieves the number value of the cell specified by the row and column indices.	Number
getObjectOrNull(<row Number>, <column NumberOrString>)	Retrieves the object value of the cell specified by the row and column indices.	Object

Example

sqlConnection := Sql.newConnection("myDatabase")
uniqueResult := sqlConnection.findUnique("SELECT * FROM APP.TABLETEST WHERE ID = 1")
Sql.closeConnection("myDatabase")

if (not uniqueResult.isValid()) then
    println("The SQL results are invalid")
    assert false
end
name := uniqueResult.getStringOrNull(0, "NAME")
if ((name = null) or (not name.equals("John"))) then
    println("The SQL check did not pass")
    assert false
end

Execute

Runs a query and updates the values.

Syntax

<results List<SqlExecutionResult>> := <connection SqlConnection>.execute(<query String>)

Returns
Returns a list of results from which further values such as the number of affected rows or generated keys can be retrieved.

Parameters

• query - The SQL query

• connection - The SQL connection

Example

connection := Sql.newConnection("myDatabaseConfig")

results := connection.execute("insert into user (name, age) values ('Jack', 10);")
for result in results
    println("")
    println("Generated Count: " + result.getGeneratedKeys().getRowsCount())
    println("Result    Count: " + result.getResultSet().getRowsCount())
    println("Update    Count: " + result.getUpdateCount())

    for i in range(0, result.getGeneratedKeys().getRowsCount() -1)
        println("Generated Key["+i+"]: " + result.getGeneratedKeys().getNumberOrNull(i, 0))
    end
end

Execute Update

Runs a query and updates or modifies values.

Syntax

<results Number> := <connection SqlConnection>.executeUpdate(<query String>)

Returns
The number of affected rows.

Parameters

• query - The SQL query

• connection - The SQL connection

Example

connection := Sql.newConnection("myDatabaseConfig")

updateResult := connection.executeUpdate("insert into user (name, age) values ('John', 1);")
println("Update Result: " + updateResult)

The example above will print 1 if the insert was successful.

SQL Update Methods

Get Generated Keys

Syntax

<generatedKeys SqlResult> := <executionResult SqlExecutionResult>.getGeneratedKeys()

Returns
The list of keys created by the SQL execution.

Parameter

• executionResult - The result of the SQL execution

Example

connection := Sql.newConnection("myDatabaseConfig")
updateResult := connection.executeUpdate("insert into user (name, age) values ('John', 1);")
myKeys := updateResult.getGeneratedKeys()

In the example above, one new row has been inserted into the database. getGeneratedKeys will return the key created by its insertion, which will be stored in the variable myKeys.

Get Result Set

getResultSet retrieves the current result as a ResultSet object.

Syntax

<resultSet SqlResult> := <executionResult SqlExecutionResult>.getResultSet()

Returns
The result set of the SQL execution.

Parameter

• executionResult - The result of the SQL execution

Example

println("Result Count: " + result.getResultSet().getRowsCount())

Get Update Count

Syntax

<updateCount Number> := <executionResult SqlExecutionResult>.getUpdateCount()

Returns
The number of rows affected by last write operation.

Parameter

• executionResult - The result of the SQL execution

Example

println("Update Count: " + result.getUpdateCount())

Get JDBC Result Set Metadata

The getColumnMetadata function returns a Java Database Connectivity (JDBC) metadata instance from a specified column. Additional methods described below can then be performed on the metadata instance.

Syntax

  <metadata SqlColumnMetadata> :=
    <sqlResult SqlResult>.getColumnMetadata(<column Number|String>)

Returns
The JDBC metadata instance.

Parameters

• sqlResult - The SQL result set

• column - The column to retrieve the JDBC metadata from

Example

sqlMetaData := sqlResult.getColumnMetadata(2)

Available SqlColumnMetadata Getters

The follow Getters may be used on the metadata instance returned by getColumnMetadata.

Is Auto Increment

Syntax

<isAutoIncrement Boolean> := <metadata SqlColumnMetadata>.isAutoIncrement()

Returns
true if the column is automatically numbered, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

autoIncrementColumn := columnSix.isAutoIncrement()

Is Case Sensitive

Syntax

<isCaseSensitive Boolean> := <metadata SqlColumnMetadata>.isCaseSensitive()

Returns
true if the column is case sensitive, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

caseSensitiveColumn := columnSix.isCaseSensitive()

Is Searchable

Syntax

<isSearchable Boolean> := <metadata SqlColumnMetadata>.isSearchable()

Returns
true if the column can be used in a where clause, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

searchableColumn := columnSix.isSearchable()

Is Currency

Syntax

<isCurrency Boolean> := <metadata SqlColumnMetadata>.isCurrency()

Returns
true if the column is a cash value, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

currencyColumn := columnSix.isCurrency()

Is Nullable

Syntax

<isNullable Boolean> := <metadata SqlColumnMetadata>.isNullable()

Returns
true if the column values are nullable, false otherwise. null if the column value nullability is unknown.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

nullableColumn := columnSix.isNullable()

Is Signed

Syntax

<isSigned Boolean> := <metadata SqlColumnMetadata>.isSigned()

Returns
true if the column values are signed numbers, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

signedColumn := columnSix.isSigned()

Is Read Only

Syntax

<isReadOnly Boolean> := <metadata SqlColumnMetadata>.isReadOnly()

Returns
true if the column is read only, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

readOnlyColumn := columnSix.isReadOnly()

Is Writable

Syntax

<isWritable Boolean> := <metadata SqlColumnMetadata>.isWritable()

Returns
true if the column is writable, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

writableColumn := columnSix.isWritable()

Is Definitely Writable

Syntax

<isDefinitelyWritable Boolean> := <metadata SqlColumnMetadata>.isDefinitelyWritable()

Returns
true if writing in the column will definitely succeed, false otherwise.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

definitelyWritableColumn := columnSix.isDefinitelyWritable()

Get Precision

Syntax

<precision Number> := <metadata SqlColumnMetadata>.getPrecision()

Returns
Get the designated column's specified column size:

• Numeric data - maximum precision.

• Character data - length in characters.

• Datetime datatypes - length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component).

• Binary data - length in bytes.

• ROWID datatype - length in bytes.

Note: 0 is returned for data types where the column size is not applicable.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnPrecision := columnSix.getPrecision()

Get Scale

Syntax

<scale Number> := <metadata SqlColumnMetadata>.getScale()

Returns
The column's scale, meaning the number of digits to right of the decimal point for that column.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnScale := columnSix.getScale()

Get SQL Type Name

Syntax

<sqlTypeName String> := <metadata SqlColumnMetadata>.getSqlTypeName()

Returns
The SQL type name used by the database. If the column type is a user-defined type, a fully-qualified type name is returned.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnSqlType := columnSix.getSqlTypeName()

Get Display Size

Syntax

<displaySize Number> := <metadata SqlColumnMetadata>.getDisplaySize()

Returns
The column's normal maximum width in characters.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnDisplaySize := columnSix.getDisplaySize()

Get Label

Syntax

 <label String> := <metadata SqlColumnMetadata>.getLabel()

Returns
The suggested title for use in printouts and displays.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnLabel := columnSix.getLabel()

Get Name

Syntax

<name String> := <metadata SqlColumnMetadata>.getName()

Returns
The column name.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnName := columnSix.getName()

Get Schema Name

Syntax

<schemaName String> := <metadata SqlColumnMetadata>.getSchemaName()

Returns
The column table's schema.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnSchemaName := columnSix.getSchemaName()

Get Table Name

Syntax

<tableName String> := <metadata SqlColumnMetadata>.getTableName()

Returns
The column table's name.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnTableName := columnSix.getTableName()

Get Catalog Name

Syntax

<catalogName String> := <metadata SqlColumnMetadata>.getCatalogName()

Returns
The column table's catalog name.

Parameter

• metadata - The metadata instance created by getColumnMetadata

Example

columnCatalogName := columnSix.getCatalogName()