Skip to content
Get Free Trial

Database Functions

Databases can be accessed via 3 APIs.

Since
Sahi Pro 3.5
Desktop Engine 7.0.0
Mobile Engine 7.5.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

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

Arguments

NameTypeDescription
$driverstringJDBC driver class (fully qualified name)
$jdbcurlstringJDBC URL
$usernamestringusername to connect to the database
$passwordstringif $isPasswordEncrypted is true, use encrpted password to connect to the database
$isPasswordEncryptedbooleanoptionalto use encrpted password to connect to the database (default value is false)

Returns

objectsahiDB object
  • NOTE: 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);
Since
Sahi Pro 3.5
Desktop Engine 7.0.0
Mobile Engine 7.5.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

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

Arguments

NameTypeDescription
$driverstringJDBC driver class (fully qualified name)
$jdbcurlstringJDBC URL
$propertiesObjProperties objectA java.util.Properties object with relevant key values
$isPasswordEncryptedbooleanoptionalto use encrpted password to connect to the database (default value is false)

Returns

objectsahiDB object
  • NOTE: 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);
Since
Sahi Pro 8.0.0
Desktop Engine 8.0.0
Mobile Engine 8.0.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

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

Arguments

NameTypeDescription
$driverstringJDBC driver class (fully qualified name)
$jdbcurlstringJDBC URL
$jsonObjJson objectA json object with relevant key values
$isPasswordEncryptedbooleanoptionalto use encrpted password to connect to the database (default value is false)

Returns

objectsahiDB object
//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);

The SahiDB object has 3 functions:

Since
Sahi Pro 3.5
Desktop Engine 7.0.0
Mobile Engine 7.5.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

SahiDB.select($sql)

Arguments

NameTypeDescription
$sqlstringsql query to execute

Returns

two dimensional array of strings

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

Id Name Age
1 Ram 18
2 Amir 20
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"];
Since
Sahi Pro 3.5
Desktop Engine 7.0.0
Mobile Engine 7.5.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

SahiDB.selectWithHeader($sql)

Arguments

NameTypeDescription
$sqlstringsql query to execute

Returns

two dimensional array of strings

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"
Since
Sahi Pro 3.5
Desktop Engine 7.0.0
Mobile Engine 7.5.0
SAP Engine 9.0.0

Modes BrowserWindowsJavaAndroidiOSSAP

SahiDB.update($sql)

Arguments

NameTypeDescription
$sqlstringsql insert/update/delete query to execute

Returns

null

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

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

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