LiteDBConnection / LiteDBStatement Introduction

LiteDBConnection and LiteDBStatement are the objects that allow you to control data by connecting to the database and performing queries. As you can see from the object name, you can connect and use SQLite, the lightweight database engine, and it provides the API to control SQLite to users.

The sample in this chapter uses the nexacro_sample_db.sqlite file. This file is an SQLite file with data entered in advance, and it consists of three tables of TB_DEPT, TB_POS, and TB_EMP. Each table has the following structure.

Creating SQLite Database

In the SQLite project, an easy-to-use command line-based tool is provided in addition to the database engine. The user can create and configure the database by entering commands, and can control the database by executing the SQL statements.

This section describes how to create the SQLite database to be used in the example using the command line tool.

Installing SQLite Command Line Tool

The SQLite command line tool can be downloaded from SQLite Download for the version suitable to the user environment (compressed file starting with 'sqlite-tools-'). After downloading the file and extracting it to the suitable directory, the exe-type executable files will appear.

These files are programs that can be executed immediately without a separate installation process. We will only use sqlite3.exe here.

Creating a Database File

Use sqlite3.exe to create the database file. You only need to enter the database file name to be created as follows to use it. You can use any extension for the database file, but generally, db, db3, and sqlite are used.

sqlite3.exe [DB File]

Go to the path where the sqlite3.exe file is located and enter 'sqlite3.exe nexacro_sample_db.sqlite' in the command prompt. If the same file exists in the corresponding path, then open the existing file instead of creating a new file. You can check the currently open database file with the '.databases' command.

If you want to create or open another database file after executing sqlite3.exe, then use the '.open' command.

sqlite> .open [DB File]

Creating a Table

Now that you have created the database file, create the table. Enter the following SQL statements in the command prompt to create the table. The table to create is the same as the TB_POS (position information), TB_EMP (employee information), and TB_DEPT (department information), and you can check the schema from the following.

/* TB_POS Table */
CREATE TABLE IF NOT EXISTS `TB_POS` (
	`POS_CD`	VARCHAR2 ( 2 ),
	`POS_NAME`	VARCHAR2 ( 50 )
);

/* TB_EMP Table */
CREATE TABLE IF NOT EXISTS `TB_EMP` (
	`EMPL_ID`	VARCHAR2 ( 5 ),
	`FULL_NAME`	VARCHAR2 ( 50 ) DEFAULT (null),
	`DEPT_CD`	VARCHAR2 ( 2 ) DEFAULT (null),
	`POS_CD`	VARCHAR2 ( 2 ),
	`HIRE_DATE`	VARCHAR2 ( 8 ),
	`GENDER`	VARCHAR2 ( 1 ),
	`MARRIED`	BOOLEAN ( 1 ),
	`SALARY`	INTEGER ( 12 ),
	`MEMO`	TEXT
);

/* TB_DEPT Table */
CREATE TABLE IF NOT EXISTS `TB_DEPT` (
	`DEPT_CD`	VARCHAR2 ( 2 ) DEFAULT (NULL),
	`DEPT_NAME`	VARCHAR2 ( 50 ) DEFAULT (NULL)
);

After SQL statement execution is completed, check whether the table has been created normally with the '.tables' command.

Entering Data

Once you have created the table, now enter the actual data. Enter data into each table with the SQL statements as follows in the command prompt.

/* TB_POS Table */
INSERT INTO `TB_POS` VALUES ('07','Officer');
INSERT INTO `TB_POS` VALUES ('06','Supervisor');
INSERT INTO `TB_POS` VALUES ('05','Assistant Manager');
INSERT INTO `TB_POS` VALUES ('04','Department Manager');
INSERT INTO `TB_POS` VALUES ('03','Division Manager');
INSERT INTO `TB_POS` VALUES ('02','President');
INSERT INTO `TB_POS` VALUES ('01','Chairman');

