Database¶

Items

Database Types
Programming Techniques

Database Types ¶

Following 3 types of database are used in intra-mart Accel Platform .

System Database

It is the database that stores the system data.

Do not use the system database as it is used internally in the system.

Tenant Database

It is the database wherein the data used in tenant is saved.

Shared Database

It is the database wherein the data other than the intra-mart Accel Platform system is saved.

Use to connect with the external system.

Programming Techniques ¶

Transaction ¶

The implementation method of transaction process is explained here.

Transaction is automatically committed at the time of process completion by passing the execution function in begin method of the Transaction object.
// Transaction Process
Transaction.begin(function() {
    // Execute query
    var result = new TeanantDatabase().execute(sql, param);
    if(result.error) {
        // Rollback in case of error.
        Transaction.rollback();
    }
});
Since rollback process is automatically performed when an exception occurs and commit process is executed when the process is normally executed, the commit/rollback of a transaction can prevent omission of execution as mentioned above.

Query Execution ¶

The execution method of the query differs with the destination database.

Tenant Database

// Execute query to tenant database.
var database = new TenantDatabase();
var result = database.execute("select * from b_m_account_b");

Shared Database

// Execute the shared database query which is set in connection ID "sample".
var database = new SharedDatabase("sample");
var result = database.execute("select * from b_m_account_b");

Use the DbParameter object when using prepared statement.

// Execute the query using prepared statement.
var sql = "select * from b_m_account_b where user_cd = ? and login_failure_count = ?";
var result = new TenantDatabase().execute(sql, {
    DbParameter.string("aoyagi"),
    DbParameter.number(0)
});

Use of external SQL ¶

External SQL is the function which is written outside the program and is executed from the program.

When using the external SQL, write SQL in the text file having ”.sql” extension and place it in the directory under options of “<CONTEXT_PATH>/WEB-INF/jssp/src”.

When using “<CONTEXT_PATH>/WEB-INF/jssp/src/sample/sql/get_sample_data.sql” SQL file, implement as given below.
// Execute query by using external SQL.
var database = new TenantDatabase();
var result = database.executeByTemplate('sample/sql/get_sample_data', params);

Readable file ¶

External SQL can separate the SQL file which is used for each database classification of destination database.

When separating the SQL files for each database classification, attach the database classification of suffix of file name.

Example: When the database classification is oracle, and SQL file name is “get_sample_data.sql”;

SQL file name is considered as “get_sample_data_oracle.sql”.

As mentioned above, when database classification is Oracle “get_sample_data_oracle.sql” is used in preparing the suffix attached to the SQL file and “get_sample_data.sql” is used in case of other databases.

The database suffix attached to the SQL files are as given below.

Database Suffix

Oracle _oracle

SQLServer _sqlserver

DB2 _db2

PostgreSQL _postgre

Database	Suffix
Oracle	_oracle
SQLServer	_sqlserver
DB2	_db2
PostgreSQL	_postgre

Description example of SQL file ¶

Example 1 Use of 2 Way-SQL
2Way-SQL is the SQL statement which can be executed as it is by the SQL tool such as SQL*Plus by writing the mapping with the program in comments.
- SQL file “sample.sql”
SELECT * FROM b_m_account_b WHERE user_cd = /*userCd*/'aoyagi'
- Program “sample.js”
new TenantDatabase().executeByTemplate('sample', { userCd : DbParameter.string("harada") });
The data of user_cd=’harada’ can be acquired by executing the program.

The data of user_cd=’aoyagi’ can be acquired by executing the “sample.sql” as it is by tool etc.

At the time of program execution, execute after the comment part of the WHERE statement is changed to prepared statement.

prepared statement can be used by adding the ”:Data type” to the comment part (/Parameter name/) of SQL file without using the DbParameter.
- SQL file “sample.sql”
SELECT * FROM b_m_account_b WHERE user_cd = /*userCd:string*/'aoyagi'
- Program “sample.js”
new TenantDatabase().executeByTemplate('sample', { userCd : "harada" });
Example 2 Use of prepared statement
When prepared statement (SQL containing?) is described in SQL file, the object for which the property name is $1, $2, …, $N, in display order is specified in argument.
- SQL file “sample.sql”
SELECT * FROM b_m_account_b WHERE user_cd = ? AND login_failure_count = ?
- Program “sample.js”
new TenantDatabase().executeByTemplate('sample', { $1 : DbParameter.string('aoyagi'), $2 : DbParameter.number(0) });
Example 3 Substitution of string
Specify the parameter name by adding “$” in the comment to execute the SQL by dynamically substituting the query string mentioned in the SQL file.
- SQL file “sample.sql”
SELECT * FROM b_m_account_b ORDER BY /*$order*/'user_cd'
- Program “sample.js”
new TenantDatabase().executeByTemplate('sample', { order : "user_cd" });
It is used to substitute the string of the part where prepared statement cannot be used like ORDER BY statement.

Example 4 Conditional branching

Use /IF/ /END/ to dynamically change the SQL to execute according to the specific conditions.

SQL file “sample.sql”
SELECT *
FROM b_m_account_b
WHERE
    /*IF userCd != null*/
    user_cd LIKE /*userCd:string*/'aoyagi'
    /*END*/
    /*IF predicate()*/
    AND login_failure_count = /*failureCount:number*/0
    /*END*/
Program “sample.js”
new TenantDatabase().executeByTemplate('sample', {
    userCd : "harada",
    failureCount : 0,
    predicate : function() {
        return true;
    }
});
Enclose the WHERE statement with /BEGIN/ /END/ when there are cases where incorrect SQL is executed, or blank WHERE statement due to condition branching.

By using /BEGIN/ /END/ following process is executed and the execution of incorrect SQL can be avoided.

When all the conditions are failed WHERE gets deleted from SQL.

AND is deleted when the query string of the condition which was satisfied first has started with AND.

SQL File “sample.sql”
SELECT *
FROM b_m_account_b
/*BEGIN*/
WHERE
    /*IF userCd != null*/
    user_cd LIKE /*userCd:string*/'aoyagi'
    /*END*/
    /*IF predicate()*/
    AND login_failure_count = /*failureCount:number*/0
    /*END*/
/*END*/