Delphi for .NET: Writing User-Defined Functions for Blackfish Databases

Introduction

I remember well when I bought my first version of Delphi in 1996. For me the number one feature at the time was the ease to build database applications without the hassle having to set up a server. Back in those days one used Paradox or dBase databases that were supported using the Borland Database Engine, the BDE. There are still a number of applications that use the BDE. However, the need to build databases in server-scenarios soon called for “more”. Interbase and FireBird soon became the database of choice for many Delphi developers. However, in order to set up any of those databases required additional work that was not required before. Now, with Delphi 2007, which is included in the RAD Studio 2007 box, CodeGear introduces a new database called “BlackfishSQL”. One characteristic of Blackfish is the fact that it is a 100% managed database written in C#. Thus, it is very easy to extend its functionality writing .NET classes with Delphi for .NET.

This article will show you how to create and connect to a Blackfish database and write your own user-defined functions, i.e. functions that you can use in SQL statements (see [2] for definition). Hopefully you will see that it is very much easier to extend Blackfish databases than it has been the case using previous databases bundled with Delphi.

Prerequisites

In order to reproduce this hands-on article, you will need RAD Studio 2007. It does not matter, however, which SKU you have as the demo will work with Professional, Enterprise and Architect. The only difference between the different SKUs is the number of users that can connect to a database. The Blackfish license model does not restrict developers in any way. Whenever you deploy a database you need to refer to the license to make sure the required number of users can access the database you designed [1].

Is BlackFishSQL a mature product, this being the first release?

It might seem that this is the first release of Blackfish SQL and that the database is in an early state. This is wrong. BlackfishSQL is based on JDataStore, a database for Java. It is even binary-compatible, i.e. you can open JDataStore databases with Blackfish and vice-versa. This is an advantage that should not be underrated. Furthermore, this means that Blackfish is not a “new” product, but it has already a certain degree of maturity and is very much reliable because of this fact.

BlackfishSQL is based on JDataStore, a database for Java. It is even binary-compatible, i.e. you can open JDataStore databases with Blackfish and vice-versa

Checking the installation

Before we begin, we need to check if BlackfishSQL is correctly installed on your developer system. You can do this very easily as the Windows Task Manager lists all running processes and the Blackfish Server Process (BSQLServer.exe) should be one of them.

Fig. 1: Task Manager showing the BSQLServer.exe process being installed correctly

If this is not the case, you can start the BSQLServer.exe file from the RAD Studio binary directory manually or add the service to the list of services on the system.

Sadly, in some cases the RAD Studio 2007 installer does not set up the database components in the IDE and servers correctly. In the rare case that you cannot start the BlackfishSQL Server go to the CodeGear Developer Network and look for support articles that deal with installation trouble shooting for RAD Studio 2007.

To make sure that the server is being started whenever Windows starts, check the settings in the Control Panel under “Administrative Settings / Services”. There should be an entry named “BlackfishSQL” with the settings shown in figure 2.

Fig. 2: Detailed information about the BlackfishSQL server service that needs to be running on the developer machine

Please refer to the Blackfish SQL documentation how to configure the server settings. For example, you are able to specify a default data directory or another port the server is supposed to respond to very easily by modifying a configuration file in ini-format.

Creating a database

Before we can add a user-defined function to a database, we actually need a database first. For this you can use the Data Explorer of RAD Studio 2007. In the “Default Layout” you can find the Data Explorer in the same docking panel on the right where the Project Manager resides on a separate tab.

Fig. 3: The Data Explorer in the RAD Studio 2007 IDE showing all available data providers. Here, the connection nodes for BlackfishSQL have been expanded to show the sample connections that are installed.

There are several connections available for BlackfishSQL