/* TB_EMP Table */
INSERT INTO `TB_EMP` VALUES ('AA001','Oleg','08','03','20072504','W',1,70000,'green');
INSERT INTO `TB_EMP` VALUES ('AA010','Aladdin','07','06','20041909','M',0,120000,'green');
INSERT INTO `TB_EMP` VALUES ('BB010','Curran','02','03','20070111','M',1,210000,'orange');
INSERT INTO `TB_EMP` VALUES ('BB020','Adam','07','07','20011602','W',1,90000,'indigo');
INSERT INTO `TB_EMP` VALUES ('BB030','Heather','03','03','20061406','M',0,50000,'blue');
INSERT INTO `TB_EMP` VALUES ('CC110','Lester','07','05','20171604','M',0,100000,'green');
INSERT INTO `TB_EMP` VALUES ('CD120','Elijah','10','01','20151304','W',0,110000,'gold');
INSERT INTO `TB_EMP` VALUES ('AB100','Angela','08','04','20021110','M',1,670000,'blue');
INSERT INTO `TB_EMP` VALUES ('AC310','Rae','02','05','20072603','W',1,90000,'green');
INSERT INTO `TB_EMP` VALUES ('AC210','Denton','02','04','20172503','M',1,90000,'skyblue');
INSERT INTO `TB_EMP` VALUES ('DD002','Nora','05','01','20160202','W',0,420000,'blue');
INSERT INTO `TB_EMP` VALUES ('DD200','Adrian','05','04','20160212','W',0,80000,'violet');
INSERT INTO `TB_EMP` VALUES ('AD020','Ulric','04','04','20042107','W',0,420000,'yellow');
INSERT INTO `TB_EMP` VALUES ('BD030','Veronica','10','06','20130701','M',1,70000,'green');
INSERT INTO `TB_EMP` VALUES ('AA200','Phyllis','02','06','20120812','W',1,350000,'orange');

/* TB_DEPT Table */
INSERT INTO `TB_DEPT` VALUES ('01','Accounting Team');
INSERT INTO `TB_DEPT` VALUES ('02','Finances Team');
INSERT INTO `TB_DEPT` VALUES ('03','Human Resource Team');
INSERT INTO `TB_DEPT` VALUES ('04','Marketing Team');
INSERT INTO `TB_DEPT` VALUES ('05','Sales Team');
INSERT INTO `TB_DEPT` VALUES ('06','Consulting  Team');
INSERT INTO `TB_DEPT` VALUES ('07','Design Team');
INSERT INTO `TB_DEPT` VALUES ('08','Mobile Team');
INSERT INTO `TB_DEPT` VALUES ('09','Education Team');
INSERT INTO `TB_DEPT` VALUES ('10','Technical Support Team');
INSERT INTO `TB_DEPT` VALUES ('11','Customer Support Team');

After entering the data, you can check the data entered in each table using the SQL statement.

After inputting and checking data is completed, end the program with the '.exit' command. The created nexacro_sample_db.sqlite file can be used by connecting it to the LiteDB object without any special processing.

Connecting/Ending the Database

When connecting and ending the database in the application, use the LiteDBConnection object. By setting properties such as async, busytimeout, datasource, openflag, and preconnect, you can set options when connecting to the database.

${sample}

The following is an example of connecting to the database, searching a specific table in the database, and outputting it to Grid.

Touch the Open button to set the connection to the database. Search the TB_EMP table to output the data to Grid. Touch the Close button to end the connection to the database.

The nexacro_sample_db.sqlite file is the SQLite database file created earlier. Please refer to Creating SQLite Database on how to create it.

${sample_element}

LiteDBConnection > open

This is the method that connects to the database. Enter the location and file name of the SQLite file as the first parameter and set how to open the database as the second parameter.

LiteDBConnection > datasource

This is the property that sets the location information of the database to be connected. Enter the database path and file name together.

LiteDBConnection > close

This is the method that closes the database connection.

LiteDBConnection > isConnected

This is the method that checks if the database is connected.

LiteDBErrorEventInfo

This is the EventInfo object transmitted to the onerror event function when an error occurs in the execution of the LiteDBConnection and LiteDBStatement objects. You can check which error has occurred to process exceptions.

LiteDBEventInfo

This is the EventInfo object transmitted to the onsuccess event function when the operation of the LiteDBConnection and LiteDBStatement objects is successful. It has the information on the cause of the event and the result value of the task.

${sample_step}

Add the LiteDBConnection and LiteDBStatement objects. The added object can be checked in the Invisible Object window.

Place the Edit component to input or display the database file path and the Button component to receive commands from the user appropriately as shown in the example figure. Receive data from the database additionally opened, and then add the Grid component and the Dataset object to display on the screen.

