SQL

Squish provides its own APIs for accessing SQL databases since the JavaScript specification does not include them. To see the SQL APIs in action, see the JavaScript examples in How to Access Databases from Squish Test Scripts.

SQL Object

The SQL object provides a means of connecting to a database, executing queries on the database, and traversing and inspecting results. This functionality might be useful for retrieving test data or for providing data to the AUT.

SQLConnection SQL.connect(informationObject)

This function tries to connect to a SQL database. If it succeeds a SQLConnection Object is returned—this can be used to execute SQL statements on the database. If the connection fails a catchable exception is thrown. The information required to establish the connection must be passed as an object whose properties are then interpreted.

Here is an example invocation showing how to connect to a MySQL server on a host called "dulsberg", with the given username and password:

var conn = SQL.connect( { Driver: "MySQL",
                          Host: "dulsberg",
                          Port: 1342,
                          Database: "mydatabase",
                          UserName: "test",
                          Password: "secretPhrase" } );

The object's attributes have the following meanings:

• Driver: The driver is used to specify what kind of database we are connecting to. Possible values are:
  • DB2^*: IBM DB2, v7.1 and higher
  • IBase^*: Borland Interbase Driver
  • MySQL: MySQL Driver
  • ODBC: ODBC Driver (includes Microsoft SQL Server)
  • Oracle^*: Oracle Call Interface Driver
  • PostgreSQL: PostgreSQL v8.x Driver
  • SQLite: SQLite 3 Driver
  • Sybase^*: Sybase Adaptive Server

  Note: The drivers marked with an asterisk (^*) are not supported in Squish binary packages. This is usually because the database vendor's license does not permit redistributing their client libraries without owning a license for their product. The solution is to either try the ODBC driver, or to build Squish yourself against a Qt library which includes support for your particular SQL database.

• Host: This is used to specify the host name (or the IP address) of the computer on which the SQL database is installed.
• Port: This is used to specify the port on the remote computer to which the connection should be established. If this is omitted, the default port for the specified driver is used.
• Database: This is used to specify the name of the database to which the connection should be made.
• DataSource: This is used to specify the Data Source Name (DSN) to use. Note that specifying this attribute is only necessary when using the ODBC driver.
• UserName: This is used to specify the user name to use when logging into the database.
• Password: This is used to specify the password to use when logging into the database. If omitted, an empty password is assumed.

SQLConnection Object

SQLConnection objects are returned by the SQLConnection SQL.connect(informationObject) function described above; the object provides the methods listed below.

sqlConnection.close()

This method closes the SQL connection.

Number sqlConnection.execute(sql)

This method executes the given sql statement (such as DELETE, INSERT, or UPDATE) on the SQLConnection object. If the statement succeeds the number of rows affected is returned; or -1 if the number cannot be determined. If an error occurs, a catchable exception is thrown.

If the result returned by the statement needs to be read (e.g. for CALL) use SQLResult sqlConnection.query(sql).

SQLResult sqlConnection.query(sql)

This method executes the given sql statement (e.g., a SELECT statement) on the SQLConnection object. If the statement succeeds a SQLResult Object is returned. If an error occurs, a catchable exception is thrown.

Here is an example that shows how to execute a SQL query on a connection object:

var result = connection.query("SELECT last_name, first_name " +
        "FROM people WHERE country LIKE 'A%';");

SQLResult Object

SQLResult objects are returned by the Number sqlConnection.execute(sql) and SQLResult sqlConnection.query(sql) methods. A SQLResult object provides the functions and properties listed below. Note that in the case of SELECT statements, the SQLResult object is automatically set to the first row (if any were retrieved).

Boolean sqlResult.isValid

This property is true if the SQLResult object is in a valid state; otherwise it is false. Some functions return invalid result objects to indicate errors or other special conditions (see below).

int sqlResult.size

This property holds the number of rows affected by the query (for example, the number of rows deleted, inserted, or updated)—or -1 if the number could not be determined. And for SELECT queries this property holds the number of rows which this result contains.

sqlResult.toFirst()

This function navigates to the first row in this result. This is useful if you want to iterate over the rows multiple times: after processing the rows using the sqlResult.toNext() function, call this function to return to the first row so that you can use the sqlResult.toNext() function once again.

sqlResult.toNext()

This function navigates to the next row in this SQL result. If there is no next row (because there are no rows at all or because we have already navigated to the last row), this SQLResult object is marked as invalid. This function and the isValid property make it easy to iterate over the results of a SELECT query. For example:

var result = connection.query("SELECT last_name, first_name " +
        "FROM people WHERE country LIKE 'A%';");
// result is automatically set the first row
while (result.isValid) {
    // do something with the result
    result.toNext();
}

String sqlResult.value(fieldNumber|fieldName)

This function can be used to get the data in the given column (field) for the current row (record). The column can be identified by its position (counting from zero), or by using the (case-insensitive) field name. The data in the given column is implicitly converted into a string. Note that you can also use the bracket-syntax (shown in the example below) as a shortcut.

var result = connection.query("SELECT first_name, last_name " +
        "FROM people WHERE country LIKE 'A%';");
// result is automatically set the first row
while (result.isValid) {
    var name1 = result.value(0) + " " + result.value(1);
    var name2 = result.value("first_name") + " " + result.value("last_name");
    var name3 = result["first_name"] + " " + result["last_name"];
    test.verify(name1 == name2 && name2 == name3);
    result.toNext();
}

This example shows three different ways of indexing fields, all of which are equivalent.