Sahi Pro - Database APIs
Databases can be accessed via 2 APIs._getDB
Since Sahi Pro: 3.5Since Sahi OS: 2
_getDB($driver, $jdbcurl, $username, $password)
Arguments
$driver | string | JDBC driver class (fully qualified name) |
$jdbcurl | string | JDBC URL |
$username | string | username to connect to the database |
$password | string | password to connect to the database |
Details
Returns a sahiDB object.
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
More info: Adding jars to Sahi's classpath
_getDB
Since Sahi Pro: 3.5Since Sahi OS: 2
_getDB($driver, $jdbcurl, $propertiesObj)
Arguments
$driver | string | JDBC driver class (fully qualified name) |
$jdbcurl | string | JDBC URL |
$propertiesObj | Properties object | A java.util.Properties object with relevant key values |
Details
Returns a sahiDB object.
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
More info: Adding jars to Sahi's classpath
Using the SahiDB object
The SahiDB object has 3 APIs:SahiDB.select
Since Sahi Pro: 3.5Since Sahi OS: 2
SahiDB.select($sql)
Arguments
$sql | string | 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
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"];
infoNote the possibility of using index (
$rs[0][1]
) as well as name for column ($rs[0]["Age"]
).SahiDB.selectWithHeader
Since Sahi Pro: 3.5Since Sahi OS: 2
SahiDB.selectWithHeader($sql)
Arguments
$sql | string | 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
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.5Since Sahi OS: 2
SahiDB.update($sql)
Arguments
$sql | string | sql insert/update/delete query to execute |
Details
Executes a non-select query. Used for insert/update/delete queries.
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();
}