Retreiving data

Just in case any C# coder gets in the situation I got in, I'll try to clear up the concept of data retreival in Clarion. It is totally different than anything I expected and understanding the concept from looking at the code and reading the help was aufully tiresome.

When I first learnt how to retreive data from the database in Clarion, I was not sure if I should laugh or cry. In fact, I'm still puzzled why we actually used a real database in the first place. In Clarion, not much of the functionality provided by a database is actually used. Let's take a simple SQL example like this:

SELECT * FROM Customer WHERE Lastname = 'Johnson';

In order to do this, you would first have to make sure you have an index specified with the Lastname field as the first index field, let's call this index 'LastNameIndex' and to keep this example simple lets say it only has one field (LastName).

In Clarion, every table has a prefix. A table has a default "record", used for all interaction with the table. The entire record can be accessed by a variable using the table's prefix, a colon and 'Record'. Let's make our table's prefix 'Cus' in this example. The record is then Cus:Record. Fields of that record are accessed by the prefix, colon, ColumnName, so FirstName would be Cus:FirstName.

So, after opening the "file" 'Customer' (somewhat comparable to opening a connection to a database), you would set all values of the current Record to the smallest possible value (luckily they have a built in command that knows how to do that for all datatypes) and then fill the Lastname field with 'Johnson'.

Clear(Cus:Record, -1) ! sets all fields to the minimum possible value
Cus:Lastname = 'Johnson'

Then you call a procedure that does something like "Find the first item in LastNameIndex who's value is smaller than the current record". Let's illustrate that just to get things clear. (I hope it is in fact a binary search or so, but the result would be the same as this pseudo code):

int RecordPointer = 0
While (LastNameIndex[RecordPointer] < Cus:Record) RecordPointer++;

In clarion the syntax is:

Set(Cus:LastNameIndex, Cus:LastNameIndex);

Set does have some overloads, but the documentation is a bit fuzzy and in practice this overload is the only one I have seen used so far. Now, the record is not filled yet, to fill the record you should call "Next", however usually one would start a loop at this point since there may be more than one customer with the name of Johnson.

Loop
  Next

In Clarion errors are not thrown or caught (no 'Exceptions'). An error might have occurred during the call to Next (you might be past the end of the LastNameIndex), however if you do not check if there were errors, the error will go by unnoticed and your app will "forevver hold it's peace" about it. In fact, the "Next" statement will "loop" to the first record of your Index.

  If ~Error() ...

Be aware! You might not have got an error, but just because you have a record here does not mean that record meet's your requirements! If there is no customer called Johnson but the next record after Isaacson is Karlson, then Karlson will be the record you get! That means you need to check the retreived record.

  If Error() Or (Cus:Lastname <> 'Johnson') Then Break
  ! Do something with your record
  End

That sums it up, so the whole thing looks like:

Clear(Cus:Record, -1)
Cus:Lastname = 'Johnson'
Set(Cus:LastNameIndex, Cus:LastNameIndex);
Loop
  Next
  If Error() Or (Cus:Lastname <> 'Johnson') Then Break
  ! Do something with your record
  End

This cursor-like code is all handled on the client side. To make things worse, Referential Integrety is handled on the client as well!

Look at this example of an Auto Incremented field named "Id":

Sav:Record = Cus:Record
Clear(Cus:Record, -1)        ! Sets the pointer before the first record
Previous                     ! This "loops" the pointer to the last record
Sav:Id = Cus:Id+1            ! Increment
Cus:Record = Sav:Record   
Put(Customer)

In templates there is often a 3 times retry around it but in custom code the retry's are usually omitted.

Hope that clears up the concept of DB-interactivity in Clarion for someone!