menu
  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:

employee_jds_directory_



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:

create_connection



and:

  • 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

new_blackfishsql_connnection

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:

initialize_blackfishsql_connnection

   enter
  • 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:

test_connection

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

advanced_connection_parameters

and

  • 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:

data_explorer_display_table



2.2 - The DBXCONNECTIONS.INI connection parameters

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

dbxconnections_ini

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

 
          [BLACKFISHSQLCONNECTION] 
          DriverName=BlackfishSQL 
          HostName=localhost 
          port=2508 
          Database=databasename 
          create=true 
          User_Name=sysdba 
          Password=masterkey 
          BlobSize=-1 
          TransIsolation=ReadCommited 
          

[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:

      C:\programs\us\db\blackfishsql\_datat\training.jds

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

create_blackfishsql_database

click "Ok"

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

the_new_training_jds



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"

tsqlconnection_palette

(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;

  begin
    l_c_sql_connection:= tSqlConnection.Create(Nil);

    with l_c_sql_connection do
    begin
      DriverName:= 'BLACKFISHSQL';

      with Params do
      begin
        Add('drivername=BLACKFISHSQL');
        Add('Password=masterkey');
        Add('user_name=sysdba');
        Add('port=2508');
        Add('create=True');
        Add('readonlydb=False');
        Add('HostName=localhost');
        Add('Database=C:\programs\us\db\blackfishsql\_data\'
            + 'training_1.jds');
      end;

      LoginPrompt:= False;

      Connected:= True;
    end;
  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:

system_data_dll_reference

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 system.data.common// dbConnection

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

procedure TForm1.create_database_Click(SenderTObject);
  var l_c_db_provider_factoryDbProviderFactory;
      l_c_db_connectionDbConnection;
  begin
    l_c_db_provider_factory:= DbProviderFactories.GetFactory(
        'Borland.Data.BlackfishSQL.LocalClient');

    l_c_db_connection:= l_c_db_provider_factory.CreateConnection();

    l_c_db_connection.ConnectionString:=
           'host=LocalHost'
         + ';database='k_database_file_name
         + ';user=SYSDBA'
         + ';password=masterkey'
         + ';create=True'
         ;

    l_c_db_connection.Open;
  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:

    create_blackfishsql_database

  • 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;
      begin
        l_c_db_connection:= DataStoreConnection.Create();
        l_c_db_connection.ConnectionString:= ...ooo...

  • 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:

blackfishsql_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:

bsqlserver_exe

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

bsqlserver_options

Therefore

  • 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:

blackfishsql_service

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" ?>
<configuration>
  <appSettings>
    <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,
          CONNECT,PREPARE,
          SET_PARAMETER,EXECUTE,TRANSACTIONS,
          ERRORS,LOCK_ERRORS,SYSTEM
" />
      <add key="blackfishsql.assemblyPath" value="" />
  </appSettings>
</configuration>

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:

blackfishsql_server_and_sp

And

  • 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:

blackfishsql_server_win32



3.6.2 - BlackfishSql and .Net

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

    blackfishsql_server_net

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

    blackfishsql_server_net_db_xxx

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:

blackfishsql_server_net_in_process




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:

 
CREATE TABLE course
  (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:

visual_query_builder

and
   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:

vqb_select_department

   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:

vqb_one_table



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:

vqb_order_by



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:

vqb_join_tables



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)

create_table_aqb



Note that

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

    data_explorer_add_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:

lconnection_editor

and click "Ok"

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

const k_planning_table_name'planning';
      k_create_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;
  begin
    with Form1 do
    begin
      Try
        Try
          SqlConnection1.Open;
          l_c_dbx_transaction:= SqlConnection1.BeginTransaction;
          SqlConnection1.ExecuteDirect(p_sql_request);
          SqlConnection1.CommitFreeAndNil(l_c_dbx_transaction);
        Except
          SqlConnection1.RollBackFreeAndNil(l_c_dbx_transaction);
        end;
      finally
        SqlConnection1.Close;
      end;
    end// with Form1
  end// execute_request

procedure TForm1.create_table_Click(SenderTObject);
  begin
    execute_request(k_create_planning);
  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);
  begin
    execute_request(k_drop_planning);
  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);
  begin
    SqlDataSet1.Close;
    SqlDataSet1.DbxCommandType:= 'Dbx.MetaData';
    SqlDataSet1.CommandText:= 'GetTables';
    SqlDataSet1.Open;

    table_name_listbox_.Items.Clear;
    while not SqlDataSet1.Eof do
    begin
      table_name_listbox_.Items.Add(
          SqlDataSet1.FieldByName('TableName').AsString);
      SqlDataSet1.Next;
    end;
  end// list_tables_Click

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

