Promotic
WikipediaLinkedInYoutubeTwitterFacebook

PmaAdo - Practical examples

See: Object PmaAdo, 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 starting the application 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 data into the application

The sequence of operations while reading the data into the PROMOTIC 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 comleted 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 application into database

There are two possible ways of writing the data:
1. Writing data into the table without previously opening it
2. Opening the table and adding a new record into this open table
 
When writing/changing the data the first way, the following steps must be completed:
- connect to database
- write/change data in the database
- (optional: close database)
This data writing approach uses SQL commands INSERT or UPDATE.
 
When writing/changing the data the second way, the following steps must be completed:
- connect to database
- open table and event. 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 command (see Usage of the command insert into). Current record can be changed by the UPDATE command (see Usage of the command update). These SQL commands must be written in the syntax of SQL language of the corresponding databse 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 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..
Usage of the command insert into:
JavaScriptVBScriptSelect and copy to clipboard

var oAdo, oRs, sSQL, bRes, sTime;

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

//time format for MSSQL
sTime = Pm.FormatDate(Pm.Time, 20);

//time format for Oracle
sTime = Pm.FormatDate(Pm.Time, 21);

//time format for MySQL
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 command
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 into the database");
return 1;
}
else
{
Pm.Debug("Error of writing new record into the database");
return -1;
}
}
else
{
Pm.Debug("Database opening error");
return -1;
}
 
Usage of the command update:
JavaScriptVBScriptSelect and copy to clipboard

var oAdo, oRs, sSQL, bRes, sTime;

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

//time format for MSSQL
sTime = Pm.FormatDate(Pm.Time, 20);

//time format for Oracle
sTime = Pm.FormatDate(Pm.Time, 21);

//time format for MySQL
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 command
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;
}
 
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 comleted 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 into the database");
return 1;
}
else
{
Pm.Debug("Error of writing new record into 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 comleted 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;
}

History:
Pm8.03.21: Created
PROMOTIC 9.0.7 SCADA system documentation - MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice