Database APIs



Databases can be accessed via 3 APIs.

_getDB

Since: Sahi ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.57.0.07.5.09.0.0NA

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

_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

Sahi Pro Flowcharts Action :Get DB

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 ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.57.0.07.5.09.0.0NA

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

_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

Sahi Pro Flowcharts Action :Get DB

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 ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
8.0.08.0.08.0.09.0.0NA

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

_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

Sahi Pro Flowcharts Action :Get DB

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 ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.57.0.07.5.09.0.0NA

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

SahiDB.select($sql)

Arguments
$sqlstring sql query to execute

Returns
two dimensional array of strings

Sahi Pro Flowcharts Action :Sahi DB.select

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 ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.57.0.07.5.09.0.0NA

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

SahiDB.selectWithHeader($sql)

Arguments
$sqlstring sql query to execute

Returns
two dimensional array of strings

Sahi Pro Flowcharts Action :Sahi DB.select With Header

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 ProDesktop Add-OnMobile Add-OnSAP Add-OnAI Assist Add-On
3.57.0.07.5.09.0.0NA

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

SahiDB.update($sql)

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

Returns
null

Sahi Pro Flowcharts Action :Sahi DB.update

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