list_tables



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:

const
      k_registering_table_name'registering';
      k_create_registering=
          '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);
  begin
    with Form1 do
    begin
      Try
        Try
          SqlConnection1.Open;
          SqlConnection1.ExecuteDirect(p_sql_request);
        Except
          on eException do
            display('  *** error 'e.Message);
        end;
      finally
        SqlConnection1.Close;
      end;
    end// with Form1
  end// execute_request

procedure TForm1.create_w_sql_connection_Click(SenderTObject);
  begin
    execute_request(k_create_registering);
  end// create_sql_connection_Click




Note
  • 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;
  begin
    l_c_db_provider_factory:= DbProviderFactories.GetFactory('Borland.Data.BlackfishSQL.LocalClient');

    Result:= l_c_db_provider_factory.CreateConnection();

    Result.ConnectionString:=
           'host=LocalHost'
         + ';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_transactionDbTransaction;
      l_c_db_commandDbCommand;
  begin
    Try
      Try
        l_c_db_connection:= f_c_db_connection;
        l_c_db_connection.Open;
        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;

        l_c_db_command.ExecuteNonQuery;
        l_c_db_transaction.Commit;
      except
        l_c_db_transaction.RollBack;
      end;

    finally
      l_c_db_connection.Close;
    end;
  end// execute_db_request_non_select

procedure TForm1.create_w_db_connection_Click(SenderTObject);
  begin
    execute_db_request_non_select(k_create_registering);
  end// create_w_dbconnection_Click

procedure TForm1.drop_w_db_connection_Click(SenderTObject);
  begin
    execute_db_request_non_select(k_drop_registering);
  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:

dbconnection_execute_non_reader



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:

 
INSERT INTO course
  (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

insert_into_active_query_builder



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;
      p_course_nameStringp_daysIntegerp_priceDouble);
    var l_requestString;
    begin
      l_request:= k_insert_into_course
        + '('
        +          IntToStr(p_id)
        +    ', 'QuotedStr(p_course_name)
        +    ', 'IntToStr(p_days)
        +    ', 'FloatToStr(p_price)
        + ')';
      execute_request(SqlConnection1l_request);
    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;
  interface
    uses  SqlExpr// tSqlConnection

    procedure execute_request(p_c_sql_connectiontSqlConnection;
        p_sql_requestString);

  implementation
    uses DbxCommon
       , u_c_display
       ;

    procedure execute_request(p_c_sql_connectiontSqlConnection;
        p_sql_requestString);
      var l_c_dbx_transactiontDbxTransaction;
      begin
        with p_c_sql_connection do
        begin
          Try
            Try
              Open;
              l_c_dbx_transaction:= BeginTransaction;
              ExecuteDirect(p_sql_request);
              CommitFreeAndNil(l_c_dbx_transaction);
            Except
              display('  *** error');
              RollBackFreeAndNil(l_c_dbx_transaction);
            end;
          finally
            Close;
          end;
        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:

 
DELETE FROM course

Here is a snapshot of the application:

insert_into_course

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:

 
SELECT *
  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

data_explorer_select



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"

    commandtext_editor

    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:

select_clientdataset



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=
    'C:\programs\us\db\blackfishsql\_data\training.jds';
var g_c_db_connectionDbConnection;

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

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

unit u_bfs_net;
  interface
    uses System.Data.Common;

    function f_c_db_connection(p_database_filenameString): DbConnection;

  implementation

    function f_c_db_connection(p_database_filenameString): DbConnection;
      var l_c_db_provider_factoryDbProviderFactory;
      begin
        l_c_db_provider_factory:= 
            DbProviderFactories.GetFactory('Borland.Data.BlackfishSQL.LocalClient');

        Result:= l_c_db_provider_factory.CreateConnection();

        Result.ConnectionString:=
               'host=LocalHost'
             + ';database='p_database_filename
             + ';user=SYSDBA'
             + ';password=masterkey'
             + ';create=True'
             ;
      end// f_c_db_connection

  end.

   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_readerDbDataReader;
  begin
    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
      display(l_c_db_reader.GetString(0)
          + ' 'l_c_db_reader.GetString(1));

    l_c_db_reader.Close;
    l_c_db_command.Dispose;
  end// select_Click

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

