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 the application is stopped).

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, oRs, sSQL, bRes, vValues;


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

sSQL = "select * from table1";


// open 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

	oRs = oAdo.RsOpen("", sSQL, "");

	// Detects whether the opening operation was completed successfully

	if (Pm.IsValid(oRs))

	{

		// Detects whether the table is not empty

		if (oRs.EOF && oRs.BOF)

		{

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

			return -1;

		}

		else

		{

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

			vValues = oRs.GetRows();

			return vValues;

		}

	}

	else

	{

		// Pm.Debug "Table opening error"

		return -1;

	}

}

else

{

	// Pm.Debug "Database opening error"

	return -1;

}

Writing data from the application to the database

There are two possible ways of writing the data:

1. Writing data into the table without previously opening it

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

When 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.

When 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 records 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 like select TOP, LIMIT etc.

The usage of the statement insert into:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oRs, 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')";


// open 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 SQL statement

	oRs = oAdo.DbExecute("", sSQL, "");


	// checking whether adding operation was completed successfully

	if (oAdo.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 -1;

	}

}

else

{

	Pm.Debug("Database opening error");

	return -1;

}

The usage of the statement update:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oRs, 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;


// open 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 SQL statement

	oRs = oAdo.DbExecute("", sSQL, "");


	// checking whether adding operation was completed successfully

	if (oAdo.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 -1;

	}

}

else

{

	Pm.Debug("Database opening error");

	return -1;

}

The usage of the "AddNew" method:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oRs, sSQL, bRes;


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

sSQL = "select * from table1";


// open 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

	oRs = oAdo.RsOpen("", sSQL, "lock:optimistic;");


	// Detects whether the opening operation was completed successfully

	if (Pm.IsValid(oRs))

	{

		// 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 -1;

		}

	}

	else

	{

		Pm.Debug("Table opening error");

		return -1;

	}

}

else

{

	Pm.Debug("Database opening error");

	return -1;

}

Edit the current record:

JavaScriptVBScriptSelect and copy to clipboard



var oAdo, oRs, sSQL, bRes, nTime;


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

nTime = Pm.Time;

sSQL = "select * from table1";


// open 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

	oRs = oAdo.RsOpen("", sSQL, "lock:optimistic;");


	// Detects whether the opening operation was completed successfully

	if (Pm.IsValid(oRs))

	{

		// Detects whether the table is not empty

		if (oRs.EOF && oRs.BOF)

		{

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

			return -1;

		}

		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 -1;

					}

				}

				oRs.MoveNext();


			} while (! oRs.EOF);

		}

	}

	else

	{

		Pm.Debug("Table opening error");

		return -1;

	}

}

else

{

	Pm.Debug("Database opening error");

	return -1;

}