Category Archives: InterBase

Articles about the InterBase database

Returning specific rows of data with InterBase

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

Speeding up SQL processing with Array DML

If you want to really make your application scream, then one key area to get your interaction right is with the database layer. The example below becomes 8 (eight) times faster processing 10,000 records!

A number of high end databases (including Oracle and InterBase) include support for array inserts which, in short, is a much faster way to insert data. Rather than having to process record after record, the process accepts a block of changes that are processed in one hit. To be clear, this is not the same as starting and ending a database transaction, (something else that can also really help with speed).

Array DML in FireDAC helps you take advantage of database engines that support this feature easily. To see the difference in code and speed, lets explore a simple example. The example uses the following table called test containing two fields, Integer and string

CREATE TABLE test(Field1 INTEGER, field2 VARCHAR(20))

The typical way to insert 10000 records is to use a loop, where we have a query that just updates the parameters and executes the SQL.

const
  NUM_INSERTS = 10000;

procedure TForm7.Button1Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsInteger := i;
      FDQuery1.Params[1].AsString := 'Str' + IntToStr(i);
      FDQuery1.ExecSQL;
    end;
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

To alter this code to use Array DML, there is very little to do. Firstly, before setting the parameter values you can set the size of the array you want to insert. (You can always set this higher that needed if your not sure)

Once the array size is set, there is a small modification to the way the parameter values are set. FireDAC supports the plural setters for setting each record in the dynamic array. (see code below)

Finally, there execute command is changed to tell it to run for the total records you want to insert.

procedure TForm7.Button2Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    FDQuery1.Params.ArraySize := NUM_INSERTS;
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsIntegers[i] := i;
      FDQuery1.Params[1].AsStrings[i] := 'Str' + IntToStr(i);
    end;
    FDQuery1.Execute(NUM_INSERTS,0);
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

So what is the difference at run time? On my PC (virtual machine), running the 10,000 inserts takes 1.014 seconds in the first block of code, compared with 0.125 seconds with the second block of code. i.e. only 12% of the original processing time and over 8 times faster!

Thats about it! The code for the above Array DML FireDAC demo is available at code central http://cc.embarcadero.com/item/29832

An video example of this in action, along with a number of other FireDAC capabilities is also available in the FireDAC Session from Developer Direct Season 4 video.

Looking to rise to the data security challenge?

Join me on Wednesday 28th May as we rise to the database security challenge on Windows, Android, iOS, Mac OSX, Linux and Solaris.

Data security is a hot topic right now, and one that is causing a lot of headaches. When it comes to securing your data and applications, small changes can make a massive difference. During the webinar there will be a number of tips relevant to whatever platform or database you develop with. We will also look at how to address a number of these challenges easily by exploring secure database storage with InterBase.

To find out more on this topic, or even if you can’t make it and want to catch the replay later, register now and “Rise to the Data Security Challenge”.

Wednesday, May 28, 2014

  • 6AM San Francisco / 9AM New York / 2PM London / 3PM Milan
  • 11AM San Francisco / 2PM New York / 7PM London / 8PM Milan
  • 5PM San Francisco / 29-May 9AM Tokyo / 29-May 10AM Sydney

Mobilizing Enterprise data – Free White Paper.

If you want to get a head start before the webinar, then it may also be worth downloading this free white paper

OpenSSL and InterBase = all is OK!

I am sure many of you have seen in the news this week the well publicised vulnerability in OpenSSL that has been named Heartbleed. https://www.openssl.org/news/secadv_20140407.txt and http://heartbleed.com/

InterBase is not affected by this issue. InterBase encryption uses OpenSSL versions that are NOT affected by this vulnerability.

For more information on InterBase please visit http://www.embarcadero.com/products/InterBase

InterBase XE3 update 4 released

We are proud to announce InterBase XE3 Update 4 is now available for download for registered users: http://cc.embarcadero.com/reg/interbase

New platform on XE3!

Along with the regular speed improvements, bug fixes etc, this release also brings the InterBase Linux engine up to XE3 from the previous InterBase XE edition.

Full details of what is contained in the update are available in the readme. http://docs.embarcadero.com/products/interbase/IBXE3Update4/Readme.html

InterBase XE3 versions

If you are developing business applications, InterBase gives a real edge around data security and best practice in data protection across all supported platforms, including mobile.

InterBase XE3 Update 4 – editions includes

Server Edition Desktop Edition ToGo and IBLite (embeddable)
  • Windows (32bit & 64bit)
  • Linux
  • Windows
  • Windows (32bit & 64bit)
  • Mac OS X
  • iOS Simulator and Device
  • Android

There is an Update/Patch installer for existing installations of InterBase XE3 Server/Desktop/Developer Edition on Windows. Trial editions will be updated soon.

Embedding InterBase

The quick guide for ToGo has also been updated and is available here http://docs.embarcadero.com/products/interbase/IBXE3Update4/ToGoQuickStart/Introduction.htm or if you prefer video’s check out InterBase Labs

Why InterBase?

If you want to know more about why customers are upgrading to the latest versions of InterBase and using InterBase in their current applications please read this Mobilising Enterprise Data – free white paper and also visit the InterBase pages