kony.db Namespace

The changeVersion method allows scripts to automatically verify the version number and change it at the same time during a schema update. This method creates an SQLTransactionSync object (executeSQL) for a read/write transaction. The database's actual version changes to newVersion only if the first argument (oldVersion) exactly matches the database's actual version, otherwise throws a SQLException.

When you invoke this API, it returns immediately and asynchronously reads the transaction.

Syntax

kony.db.changeVersion(dbaseObjectId, oldVersion, newVersion, SQLTransactionCallback, SQLTransactionErrorCallback, SQLTransactionVoidCallback);

Input Parameters

function callback(dbId){
//SQLTransaction contains implementation of executeSql method
// invoke database.executesql method for sql trasaction
}

function errorCallback(SQLError){
//code
}

function successCallback(){
//code
}

Example

changeVersion: function() {
    this.baseObjectId = kony.db.openDatabase("webSqlDB",
        "1.0",
        "Sample SQL Database",
        5 * 1024 * 1024);
    kony.db.changeVersion("1.0", "1.1", null, this.commonErrorCallback, this.commonErrorCallback);
    kony.print("");
}

Return Values

None

Platform Availability

Available on all platforms.

This API allows you to close the database handler of the specified version of a database.

Syntax

kony.db.closeDatabase(dbaseObjectId);

Input Parameters

Example

//The following function invokes the closeDatabase API
function closeDatabase() {
    var dbName = "konytestDB";
    var version = "1.0";
    var displayName = "demo web SQL Database";
    var estimatedSize = 5 * 1024 * 1024; //5*1024*1024 indicates 5 MB
    var databaseObjectId = kony.db.openDatabase(dbName, version, displayName, estimatedSize);
    //databaseObjectId contains the unique ID of the database	
    kony.db.closeDatabase(databaseObjectId);
}

Return Values

None

Platform Availability

• iOS

This API allows you to execute a specified SQL statement on the given database. This is an asynchronous API.

IMPORTANT: The table names and column names are case sensitive.

Syntax

kony.db.executeSql(transactionId, SQLStatement, arguments, SQLStatementSuccessCallback, SQLStatementErrorCallback);

Input Parameters

Example

//The below function inserts 3 rows into the 'employee_details' table. 
var insertTable = [
    ["Siberius", 10],
    ["Clark", 10],
    ["Richard", 20]
];
for (i = 1;
    ((insertTable) != null) & amp; & amp; i & lt; = insertTable.length; i++) {
    var v = insertTable[kony.decrement(i)];
    var sqlStatement = "INSERT INTO emp_details VALUES (" + (this.count + 1000) + ",\"" + v[kony.decrement(1)] + "\"," + v[kony.decrement(2)] + ")";
    this.count = this.count + 1;
    kony.db.executeSql(transactionID,
        sqlStatement,
        null,
        this.success_insertData,
        this.commonErrorCallback);
}

Return Values

None

Error Codes

The following table lists the error codes along with its corresponding error messages:

For more information, refer SQL Error Codes

Platform Availability

Available on all platforms.

This API allows you to open the specified version of a database. This is an asynchronous API.

IMPORTANT: The passphrase parameter in this API is not supported in 6.5 plugins, so database encryption is not available in 6.5 plugins.

Syntax

kony.db.openDatabase(dbname, version, displayName, estimatedSize, passphrase);

Input Parameters

passphrase

This is applicable for iOS and Android

iOS

      passphrase[Array] - Optional

        In iOS, this argument is used to specify Pragma statements (Array) to encrypt database.

   Usage

• To link the SQLCipher library, extract the KAR file with -sqlcipher option. After the extraction, pass the passphrase for the SQL engine to encrypt the database.  

perl extract.pl /Users/PLATFROM/Downloads/konyappipad.KAR –sqlcipher

NOTE: For more information on this parameter and Pragma statements, see https://www.zetetic.net/sqlcipher/sqlcipher-api/.

Android

passphrase [String] - Optional

In Android, this argument is used to specify password (String) to encrypt database.

The developer needs to enable Support SQL DB Encryption (FIPS) option in Quantum Visualizer> Application Properties > Native > Android section in order to support database encryption using passphrase.

IMPORTANT:
The database will not be encrypted in the following conditions: 
1. If Support SQL DB Encryption (FIPS) option is enabled and empty string is passed as passphrase.
2. If Support SQL DB Encryption (FIPS) option is not enabled ,passphrase will be ignored and database is non encrypted.
3. If passphrase is not passed.

IMPORTANT: Encrypted and non-encrypted databases cannot be interchangeably opened using this API.

IMPORTANT: The other Web SQL APIs operate on encrypted DB seamlessly if the databaseObjectId passed to them is opened using kony.db.openDatabase() API with passphrase.

IMPORTANT: The framework does not store the passphrase argument to kony.db.openDatabase() API, it is responsibility of application developer to secure the password.

Example

//The below function will invoke openDatabase
function openDatabase() {
    var dbName = "konytestDB";
    var version = "1.0";
    var displayName = "demo web SQL Database";
    var estimatedSize = 5 * 1024 * 1024; //5*1024*1024 indicates 5 MB

    //Note: Use only one of the following passphrases depending on platform 
    //Passphrase is string in case of android.
    var passphrase = "samplepassword";

    //Passphrase is pragma array in case of iOS.
    passphrase = ["PRAGMA key = 'old passphrase';"];

    var databaseObjectId = kony.db.openDatabase(dbName, version, displayName, estimatedSize, passphrase);
    //databaseObjectId contains the unique ID of the database
}

Return Values

Using the Pre Bundled Database

Bundling: For information about how to bundle a database, refer to the Pre Bundling the Files.

Accessing: The kony.io.FileSystem.getDatabaseDirectoryPath API returns the path where kony.db.openDatabase API opens a specified database file.