Components and objects used to configure the screen are as follows.

Set the value property of the added Edit component to '%USERAPP%File/nexacro_edu_db.sqlite'. This file is an SQLite file, which is provided as a sample with the project. It can be checked in the File directory of the Project Explorer in Nexacro Studio.

/* Open Button onclick Event Function */
this.btn_dbopen_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBConnection00.isConnected();
};

/* Close Button onclick Event Function */
this.btn_dbclose_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBConnection00.close();
};

Write the onsuccess event function to be performed when the database connection, connection check, and ending task are successful. You can check which method was called through the e.reason property value.

/* LiteDBConnection Object onsuccess Event Function */
this.LiteDBConnection00_onsuccess = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBEventInfo)
{
	switch(e.reason)
	{
		case 2:	// LiteDBConnection close()
			this.Grid00.set_visible(false);
			alert("DB close succeed.");
			break;
		case 4: // LiteDBConnection isConnected()
			alert("Already connected to DB.");
			break;
		case 5: // LiteDBConnection open()
			this.Grid00.set_visible(true);
			
			this.LiteDBStatement00.set_ldbconnection(this.LiteDBConnection00);
			this.LiteDBStatement00.executeQuery("select * from tb_emp");
			break;		
		case 1:	// LiteDBConnection begin()
		case 3: // LiteDBConnection commit()
		case 6:	// LiteDBConnection rollback()
		default:
			break;
	}
};

Write the onerror event function to be performed when the database connection, connection check, and ending task fail. You can check through which method an error occurred when executed through the e.statuscode property value.

/* LiteDBConnection Object onerror Event Function */
this.LiteDBConnection00_onerror = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBErrorEventInfo)
{	
	switch(e.statuscode.toString())
	{
		case "1203":	//LiteDBConnection close() error
			alert("DB close failed.");
			break;
		case "1205":	//LiteDBConnection isConnected() error
			//alert("DB is not connected.");		
			this.LiteDBConnection00.set_datasource(this.edt_dbfile.value);
			this.LiteDBConnection00.open();		
			break;
		case "1206":	//LiteDBConnection open() error
			alert("DB open failed.");
			break;	
		case "00001":	//Parameter setting error
		case "1201":	//DB response timeout error
		case "1202":	//LiteDBConnection begin() error
		case "1204":	//LiteDBConnection commit() error			
		case "1207":	//LiteDBConnection rollback() error
		default:		//Unknown error		
			var strResult = "[LiteDBConnection00_onerror]";	
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;
			alert(strResult);				
	}	
};

Write the onsuccess event function that will occur when the query execution to the connected database is successful. Receive the data of the result value of the query statement as Dataset and output to Grid.

/* LiteDBStatement Object onsuccess Event Function */
this.LiteDBStatement00_onsuccess = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBEventInfo)
{		
	this.Dataset00.copyData(e.returnvalue);		
	this.Grid00.createFormat();
};

Write the onerror event function that will occur when the query execution to the connected database fails.

/* LiteDBStatement Object onerror Event Function */
this.LiteDBStatement00_onerror = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBErrorEventInfo)
{
	switch(e.statuscode.toString())
	{
		case "1210":	//LiteDBStatement executeQuery() error
			alert("LiteDBStatement executeQuery() error");
			break;
		case "1211":	//LiteDBStatement executeUpdate() error
			alert("LiteDBStatement executeUpdate() error");
			break;
		case "00001":	//Parameter setting error
		case "1201":	//DB response timeout error
		default:		//Unknown error
			var strResult = "[LiteDBStatement00_onerror]";
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;
			alert(strResult);	
	}	
};

Touch the Open button to check that the database connection is performed. If the connection is performed normally, then the data of the TB_EMP table is displayed on the Grid.

Touch the Close button to close the database connection. If the database is closed normally, then the Grid will disappear.

Executing Query

LiteDBStatement is the object used when executing queries after connecting to DB. The LiteDBStatement object provides the executeQuery, executeUpdate methods, and the close method to stop queries from being executed. Depending on the type of query statement, the method that executes the query uses the executeQuery method for the Select statement and the executeUpdate method for the Insert, Update, and Delete statements.

${sample}

The following is an example of connecting to the database and outputting the result of executing the query statement to the Grid.