There are several connections available for BlackfishSQL (see figure 3).  In this article, we will use the Ado.Net provider. As we are on the developer machine, we do not want the extra hassle of using a server and thus use the “BlackfishSQL Local Provider”. Right-click on the name of the provider and select “Add New Connection”.  In the “Add New Connection” dialog set the connection name to “ClassroomDb” and confirm all the values by clicking “OK”. Delphi will add a node for this connection which we can expand to see the contents. Furthermore, we can right-click the connection, so that we can modify the connection settings. It might seem odd as we set up the connection before actually having a database. However, this approach is being used by Interbase and Firebird as well and thus is known to a majority of database application developers. Before we can design tables and add other features of the database, we need to create it. Thus, right-click the connection and select “Modify Connection” which will make the IDE display the “Add Connection” dialog. You are supposed to provide three values: User Name, Password and Database. The user name and password are constant and have to be “SYSDBA” and “masterkey”. This article will not dive into authentication and authorization of Blackfish databases. Enter a filename for the database into the “Database” textbox, e.g. “c:\mydbs\classroom\classroom.jdb”. The extension “jdb” is rather common to be used for Blackfish databases due to its heritage, it is not mandatory though. In addition, this means that a connection to a Blackfish database always refers to a file in the file system that contains the database. Sadly, Blackfish does not only create one file, but additional temporary files and log files for transactions that are required by the database as well. I suggest you always create one directory per database so that database backup is easy. Copying the complete directory will then yield a backup of the database (BlackfishSQL has additional backup procedures that cannot be described in detail here, please refer to the documentation).  If we would try to go ahead by pressing “OK” we would get an error message that the database does not exist. This is correct and thus we need to tell the IDE to create the database file by clicking on the “Advanced…” button. This will open another dialog which allows you to define all the connection settings. In the section named “Database” you find an item named “Create” which is set to “false”. Set this to “true” and the database will be created if it does not exist – it will not override an existing database. Close the Advanced dialog and press “Test Connection”. If you get the message “Test connection succeeded.” all went well and the database has been created.

Using the SQL Window

After setting up the connection to the database, we can browse it by expanding the node with the name of the connection (“ClassroomDb”). Furthermore, we can expand the Tables node, but no tables are listed. This yet again makes sense, as we created an empty database. You can add a table by rick-clicking the Tables node and then select “New Table” in the context menu. The IDE will display a grid that allows you to customize all the attributes for your table. We will use that in the next section after I gave some details about the example we will build. Before that we need to look at the so-called SQL Window which you can open by right-clicking on the connection and selecting “SQL Window”. The SQL Window is a new feature of RAD Studio 2007 which needs an article of its own to describe all the things you can do with it. However, in this article we will use it only to send SQL statements to the database and see the results of these.

Sample Application

We will build a very small application that uses the user-defined function we develop. The idea is to design a table that takes a list of students with the distance of centimetres they jumped in a sporting competition. The user-defined function will yield the same value for an American unit “feet”. Keep always in mind that you can put “anything” into a user-defined function. If you are not picky about the time it takes your database to evaluate the SQL statement, you could call a web service or do very complex calculations in a user-defined function. The size and complexity is not restricted. As mentioned right from the start, the application is thus not very difficult to write. This article will focus on the steps necessary to implement a user-defined function. But before we do that, we need to create one plain table with two attributes that stores the students and the credits they collected in the exam. Normally, one would use an Entity/Relationship model to build a database that allows administrating more than one exam of course. Creating the table can be done using the following SQL statement:

CREATE TABLE students ( name VARCHAR(255), distance INT )

If you enter this statement and it is executed correctly, the IDE will show a message that says that “0 rows have been affected” and if you expand the Tables node in the Data Explorer you will be able to see the table with its attributes. We will also add some values into the table:

INSERT INTO students ( name, distance )
  VALUES ( 'Bob', 340 );
INSERT INTO students ( name, distance )
  VALUES ( 'Holger', 330 );
INSERT INTO students ( name, distance )
  VALUES ( 'Johan', 370 );
INSERT INTO students ( name, distance )
  VALUES ( 'Olaf', 410 );

Now we can write the user-defined function that allows us to query the table with an additional column showing the distance in feet.

Implementing the UDF

As BlackfishSQL is a managed database written in C#, we can easily use Delphi for .NET to write an additional function. All functions that we want to add to our database can be stored in a single DLL, an assembly. Thus we start by creating a new Delphi for .NET Package using File / New / Others… / Delphi for .NET Projects / Package. The wizard will create the package without any units in it. We need to continue by adding a unit to the project. Right-click “Package1.dll” in the Project Manager, select “Add New / Other… / Delphi for .NET Projects / New Files / Unit”. As we have all the files now, we can set up the package by saving it on the disk. Name the package “UnitConverter” and the unit “Centimeters.pas”.

In the unit, we create a class called “TCentimeterConverter”. For every user-defined function we need to define a public static function so that it can be accessed by Blackfish. So, the class that allows us to convert centimetres into feet looks like this:

unit Centimeters;

interface

type
  TCentimeterConverter = class
    public
      class function ToFeet( value: Integer )
        : Double; static;
  end;

implementation

{ TCentimeterConverter }

class function TCentimeterConverter.ToFeet(value: Integer)
  : Double;
begin
  Result := value * 0.032808399;
end;

end.

Listing 1: Class “TCentimeter”

The implementation of the function is actually only 1 line that multiplies the centimetre value with a factor that transforms it into feet. The function also only takes one parameter, which I named “value”. In your SQL statement you will have to pass exactly one integer value to the function when using it in statements. Blackfish will take care of converting the .NET-data-types to Blackfish attribute types.

When you compile the package, Delphi will create a DLL for you in the standard package output directory. Normally, you find it in the common document folder in the RAD Studio tree. On my machine here it is “C:\Dokumente und Einstellungen\All Users\Dokumente\RAD Studio”. This complicated directory name has been necessary due to Vista compatibility as Vista has very strict rules where an application can store settings and other files.

Adding the UDF to the database

Now we need to add the UDF to the database. We need to tell Blackfish the name of the function and in where it can find its implementation. The DLL needs to be either in the Blackfish server directory, the standard package directory, in the directory specified in the configuration file or in the Global Assembly Cache (GAC). Thus, we do not need to copy the DLL to any other directory and can add the function executing the following SQL statement in the SQL Window:

CREATE METHOD CmToFeet AS ‘UnitConverter::
  Centimeters.TCentimeterConverter.ToFeet'

In case the DLL cannot be found, you get a detailed error message where Blackfish searched for the DLL. Furthermore, you also might have to restart the Blackfish service so that it picks up the new DLL. If the statement executes successfully, you get the standard “0 rows affected.” message.

Testing the UDF using the SQL Window

We already added a couple of records to our table. In addition to displaying the data that is stored in the table, we also are able to display the distance in feet now.

Enter the following SQL statement in the SQL Window:

SELECT name, distance as cm, CmToFeet(distance)
  as feet FROM students

You will get the same result as shown in figure 4. The user-defined function we implemented is being called using the integer attribute “distance” as a parameter. Note that the result of the function is a floating field, which matches the “double”-type in Delphi.

Fig. 4: Executing a SQL statement that uses the user-defined function implemented in this article. The result set contains the additional column “feet” that has been generated using the user-defined function “CmToFeet”.

Demo Application

In addition to this article, I created a demo application that executes two SQL statements and displays the data in a grid. It is basically the same thing as using the SQL Window, however you can see how to connect to the Blackfish database and how to display information in VCL database controls.

However, the key aspect is not visible if you look at the screenshot as the application is a Win32 VCL Forms application. Still, I can connect to a BlackfishSQL database that is fully managed and that even contains customized .NET code to realize additional functionality. The application with source code is available for download.

Fig. 5: Screenshot of the demo application showing two grids. The first grid only shows columns stored in the database, the second grid displays a query that uses a user-defined function

Conclusion

This article showed in detail how to write user-defined functions in Delphi for .NET for BlackfishSQL databases. Furthermore, you have been given basic information to administrate and maintain the Blackfish service and are also able to connect and to create databases. Finally, a native 32-bit application has been shown to you that connect to the Blackfish database that contains the user-defined function. Delphi allows you to make use of a database based on managed code and still keep your code native. Thus, you can run the database in a fully managed environment on a Windows Server system and can still develop your applications in native code in Delphi. In my opinion, this is a very efficient way of developing and deploying database applications that use the full feature set of BlackfishSQL.

References

• [1] Hodges, Nick; “Developing and Deploying with Blackfish SQL and Delphi”, URL: http://tinyurl.com/5s9zyr
• [2] Wikipedia, User-defined function, http://en.wikipedia.org/wiki/User-defined_function

De sources die bij dit artikel horen kun je downloaden via Flick_BlackfishUDF_SRC.zip.