Home  ==>  papers  ==>  db  ==>  blackfishsql   

BlackfishSql - Felix John COLIBRI.

1 - The new Standalone BlackfishSql database engine

RAD Studio, available since mid September 2007, offers a new standalone database engine named BlackfishSql. This database is is available for Win32 and for the .Net world (Vcl.Net and Asp.Net) and will be of great benefit to replace the BDE local databases and as an embedded database.

We will present

  • how to create such a database and connect to it
  • create tables, write some rows, and read them back
  • create User Defined Functions and Stored Procedures in Pascal

2 - Connecting to the Blackfish Sql EMPLOYEE.JDS Database

2.1 - The EMPLOYEE.JDS demo database

First we will create a connection to the EMPLOYEE.JDS Database. This is the BlackfishSql version of the traditional EMPLOYEE.GDB which comes with Interbase, since nearly 10 years now. It is located in the "Documents and Settings | RAD Studio | Databases" folder:


So lets create a new connection using the Data Explorer:
   load RAD Studio - Delphi personality (you can load all personalites if you want to, but the menus will be more cluttered)
   in the top-right pane, select the DataExplorer tab (red arrow)
   the available drivers are presented:



  • there are 2 categories of drivers
    • dbExpress drivers, with the usual Interbase, MySql, Oracle, SqlServer, AND BlackfishSql
    • Ado.Net drivers, with Oracle, SqlServer, AND BlackfishSql "in process" and "out of process"
  • those drivers belong to the new dbx4 driver architecture, wich lets us use those drivers from Win32, Vcl.Net or Asp.Net

We are interested in using a dbExpress BlackfishSql driver, so
   select "dbExpress | BlackfishSql" (green arrow) and "right click | New Driver"
   the usual connection name dialog is presented
   type the connection name. For Instance dbx_employee_jds


and click "Ok"

   the new "dbExpress | BlackfishSql | dbx_employee_jds" node is added to the Data Explorer

   to initialize the connection parameters, select this node, and "right click | modify connection"

   a connection dialog is displayed:


  • the server name : localhost
  • the database name: the full EMPLOYEE.JDS path

      C:\Documents and Settings\All Users.WINDOWS\Documents\RAD Studio\5.0\Demos\databases\BlackfishSQL\employee.jds

  • the user name : SYSDBA
  • the password : masterkey