You need to copy pre bundled database files using the kony.io.FileSystem.copyBundledRawFileTo API to the path returned by kony.io.FileSystem.getDatabaseDirectoryPath API and then you can read or edit a pre bundled database information using the kony.db.openDatabase API just by opening database using the file name.

//Example for copying and opening the pre bundled database
//The destination file name can be different from the source.
var destFilePath = kony.io.FileSystem.getDatabaseDirectoryPath()+”test.db”;
var fileObj = null;
try{
    var file = new kony.io.File(destFilePath);
    //copyBundledRawFileTo API overrides the destination file with new one.
    //Hence check before copying
    if(!file.exists()){
      fileObj = kony.io.FileSystem.copyBundledRawFileTo(dbName, destFilePath);
    }else{
      fileObj = file;
      alert("File is already available");
      return;
    }
} catch(e) {
    kony.print(“Exception “+e);
  }
  if(fileObj == null){
    kony.print(“Copy failed”);
  }else{
    kony.print(“Copy Success”);
  }
//Opening the copied DB using openDatabase API
dbObject = kony.db.openDatabase("test.db", "1.0", "Prebundled SQL Database", 5 * 1024 * 1024);

Platform Availability

Available on all platforms.

This API allows you to read a specified transaction. This API creates a SQLTransaction object for read-only transactions.

When you invoke this API, it returns immediately and asynchronously reads the transaction.

IMPORTANT: You can only read queries using this API. The kony.db.readTransaction API results in a SYNTAX_ERR in case of DML commands in the SQL statement. DML commands are not supported in this API.

IMPORTANT: The table names and column names are case sensitive.

Syntax

kony.db.readTransaction(dbaseObjectId, TransactionCallback, TransactionErrorCallback, SuccessCallback);

Input Parameters

function callback(dbId){
//Code
}

function errorCallback(SQLError){
//Code
}

function successCallback(){
//code
}

Example

//The below function specifies the callback function that contains the transactions.
function myTransactionCallback(dbId) {
    //SQLTransaction contains implementation of executeSql method
    // invokekony.db.executeSql method for sql trasaction
}
//The below function specifies the callback that must be executed if there is an error in executing the transaction. This callback function is used to roll back the updates to the database.
function myTransactionErrorCallback(SQLError) {
    // proceed with the logic
}
//The below function specifies the callback that must be executed if the transaction is successful.
function mySuccessCallback() {
    // proceed with the logic
}
//The below function will invoke readTransaction 
function readTransaction() {
    var dbName = "konytestDB";
    var version = "1.0";
    var displayName = "demo web SQL Database";
    var estimatedSize = 5 * 1024 * 1024; //5*1024*1024 indicates 5 MB
    var databaseObjectId = kony.db.openDatabase(dbName, version, displayName, estimatedSize);
    //databaseObjectId contains the unique ID of the database	
    kony.db.readTransaction(databaseObjectId, myTransactionCallback, myTransactionErrorCallback, mySuccessCallback);
}

Return Values

None

Platform Availability

Available on all platforms.

This API returns the row available at the specified index. If there is no such row, then the API returns null for JavaScript.

IMPORTANT: The table names and column names are case sensitive.

Syntax

kony.db.sqlResultsetRowItem(transactionID, SQLResultSet, index);

Input Parameters

Example

/*The below function specifies the callback function that must be executed 
when the execution of the SQL statement is a success also invokes sqlResultsetRowItem.*/
function sql_success(transactionId, resultset){
	//logic to process the resultset
	for (var i=0; i<resultset.length; i++){
		var rowItem =kony.db.sqlResultsetRowItem(transactionId,resultset, i);
		alert("empID:" + rowItem["empID"] + " empName:" + rowItem["empName"] + " depID:" + rowItem["depName"]);
	}
}

//The below function specifies the callback function that must be executed when the execution of the SQL statement is a failure.
 function sql_errorCallBack(err){
	alert("Error processing sql statement error code=" + err["code"]);
}

Return Values

Platform Availability

Available on all platforms.

This API allows you to execute the specified transaction on the given database. When you invoke this API, it returns immediately and asynchronously executes the transaction.

This is an asynchronous API. This API creates an SQLTransaction object.

IMPORTANT: The table names and column names are case sensitive.

Syntax

kony.db.transaction(dbaseObjectId, transactionCallback, transactionErrorCallback, successCallback);

Input Parameters

function callback(dbId){
//SQLTransaction contains implementation of executeSql method
// invoke database.executesql method for sql trasaction
}

function errorCallback(SQLError){

//write code here

}

function successCallback(){

//code here

}

Example

//The below function specifies the callback function that contains the transactions.
function myTransactionCallback(dbId) {
    //SQLTransaction contains implementation of executeSql method
    // invokekony.db.executeSql method for sql trasaction
}
//The below function specifies the callback that must be executed if there is an error in executing the transaction. This callback function is used to roll back the updates to the database.
function myTransactionErrorCallback(SQLError) {
    // proceed with the logic
}
//The below function specifies the callback that must be executed if the transaction is successful.
function mySuccessCallback() {
    // proceed with the logic
}
//The below function will invoke transaction 
function transaction() {
    var dbName = "konytestDB";
    var version = "1.0";
    var displayName = "demo web SQL Database";
    var estimatedSize = 5 * 1024 * 1024; //5*1024*1024 indicates 5 MB
    var databaseObjectId = kony.db.openDatabase(dbName, version, displayName, estimatedSize);
    //databaseObjectId contains the unique ID of the database	

    kony.db.transaction(databaseObjectId, myTransactionCallback, myTransactionErrorCallback, mySuccessCallback);
}

Return Values

None

Platform Availability

Available on all platforms.