When the screen is loaded, it automatically connects to the database, searches the TB_POS table, and outputs the data to Grid. When the database connection is completed, select the query statement and touch the Query button. The result of the query execution is immediately reflected in the Grid. If you touch the Stop button during the query execution, then the execution stops.

${sample_element}

LiteDBStatement > ldbconnection

This is the property that connects the LiteDBConnection object connected to the database to the LiteDBStatement object. Query statements can be executed only when the property setting is completed normally.

LiteDBStatement > query

This is the property that sets the query statement. If the query statement is not entered as the argument when calling the executeQuery, executeUpdate methods, then the value set in this property is used as the query statement.

LiteDBStatement > executeQuery

This is the method that executes the select query. If no argument is entered, then the value set in the query property is used as the query statement.

LiteDBStatement > executeUpdate

This is the method that executes the insert, update, and delete queries. If no argument is entered, then the value set in the query property is used as the query statement.

LiteDBStatement > close

This is the method that stops the query execution.

${sample_step}

Add the LiteDBConnection and LiteDBStatement objects. The added object can be checked in the Invisible Object window.

Place the Edit component to input or display the database file path and the ExtCombo and Button components to receive commands from the user appropriately as shown in the example figure. Add the Grid component and the Dataset component to check whether the database is normally opened.

Components and objects used to configure the screen are as follows.

Set the value property of the added Edit component to '%USERAPP%File/nexacro_edu_db.sqlite'. This file is an SQLite file, which is provided as a sample with the project. It can be checked in the File directory of the Project Explorer in Nexacro Studio.

Write the onload event function to check whether the database is connected after loading the form.

/* onload Event Function of Form */
this.sample_litedb_02_onload = function(obj:nexacro.Form,e:nexacro.LoadEventInfo)
{
	this.LiteDBConnection00.isConnected();
};

Write the onsuccess event function to be performed when the database connection, connection check, and ending task are successful. You can check which method was called through the e.reason property value.

this.LiteDBConnection00_onsuccess = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBEventInfo)
{
	switch(e.reason)
	{
		case 2:	// LiteDBConnection close()
			this.Grid00.set_visible(false);
			this.btn_query.set_enable(false);
			break;
		case 4: // LiteDBConnection isConnected()
			alert("Already connected to DB.");
			break;
		case 5: // LiteDBConnection open()
			this.Grid00.set_visible(true);
			this.btn_query.set_enable(true);

			this.LiteDBStatement00.set_ldbconnection(this.LiteDBConnection00);
			this.LiteDBStatement00.executeQuery("select * from tb_pos");
			break;					
		case 1:	// LiteDBConnection begin()
		case 3: // LiteDBConnection commit()
		case 6:	// LiteDBConnection rollback()
		default:
			break;
	}
};

Write the onerror event function to be performed when the database connection, connection check, and ending task fail. You can check which method an error occurred when executed through the e.statuscode property value.

this.LiteDBConnection00_onerror = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBErrorEventInfo)
{	
	switch(e.statuscode.toString())
	{
		case "1203":	//LiteDBConnection close() error
			alert("DB close failed.");
			break;
		case "1205":	//LiteDBConnection isConnected() error
			this.LiteDBConnection00.set_datasource(this.edt_dbfile.value);
			this.LiteDBConnection00.open();				
			break;
		case "1206":	//LiteDBConnection open() error
			alert("DB open failed.");
			break;			
		case "00001":	//Parameter setting error
		case "1201":	//DB response timeout error
		case "1202":	//LiteDBConnection begin() error
		case "1204":	//LiteDBConnection commit() error			
		case "1207":	//LiteDBConnection rollback() error
		default:		//Unknown error		
			var strResult = "[LiteDBConnection00_onerror]";	
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;			
			alert(strResult);				
	}	
};

Write the onsuccess event function to be executed when the query execution is successful. You can check which method was called through the e.reason property value.

/* LiteDBStatement Object onsuccess Event Function */
this.LiteDBStatement00_onsuccess = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBEventInfo)
{	
	switch(e.reason)
	{
		case 7:	// LiteDBStatement executeQuery()
			this.Dataset00.copyData(e.returnvalue);
			this.Dataset00.set_keystring("S:+POS_CD+POS_NAME");
			this.Grid00.createFormat();		
			break;
		case 8:	// LiteDBStatement executeUpdate()
			this.LiteDBStatement00.set_query("select * from tb_pos");
			this.LiteDBStatement00.executeQuery();
			break;
		case 9:	// LiteDBStatement close()
		default:
			break;
	}	
};

