Sahi Documentation

Database APIs

Databases can be accessed via 3 APIs.

_getDB

Since: Sahi ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.527.0.17.0.07.5.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

_getDB($driver, $jdbcurl, $username, $password[, $isPasswordEncrypted])

Arguments
$driverstring JDBC driver class (fully qualified name)
$jdbcurlstring JDBC URL
$usernamestring username to connect to the database
$passwordstring if $isPasswordEncrypted is true, use encrpted password to connect to the database
$isPasswordEncryptedboolean optional to use encrpted password to connect to the database (default value is false)

Returns
objectsahiDB object

Details

    infoNOTE: Argument $isPasswordEncrypted was added Since Sahi Pro: 8.0.0. For old document Refer here
//password is not encrypted (secret is the password)
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", "username", "secret");

//encrypted value for $password parameter. Here 'MgkKEQBU' is the encrypted value for 'secret'.
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", "username", "MgkKEQBU", true);

var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", "username", "secret", false);
warningThe driver class has to be present in the classpath of Sahi. More info: Adding jars to Sahi's classpath
infoMore info about Password Encoder
infoNOTE: $isPasswordEncrypted is added since Sahi Pro: 8.0.0, Desktop Add-On : 8.0.0 and Mobile Add-On : 8.0.0


_getDB

Since: Sahi ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.527.0.17.0.07.5.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

_getDB($driver, $jdbcurl, $propertiesObj[, $isPasswordEncrypted])

Arguments
$driverstring JDBC driver class (fully qualified name)
$jdbcurlstring JDBC URL
$propertiesObjProperties object A java.util.Properties object with relevant key values
$isPasswordEncryptedboolean optional to use encrpted password to connect to the database (default value is false)

Returns
objectsahiDB object

Details

    infoNOTE: Argument $isPasswordEncrypted was added Since Sahi Pro: 8.0.0. For old document Refer here
//password is not encrypted (secret is the password)
var $props = new java.util.Properties();
$props.put("user", "scott");
$props.put("password", "secret");
$props.put("internal_logon", "sysoper");
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $props);

//encrypted value for $password parameter. Here 'MgkKEQBU' is the encrypted value for 'secret'.
var $props = new java.util.Properties();
$props.put("user", "scott");
$props.put("password", "MgkKEQBU");
$props.put("internal_logon", "sysoper");
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $props, true);

var $props = new java.util.Properties();
$props.put("user", "scott");
$props.put("password", "secret");
$props.put("internal_logon", "sysoper");
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $props, false);
warningThe driver class has to be present in the classpath of Sahi. More info: Adding jars to Sahi's classpath
infoMore info for Password Encoder
infoNOTE: $isPasswordEncrypted is added since Sahi Pro: 8.0.0, Desktop Add-On : 8.0.0 and Mobile Add-On : 8.0.0


_getDB

Since: Sahi ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
8.0.0NANA8.0.08.0.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

_getDB($driver, $jdbcurl, $jsonObj[, $isPasswordEncrypted])

Arguments
$driverstring JDBC driver class (fully qualified name)
$jdbcurlstring JDBC URL
$jsonObjJson object A json object with relevant key values
$isPasswordEncryptedboolean optional to use encrpted password to connect to the database (default value is false)

Returns
objectsahiDB object

Details



//password is not encrypted (secret is the password)
var $json = { "user": "scott", "password": "secret", "internal_logon": "sysoper" };
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $json);

//encrypted value for $password parameter. Here 'MgkKEQBU' is the encrypted value for 'secret'.
var $json = { "user": "scott", "password": "MgkKEQBU", "internal_logon": "sysoper" };
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $json, true);

var $json = { "user": "scott", "password": "secret", "internal_logon": "sysoper" };
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $json, false);


//password is not encrypted (secret is the password)
var $json = {};
$json["user"] = "scott";
$json["password"] = "secret";
$json["internal_logon"] = "sysoper";
var $db = _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@dbserver:1521:sid", $json);
warningThe driver class has to be present in the classpath of Sahi. More info: Adding jars to Sahi's classpath
infoMore info for Password Encoder


Using the SahiDB object

The SahiDB object has 3 APIs:

SahiDB.select

Since: Sahi ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.527.0.17.0.07.5.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

SahiDB.select($sql)

Arguments
$sqlstring sql query to execute

Returns
two dimensional array of strings

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 ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.527.0.17.0.07.5.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

SahiDB.selectWithHeader($sql)

Arguments
$sqlstring sql query to execute

Returns
two dimensional array of strings

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 ProSahi OSSahi Pro StarterDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.527.0.17.0.07.5.09.0.0NA

Available for modes: Browser | Windows | Java | Android | iOS | SAP | AI Assist

SahiDB.update($sql)

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

Returns
null

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();
}