PmaAdo - Practical examples

See: the PmaAdo object, PmaAdo - Deatiled object description

The database connection operation can be time consuming. Therefore often connecting/disconnecting a database may cause application overload. That is why it is recommended to connect the database after the application is launched and disconnect the database when the application is stopping.

It is recommended to close the database after all requested database operations are completed (usually after application stopping).

While working with databases it is necessary to verify whether the operation was completed successfully or failed.

Using PmaAdo for database operations in the PROMOTIC system can be divided into two basic approaches:

- reading the data in order to display it

- writing/changing the data in the database

Reading the data into the application

The order of operations while reading the data into the application by the PmaAdo object:

- connect to the database

- open the corresponding table

- process data in the application

- close current table

- (optional: close the database)

The mentioned operations are demonstrated in the following example:

Example:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oMap, oRs, sSQL, bRes, aValues;


oAdo = pMe.Pm("../PmaAdo");

sSQL = "select * from table1";


// Opening the database, if not open:

if (!oAdo.DbIsOpen())

{

	bRes = oAdo.DbOpen();

}

else

{

	bRes = true;

}


// If the database is open, then continue

if (bRes)

{

	// Opening the corresponding table

	oMap = oAdo.RsOpen("", sSQL, "return:map;");

	// Detects whether the opening operation was completed successfully

	if (oMap.ErrorCode == 0)

	{

		oRs = oMap.Result;

		// Detects whether the table is not empty

		if (oRs.EOF && oRs.BOF)

		{

			// Pm.Debug("Table (recordset) contains no records");

			return -3;

		}

		else

		{

			// If the table is not empty then it can be read as a 2-dimensional array

			aValues = Pm.CreatePmArray();

			aValues.LoadFromVbArray(oRs.GetRows());

			return aValues;

		}

	}

	else

	{

		// Pm.Debug("Table opening error");

		return -2;

	}

}

else

{

	// Pm.Debug("Database opening error");

	return -1;

}

Data writing from the application to the database

There are two possible ways of data writing:

1. Data writing into the table without previously opening it

2. Open the table and add a new record into this open table

On writing/changing the data the first way, the following steps must be completed:

- connect to the database

- write/change data in the database

- (optional: close database)

This data writing approach uses SQL statements INSERT or UPDATE.

On writing/changing the data the second way, the following steps must be completed:

- Connect to the database

- Open table and eventually find the corresponding record

- write/change data in the database

- (optional: close database)

This data writing approach uses methods of objects PmaAdo, AdoRecordset etc.

The first approach is more considerate to the application and the database because it is not necessary to open each table and load the corresponding table rows into the memory. New record can be added just by using the INSERT INTO statement (see The usage of the statement insert into). Current record can be changed by the UPDATE statement (see The usage of the statement update). These SQL statements must be written in the SQL language syntax of the corresponding database system. This approach therefore requires knowledge of the SQL language of the corresponding database that is being used.

The second approach does not require detailed knowledge of the SQL language but it is more demanding (especially from the memory point of view) on the application and the database, because adding a new record requires opening (=loading) the table (see The usage of the "AddNew" method). When changing the data it is necessary to find the corresponding record that can then be modified (see Edit the current record). Therefore this approach is recommended to be used only with tables that contain only a small number of records. It is also possible to modify the SQL query for table opening so it returns only a selected portion of records (ideally only a single record) - e.g. by using complex SQL queries e.g. as this:

"select * from table1 where ctime = (select max(ctime) from table1)"

For this purpose it is not recommended using the limiting clauses of the select TOP, LIMIT type etc.

The usage of the statement insert into:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oMap, sSQL, bRes, sTime;


oAdo = pMe.Pm("../PmaAdo");


// Time format for the MS SQL database

sTime = Pm.FormatDate(Pm.Time, 20);


// Time format for the Oracle database

sTime = Pm.FormatDate(Pm.Time, 21);


// Time format for the MySQL database

sTime = Pm.FormatDate(Pm.Time, 22);


sSQL = "insert into table1(ctime, ctemperature, cpress, cstatus, cnote) values (" + sTime + ", 12.5, 1.5, 5, 'New record - INSERT')";


// Opening the database, if not open:

if (!oAdo.DbIsOpen())

{

	bRes = oAdo.DbOpen();

}

else

{

	bRes = true;

}


// If the database is open, then continue

if (bRes)

{

	// Execution of the SQL statement

	oMap = oAdo.DbExecute("", sSQL, "return:map;");


	// Checking whether adding operation was completed successfully

	if (oMap.ErrorCode == 0)

	{

		// If the query result is a set of records, then it is possible to process it further

		// Pm.Debug("New record was successfully written to the database");

		return 1;

	}

	else

	{

		// Pm.Debug("Error of writing new record to the database");

		return -2;

	}

}