Write the onerror event function to be executed when the query execution fails. You can check through which method an error occurred when executed through the e.statuscode property value.

/* LiteDBStatement Object onerror Event Function */
this.LiteDBStatement00_onerror = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBErrorEventInfo)
{
	switch(e.statuscode.toString())
	{
		case "1210":	//LiteDBStatement executeQuery() error
			alert("LiteDBStatement executeQuery() error.");
			break;
		case "1211":	//LiteDBStatement executeUpdate() error
			alert("LiteDBStatement executeUpdate() error.");
			break;		
		case "00001":	//Parameter setting error
		case "1201":	//DB response timeout error
		default:		//Unknown error		
			var strResult = "[LiteDBStatement00_onerror]";	
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;			
			alert(strResult);
	}
};

Open the editor in the innerdataset property of the ExtCombo component to set innerdataset. Enter the following SQL query statement to be used in the example.

Set the properties of the ExtCombo component as follows. Bind the innerdataset and set the pop-up window form to be opened when the ExtCombo is clicked in the popupurl property.

Determine which query statement it is by looking at the character string input from the user, call the executeQuery method for select, and then call the executeUpdate method for insert, update, and delete.

/* Query Button onclick Event Function */
this.btn_query_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBStatement00.set_query(this.ExtCombo00.text);
	
	var arrString = this.LiteDBStatement00.query.split(" ");	
	
	switch(arrString[0])
	{
		case "select":
			this.LiteDBStatement00.executeQuery();
			break;
		case "insert":
		case "update":
		case "delete":
			this.LiteDBStatement00.executeUpdate();
			break;
		default:
			alert(arrString[0] + " is illegal query statement.");
	}
};

If the close method of LiteDBStatement is called, then the Query execution in process is stopped.

/* Stop Button onclick Event Function */
this.btn_stop_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBStatement00.close();		
};

When the screen is loaded, the database is automatically connected. Check whether the connection is normally performed and the TB_POS table data is displayed on the Grid.

Select the query statement and touch the Query button. If the query is executed normally, then check whether the execution result is reflected in the Grid.

Touch the Stop button during the query execution to check whether the query being executed is stopped.

Configuring Transaction

In the database, the most important thing is to ensure the integrity of data. The transaction is the mechanism to protect data from fatal errors that can occur while changing the status of the database.

SQLite supports transactions by default. Since the AUTOCOMMIT mode is supported, the SQL operations are internally processed within one transaction, even if the user does not set a separate transaction. It is convenient to use as it does not require separate settings, but it may be inefficient when a large amount of data is input or a complex operation consisting of multiple SQL statements is required, because transaction setting/cancellation is repeated for every SQL operation. In this case, the user needs to configure the transaction directly and perform the commit/rollback.

This section describes how to use the Commit function to configure the transaction explicitly by the user and reflect it in the database, and how to use the Rollback function to cancel.

${sample}

The following is an example of connecting to the database, configuring the transaction, performing the SQL operation, and outputting the result to the Grid.

When the screen is loaded, it automatically connects to the database, searches the TB_POS table, and outputs the data to Grid. When the database connection is completed, select the query statement and touch the Query button. The result of the query execution is immediately reflected in the Grid.

When the screen is loaded, it automatically connects to the database, searches the TB_POS table, and outputs the data to the Grid. When the database connection is completed, you can configure the transaction by touching the Begin button. After the transaction is set, select the appropriate query statement and touch the Query button to perform SQL operations that input, delete, or change data. The executed result is immediately reflected in the Grid and can be checked.

Touch the Commit button to reflect the changes made to the database after the query execution is completed, or touch the Rollback button to cancel. If you end the application in the middle of the transaction, then the SQL operations until then are automatically processed as Rollback.

${sample_element}

LiteDBConnection > begin

This is the method that configures the transaction in the database.

LiteDBConnection > commit

This is the method that reflects the changes made to the database where the transaction is set. If you perform commit, then the transaction setting is canceled.

LiteDBConnection > rollback

