kony.db Namespace
The kony.db namespace contains the following API elements.
Functions
The kony.db
namespace contains the following functions.
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
Parameter | Description |
---|---|
dbaseObjectId [String] - Mandatory | Specifies the unique ID of the database |
oldVersion [String] - Mandatory | Specifies the older version of the database. |
newVersion [String] - Mandatory | Specifies the newer version of the database. |
SQLTransactionCallback [Function] - Mandatory |
Specifies the callback function that contains the transactions. For example: function callback(dbId){ |
SQLTransactionErrorCallback [Function] - Optional |
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. For example: function errorCallback(SQLError){ |
SQLVoidCallback [Function] - Optional |
Specifies the callback that must be executed if the transaction is successful. For example: function successCallback(){ |
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
Parameter | Description |
---|---|
dbaseObjectId [String] - Mandatory | Specifies the unique ID of the database |
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
Parameter | Description |
---|---|
transactionId [String] - Mandatory | Specifies the unique ID of the transaction. |
SQLStatement [String] - Mandatory | Specifies the SQL statement that must be executed. |
arguments [Object] - Optional | Specifies the arguments for executing the SQL statement. If this parameter is not specified or is nil, then the statement is executed without any arguments. The supported argument types are:
No type affinity is performed. |
SQLStatementSuccessCallback [Function] - Optional | Specifies the callback function that must be executed when the execution of the SQL statement is a success. |
SQLStatementErrorCallback [Function] - Optional | Specifies the callback function that must be executed when the execution of the SQL statement is a failure. This callback returns a boolean value.
The default return value is false for this callback. |
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:
Error Code | Error Message | Description |
---|---|---|
1 | UNKNOWN_ERR | The statement failed for database reasons not covered by any other error code. |
2 | DATABASE_ERR | The operation failed because the actual database version was not what it should be. For example, a statement found that the actual database version no longer matched the expected version of the Database or DatabaseSync object, or the Database.changeVersion() or DatabaseSync.changeVersion() methods were passed a version that doesn't match the actual database version. |
3 | TOO_LARGE_ERR | The statement failed because the data returned from the database was too large. The SQL "LIMIT" modifier might be useful to reduce the size of the result set. |
4 | QUOTA_ERR | The statement failed because there was not enough remaining storage space, or the storage quota was reached and the user declined to give more space to the database. |
5 | SYNTAX_ERR | The statement failed because of a syntax error, or the number of arguments did not match the number of ? placeholders in the statement, or the statement tried to use a statement that is not allowed, such as BEGIN, COMMIT, or ROLLBACK, or the statement tried to use a verb that could modify the database but the transaction was read-only. |
6 | CONSTRAINT_ERR | An INSERT, UPDATE, or REPLACE statement failed due to a constraint failure. For example, because a row was being inserted and the value given for the primary key column duplicated the value of an existing row. |
7 | TIMEOUT_ERR | A lock for the transaction could not be obtained in a reasonable time. |
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
Parameter | Description |
---|---|
dbname [String] - Mandatory | Specifies the actual name of the database that you want to open. |
version [String] - Mandatory | Specifies the version of the database that you want to open. |
displayName [String] - Mandatory | Specifies the display name of the database that you want to open. |
passphrase
This is applicable for iOS and Android
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/.
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
Return Value | Description |
---|---|
databaseObjectId [userdata] | Returns the unique ID of the database. The identifier is userdata ( platform specific instance) and is not specifically String. Developers must not rely on the type of the identifier. |
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
Parameter | Description |
---|---|
dbaseObjectId [String] - Mandatory | Specifies the unique ID of the database. |
TransactionCallback [Function] - Mandatory |
Specifies the callback function that contains the transactions. For example:function callback(dbId){ |
TransactionErrorCallback [Function] - Optional | 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.For example:function errorCallback(SQLError){ |
SuccessCallback [Function] - Optional |
Specifies the callback that must be executed if the transaction is successful. function successCallback(){ |
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
Parameters | Description |
---|---|
transactionID [String] - Mandatory | Specifies the unique ID of the transaction. |
SQLResultSet [Array of JS Objects] - Mandatory | Specifies the name of the SQL result set. |
index [Number] - Mandatory | Specifies the index from which the row is to be retrieved. |
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
Return Value | Description |
---|---|
rowitem [Array of Objects] | Returns the row item at the specified index |
null/nil | Returns null/nil if there is no row available at the specified index |
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
Parameter | Description |
---|---|
dbaseObjectId [String] - Mandatory | Specifies the unique ID of the database |
transactionCallback [Function] - Mandatory |
Specifies the callback function that contains the transactions. For example: function callback(dbId){ //SQLTransaction contains implementation of executeSql method // invoke database.executesql method for sql trasaction } |
transactionErrorCallback [Function] - Optional |
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. For example: function errorCallback(SQLError){ |
successCallback [Function] - Optional |
Specifies the callback that must be executed if the transaction is successful. For example: function successCallback(){ When the transaction is successful, this callback is executed along with the callback function of the kony.db.executeSql API. |
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.