Sahi Pro - Database APIs

Databases can be accessed via 2 APIs.

_getDB

Since Sahi Pro: 3.5
Since Sahi OS: 2

_getDB($driver, $jdbcurl, $username, $password)

Arguments
$driverstring JDBC driver class (fully qualified name)
$jdbcurlstring JDBC URL
$usernamestring username to connect to the database
$passwordstring password to connect to the database

Details

Returns a sahiDB object.
var $db = _getDB("oracle.jdbc.driver.OracleDriver",
          "jdbc:oracle:thin:@dbserver:1521:sid",
          "username", "password");
warningThe driver class has to be present in the classpath of Sahi.
More info: Adding jars to Sahi's classpath

_getDB

Since Sahi Pro: 3.5
Since Sahi OS: 2

_getDB($driver, $jdbcurl, $propertiesObj)

Arguments
$driverstring JDBC driver class (fully qualified name)
$jdbcurlstring JDBC URL
$propertiesObjProperties object A java.util.Properties object with relevant key values

Details

Returns a sahiDB object.
var $props = new java.util.Properties();
$props.put("user", "scott");
$props.put("password", "tiger");
$props.put("internal_logon", "sysoper");
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $props);
warningThe driver class has to be present in the classpath of Sahi.
More info: Adding jars to Sahi's classpath

Using the SahiDB object

The SahiDB object has 3 APIs:

SahiDB.select

Since Sahi Pro: 3.5
Since Sahi OS: 2

SahiDB.select($sql)

Arguments
$sqlstring sql query to execute

Details

Returns an array of rows. Each column in an array can be accessed by index or column name.

Example

Given a database table "User" like
IdNameAge
1Ram18
2Amir20

var $db = _getDB("..."); // look at _getDB for syntax

var $rs = $db.select("select Name, Age from User");

var $row = $rs[1]; // second row

// access column using index
var $userName = $row[0];

// or access column using name
var $userName = $row["Name"];

// You can also access directly from $rs without using row
var $userName = $rs[1]["Name"];
infoNote the possibility of using index ($rs[0][1]) as well as name for column ($rs[0]["Age"]).

SahiDB.selectWithHeader

Since Sahi Pro: 3.5
Since Sahi OS: 2

SahiDB.selectWithHeader($sql)

Arguments
$sqlstring sql query to execute

Details

Returns data along with column names as the first row.
Helps in knowing column names when select query has been made with select * ...

var $db = _getDB("..."); // look at _getDB for syntax

var $rs = $db.selectWithHeader("select * from User where 1=0");
var $headers = $rs[0];// returns ["Name","Age"]
var columnName0 = $headers[0]; // "Name"
var columnName1 = $headers[1]; // "Age"

SahiDB.update

Since Sahi Pro: 3.5
Since Sahi OS: 2

SahiDB.update($sql)

Arguments
$sqlstring sql insert/update/delete query to execute

Details

Executes a non-select query. Used for insert/update/delete queries.

$db.update("delete from User where id=2");

Accessing databases directly

Sahi's _getDB.select automatically converts the result set into a 2D array.
This can be expensive if the data fetched is a lot. If for some reason you wish to fetch a lot of data,
directly call Java's database access APIs instead of using Sahi's wrappers.

Below is sample code for accessing a mysql database.

var $props = new java.util.Properties();
$props.put("user", "user");
$props.put("password", "password");
function getRawDB(driverName, jdbcurl, props, sql) {
    try {
        java.lang.Class.forName(driverName);
        var connection = java.sql.DriverManager.getConnection(jdbcurl, props);
        var stmt = connection.createStatement();
        var rs = stmt.executeQuery(sql);
        return [rs, stmt, connection];
    } catch ($e) {
      _debug($e);
    } finally {
    }
}
var $sql = "select id, name, age from user order by age asc limit 10";
var $rsObj = getRawDB("com.mysql.jdbc.Driver",
                        "jdbc:mysql://localhost/sahitest?user=user&password=password",
                        $props, $sql);
var $resultSet = $rsObj[0];
var $statement = $rsObj[1];
var $connection = $rsObj[2];
try {
  while ($resultSet.next()) {
    var $name = $resultSet.getString("name");
    var $age = $resultSet.getString("age");
    _log($name + " " + $age);
  }
} catch (e) {
} finally {
  $statement.close();
  $connection.close();
}