Database Functions
Databases can be accessed via 3 APIs.
_getDB
Section titled “_getDB”- 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
| Name | Type | Description |
|---|---|---|
$driver | string | JDBC driver class (fully qualified name) |
$jdbcurl | string | JDBC URL |
$username | string | username to connect to the database |
$password | string | if $isPasswordEncrypted is true, use encrpted password to connect to the database |
$isPasswordEncrypted | booleanoptional | to use encrpted password to connect to the database (default value is false) |
Returns
object | sahiDB object |
NOTE: Argument
$isPasswordEncryptedwas 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);_getDB
Section titled “_getDB”- 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
| Name | Type | Description |
|---|---|---|
$driver | string | JDBC driver class (fully qualified name) |
$jdbcurl | string | JDBC URL |
$propertiesObj | Properties object | A java.util.Properties object with relevant key values |
$isPasswordEncrypted | booleanoptional | to use encrpted password to connect to the database (default value is false) |
Returns
object | sahiDB object |
NOTE: Argument
$isPasswordEncryptedwas 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);_getDB
Section titled “_getDB”- 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
| Name | Type | Description |
|---|---|---|
$driver | string | JDBC driver class (fully qualified name) |
$jdbcurl | string | JDBC URL |
$jsonObj | Json object | A json object with relevant key values |
$isPasswordEncrypted | booleanoptional | to use encrpted password to connect to the database (default value is false) |
Returns
object | sahiDB 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);Using the SahiDB object
Section titled “Using the SahiDB object”The SahiDB object has 3 functions:
SahiDB.select
Section titled “SahiDB.select”- 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
| Name | Type | Description |
|---|---|---|
$sql | string | sql 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 indexvar $userName = $row[0];
// or access column using namevar $userName = $row["Name"];
// You can also access directly from $rs without using rowvar $userName = $rs[1]["Name"];SahiDB.selectWithHeader
Section titled “SahiDB.selectWithHeader”- 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
| Name | Type | Description |
|---|---|---|
$sql | string | sql 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"SahiDB.update
Section titled “SahiDB.update”- 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
| Name | Type | Description |
|---|---|---|
$sql | string | sql 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");Accessing databases directly
Section titled “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();}