and click "Ok" to save those settings

   to test the connection, select this connection again, and "right click | modify connection | Test Connection"

   the connection succeeds:


   you may also modify other connection parameters, using the "connection dialog | Advanced" button:



  • the default Port is 2508. If this port is not present, you can enter your port value with this dialog
  • the connection string is displayed at the bottom ("drivername= ...). You may copy and paste this connection in some occasions
click "Ok", "Ok"

We can look at the content of the EMPLOYEE.JDS database:
   select "dbExpress | BlackfishSql | dbx_employee_jds | Tables" and double click "Country"
   the content of this table is displayed in the center pane:


2.2 - The DBXCONNECTIONS.INI connection parameters

The parameters displayed in the Data Explorer are contained in the DBXCONNECTIONS.INI. This file is in:


This file contains a section for each connection, and, for BlackfishSql, there is now the default BLACKFISHSQLCONNECTION and our new DBX_EMPLOYEE_JDS:


[DBX_EMPLOYEE_JDS] drivername=BLACKFISHSQL password=masterkey user_name=sysdba port=2508 create=False readonlydb=False HostName=localhost Database=C:\programs\us\db\blackfishsql\_data\employee.jds

You may, eventually, create new connections by directly modifying this .INI.

Before doing so, we would strongly recommend that you save a copy of the original DBXCONNECTIONS.INI in some folder.

You may also have a look at our Dbx4 programming paper which presents sample code for creating new connections from within your own application

2.3 - Ado.Net connection

The Data Explorer also allows us to create new Ado.Net connections to BlackfishSql databases, using the same steps ("New Connection" etc)

3 - Creating a new BlackfishSql Database

3.1 - Create Database = Open connection

To create a new database, we simply
  • create a connection with the new .JDS path and file name
  • make sure that the "Create" parameter is set to True (the default value is False
  • open the connection
All operations can be performed using the Data Explorer or Delphi code.

3.2 - Create a new BlackfishSql base using the Data Explorer

Here is how to create an new TRAINING.JDS database using a new DBX_TRAINING_JDS connection:
   in the Data Explorer, select "dbExpress | BlackfishSql | right click | Add New Connection"
   enter the connection name, like "dbx_training_jds" and click "Ok"
   right click on this new connection node, and select "Modify Connection"
   the Connection Dialog is opened
   enter the new database parameters:
  • the server name : localhost
  • the database name: your full database path and name, like:


  • the user name : SYSDBA
  • the password : masterkey
   click the "Advanced" button to open the detailed Connection Editor, and toggle "Create" to True:


click "Ok"

   click "Test Connection"
   the "Connection Succeeds" dialog is displayed
   click "Ok"
   a new empty database is created:


Note that
  • TRAINING.JDS will contain the actual values (Tables with rows and column values)
  • TRAINING_LOGA_0000 is a lock file, containing 0 values after creation

3.3 - Creating a BlackfishSql database by code

The same creation can be performed using Delphi code, using an SqlConnection
  • we drop this component
  • we initialize its tSqlConnection.Params property with the connection parameters (in the Object Inspector or by code)
  • we open it

Here is the "pure code" version:
   select "File | New | Vcl Forms Application"
   Delphi creates a project with our tForm
   save the project and form, using for instance CREATE_DATABASE
   from the Tools Palette, select "dbExpress | tSqlConnection"


(rather than opening the dbExpress tab, most developers would click on the Tools Palette title, and type "tslqc" to filter out this component)

Also toggle its LoginPrompt to False

   drop a tButton, and in its OnClick event initialize and open SqlConnection1:

procedure TForm1.create_w_sqlconnection_Click(SenderTObject);
  var l_c_sql_connectiontSqlConnection;

    l_c_sql_connection:= tSqlConnection.Create(Nil);

    with l_c_sql_connection do
      DriverName:= 'BLACKFISHSQL';

      with Params do
            + 'training_1.jds');

      LoginPrompt:= False;

      Connected:= True;
  end// create_w_sqlconnection_Click

   run, click "create_w_sqlconnection"
   the new .JDS file is created

Please note
  • since we directly parametrized the SqlConnection, no entry is added to DBXCONNECTIONS.INI. This is no problem, if you are willing to parametrize the connection whenever you want to use it
  • once the .JDS file is created, you can add a DBXCONNECTIONS.INI entry using the Data Explorer, and this connection can then be used in future projects
  • you could also have
    • created a DBXCONNECTIONS.INI entry using the Data Explorer, or even Delphi code
    • use a tSqlConnection and open it (in the Object Inspector or by code)
  • creating DBXCONNECTIONS.INI entry by code is in the Dbx4 programming demos
  • if a DBXCONNECTIONS.INI entry has been created (but the database is not yet present), we can drop this connection on the Form (which automatically initializes SqlConnection1.ConnectionName, and create the database by toggling Connected, or with code

3.4 - Create a BlackfishSql database with .Net code

We can also create a .JDS database using either Vcl.Net or Asp.Net. To do so:
  • the project "References" list must contain System.Data (this is the default)
  • we use the DbConnection Ado.Net component
  • our UNIT must contain the corresponding USES System.Data.Common import

Here is an example:
   load the RAD Studio, .Net personality (or use the complete RAD Studio)
   create a VCL.Net application, by selection "File | New | Vcl Forms Application - Delphi for .Net" and rename it CREATE_DATABASE_NET
   select "Data Explorer | References" and click this node

   the referenced assemblies are check that it contains the System.Data.dll:


If this assembly is missing, select "References | right click | Add Reference" and use this dialog to add System.Data.dll

   select the "code" central pane
   add the System.Data.Common to the USES clause
   drop a tButton on the form, and add the code which creates the database:

uses dbConnection

const k_database_file_name=

procedure TForm1.create_database_Click(SenderTObject);
  var l_c_db_provider_factoryDbProviderFactory;
    l_c_db_provider_factory:= DbProviderFactories.GetFactory(

    l_c_db_connection:= l_c_db_provider_factory.CreateConnection();

         + ';database='k_database_file_name
         + ';user=SYSDBA'
         + ';password=masterkey'
         + ';create=True'

  end// create_database_Click

   run and click "create_database"

Note that:
  • the DbConnection is the generic Ado.Net provider connection which implements the iDbConnection INTERFACE:


  • we created the DbConnection using a DbProviderFactory, but we could have directly created the connection with:

    procedure TForm1.create_w_datastore_Click(SenderTObject);
      var l_c_db_connectionDbConnection;
        l_c_db_connection:= DataStoreConnection.Create();

  • the connection string has a really easy structure (no insanely complex assembly IDs or other crazy parameter nobody is able to remember). And if you have a doubt, you still can use the Data Explorer "Advanced" dialog which presents the connection string at the bottom, as presented above

  • at this time, we cannot use the Data Explorer to drop an Asp.Net connection on the Form, but this should be present in the next versions of Delphi  

3.5 - The disc files

3.5.1 - The BlackfishSql Server

BlackfishSql is the new version of the Java jDataStore Sql Engine. Since C# is just a Java version, this engine could be brought over to the .Net world, and adapted to the Delphi environment. And the jDataStore origin explains the .JDS file extension.

Steve SHAUGHNESSY who heads the Database development at CodeGear explains:

  • Blackfish is another name for an Orca or whale killer.
  • Blackfish is a member of the Delphinidae family of whales and dolphins. Marine biologists seem to just call it a Delphinid.
  • A Blackfish is a beautiful, powerful animal with a wide range of habitat.

This certainly explains why we can run the server in the Java habitat as well as the .Net habitat.

We will only be concerned here with the .Net version. In this case, the server runs on the .Net Framework. So this framework must be present for BlackfishSql. And the server is contained in .Net assemblies:


You can find where those assemblies are located using the Assembly referencing tool ("Project | Add References).

This 1.3 MB is remarkably slim. As a comparison, the BDE took 30 Mb, and when you look at SqlServer or Oracle, well ...

3.5.2 - Start and Stop BlackfishSql

We find in the Delphi BIN directory a small wrapper which starts or stops the server:


If you run the .EXE with a /h attribute to get all available options:



  • to start the server, you type

    bSqlServer.Exe <enter>

  • to stop the server:

    bSqlServer.Exe -shutdown <enter>

    or by typing Ctrl-C in the console window

After RAD Studio installation, the BlackfishSql server is started as a Windows Service. We can view, start and stop the server, we can use the Windows administration tool:
   select "Start | Parameters | Configuration Panel | Administration Tools | Services"
   the services dialog is displayed, with BlackfishSql among those:


A displayed above, the service is running, and automatically started at boot time, and this explains why we could use BlackfishSql at the start of this article.

You can start / stop the service

  • from within the service dialog either by right clicking and then selecting "Start" or "Stop", or by using the bitbutton icons (the green arrow to start, the red arrow to stop)
  • or use a command line console window and, to start the service, type

    bSqlServer.Exe -install <enter>

  • to stop the server service:

    bSqlServer.Exe -remove <enter>

All the command line commands can be placed in .BAT file, which can even be added to the Delphi Tools menu

3.5.3 - BlackfishSql server configuration

The BSQLServer.exe.Config is an ASCII file which we can display and change using NOTEPAD (you can even load in in Delphi by simply clicking on the file, since Web.Config usually associates .CONFIG with Delphi !)

This file has MANY comments, clearly explaining the use of each tag. Here is a trimmed down version of this file (we removed the comments):

<?xml version="1.0" encoding="utf-8" ?>
    <add key="ServiceName" value="BlackfishSQL" />
      <add key="blackfishsql.minCacheSize" value="" />
      <add key="blackfishsql.maxSortBuffer" value="" />
      <add key="blackfishsql.tempDir" value="" />
      <add key="blackfishsql.licenseDirectory" value="" />
      <add key="blackfishsql.port" value="2508" />
      <add key="blackfishsql.dataDirectory" value="" />
      <add key="blackfishsql.logFile" value="" />
      <add key="blackfishsql.logFilters" value="INIT,
" />
      <add key="blackfishsql.assemblyPath" value="" />

Two entries might be of interest

  • "DataDirectory" enables us to enter a path which will replace the |DataDirectory| tag-like value in the DbExpress connection entry.

    For instance:

    • if we enter

      <add key="blackfishsql.logFile" value="c:\programs\us\blackfishsql\_data\" />

    • then we can specify the "database=" connection string property with:

      ... database=|DataDirectory|training.jds ...

      and this alias-kind of shortcut works in a DBXCONNECTIONS.INI database name entry, in the Data Explorer, and in the tSqlConnection Parameters.

  • the "LogFile" tag allows us to use a log file, for the operations specified in the "logFilters"

3.6 - The conceptual organization

As explained above, the BlackfishSql server is living in the .Net world.

Assuming that the .JDS database is on a server PC, this can be pictured like this:



  • on the data side, you have the .JDS data file (and the log, the locks etc) and a SLIP file which contains the licences
  • on the code side, there is
    • the server code, which we presented above
    • the UDF (User Defined Functions), the Stored Procedures and the Triggers
The User Defined Functions, the Stored Procedure and Triggers will be presented below. They can be all written in Delphi code (Object Pascal code), but MUST be written in the .Net world. So we will use the Vcl.Net to create the corresponding .DLL code.

Since the BlackfishSql server lives in a .Net world, we can write Vcl.Net or Asp.Net code which handles our .JDS databases. But we can ALSO access .JDS databases from Win32 application, using Dbx4 bridges which allow such access.

3.6.1 - BlackfishSql and Win32

In the native Win32 world, the .JDS database can be handled
  • either with the Dbx4 low-level components: tDbxConnection, tDbxCommand, tDbxReader. Those dbExpress components are handled in code, and are not linked to the visual components (tDbGrid). We have presented extensive examples about how to use those components in the Dbx4 programming article.
  • or by using the traditional dbExpress access components, tSqlConnection, tSqlDataset (or equivalents), and, for updates, tDataProvider, tClientDataset. Those components can be linked to visual components.

This can be represented by the following figure:


3.6.2 - BlackfishSql and .Net

To handle .JDS databases, we can use
  • the same dbExpress components as those used with Win32:


  • to use an Ado.Net connection, we must use the Ado.Net components: DbConnection, DbCommand, DbDataReader:


Currently the dbExpress components only allow remote connections. Ado.Net connections however allow remote connections, AND "in-process" connection; the server is in the same process as the application. There is even an mixed "in-process" and "out of process" mode.

The "in-process" mode can be displayed like this:


4 - Creating Database Tables

4.1 - Issuing Sql Requests

To create a Table, we simply have to issue a CREATE TABLE request. To build a COURSE Table, we could use:

  (c_id INTEGERc_course_name CHAR(17), c_days INTEGERc_price NUMERIC(12, 2))

This request can be executed using

  • the Active Query Builder
  • by code
    • in Win32 dbExpress, either the dbx_xxx or the Sql_xxx component suites
    • in .Net with the Ado.Net db_xxx components

4.2 - The Active Query Builder

The new Active Query Builder, which is a visual query builder which can be used to execute and build Sql statements

4.2.1 - Creating SELECT queries visually

First we will present this new tool with the existing EMPLOYEE.JDS database and the dbx_employee_jds connection.

To open the Active Query Builder:
   select "dbExpress | BlackfishSql | dbx_employee_jds | right click | Sql Window"
   the Active Query Builder is opened in the center pane:


   you select and drag a Table from the Table list (1) into the design area (2)
   the Structure view (3) and the Sql pane (4) are updated
   you may add additional constraints using the Parameter pane (5), for Column or expression, sort order, group by etc
   you execute the request by clicking the "Execute Sql" speedbutton (red arrow)
   the result is displayed in a dbGrid at the bottom (green arrow)
The resulting Sql request can then be copied and pasted in some data access component.

Here is an example with the DEPARTMENT Table:
   select the DEPARTMENT Table in the right pane (1), drag it to the central pane (2)
   the tree view and the request are updated:


   check the DEPT_NO, DEPARTMENT and LOCATION fields, and click the "Execute Sql" speedbutton near the top

   the left TreeView, the parameters grid and the request are updated, and the answer set displayed at the bottom:


We can sort the Table and specify ORDER BY, GROUP BY or other clauses using the middle parameter pane:
   to sort the answer set by location
  • select the LOCATION column (clicking on this column in the left TreeView or directly selecting it in the parameters grid
  • on the DEPARTMENT.LOCATION line, select the "SORT TYPE", maybe add an "ALIAS", and execute the request
   here is the result:


And now the best part, JOIN two tables:
   in the Table list, select the EMPLOYEE Table and drag it to the center pane
   in the center pane, check its EMP_NO, FIRST_NAME and DEPT_NO fields
   to set the JOIN condition, select the EMPLOYEE.DEPT_NO field, and drag it over the DEPARTMENT.DEPT_NO field
   click the "Execute Sql" speedbutton at the top
   here is the SELECT request and the result:


Note that

  • you have contextual menus in the central designer pane (to add derived tables, UNION etc) as well as on the bottom dbGrid
  • you can use transactions with the top speedbuttons
  • you can build several requests and navigate between them using the top speedbutton with left and right arrows
  • at this time, it seems than only SELECT request can be generated (INSERT, UPDATE and DELETE is already present in other Delphi tools, so I guess this will be added in the the future)
  • you CAN however enter any Blackfish Sql request (INSERT, UPDATE, DELETE but also CREATE TABLE and other Sql requests). In addition, you can enter whole scripts, where request are separated by semi-colons

4.2.2 - CREATE TABLE with the Active Query Builder

Now we will use the Active Query Builder to create our COURSE Table in the TRAINING.JDS database

We already have created the TRAINING.JDS database and added a connection entry for it in DBXCONNECTIONS.INI for it.

To create a Table:
   in the Data Explorer, select "DbExpress | BlackfishSql | dbx_training_jds | right click | Sql Window"
   the Active Query Builder is opened
   in the Sql Editor, type the CREATE COURSE Sql request presented above
   click the Execute Sql icon at the top-left
   the COURSE Table is created (if the table is not displayed, close the Active Query Builder and open it again)


Note that

  • we could also have used the "create table" option of the "dbExpress | BlackfishSql | dbx_training_jds | Tables | right click | New Table":


4.2.3 - CREATE TABLE using Win32 code

To create a new table by code, we can use either tDbxConnection or tSqlConnection. The first technique has been presented in the Dbx4 paper. So let's use the other technique.

Here is how to proceed:
   create a new Win32 application by selecting "File | New | Vcl Forms Delphi for Win32" and rename it CREATE_TABLE_WIN32
   from the Tools Palette, drop a tSqlConnection, double click SqlConnection1, and, in the Connection Editor select dbx_training_jds:


and click "Ok"

   drop a tButton and in its OnClick event, create another table, say a PLANNING Table:

const k_planning_table_name'planning';
          'CREATE TABLE 'k_planning_table_name
        + '  (p_id INTEGER, p_city CHAR(15), p_date CHAR(10) )';

procedure execute_request(p_sql_requestString);
  var l_c_dbx_transactiontDbxTransaction;
    with Form1 do
          l_c_dbx_transaction:= SqlConnection1.BeginTransaction;
    end// with Form1
  end// execute_request

procedure TForm1.create_table_Click(SenderTObject);
  end// create_table_Click

   run and click "create_table_"
   the table is created. You can see this table in the Data Explorer

While we are at it, we can also add a DROP TABLE request
   drop a tButton which will drop the Table using the general execute_request procedure presented previously:

const k_drop_planning=
          'DROP TABLE 'k_planning_table_name;

procedure TForm1.drop_table_Click(SenderTObject);
  end// drop_table_Click

And to list the available Tables, we can use the DbxMetadata technique:

  • use a tSqlDataset
  • set its DbxCommandType to 'Sql.MetaData' and it CommandText to 'GetTables'
This will retrieve all tables and for each many parameters. We are only interested in the TableName field, so we will extract those values in a tListBox:
   drop a tListBox on the Form
   drop a tSqlDataSet on the Form, set
  • its SqlConnection property to SqlConnection1
   drop a tButton, and in its OnClick event initialize DbxCommandType, CommandText and add the TableName field in ListBox1:

procedure TForm1.list_tables_Click(SenderTObject);
    SqlDataSet1.DbxCommandType:= 'Dbx.MetaData';
    SqlDataSet1.CommandText:= 'GetTables';

    while not SqlDataSet1.Eof do
  end// list_tables_Click

   run and click "list_tables_"
   here is a snapshot of our application:


Note that
  • we could have presented the Table metadata in a tDbGrid (event at design time), by adding a tDataSetProvider and tClientDataSet. However this displays contains many other metadata columns irrelevant to our simple table listing
  • the ListBox displays also BlackfishSql system table names
  • we used the tSqlConnection component, but a code created tDbxConnection could also have been used
  • BlackfishSql has its own metadata Stored Procedures

4.2.4 - CREATE TABLE .Net personality

The technique is quite similar:
   select "File | New | Vcl Forms Application - Delphi for .Net" and rename it CREATE_TABLE_NET
   drop a tSqlConnection, double click on it and select the dbx_training_jds connection
   drop a tButton, an in its OnClick event create another table, using a code similar to the Win32 code:

          'CREATE TABLE 'k_registering_table_name
        + '  (r_id INTEGER, r_training_ref INTEGER, '
        + '   r_company CHAR(15), r_trainee CHAR(15) )';

procedure execute_request(p_sql_requestString);
    with Form1 do
          on eException do
            display('  *** error 'e.Message);
    end// with Form1
  end// execute_request

procedure TForm1.create_w_sql_connection_Click(SenderTObject);
  end// create_sql_connection_Click

  • we did not use transactions in this example
  • we added the DROP TABLE request to this example

And we can also use the Ado.Net components:
  • we create an DbConnection and initialize its ConnectionString, as done above
  • this DbConnection is used to create a DbCommand object, and
    • we initialize its CommandText property
    • we call its ExecuteNonQuery method (meaning that this is not a SELECT request)
Here is the code:
   drop a tButton, and write code which creates the DbConnection, creates and initializes a DbCommand and sends the request
   drop another button which drops the Table:

const k_database_file_name'C:\programs\us\db\blackfishsql\_data\training.jds';

function f_c_db_connectionDbConnection;
  var l_c_db_provider_factoryDbProviderFactory;
    l_c_db_provider_factory:= DbProviderFactories.GetFactory('Borland.Data.BlackfishSQL.LocalClient');

    Result:= l_c_db_provider_factory.CreateConnection();

         + ';database='k_database_file_name
         + ';user=SYSDBA'
         + ';password=masterkey'
         + ';create=True'
  end// f_c_db_connection

procedure execute_db_request_non_select(p_sql_requestString);
  var l_c_db_connectionDbConnection;
        l_c_db_connection:= f_c_db_connection;
        l_c_db_transaction:= l_c_db_connection.BeginTransaction;

        l_c_db_command:= l_c_db_connection.CreateCommand;
        l_c_db_command.CommandText:= p_sql_request;
        l_c_db_command.CommandType:= CommandType.Text;


  end// execute_db_request_non_select

procedure TForm1.create_w_db_connection_Click(SenderTObject);
  end// create_w_dbconnection_Click

procedure TForm1.drop_w_db_connection_Click(SenderTObject);
  end// drop_w_db_connection_Click

   run and click "create_w_db_connection_" and "drop_w_db_connection_"
   here is a snapshot of our application:


Please note
  • while writing the Ado.Net code we received "Attempting to open xxx that is still marked open by another process" errors. We obviously had neglected to close the connection. To kill this orphan connection, we closed RAD Studio and opened it again

5 - Reading and Writing data

5.1 - INSERT INTO request

To insert some rows, we issue commands like:

  (c_idc_course_namec_daysc_price NUMERIC)
  VALUES (101, 'RAD Studio 2007', 5, 2680);

5.1.1 - INSERT INTO with the Active Query Builder

We can use the Active Query Builder by simple type (or pasting) the above request kind into the Sql Request pane. We can even write several requests terminated by a semi colon ; :
   in the Data Explorer, select "DbExpress | BlackfishSql | dbx_training_jds | right click | Sql Window"
   the Active Query Builder is opened
   in the Sql Editor, enter or paste the INSERT INTO script
   click the Execute Sql icon at the top-left
   the COURSE Table is filled


Note that
  • we have watched Jens Ole LAURIDSEN use the Data Explorer display dbgrid ("DbExpress | BlackfishSql | dbx_training_jds | Tables | COURSE | right click | Retrieve Data") to insert values, but have not succeded so far to modify those values from this grid

5.2 - INSERT INTO Win32

We can also use a tSqlConnection to do the same. And this is our prefered way, since we can easily repeat the operation with a simple button clic. Here is the code
   select "File | New | Vcl Forms Application - Delphi for Win32" and rename it INSERT_INTO_WIN32
   drop a tSqlConnection, double click on it and select the dbx_training_jds connection
   drop a tButton, an in its OnClick event enter the code which adds training rows:

const k_insert_into_course=
          'INSERT INTO course'
        + '  (c_id, c_course_name, c_days, c_price)'
        + '  VALUES ';

procedure TForm1.insert_into_course_Click(SenderTObject);

  procedure insert_into_course(p_idInteger;
    var l_requestString;
      l_request:= k_insert_into_course
        + '('
        +          IntToStr(p_id)
        +    ', 'QuotedStr(p_course_name)
        +    ', 'IntToStr(p_days)
        +    ', 'FloatToStr(p_price)
        + ')';
    end// insert_into_course

  begin // insert_into_course_Click
    insert_into_course(101, 'Delphi Tutorial', 3, 1580);
    insert_into_course(102, 'Interbase Client Server', 3, 1580);
    insert_into_course(103, 'TCP/IP', 2, 1160);
    insert_into_course(104, 'RAD Studio 2007', 5, 2680);
  end// insert_into_course_Click

the execute_request has been moved to a U_BFS_WIN32, in order to gradually build a database utility library:

unit u_bfs_win32;
    uses  SqlExpr// tSqlConnection

    procedure execute_request(p_c_sql_connectiontSqlConnection;

    uses DbxCommon
       , u_c_display

    procedure execute_request(p_c_sql_connectiontSqlConnection;
      var l_c_dbx_transactiontDbxTransaction;
        with p_c_sql_connection do
              l_c_dbx_transaction:= BeginTransaction;
              display('  *** error');
        end// with p_c_sql_connection  
      end// execute_request

   end// u_bfs_win32

Usually when we do an operation, we add the ability do undo. In this case, the symmetric command is:


Here is a snapshot of the application:


As you can see, we have also added a dbGrid to be able to monitor what was achieved. This will be explained now.

6 - Displaying BlackfishSql tables

6.1 - The SELECT request

Displaying a Table uses the classic SELECT, similar to:

  FROM course
  WHERE c_course_name= 'RAD Studio 2007'

6.2 - Display in the Object Inspector

We can either use "Table | Retrieve Data", or the Active Query Builder

For the first solution:
   in the Data Explorer, select "DbExpress | BlackfishSql | dbx_training_jds | Tables | COURSE" and either double click "COURSE" or "right click | Retrieve Data"
   the data is displayed in a DbGrid


For the second solution, we simply open the Active Query Builder (in the Data Explorer, select "DbExpress | BlackfishSql | dbx_training_jds | Sql Window"), type the SELECT request and execute it, as explained above

6.3 - Display using a tClientDataSet

6.3.1 - Display in a tDbGrid

This is the usual dbExpress way of displaying data:
   select "File | New | Vcl Forms Application - Delphi for Win32" and rename it DISPLAY_WIN32
   from the "Tools Palette | dbExpress" tab drop a tSqlConnection, double click on it and select the dbx_training_jds connection

Also toggle its LoginPrompt to False. You may check the connection by toggling Connected

   from the "Tools Palette | dbExpress" drop a tSqlDataSet, and
  • in the Object Inspector set SqlConnection to SqlConnection1
  • CommandType to ctQuery
  • CommandText click the ... ellipsis to open the Command Text Editor
  • in the Command Text Editor, select the "connection" combo and select "Form1.SqlConnection1", and click "Get Database Objects"


    In the "Tables" listbox, select COURSE and click "Add Table to Sql", and in the "Fields" listbox, select * and click "Add Field to Sql"

    Alternately, you may directly enter the "SELECT * FROM course" request.

    Then click "Ok"

  • toggle Active to True to check the Sql syntax
   from the "Tools Palette | Data Access" tab, drop a tDataSetProvider and initialize DataSet to SqlDataSet1

   from the "Tools Palette | Data Access" tab, drop a tClientDataSet and initialize ProviderName tDataSetProvider1

To check all links, toggle Active to True

   from the "Tools Palette | Data Access" tab, drop a tDataSource and initialize DataSet to ClientDataSet1

   finally, from the "Tools Palette | Data Controls" tab, drop a tDbGrid, and initialize DataSource to DataSource1

   if ClientDataSet1 is Active (= open), you will see the Table:


Please note that
  • instead of initializing the tSqlConnection.ConnectionString property, we could have dragged the dbx_training_jds connection from the DataExplorer, or even dragged the COURSE Table

6.3.2 - Display in the .Net World

To read data, we use
  • an Ado.Net DbConnection
  • this connection is used to create a DbCommand
  • the DbCommand.ExecuteReader yields a DbReader which is used to browse the rows using DbReader.Read

Here is an example:
   start a new .Net project, call it SELECT_NET
   drop a tButton and write the code which will get and open a DbConnection

const k_database_file_name=
var g_c_db_connectionDbConnection;

procedure TForm1.connect_Click(SenderTObject);
    g_c_db_connection:= f_c_db_connection(k_database_file_name);
  end// connect_Click

As previously, the f_c_db_connection has been moved to a helper unit:

unit u_bfs_net;
    uses System.Data.Common;

    function f_c_db_connection(p_database_filenameString): DbConnection;


    function f_c_db_connection(p_database_filenameString): DbConnection;
      var l_c_db_provider_factoryDbProviderFactory;

        Result:= l_c_db_provider_factory.CreateConnection();

             + ';database='p_database_filename
             + ';user=SYSDBA'
             + ';password=masterkey'
             + ';create=True'
      end// f_c_db_connection


   drop a tButton, and write the code which creates the DbCommand, the DbReader and displays some values from the COURSE Table in a WHILE loop:

procedure TForm1.select_Click(SenderTObject);
  var l_c_db_commandDbCommand;
    l_c_db_command:= g_c_db_connection.CreateCommand;

    l_c_db_command.CommandText:= 'SELECT * FROM course';
    l_c_db_reader:= l_c_db_command.ExecuteReader;

    while l_c_db_reader.Read do
          + ' 'l_c_db_reader.GetString(1));

  end// select_Click

   run, click "Connect" and "Select"
   here is a snapshot of the result


6.4 - Parametrized Queries

We can also build parametrized queries, which is a two step process
  • we send a query to the BlackfishSql server with some values in the WHERE clause unspecified:

  • we give the values to the unknown parameters and sent this request to the server

6.4.1 - Win32 Parametrized Query

We can also create parametrized queries, for which the WHERE clause contains undefined values, using the question mark "?" as the undefined value.

To let the user find all training with more than n days:
   from the "Tools Palette | dbExpress" drop a tSqlDataSet, and
  • in the Object Inspector set SqlConnection to SqlConnection1
  • CommandType to ctQuery
   drop the usual tDataSetProvider, tClientDataset, tDataSource and tDbGrid and connect them together
   drop a tButton and initialize the parametrized query:

const k_parametrized_select=
          'SELECT c_id, c_course_name, c_days '
        + '  FROM COURSE'
        + '  WHERE (c_days >= ?)';

var g_c_parametertParam;

procedure TForm1.prepare_Click(SenderTObject);

    with SqlDataset2 do
      CommandText:= k_parametrized_select;



      // no Prepare;
      // no PrepareStatement;
    end// with SqlQuery1
  end// prepare_Click

   drop a tEdit to let the user enter the minimal training days, and a tButton to send the parameters and open the ClientDataSet2:

procedure TForm1.execute_Click(SenderTObject);
    // SqlDataset2.ParamByName('c_days').AsString:= days_edit_.Text;
    // SqlDataset2.Params[0].AsString:= days_edit_.Text;
    g_c_parameter.Value:= StrToInt(days_edit_.Text);
  end// execute_Click

   compile, execute, click "prepare" and "execute"
   here is the result:



6.4.2 - .Net parametrized queries

As an example, we will use:

  FROM course
  WHERE c_days= ?

Here is the .Net code:
   load the SELECT_NET application started before
   add a tButton which will prepare a parametrized query:

const k_parametrized_select=
    'SELECT * '
  + '  FROM course'
  + '  WHERE c_days= ?';

var g_c_db_parametrized_commandDbCommand;

procedure TForm1.prepare_Click(SenderTObject);
    g_c_db_parametrized_command:= g_c_db_connection.CreateCommand;

    g_c_db_parametrized_command.CommandText:= k_parametrized_select;

    g_c_db_parameter:= g_c_db_parametrized_command.CreateParameter;

    // -- do NOT use WITH: field has same name at type !!!
    g_c_db_parameter.dbType:= DbType.Int32;

  end// prepare_Click

   add a tEdit which will contain the parameter value, and another tButton which will initialize the parameters and execute the request:

procedure TForm1.execute_param_Click(SenderTObject);
  var l_c_db_readerDbDataReader;
    g_c_db_parameter.Value:= parameter_edit_.Text;
    l_c_db_reader:= g_c_db_parametrized_command.ExecuteReader;

    while l_c_db_reader.Read do

  end// execute_param_Click

   run, click "Prepare_" and "Execute"
   here is the snapshot:


7 - Tracing and Pooling delegate

We can add tracing to any BlackfishSql connection, pool the connections, and even chain those techniques.

This has been presented in depth in the Dbx4 programming paper.

However this "Delegate driver" technique is only available for Win32 applications (not for .Net).

7.1 - Adding a tracing delegate

Here is how to add tracing capability to BlackfishSql
   open the DBXONNNECTIONS.INI file with Notepad, add a tracing delegate and then duplicate the current DBX_TRAINING_JDS entry adding a reference to the tracing delegate:






   select "File | New | Vcl Forms Application - Win32", and rename it TRACING_DELEGATE
   selecte "Data Explorer | BlackfishSql"
   the DBX_TRAINING_JDS_TRACE connection is displayed
   test the connection
   select "Data Explorer | dbExpress | BlackfishSql | dbx_training_jds_trace | tables | COURSE" and drag it on the Form
   a tSqlConnection and a linked tSqlDataSet are dropped on the Form
   drop the usual tDataSetProvider, tClientDataset, tDataSource and tDbGrid and connect them together
   drop a tButton and let the Button open ClientDataSet1

   compile, run, click "open"
   on disk you will find a file similar to:

Log Opened ==========================================
{CONNECT } ConnectionC1.Open;
{COMMAND } CommandC1_1 := ConnectionC1.CreateCommand;
{COMMAND } CommandC1_1.CommandType := 'Dbx.Table';
{COMMAND } CommandC1_1.CommandType := 'Dbx.Table';
{COMMAND } CommandC1_1.Text := ' select * from "COURSE"';
{PREPARE } CommandC1_1.Prepare;
{COMMAND } ReaderC1_1_1 := CommandC1_1.ExecuteQuery;
{COMMAND } CommandC1_2 := ConnectionC1.CreateCommand;
{COMMAND } CommandC1_2.CommandType := 'Dbx.MetaData';
{COMMAND } CommandC1_2.Text := 'GetIndexes "C:\programs
       \training.jds"."DEFAULT_SCHEMA"."COURSE" ';
{COMMAND } ReaderC1_2_1 := CommandC1_2.ExecuteQuery;
{READER } {ConstraintName TDBXTypes.WIDESTRING }
{READER } {IsPrimary TDBXTypes.BOOL }
{READER } {IsUnique TDBXTypes.BOOL }
{READER } {IsAscending TDBXTypes.BOOL }
{READER } { ReaderC1_2_1 closed. 0 row(s) read }
{READER } FreeAndNil(ReaderC1_2_1);
{COMMAND } FreeAndNil(CommandC1_2);
{READER } { ReaderC1_1_1 closed. 4 row(s) read }
{READER } FreeAndNil(ReaderC1_1_1);
{COMMAND } FreeAndNil(CommandC1_1);

8 - UDF, Stored Procedures, Triggers

8.1 - Sql Engine routines in Pascal

One of the most innovative possibility is to write User Defined Functions, Stored Procedures and Triggers in Pascal code.

Obviously, since this engine is now totally under CodeGear's control, it was more easy to do then translate Pascal code to some Sql Engine interpreter.

However remember that the Sql Engine uses the .Net framework. Therefore the additional routines must be placed in .Net Packages. The resulting compiled code will be a .DLL.

There are two more constraints:

  • this .DLL must reside where the engine will be able to locate it. There is naturally a predefined search order (the engine's own executive directory, the DataDirectory, a directory specified in the .CONFIG).
  • in addition the .DLL must be loaded by the engine. To achieve this, we must close and reopen the engine. If the BlackfishSql engine runs as a .DLL, then we must stop the service, compile the Package, and restart the service

The routine (UDF, Stored Procedure, Trigger) are placed in a CLASS as special CLASS PROCEDURE xxx; STATIC or CLASS FUNCTION yyy: zzz; STATIC.

Here is a simplified example with a function computing the square:

  • we start a .Net package, and rename it PK_COMPUTE_NET
  • we add a UNIT, call it U_C_COMPUTE_NET, and add a CLASS which computes the square:

    unit u_c_compute_net;

        type c_compute
                class function f_square(p_valueinteger): Integerstatic;
            end// c_compute


        class function c_compute.f_square(p_valueinteger): Integer;
            result:= p_valuep_value;
          end// f_square


  • we compile this unit

  • the BlackfishSql engine must be aware of this new f_square routine. To do so, we use an SQL request which is similar to the usual Stored Procedure or Trigger creation:

    CREATE METHOD  f_square
      AS 'pk_commute_net::u_c_compute_net.c_compute.f_square'

    Please note the AS syntax: it goes from the outside to the inside:

      package :: unit . class . routine

  • after that the f_square FUNCTION can be used as any Sql FUNCTION like UPPER, SUM, AVG, etc

8.2 - Writing a User Defined Function

UDFs are routines like COS() or SINE() which the developer adds to the BlackfishSql runtime library in order to extend the existing routines.

Let's for instance translate Euros into US Dollars. The text of a usual Pascal function would be:

FUNCTION f_euro_do_us_dollare(p_euroDouble): Double;
    Result:= p_euro* 1.40;
  END// f_euro_to_us_dollar

Before starting we will prepare the Windows Service dialog and keep it on the task bar in order to be able to easily start and stop the BlackfishSql service:
   select "Start | Parameters | Configuration Panel | Administration Tools | Services" (you may create a desktop shortcut for this)
   the services dialog is displayed, with BlackfishSql among those:
   stop the service :
  • either select "BlackfishSql | right click | stop service"
  • or click the top rectangular icon
   do NOT close this dialog, and keep it on the task bar

Then write the Package
   start RAD Studio, .Net personality
   start a new Package with "File | New | Other | Delphi for .NET projects | Package"
   a new Package for .Net is created.
   in the Project Manager select "Package1 | right click | rename" and rename it PK_BFS_UDF_NET
   select "File | New | Other | Delphi for .NET projects | New Files | unit" (this could also be performed by "package | right click | add other" etc
Rename this unit U_C_BFS_UDF

   write the code converting some euros to dollars:

unit u_c_bfs_udf;

    type c_convertClass
                        class function f_euro_to_dollar(p_euroDouble): Doublestatic;
                    end// c_convert


    class function c_convert.f_euro_to_dollar(p_euroDouble): Double;
        Result:= p_euro* 1.40;
      end// f_euro_to_dollar


   build the .DLL by typing Ctrl F9

   the .DLL is stored in the default RAD Studio BPL directory, which is known by the BlackfishSql engine:


   restart the BlackfishSql service (open the service dialog, select BlackfishSql and click the little black forward triangle at the top)

Now integrate this new routine to the engine
   open the Active Query Builder, (select the Data Explorer, then "dbExpress | BlackfishSql | dbx_training_jds | Sql Window"
   make sure the connection is open (by expanding for instance on the Table node)
   type the CREATE METHOD for our new function


and click the "Execute Sql" button at the top
   a "0 rows affected" dialog box is displayed

Please note

  • those steps must be followed with some care. You will more often than not hit errors like "server explicitely refuses the connection", or, when the orthograph of the AS is wrong, "the function could not be found on the server"
  • the Package was written using the .Net personality, but we are not forced to use an Asp.Net connection. In our case, we used the dbExpress connection
  • the creation of the method could have been performed by code, but we still must take care of closing and restarting the server

Now we can use this f_euro_to_dollar UDF:
   open the Visual Query Builder, and write an Sql statement using this UDF (you can paste our routine, or "visually build" the request by clicking COURSE, then the columns, as explained before:

SELECT c_course_namef_euro_to_dollar(c_price
  FROM course

   click the "Execute Sql" button at the top
   here is the result


Naturally we can use this function now in any Sql request executed from .Net or Win32 code.

8.2.1 - An UDF with an OUT parameter

Here is an example of a PROCEDURE with an OUT parameter:
   stop the BlackfishSql service
   in the previous U_C_BFS_UDF, add a PROCEDURE with this OUT parameter. The c_convert CLASS definition now becomes:

type c_convert=
             class function f_euro_to_dollar(p_euroDouble): Doublestatic;
             class procedure convert_euro_to_dollar(p_euroDouble;
                 Out pv_dollarDouble); static;
         end// c_convert

Implement this simple PROCEDURE

   build the .DLL (Ctrl F9)

   restart the BlackfishSql service
   from the Data Explorer
  • open the connection
  • open the Active Query Builder and type the CREATE METHOD request:

    CREATE method convert_euro_to_dollar
      AS 'pk_bfs_udf_net::u_c_bfs_udf.c_convert.convert_euro_to_dollar'

We can use the Parameter displaying dialog of the Data Explorer, but this only works with an Ado.Net connection.

   create an Ado.Net connection, by selecting "Data Explorer | Ado.Net | BlackfishSql Remote Procedure | Add New connection", name it ANR_TRAINING_JDS, and enter the usual parameters by modifying the connection.
   select "ANR_TRAINING_JDS | Procedures | CONVERT_EURO_TO_DOLLAR | right click | view parameters"
   the stored procedure parameter dialog is displayed, with the two parameters
   select "p_euro", and type a value, 30 for instance. Then click the upper left "execute" button
   the value of the OUT parameter is displayed:


8.3 - Stored Procedures

Stored procedures can be written using exactly the same steps.

First, we will use a SP which does some internal requests, with some input and output parameters. To execute Sql requests, the stored procedure must have a connection. This is done by adding a DbConnection parameter to the request.

Our example will simply return the list of training names.

Here are the steps:
   close the BlackfishSql service
   create a new .Net Package, rename it PK_BFS_STORED_PROC
   since we will use a DbConnection, we must include a reference to the SYSTEM.DATA assembly.

To do so, in the Data Explorer, select "pk_bfs_store_proc.dll | Add Reference"

   the .Net Add Reference is displayed, and the available assemblies are gradually added
   after a couple of seconds, select in the top listbox SYSTEM.ASSEMBLY, and click "Add Reference"
   this assembly is added to the bottom "New references" listbox


   click "Ok"
   this assembly is added to the "Requires" clause of the Package
   add a UNIT to this package ("File | New etc), rename it U_BFS_STORED_PROC
   add the code which computes the list of training names:

unit u_bfs_stored_proc;
    uses system.Data
        , system.Data.Common

    type u_c_course_sp=
                 class function f_training_names(
                     p_c_connectionDbConnection): Stringstatic;
               end// u_c_course_sp


    // -- u_c_course_sp

    class function u_c_course_sp.f_training_names(p_c_connectionDbConnection): String;
      var l_c_db_commandDbCommand;
        l_c_db_command:= p_c_connection.CreateCommand;

        l_c_db_command.CommandText:= 'SELECT c_course_name FROM course';
        l_c_db_reader:= l_c_db_command.ExecuteReader;

        Result:= '';

        while l_c_db_reader.Read do
          Result:= Resultl_c_db_reader.GetString(0)+ ' ';

      end// f_training_names


   build the .DLL (Ctrl F9)

   start the service again

   from the Data Explorer
  • open the connection
  • open the Active Query Builder and type the CREATE METHOD request:

    CREATE method f_training_names
      AS 'pk_bfs_stored_proc::u_bfs_stored_proc.u_c_course_sp.f_training_names'

   select "Data Explorer | ANR_TRAINING_JDS | Procedures | f_training_names | right click | view parameters"
   the stored procedure parameter dialog is displayed
   click the top-left execute button
   the list of names is displayed


8.4 - Triggers

Triggers can be handled in a way similar to stored procedures.

8.5 - BlackfishSql Live templates

We have presented how to write your UDFs and Stored Procedures. In each case, you have to
  • write the CLASS
  • for each method
    • write the declaration of the routine
    • implement this routine
    • write the Sql request to add the routine to the Database

To automate this process, Nick HODGES has written an placed in Code Central a complete set of Blacckfishlive templates

In order to add this template suite to RAD Studio 2007
   download the .ZIP from code central
   copy the .XML live templates files to

    C:\Documents and Settings\My Documents\RAD Studio\code_templates

To use those templates
   open the .PAS with our stored procedure
   place the caret in the CLASS in order to add a new method
   type Ctrl J (or use the contextual menu)
   the live templates starting with bf are displayed:


   select "bfmethod", for instance and double click it
   the template is inserted into the text (Ed : partial


The template added

  • the declaration
  • the Sql creation request
   as for other templates, fill in slots whith your identifiers

The README which comes whith the .ZIP also contains detailed steps to install the additional .BPLs

9 - Download the Sources

Here are the source code files: The .ZIP file(s) contain:
  • the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
  • any .TXT for parameters, samples, test data
  • all units (.PAS) for units
Those .ZIP
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • for Delphi 6 projects, can be used from any folder (the pathes are RELATIVE)
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
To use the .ZIP:
  • create or select any folder of your choice
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

The Pascal code uses the Alsacian notation, which prefixes identifier by program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lass etc. This notation is presented in the Alsacian Notation paper.

As usual:

  • please tell us at if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.

10 - BlackfishSql Documentation and References

Among the available resources on the Web, which were much used to bring our examples together:

And on our side

11 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: sep-07. Last updated: dec-15 - 99 articles, 220 .ZIP sources, 1068 figures
Copyright © Felix J. Colibri 2004 - 2015. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute


+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Site Map
– search :

RSS feed