This tutorial is in beta while we work on a better LogHandler, and will be updated accordingly
This program provides an example of how we can use SIMPOL to access database files via the Superbase Peer-to-Peer Client/Server protocol. This means we need to first launch the server which will contain the database, and then connect to the server containing the database so we can edit records within the table. In this program we are going to do the following to our database:
- Connect to a databse and find an index
- Find a record
- Add a record to the database
- Update a record
- Show a record
- Deleting a record
The server configuration and launch of the server is covered here and should be done before continuing with this program
We are going to be using two libraries, one old and one new, first and most importantly
ppcstype1 is the library that will allow us to actually connect to the server and use our database. The second library we are going to be using is
LogManager this is going to be our log handler and will allow us to see what is happening in our program. At the end of the process you should have a log file with the following information in it:
After you have created your new project, and before you actually start programming we have to configure the project settings. For this project this is quite simple, as we only need to add the LogManager library (found under
SIMPOL\lib\logmanager.sml) The Project settings tab should now look like this:
This program consists of the main function and one library, we are gonna walk our way through the main function and then explore what the library functions
Before we even get to the
main function, we will need to include our log-handler library, this is an updated version of the inbuilt LogManager it is available for download here and at the bottom of this page and is inserted first into the bin folder in your project and then into the program as follows:
Declaration and Initialisation
Within the actual main function the first thing we need to do is to declare the variables we need:
LogHandler log_handler; string user_name; string result; integer errno; ppcstype1 ppcs;
The first of these, LogHandler, will be used to log the interactions between the program and the database.
user_name will be used to declare the username with which you will access the database,
resultwill be used as the return variable for the main function, allowing us to see errors within the IDE.
errorno is an integer which will be used for debugging, and finally
ppcs will be used to declare a
ppcstype1 object which allows us to actually read and write to the database although not allowing us to modify the database definiton or create or modify database tables
user_name = "test"; log_handler =@ LogHandler.new("ppcs-client.log"); log_handler.info_message("Start PPCS client application"); errno = 0; ppcs =@ ppcstype1.new(udpport=.nul, error=errno, username=user_name);
Two important things in this section of code: we are first initialising a LogHandler object, the string we parse into the object will be used as the file name for the log file. The precise mechanics of LogHandler will be discussed later for now all you need to know is that it will write to a file and allow us to debug the program using functions such as
info_message. The second thing we are doing is initalising a PPCS object which we will use to connect to our database server. To initalise the PPCS object we have to set values for the following:
username. The actual values for these are not important so for this example we are setting the UDP port to
.nul, the error to
0 and the username to any string we like (in this case test), if these are not set the program will throw errors and not execute correctly.
Connecting to the Database and finding an index
Checking the PPCS Object
Now that we have created our PPCS object we want to actually connect to server containing the database, right. However, before we can do this we should check whether we successfully created our ppcs object, this is done using the following code:
if ppcs =@= .nul result = log_handler.error_message("Cannot create ppcs object. Error code: " + \ .tostr(errno, 10)); else
This code checks whether
ppcs refers to a
.nul object, this is done using
=@= operator. The
=@= operator returns true if both operands are references to the same object, or are both nul object references, otherwise it will return as false. This means that if anything has gone wrong with the initialisation of ppcs it will give us a nice error message. To allow this nice representation we have to convert the error code into a string, this is done using
.tostr(errno, 10) where
errno is the error code and
10 represents the base in which this number is written. This error message will be both logged using our log handler and displayed within the IDE, this is done to ease debugging. To display it in the IDE we are going assigning the error message to our return variable
Connecting to the database
Once we confirmed that the PPCS object has initalised we can open the database on our PPCS server
else ppcstype1file file; string db_name; db_name = "user"; file =@ ppcs.openudpfile("127.0.0.1:4000", db_name, error=errno);
To do this we need to first declare a
ppcstype1file object, in this case called
file, as well as a string for storing the database name, called
db_name. Opening the database on our PPCS server will be done using the
openudpfile function of our ppcs object, we need to parse at a minimum the IP address and port number of the PPCS server, this should by default be
127.0.0.1:4000, we also need to parse in the name of the file we want to open, finally to allow us to better see what error is raised we also need to parse in our error code variable. At this point we are checking whether we have successfully opened the database file
if file =@= .nul result = log_handler.error_message("Cannot open file '" + db_name + \ "'. Error code " + .tostr(errno, 10)); else
This works because if the attempt to open the file is unsuccessful
.nul. If the attempt is successful it will return a ppcstype1file object, which is what we want here
Finding an index
We have now successfully connected to the database and now want to find a specific index, to do this we first need to initialise several variables. We are creating first two objects a
ppcstype1record, and a
ppcstype1index these represent a databsae record and datbase index respectively.
index_name will be used to specify the name of the index we want to look for,
index_found will be used to determine whether we successfully found an index and
index_id will be used to find a record within that index.
ppcstype1record record; ppcstype1index index; string index_name; integer index_id; boolean index_found;log_handler.info_message("Connect to database '" + db_name + "'");
We are then also going to log that we have successfully connected to the database, and set some values to the variables we have just declared.
index_name can be set to anything but for our examples the values below will work best and
index_found should always be set to default to false
index_id = 137; index_name = "id"; index_found = .false;
We will now find out how many records there are in the table and log this information, this step is not required but we are highlighting the existence of such a function.
log_handler.trace_message("Number of records in table: " + .tostr(file.recordcount(), 10));
The first thing we do in this code is to find the first index in our file. This is done using the
firstindex function which refers to the ppcstype1index object that represents the first index in the file, and if there are no indexes on the file then it will refer a
.nul object. We want to do two things now, loop through the database looking for an index with a specified name and to check whether there are any indexes actually present in the database. We can kill two proverbial stones with one stone using the following while loop
index =@ file.firstindex; while index !@= .nul log_handler.trace_message("found index '" + index.field.name + "'"); if index.field.name == index_name index_found = .true; else index =@ index.next; end if end while index =@= file.firstindex or index_found;
.nulobject and will exit when it has either run through the database, and is back to the beginning or
index_foundis true. Inside this while loop we will do several things, first we will log all indexes we have found and we will then check to see whether the index name matches the one we are looking for. If it does we will set index_found to true and thus exit the while loop or we will refer to the next index in the table. The indexes for a file form a closed loop, this means once we have cycled through once it will try and search from the first index again, and we will exit the loop.
Finding a record
In order to find a record we should first check whether the index we are looking in exists, we should then attempt to select the record we want and then check whether this was successful and providing logs of success/failure.
if not index_found result = log_handler.error_message("Index '" + index_name + "' not found!"); else boolean found_one; found_one = .false; record =@ index.selectkey(index_id, error=errno);
We are checking if
index_found is not true (i.e. false) and if we haven’t found the index we want we will log this and display it in the IDE. If however, we have found the index we want we will create a boolean object which we will set to
.false, this will be used later in the program. The next thing we will do is select a record with a specified value in the index,
selectkey will return a ppcstype1record type object if it is successful and
.nul if it is not. This means if we make
record refer to the object returned we can check what object is referenced and if it is
.nul we can log that we did not find the relevant record.
selectfunction which will select by default the first record in the database. We then check whether this was successful, if it was found_one can be set to .true otherwise we should exit this if statement and move onto the next piece of code.
if record =@= .nul log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \ "'. Error code: " + .tostr(errno, 10)); record =@ index.select(error=errno); if record =@= .nul result = log_handler.error_message("Did not find first record. Error code: " + \ .tostr(errno, 10)); else log_handler.trace_message("Start with first record"); found_one = .true; end if else log_handler.trace_message("Start with record with index '" + .tostr(index_id, 10) + "'"); found_one = .true; end if
found_oneshould be set to
.trueas done in the else statement above
Displaying the entry
Once we have found a record we want to display all the fields in it nicely, this is made easier by dealing with the different datatypes that the field could have differently. For this we want a while loop that starts from the first field and loops through all the fields until it has reached the first field again, and inside that an if statement which seperates the different datatypes.
if found_one == .true ppcstype1field field; field =@ file.firstfield while if field.datatype =@= string log_handler.trace_message(" -> " + field.name + ": " + record.get(field)); else if field.datatype =@= integer or \ field.datatype =@= date or \ field.datatype =@= time log_handler.trace_message(" -> " + field.name + ": " + \ .tostr(record.get(field), 10)); else if field.datatype =@= number log_handler.trace_message(" -> " + field.name + ": " + \ .tostr(.fix(record.get(field), 100), 10)); end if field =@ field.next end while field =@= file.firstfield
We are here initially checking if we have actually found a record, we then declare a ppcstype1field object which we will initalise by referring it to the
firstfield function. Once this has been done we can begin to loop through the fields, and then we need to convert the contents of the field into a string so we can parse them into our
trace_message function. For strings this requires no work and we can simply get the value of the field in our record using the
get function. However when we are dealing with an integer, a date or a time we need to convert this to a string using
.tostr before we can parse it. The last type we will address is the number type, this will both be converted using
.tostr but also rounded to the nearest 10th of a fraction using
.fix Once this has been done we move onto the next field and repeat until we have come back to the first field at which point we exit the while loop
At this point we also want to make sure we have gotten all the records with the same index, this is done as follows
while record =@ record.select(error=errno) if record =@= .nul log_handler.trace_message("No more record found. Error code: " + .tostr(errno, 10)); else log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + "; first name: " + \ record!first_name + "; last name: " + record!last_name); end if end while record =@= .nul
The while loop here loops until
record refers to
.nul, this is possible because the select function will return a ppcstype1record when you successfully select a record and return
.nul if it fails. If record refers to .nul we log that there are no more records found otherwise we will log the record id, as well as the first and last name of that record, this is done using the
! member operator, this operator will attempt to return the value of the field whose name is given after the operator (exact use above). Next we are goign to add a record to the database
Adding a record to the Database
To add a record you need to create a ppcstype1record object, set the fields and then save it to the database. The first thing we will do is reset the error code. Then we will create a new ppcstype1record object using the newrecord function, and subsequently using the member operator
! we will set values for the record fields.
errno = 0 log_handler.info_message("Add record with id '" + .tostr(index_id, 10) + "'"); record =@ file.newrecord(); record!id = index_id; record!first_name = "First"; record!last_name = "Record";
We now need to save the record into the database, as up until this point it has not been permanently stored. We should also check if this is successful, and if it is not log it and return it to the IDE. If it is successful we will add a second record, using the same method as above, checking if this recordwas saved successfully and if it wasn’t logging this and returning it to the IDE
if record.save(error=errno) !@= .nul result = log_handler.error_message("Cannot add record to table. Error code: " + \ .tostr(errno, 10)); else record =@ file.newrecord(); record!id = index_id; record!first_name = "Second"; record!last_name = "Record"; if record.save(error=errno) !@= .nul result = log_handler.error_message("Cannot add record to table. Error code: " + \ .tostr(errno, 10)); end if end if
Updating a record
To update a record we first have to check whether we have a valid index and have been able to save the records we added, we then want to select the record with a lock (this is required if we want to modify or delete the record, and to prevent modification or deletion by others). We can then update the record, in this example we are merely switching the first and second names of the record. We then need to save the updated record and test whether the save was successful or not. If it is unsuccessful we should log this, plus the error code and error text that the save function returns
if errno == 0 and index_found == .true log_handler.info_message("Update first record with id '" + .tostr(index_id, 10) + "'"); record =@ index.selectkey(index_id, lock=.true, error=errno); if record =@= .nul log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \ "'. Error code: " + .tostr(errno, 10)); errno = -1; else
The above code will only run if we managed to successfully add the records we are about to update and have found an index. We are first checking for these conditions, then selecting the records, making sure we have set the
lock varibale to
.true and then tested whether this this operation was succesful or not.
Once we have determined we have successfully selected the record to modify we will declare a buffer string so we can switch the first and last name of the record. We will then save our modification, and as ever log if this fails and return it to the IDE. The error logging here is of note because we are not only logging the error code as we have done with all previous error logs but also any error text that is generated during the execution of the save function.
string buffer; buffer = record!first_name; record!first_name = record!last_name; record!last_name = buffer; if record.save(error=errno, errortext=buffer) !@= .nul result = log_handler.error_message("Cannot update record. Error code: " + \ .tostr(errno, 10) + ": " + buffer); end if
Show the updated record
We have now successfully updated the record and now want to show this updated record to do so we are again going to check whether any error has been thrown during any of the previous code, as well as whether we have found an index. We then need to select the record, check whether this was done successfully, log if it wasn’t. And upon success we are going to log the updated record as well as any in the same index
if errno == 0 and index_found == .true log_handler.trace_message("Show updated record with id '" + .tostr(index_id, 10) + "'"); record =@ index.selectkey(index_id, error=errno); if record =@= .nul log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \ "'. Error code: " + .tostr(errno, 10)); errno = -1;
The above code snippet checks whether no error has occurred and whether we have found an index. It then logs which record we are going to show, attempts to open the record, and if it fails to open a record, logs this error. Finally we set the error code to
Below we are logging the updated record and then using a while loop going through all other records in the index and also logging these
else log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + "; first name: " + \ record!first_name + "; last name: " + record!last_name); while record =@ record.select(error=errno) if record !@= .nul log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + \ "; first name: " + record!first_name + \ "; last name: " + record!last_name); end if end while record =@= .nul end if
Deleting a record
Deleting a record itself is rather simple but to make this tutorial as useful as possible we are going to do this. deleting record with specific ID
First we are going to check whether we actually have an index from which we can delete records. Then we need to select all records we want to delete, we are going to be deleting all the records we have created. Finally we are going to delete the record itself.
if index_found == .true integer count; log_handler.info_message("Delete all record with id '" + .tostr(index_id, 10) + "'"); count = 0; record =@ index.selectkey(index_id, lock=.true, error=errno); if record =@= .nul log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \ "'. Error code: " + .tostr(errno, 10)); errno = -1; else if record.delete(error=errno) !@= .nul log_handler.error_message("Cannot delete first record. Error code: " + \ .tostr(errno, 10)); else count = count + 1; end if
In the code above we are first of all checking whether our
index_found variable is set to
.true, this means that we have an index from which we can delete records, we are then declaring a variable that will allow us to count the number of records we have deleted. We then need to select the record we want to delete, if this doesn’t happen successfully we need to log the error. If we have successfully selected a record we can delete it, again making sure this happens successfully and if it doesn’t we need to log the error. If we successfully delete something we should increment the number of deleted records.
Once we have delted this initial entry we also want to delete all other records with the same id. This is done with a while loop that will exit when we can no longer select a record successfully. We are looping through the records using the
select function and deleting all records of the same ID. We are of course going to test if the deletion occurred successfully and log if it does not and increment the number of deleted records if it does. The final thing we are going to do is log the amount of records we have deleted.
while record =@ record.select(lock=.true, error=errno) if record !@= .nul if record.delete(error=errno) !@= .nul log_handler.error_message("Cannot delete " + .tostr(count, 10) + \ ". record. Error code: " + .tostr(errno, 10)); else count = count + 1; end if end if end while record =@= .nul log_handler.trace_message("Deleted " + .tostr(count, 10) + " record(s)");
program zip containg the program, database and configuration file.