| Object-Oriented Database Access | ||
|---|---|---|
 | Chapter 13. Using PPCS in SIMPOL |  |
Object-Oriented Database Access
Unlike the standard command-based approach provided with SBL, dBase, and other products, in SIMPOL database access is done via objects. To use the database access in SIMPOL easily and effectively it is very useful to learn about the logic behind the decisions on how the database objects were designed. Once the underlying system is clear, it will feel very natural using the database objects to accomplish database-oriented tasks in SIMPOL. Also, the object-oriented approach will completely eliminate many sources of errors that occurred in SBL programs in the past. As is true in general in SIMPOL, there is nothing global about database objects. There is no current record, current file, current index, etc. Instead, it is possible to have as many current records as required, simply by having a different variable (or element in a set, or element in an array) that references each record object.
In the beginning, there is the ppcstype1 object. This is used to open tables from various backend PPCS servers. Unlike the implementation in SBL, it is only necessary to create one of these per communication method, since the connector is not specific to a target backend (in the case of UDP connections — in the case of serial connections it would be necessary since this is a hardware limitation). Using the ppcstype1 object, a database table is opened from a backend and a reference to the database file is assigned to a ppcstype1file variable. At this point we can already retrieve records in sequential order from the database table, either starting at the beginning or the end. The database file object holds all of the information necessary to analyze the structure of the database table. There is a firstfield property that holds a reference to the first field in the database table definition. It contains a property called next that holds a reference to the next database field in the file definition. The next property of the last field in the file definition will contain a reference to the first field in the definition thus forming a ring. A similar ring exists for the indexes.
When a record is selected from the database using either an index object, a database table object,
or a record object, then a ppcstype1record object is returned. This object contains
the data, but has no concept of the actual file description, fields, etc. The reason for this is
fairly technical, but essentially for it to do so, it would have needed to be a record object for
a specific data type created at the time the database was opened that was designed specifically for
that database file and would have made it impossible to easily use the same variable for records
from different database tables. As such, to access the data for a given field from a record object
it is necessary to make use of the ppcstype1field object for the field from which the
information is to be read (or to which the data is to be assigned). There is a
get() method and a set() method for reading and
writing data from and to the various fields of a record. Both take a field reference as a parameter
although the set() method also takes a value as its second parameter.
Another approach is to use the member operator. This was specially implemented for accessing the
data in the record object in a visually more elegant way, but has several disadvantages, such as
the fact that it can't use a variable, and if a field name is passed that is incorrect (including
incorrect case) then it will cause a runtime error that cannot currently be trapped. The example
below will demonstrate accessing a record from a table via PPCS and reading the values from the
fields.
The beginning of the program starts as usual in the function main(). We begin
by declaring the various variables that will be needed for this function. The remainder of the
description can be found directly in the comments of the program itself.
function main()
string sResult
integer iErrnum
ppcstype1 ppcs
ppcstype1file f
ppcstype1record r
ppcstype1field fld
ppcstype1index idx
boolean bFound
// iErrnum MUST be initialized or there will be
// no object in which to return the result
iErrnum = 0
// We now attempt to create a ppcstype1 object which should
// almost always work. In this case we pass .nul to the
// udpport parameter because we don't care which port we
// get, we just want one. We have to pass .nul to the named
// parameter udpport since otherwise the function won't know
// whether we want a UDP or a serial (not yet supported)
// connection.
ppcs =@ ppcstype1.new(udpport=.nul, error=iErrnum, \
username="example1")
// We can test for an error value here or for the ppcs
// variable containing .nul
if ppcs =@= .nul
sResult = "Error number " + .tostr(iErrnum, 10) + \
" creating ppcs object!{d}{a}"
else
// Now we are going to open the database table using
// the ppcstype1 object
f =@ ppcs.openudpfile("ppcs.superbase.co.uk:1280", \
"CUST", error=iErrnum)
if f =@= .nul
sResult = "Error number " + .tostr(iErrnum, 10) + \
" opening file 'CUST'!{d}{a}"
else
// We got this so far so we have the table open
// Now we are going to locate the reference to the
// index we want to use for selecting the record.
// The following loop will start at the firstindex
// and then go around until it either finds the desired
// index or returns to the first index in the ring.
bFound = .false
idx =@ f.firstindex
while idx !@= .nul
if idx.field.name == "LastFirstName"
bFound = .true
else
idx =@ idx.next
end if
end while idx =@= f.firstindex or bFound
if not bFound
sResult = "Index 'LastFirstName' not found!{d}{a}"
else
// We found the index for our test so now we select
// the record that we are looking for using the
// selectkey() method.
r =@ idx.selectkey("Johnson, Amanda", error=iErrnum)
// If the selection fails then r will not refer to an
// object because we did not pass a found parameter to
// the method. When using an inexact match that we
// expect might fail (like looking for Joh*) we would
// pass a found parameter so that we always get the
// closest matching record returned.
if r =@= .nul
sResult = "Error number " + .tostr(iErrnum, 10) + \
" retrieving record!{d}{a}"
else
// We found the record we were looking for, so let
// us now output the contents of the record. This
// time we will use the get() method and a loop that
// tests the datatype of the field to allow us to
// format it properly. We loop around the fields in
// the file retrieving each field's name and value
// and then add it to the result string.
fld =@ f.firstfield
sResult = "Record for Amanda Johnson:{d}{a}"
while
if fld.datatype =@= string
sResult = sResult + " " + fld.name + ": " + \
r.get(fld) + "{d}{a}"
else if fld.datatype =@= integer or \
fld.datatype =@= date or \
fld.datatype =@= time
sResult = sResult + " " + fld.name + ": " + \
.tostr(r.get(fld), 10) + "{d}{a}"
else if fld.datatype =@= number
sResult = sResult + " " + fld.name + ": " + \
.tostr(.fix(r.get(fld), 100), 10) + \
"{d}{a}"
end if
fld =@ fld.next
end while fld =@= f.firstfield
// As you can see from the code above, we treat
// dates and times as if they were integers. This
// is because there are no built in functions to
// format a date, a time, or a datetime. The reason
// is that there are too many different ways this
// might be done for different locales and it is
// best solved with a SIMPOL-based library. Such a
// library is part of the current distribution but
// not relevant for this example.
// Now let's get the next record in the same index
// and output that.
r =@ r.select(error=iErrnum)
if r =@= .nul
sResult = sResult + "Error number " + \
.tostr(iErrnum, 10) + \
" retrieving record!{d}{a}"
else
// Again we succeeded in getting the next record
// without error, so this time we will output the
// fields expressly using the member operator for
// the ppcstype1record object. The advantage to
// using the member operator is the code looks
// easier to understand. The disadvantage is that
// if the field name changes the code will break
// whereas the previous version would not. The
// previous version neither knows nor cares what
// the fields are called.
sResult = sResult + "{d}{a}Next Record:{d}{a}"
sResult = sResult + " RecNum: " + \
.tostr(r!RecNum, 10) + "{d}{a}"
sResult = sResult + " Firstname: " + \
r!Firstname + "{d}{a}"
sResult = sResult + " Lastname: " + \
r!Lastname + "{d}{a}"
sResult = sResult + " Organization: " + \
r!Organization + "{d}{a}"
sResult = sResult + " Street: " + \
r!Street + "{d}{a}"
sResult = sResult + " City: " + r!City + "{d}{a}"
sResult = sResult + " Country: " + \
r!Country + "{d}{a}"
sResult = sResult + " LastFirstName: " + \
r!LastFirstName + "{d}{a}"
sResult = sResult + " CreditLimit: " + \
.tostr(.fix(r!CreditLimit, 100), 10) + \
"{d}{a}"
sResult = sResult + " Balance: " + \
.tostr(.fix(r!Balance, 100), 10) + \
"{d}{a}"
end if
end if
end if
end if
end if
end function sResult
As we can see from the previous program, there is a little bit more overhead when accessing the parts of the database programmatically from SIMPOL as compared with SBL, but there is absolutely no possibility of errors in the SIMPOL method, since the record object is always a known quantity. There are also a larger number of ways to write things so that the program code does not need to know too much about the actual data to still do its job. This allows us to write more generic and library code that gradually adds to our ability to do a job more quickly.
Almost every bit of the preceding program could be applied to accessing a database using the sbme1, simply by changing the data types and the opening method. In SIMPOL a great deal of effort has been invested to ensure that program code will be able to deal with different database types without needing to be greatly rewritten.