This is the method that returns (cancels) the changed contents to the state before the transaction was configured. If you perform rollback, then the transaction setting is canceled.

${sample_step}

Add the LiteDBConnection and LiteDBStatement objects. The added object can be checked in the Invisible Object window.

Place the Edit component to input or display the database file path and the Button component to receive commands from the user appropriately as shown in the example figure. Add the Grid component and the Dataset component to check whether the database is normally opened.

Components and objects used to configure the screen are as follows.

Write the onload event function to check whether the database is connected after loading the form.

/* onload Event Function of Form */
this.sample_litedb_03_onload = function(obj:nexacro.Form,e:nexacro.LoadEventInfo)
{
	this.LiteDBConnection00.isConnected();
};

Set the value property of the added Edit component to '%USERAPP%File/nexacro_edu_db.sqlite'. This file is an SQLite file, which is provided as a sample with the project. It can be checked in the File directory of Project Explorer in Nexacro Studio.

Open the editor in the innerdataset property of the ExtCombo component to set innerdataset. Enter the following SQL query statement to be used in the example.

Set the properties of the ExtCombo component as follows. Bind innerdataset and set the pop-up window form to be opened when ExtCombo is clicked in the popupurl property.

First, write the onclick event function for each of two Query buttons as shown in the example below.

/* Query Button onclick Event Function */
this.btn_query_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{	
	this.execute_query(this.ExtCombo00.text);
};

The execute_query used in the onclick event function above is the function that analyzes the input query statement and determines which method to call. This is because the method that executes the query differs depending on the query statement, so the executeQuery method should be used for the select statement, and the executeUpdate method should be used for the insert, update, and delete statements.

/* execute_query Function */
this.execute_query = function(strQuery)
{
	this.LiteDBStatement00.set_query(strQuery);
	var arrString = this.LiteDBStatement00.query.split(" ");	
	
	switch(arrString[0])
	{
		case "select":
			this.LiteDBStatement00.executeQuery();
			break;
			
		case "insert":
		case "update":
		case "delete":
			this.LiteDBStatement00.executeUpdate();
			break;
			
		default:
			alert(arrString[0] + " is illegal query statement.");
	}
}

/* onclick Event Function of Begin Button */
this.btn_begin_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBConnection00.begin();	
};

/* onclick Event Function of Commit Button */
this.btn_commit_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBConnection00.commit();	
};

/* onclick Event Function of Rollback Button */
this.btn_rollback_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.LiteDBConnection00.rollback();	
};

Write the function to process the event that occurred in the LiteDBConnection object. When calling the LiteDBConnection method is successful, the onsuccess event occurs, and then you can check which method was called through the e.reason value.

/* LiteDBConnection Object onsuccess Event Function */
this.LiteDBConnection00_onsuccess = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBEventInfo)
{
	switch(e.reason)
	{
		case 1:	// LiteDBConnection begin()
			this.btn_begin.set_enable(false);
			this.btn_commit.set_enable(true);
			this.btn_rollback.set_enable(true);
			this.stt_transaction.set_visible(true);
			alert("Transaction setup succeeded.");			
			break;
			
		case 2:	// LiteDBConnection close()
			this.Grid00.set_visible(false);
			this.btn_query.set_enable(false);
			this.btn_begin.set_enable(false);
			this.btn_commit.set_enable(false);
			this.btn_rollback.set_enable(false);
			this.stt_transaction.set_visible(false);
			break;
			
		case 3: // LiteDBConnection commit()
			this.btn_commit.set_enable(false);
			this.btn_rollback.set_enable(false);		
			this.stt_transaction.set_visible(false);
			this.btn_begin.set_enable(true);
			alert("Commit succeeded.");			
			this.LiteDBStatement00.executeQuery("select * from tb_pos");
			break;
			
		case 4: // LiteDBConnection isConnected()
			alert("Already connected to DB.");
			break;
			
		case 5: // LiteDBConnection open()
			this.Grid00.set_visible(true);
			this.btn_query.set_enable(true);
			this.btn_begin.set_enable(true);

			this.LiteDBStatement00.set_ldbconnection(this.LiteDBConnection00);
			this.LiteDBStatement00.executeQuery("select * from tb_pos");
			break;
			
		case 6:	// LiteDBConnection rollback()
			this.btn_commit.set_enable(false);
			this.btn_rollback.set_enable(false);		
			this.stt_transaction.set_visible(false);
			this.btn_begin.set_enable(true);
			alert("Rollback succeeded.");		
			this.LiteDBStatement00.executeQuery("select * from tb_pos");
			break;
			
		default:
			break;
	}
};

When calling the LiteDBConnection method fails, the onerror event occurs, and then you can check which method an error occurred when executing through the e.statuscode value.

/* LiteDBConnection Object onerror Event Function */
this.LiteDBConnection00_onerror = function(obj:nexacro.LiteDBConnection,e:nexacro.LiteDBErrorEventInfo)
{	
	switch(e.statuscode.toString())
	{
		case "1201":	//DB response timeout error
			alert("DB response timeout.");
			break;
			
		case "1202":	//LiteDBConnection begin() error
			alert("Transaction setup failed.");
			break;
			
		case "1203":	//LiteDBConnection close() error
			alert("DB close failed.");
			break;
			
		case "1204":	//LiteDBConnection commit() error
			alert("Commit failed.");
			break;
			
		case "1205":	//LiteDBConnection isConnected() error
			this.LiteDBConnection00.set_datasource(this.edt_dbfile.value);
			this.LiteDBConnection00.open();				
			break;
			
		case "1206":	//LiteDBConnection open() error
			alert("DB open failed.");
			break;
			
		case "1207":	//LiteDBConnection rollback() error
			alert("Rollback failed.");
			break;
			
		case "00001":	//Parameter setting error
			alert("Parameter setting error.");
			break;
			
		default:		//Unknown error		
			var strResult = "[LiteDBConnection00_onerror]";	
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;			
			alert(strResult);				
	}
	
};

Write the function to process the event that occurred in the LiteDBStatement object. When calling the LiteDBStatement method is successful, the onsuccess event occurs, and then you can check which method was called through the e.reason value.

/* LiteDBStatement Object onsuccess Event Function */
this.LiteDBStatement00_onsuccess = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBEventInfo)
{	
	switch(e.reason)
	{
		case 7:	// LiteDBStatement executeQuery()
			this.ds_sqlite.copyData(e.returnvalue);
			this.ds_sqlite.set_keystring("S:+POS_CD+POS_NAME");
			this.Grid00.createFormat();		
			break;
			
		case 8:	// LiteDBStatement executeUpdate()
			this.LiteDBStatement00.executeQuery("select * from tb_pos");
			break;
			
		case 9:	// LiteDBStatement close()
			alert("Query execution stopped.");
			break;
			
		default:
			break;
	}	
};

When calling the LiteDBStatement method fails, the onerror vent occurs, and then you can check through which method an error occurred when executing through the e.statuscode value.

/* LiteDBStatement Object onerror Event Function */
this.LiteDBStatement00_onerror = function(obj:nexacro.LiteDBStatement,e:nexacro.LiteDBErrorEventInfo)
{
	switch(e.statuscode.toString())
	{
		case "1210":	//LiteDBStatement executeQuery() error
			alert("LiteDBStatement executeQuery() error.");
			break;
			
		case "1211":	//LiteDBStatement executeUpdate() error
			alert("LiteDBStatement executeUpdate() error.");
			break;
			
		case "00001":	//Parameter setting error
			alert("LiteDBStatement parameter setting error.");
			break;
			
		default:		//Unknown error		
			var strResult = "[LiteDBStatement00_onerror]";	
			strResult += "\n["+ e.errortype +" "+ e.statuscode +"] "+ e.errormsg;
			strResult += "\ne.ldberrorcode: "+ e.ldberrorcode;
			strResult += "\ne.ldberrormsg: "+ e.ldberrormsg;			
			alert(strResult);
	}
};

When the screen is loaded, the database is automatically connected. Check whether the connection is normally performed and the TB_POS table data is displayed on the Grid.

Set the transaction in the opened database by touching the Begin button of the Transaction group. If the transaction configuration is successful, the message "The transaction has been set" is displayed.

Select the query statement and touch the Query button. If the query is executed normally, then check whether the execution result is reflected in the Grid.

After completing the query execution, touch the Commit button to reflect the changes to the database, or touch the Rollback button to cancel. If you touch the Commit or Rollback button, then the transaction setting is canceled.