Promotic
WikipediaLinkedInYoutubeTwitterFacebook

PmAdo - Practical examples

See: Object PmAdo, PmAdo - 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 at application launch 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 always necessary to verify whether the operation was completed successfully or failed.
 
Using PmAdo 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 PmAdo 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:
VBScriptSelect and copy to clipboard

Dim oAdo, oRs, sSQL, bRes, vValues

Set oAdo = pMe.Pm("../PmAdo")
sSQL = "select * from table1"

'open the database, if not open
If Not oAdo.DbIsOpen() Then
  bRes = oAdo.DbOpen()
Else
  bRes = true
End If

'if the database is open, then continue
If bRes Then
  'opening the corresponding table
  Set oRs = oAdo.RsOpen("", sSQL, "")
  'checking whether the opening operation was comleted successfully
  If Pm.IsValid(oRs) Then
    'checking whether the table is not empty
    If oRs.EOF And oRs.BOF Then
      'Pm.Debug "Table (recordset) contains no records"
      pResult = -1
    Else
      'if the table is not empty then it can be read as a 2-dimensional array
      vValues = oRs.GetRows()
      pResult = vValues
    End If
  Else
    'Pm.Debug "Table opening error"
    pResult = -1
  End If
Else
  'Pm.Debug "Database opening error"
  pResult = -1
End If
 

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 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 database
- (optional: close database)
This data writing approach uses methods of objects PmAdo, AdoRecordset etc.
 
The first approach is more considerate to the application and the databse 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 Editing the current record). Therefore this approach is recommended to be used only with tables that contain only a relatively 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:
VBScriptSelect and copy to clipboard

Dim oAdo, oRs, sSQL, bRes, sTime

Set oAdo = pMe.Pm("../PmAdo")

'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 Not oAdo.DbIsOpen() Then
  bRes = oAdo.DbOpen()
Else
  bRes = true
End If

'if the database is open, then continue
If bRes Then
  'execution of SQL command
  Set oRs = oAdo.DbExecute("", sSQL, "")

  'checking whether adding operation was completed successfully
  If oAdo.LastErr = 0 Then
    '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"
    pResult = 1
  Else
    Pm.Debug "Error of writing new record into the database"
    pResult = -1
  End If
Else
  Pm.Debug "Database opening error"
  pResult = -1
End If
 
Usage of the command update:
VBScriptSelect and copy to clipboard

Dim oAdo, oRs, sSQL, bRes, sTime

Set oAdo = pMe.Pm("../PmAdo")

'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 Not oAdo.DbIsOpen() Then
  bRes = oAdo.DbOpen()
Else
  bRes = true
End If

'if the database is open, then continue
If bRes Then
  'execution of SQL command
  Set oRs = oAdo.DbExecute("", sSQL, "")

  'checking whether adding operation was completed successfully
  If oAdo.LastErr = 0 Then
    '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"
    pResult = 1
  Else
    Pm.Debug "Error of record modification in the database"
    pResult = -1
  End If
Else
  Pm.Debug "Database opening error"
  pResult = -1
End If
 
Usage of the AddNew method:
VBScriptSelect and copy to clipboard

Dim oAdo, oRs, sSQL, bRes

Set oAdo = pMe.Pm("../PmAdo")
sSQL = "select * from table1"

'open the database, if not open
If Not oAdo.DbIsOpen() Then
  bRes = oAdo.DbOpen()
Else
  bRes = true
End If

'if the database is open, then continue
If bRes Then
  'opening the corresponding table
  Set oRs = oAdo.RsOpen("", sSQL, "lock:optimistic;")

  'checking whether the opening operation was comleted successfully
  If Pm.IsValid(oRs) Then
    '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 Then
      '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"
      pResult = 1
    Else
      Pm.Debug "Error of writing new record into the database"
      pResult = -1
    End If
  Else
    Pm.Debug "Table opening error"
    pResult = -1
  End If
Else
  Pm.Debug "Database opening error"
  pResult = -1
End If
 
Editing the current record:
VBScriptSelect and copy to clipboard

Dim oAdo, oRs, sSQL, bRes, nTime

Set oAdo = pMe.Pm("../PmAdo")
nTime = Pm.Time
sSQL = "select * from table1"

'open the database, if not open
If Not oAdo.DbIsOpen() Then
  bRes = oAdo.DbOpen()
Else
  bRes = true
End If

'if the database is open, then continue
If bRes Then
  'opening the corresponding table
  Set oRs = oAdo.RsOpen("", sSQL, "lock:optimistic;")

  'checking whether the opening operation was comleted successfully
  If Pm.IsValid(oRs) Then
    'checking whether the table is not empty
    If oRs.EOF And oRs.BOF Then
      'Pm.Debug "Table (recordset) contains no records"
      pResult = -1
    Else
      oRs.MoveFirst
      Do
        If oRs.Fields.Item("ctime").Value=nTime Then
          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 - PmAdo/edit"
          oRs.Update

          If oRs.Pm_LastErr=0 Then
            '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"
            pResult = 1
          Else
            Pm.Debug "Error of record modification in the database"
            pResult = -1
          End If
        End If
        oRs.MoveNext

      Loop While Not oRs.EOF
    End If
  Else
    Pm.Debug "Table opening error"
    pResult = -1
  End If
Else
  Pm.Debug "Database opening error"
  pResult = -1
End If
PROMOTIC 8.3.27 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