else

{

	// Pm.Debug("Database opening error");

	return -1;

}

The usage of the statement update:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oMap, sSQL, bRes, sTime;


oAdo = pMe.Pm("../PmaAdo");


// Time format for the MS SQL database

sTime = Pm.FormatDate(Pm.Time, 20);


// Time format for the Oracle database

sTime = Pm.FormatDate(Pm.Time, 21);


// Time format for the MySQL database

sTime = Pm.FormatDate(Pm.Time, 22);


sSQL = "update table1 set ctemperature = 26.5, cPress = 1.83, cstatus = 8, cnote = 'change record UPDATE' where ctime = " + sTime;


// Opening the database, if not open:

if (!oAdo.DbIsOpen())

{

	bRes = oAdo.DbOpen();

}

else

{

	bRes = true;

}


// If the database is open, then continue

if (bRes)

{

	// Execution of the SQL statement

	oMap = oAdo.DbExecute("", sSQL, "return:map;");


	// Checking whether adding operation was completed successfully

	if (oMap.ErrorCode == 0)

	{

		// If the query result is a set of records, then it is possible to process it further

		// Pm.Debug("Current record was successfully modified in the database");

		return 1;

	}

	else

	{

		// Pm.Debug("Error of record modification in the database");

		return -2;

	}

}

else

{

	// Pm.Debug("Database opening error");

	return -1;

}

The usage of the "AddNew" method:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oMap, oRs, sSQL, bRes;


oAdo = pMe.Pm("../PmaAdo");

sSQL = "select * from table1";


// Opening the database, if not open:

if (!oAdo.DbIsOpen())

{

	bRes = oAdo.DbOpen();

}

else

{

	bRes = true;

}


// If the database is open, then continue

if (bRes)

{

	// Opening the corresponding table

	oMap = oAdo.RsOpen("", sSQL, "lock:optimistic;return:map;");


	// Detects whether the opening operation was completed successfully

	if (oMap.ErrorCode == 0)

	{

		oRs = oMap.Result;

		// If you want to add a new record, then it is not necessary to check whether the table is empty or not.

		oRs.AddNew();

		oRs.Fields.Item("ctime").Value = Pm.Time;

		oRs.Fields.Item("ctemperature").Value = 12.5;

		oRs.Fields.Item("cpress").Value = 1.5;

		oRs.Fields.Item("cstatus").Value = 5;

		oRs.Fields.Item("cnote").Value = "New record - AddNew";

		oRs.Update();


		if (oRs.Pm_LastErr == 0)

		{

			// If the query result is a set of records, then it is possible to process it further

			// Pm.Debug("New record was successfully written to the database");

			return 1;

		}

		else

		{

			// Pm.Debug("Error of writing new record to the database");

			return -3;

		}

	}

	else

	{

		// Pm.Debug("Table opening error");

		return -2;

	}

}

else

{

	// Pm.Debug("Database opening error");

	return -1;

}

Edit the current record:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oMap, oRs, sSQL, bRes, nTime;


oAdo = pMe.Pm("../PmaAdo");

nTime = Pm.Time;

sSQL = "select * from table1";


// Opening the database, if not open:

if (!oAdo.DbIsOpen())

{

	bRes = oAdo.DbOpen();

}

else

{

	bRes = true;

}


// If the database is open, then continue

if (bRes)

{

	// Opening the corresponding table

	oMap = oAdo.RsOpen("", sSQL, "lock:optimistic;return:map;");


	// Detects whether the opening operation was completed successfully

	if (oMap.ErrorCode == 0)

	{

		oRs = oMap.Result;

		// Detects whether the table is not empty

		if (oRs.EOF && oRs.BOF)

		{

			// Pm.Debug "Table (recordset) contains no records"

			return -4;

		}

		else

		{

			oRs.MoveFirst();

			do

			{

				if (oRs.Fields.Item("ctime").Value ==nTime)

				{

					oRs.Fields.Item("ctemperature").Value = 26.5;

					oRs.Fields.Item("cpress").Value = 1.83;

					oRs.Fields.Item("cstatus").Value = 8;

					oRs.Fields.Item("cnote").Value = "Change record - PmaAdo/edit";

					oRs.Update();


					if (oRs.Pm_LastErr == 0)

					{

						// If the query result is a set of records, then it is possible to process it further

						// Pm.Debug("Current record was successfully modified in the database");

						return 1;

					}

					else

					{

						// Pm.Debug("Error of record modification in the database");

						return -3;

					}

				}

				oRs.MoveNext();


			} while (!oRs.EOF);

		}

	}

	else

	{

		// Pm.Debug("Table opening error");

		return -2;

	}

}

else

{

	// Pm.Debug("Database opening error");

	return -1;

}