Category Archives: Beginner

Articles for those who are in the early stages of a programming career

Returning specific rows of data with InterBase

July 16, 2014 Stephen Ball Leave a comment

Ever wanted to select specific rows from a dataset? Well InterBase has some very cool features in the SQL syntax that allows you to do just that!

Paging Data

Adding the ROWS command to the end of your SQL statement (as per the example below) will return a specific number of records. For examples, to fetch the first 10 records just add rows 10.

Select * from MyTable
rows 10;

When you want to get the next 10 records you can use the extended syntax with the “to” command.

Select * from MyTable
rows 11 to 20;

The second query can obviously be used to fetch records 1 to 10 as well and is very useful when combined with a parameterised query to allow restful paging of data sets.

Fetching Page Headers

Now, say you wanted to get the first record of every page of data, you can also easily do this by adding an additional optional parameter “by” for example

Select * from MyTable
rows 1 to 100 by 10;

The above statement would return records 1, 11, 21, 31 etc… for the total records up to 100th record.

You can also skip records to return by a percentage of the dataset result size by simply adding “PERCENT” to the end of the above statement.

Select * from MyTable
rows 1 to 100 by 7 PERCENT;

Getting the top 10

But what if you want to use ROWS to get the top 10? Well, you can easily select 10 records, but what if the 11th and 12th record have the same value as the 10th? They should be returned as well, but how?

InterBase supports this easily by adding “WITH TIES”. To use “WITH TIES” you also need to include an “ORDER BY” clause to indicate what field to measure the ties on. For example, to get the top 10 sales people by a field storing the sales_booked, you could run the following statement.

Select * from salespeople
order by sales_booked desc
rows 10 with ties;

This will include the top 10 and also anyone with sales_booked matching the 10th record.

Getting the first 10 percent

Following on, the first 10% of records can be returned by using the following syntax.

Select * from salespeople
rows 10 percent;

While getting 10% of records is a nice feature, combining this with the “ORDER BY” and “WITH TIES” makes a very easy way to select the top 10% of sales people (for example) rather than the top 10.

Summary

There are some very cool features in the InterBase language for defining the specific size and scope of data to return that lend itself nicely to a range of useful implementations around statistics and restful data service development.

For more fun details on the InterBase language, check out the language reference guide on http://docs.embarcadero.com/products/interbase

Beginner, Objective Pascal

Using the TListView to select multiple items

July 9, 2014 Stephen Ball Leave a comment

Its very easy to create a drop down list using the TComboBox to select a single item, but how can you easily select multiple items from a list across iOS and Android? Once easy way is to use the TListView component in EditMode. To view an example of this, add a TListView to a new form and using the FormCreate add in 20 items using a simple loop.

procedure TForm1.FormCreate(Sender: TObject);
var
  I: Integer;
begin
  ListView1.BeginUpdate;
  try
    for I := 1 to 20 do
      ListView1.Items.Add.Text := I.ToString();
  finally
    ListView1.EndUpdate;
  end;
end;

Also note the use of BeginUpdate and EndUpdate using the Try Finally block to speed up the processing. This prevents the ListView from redrawing between each item add.

Now using two buttons we can add on click events to see how to toggle the EditMode..

procedure TForm1.btnEditClick(Sender: TObject);
begin
  ListView1.EditMode := not ListView1.EditMode;
  (Sender as TButton).Text := 'EditMode '+ListView1.EditMode.ToString(TUseBoolStrs.True);
end;

..and secondly show the selected items.

Each TListViewItem has a Checked property that can be queried to discover if the item is selected. Again using a simple look it is possible to check each item and then add it to a list that we will show.

procedure TForm1.btnShowSelectedClick(Sender: TObject);
var
  SL : TStringList;
  I: Integer;
begin
  if ListView1.Items.CheckedCount = 0 then begin
    ShowMessage('No selected items');
    Exit;
  end;

  SL := TStringList.Create;
  try
    for I := 0 to ListView1.ItemCount -1 do begin
       if ListView1.Items[I].Checked then
         SL.Add(ListView1.Items[I].Text);
    end;
    ShowMessage('Items selected: '+SL.CommaText);
  finally
    SL.Free;
  end;
end;

For simplicity in the code above I have used the TStringList.CommaText to give a formatted output of the data.

Beginner, Intermediate, Objective Pascal

How to convert an object to JSON and back with a single line of code

June 5, 2014 Stephen Ball 3 Comments

Ever wanted to take an Object into a format that is easily persisted and back? Well now you can. New in XE6 is the REST.JSON unit. This allows you access to TJSON a class with some very helpful class methods. Using TJSON you can convert an object to a JSON string and back with a little help from generics along the way.

The following code uses a class called TFoo that has a Foo and a Fee property (string and Integer) Using TJson you can then see how to covert the object to a string and back ready for storage, transport etc.

uses REST.JSON; // Also new System.JSON

procedure TForm1.Button1Click(Sender: TObject);
var
  Foo: TFoo;
begin
  Foo := TFoo.Create;
  try
    Foo.Foo := 'Hello World';
    Foo.Fee := 42;
    Memo1.Lines.Text := TJson.ObjectToJsonString(Foo);
  finally
    Foo.Free;
  end;
  Foo := TJson.JsonToObject<TFoo>(Memo1.Lines.Text);
  try
    Foo.Fee := 100;
    Memo1.Lines.Add(TJson.ObjectToJsonString(Foo));
  finally
    Foo.Free;
  end;
end;

If you want to watch this being demo’ed this very show video shows it in action http://youtu.be/TSqWoFvjj5g

Stephen Ball's Technical Blog