select_dbreader



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);
  begin
    ClientDataSet2.Close;
    SqlConnection1.Open;

    with SqlDataset2 do
    begin
      Close;
      CommandText:= k_parametrized_select;

      Params.Clear;

      g_c_parameter:=
          Params.CreateParam(ftInteger'c_days'ptInput);

      // 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);
  begin
    // SqlDataset2.ParamByName('c_days').AsString:= days_edit_.Text;
    // SqlDataset2.Params[0].AsString:= days_edit_.Text;
    ClientDataSet2.Close;
    g_c_parameter.Value:= StrToInt(days_edit_.Text);
    ClientDataSet2.Open;
  end// execute_Click

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

win32_parametrized_query

 

6.4.2 - .Net parametrized queries

As an example, we will use:

 
SELECT * 
  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;
    g_c_db_parameterDbParameter;

procedure TForm1.prepare_Click(SenderTObject);
  begin
    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;
    g_c_db_parametrized_command.Parameters.Add(g_c_db_parameter);

    g_c_db_parametrized_command.Prepare;
  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;
  begin
    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
      display(l_c_db_reader.GetString(1));

    l_c_db_reader.Close;
  end// execute_param_Click

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

parametrize_query_net




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:

 

[DBXTRACECONNECTION]
DriverName=DBXTrace
TraceFlags=NONE

[DBXTRACECONNECTION_W_FILE]
DriverName=DBXTrace
TraceFlags=NONE
TraceFile=dbx_trace.txt
TraceDriver=False

[dbx_training_jds]
DriverName=BlackfishSQL
HostName=localhost
port=2508
Database=C:\programs\us\db\blackfishsql\_data\training.jds
create=true
User_Name=sysdba
Password=masterkey
BlobSize=-1
TransIsolation=ReadCommited

[dbx_training_jds_trace]
DriverName=BlackfishSQL
HostName=localhost
port=2508
Database=C:\programs\us\db\blackfishsql\_data\training.jds
create=true
User_Name=sysdba
Password=masterkey
BlobSize=-1
TransIsolation=ReadCommited
delegateconnection=DBXPOOLTRACECONNECTION_W_FILE

   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;
{READER } {C_ID TDBXTypes.INT32 }
{READER } {C_COURSE_NAME TDBXTypes.WIDESTRING }
{READER } {C_DAYS TDBXTypes.INT32 }
{READER } {C_PRICE TDBXTypes.BCD }
{COMMAND } CommandC1_2 := ConnectionC1.CreateCommand;
{COMMAND } CommandC1_2.CommandType := 'Dbx.MetaData';
{COMMAND } CommandC1_2.Text := 'GetIndexes "C:\programs
       \us\db\blackfishsql\_data
       \training.jds"."DEFAULT_SCHEMA"."COURSE" ';
{COMMAND } ReaderC1_2_1 := CommandC1_2.ExecuteQuery;
{READER } {CatalogName TDBXTypes.WIDESTRING }
{READER } {SchemaName TDBXTypes.WIDESTRING }
{READER } {TableName TDBXTypes.WIDESTRING }
{READER } {IndexName TDBXTypes.WIDESTRING }
{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;
      interface

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

      implementation  

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

      end.

  • 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;
  BEGIN
    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;
  interface

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

  implementation

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

  end.

   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:

udf_dll

   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

create_blackfishsql_udf_method

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

use_bfs_udf



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
           public
             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.

So
   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:

udf_with_out_param



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

add_system_data_reference

   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;
  interface
    uses system.Data
        , system.Data.Common
        ;

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

  implementation

    // -- 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_readerDbDataReader;
      begin
        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)+ ' ';

        l_c_db_reader.Close;
        l_c_db_command.Dispose;
      end// f_training_names

  end.

   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

stored_procedure_select_net



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_live_template

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

add_template

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 fcolibri@felix-colibri.com 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 fcolibri@felix-colibri.com.
  • 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: feb-2018 - 98 articles, 232 .ZIP sources, 1232 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2018. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ 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
      + oracle
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
    + vcl
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog