BlackfishSql - Felix John COLIBRI.

• abstract : using the new BlackfishSql standalone database engine of RAD Studio 2007 (Win32 and .Net)
  
• key words : BlackfishSql - tDbxConnection - Rad Studio 2007
  
• software used : Windows XP Home, RAD Studio 2007
  
• hardware used : Pentium 2.800Mhz, 512 M memory, 140 G hard disc
  
• scope : Delphi 2007, RAD Studio 2007 - Win32 and .Net framework 2.0
  
• level : Delphi developer
  
• plan :
  
  • The new Standalone BlackfishSql database engine
    
  • Connecting to the BlackfishSql EMPLOYEE.JDS Database
    
  • Creating a new BlackfishSql Database
    
  • Creating Database Tables
    
  • Reading and Writing data
    
  • Displaying BlackfishSql tables
    
  • UDF, Stored Procedures, Triggers

  • Download the Sources

  • BlackfishSql Documentation and References
    

1 - The new Standalone BlackfishSql database engine

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

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:

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
  

	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:
	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:
	you may also modify other connection parameters, using the "connection dialog | Advanced" button: 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"

	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

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

• 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
  

3.2 - Create a new BlackfishSql base using the Data Explorer

	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: click "Ok"
	click "Test Connection"
	the "Connection Succeeds" dialog is displayed
	click "Ok"
	a new empty database is created:

• 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

• we drop this component
  
• we initialize its tSqlConnection.Params property with the connection parameters (in the Object Inspector or by code)
  
• we open it
  

	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(Sender: TObject);   var l_c_sql_connection: tSqlConnection;   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

• 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 system.data.common; // dbConnection const k_database_file_name=           'C:\programs\us\db\blackfishsql\_data\training_2.jds'; procedure TForm1.create_database_Click(Sender: TObject);   var l_c_db_provider_factory: DbProviderFactory;       l_c_db_connection: DbConnection;   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: we created the DbConnection using a DbProviderFactory, but we could have directly created the connection with: procedure TForm1.create_w_datastore_Click(Sender: TObject);   var l_c_db_connection: DbConnection;   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

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

If you run the .EXE with a /h attribute to get all available 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
  

	select "Start | Parameters | Configuration Panel | Administration Tools | Services"
	the services dialog is displayed, with BlackfishSql among those:

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>

  
  

3.5.3 - BlackfishSql server configuration

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

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

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

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
    

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

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

3.6.2 - BlackfishSql and .Net

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

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

4 - Creating Database Tables

4.1 - Issuing Sql Requests

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

4.2.1 - Creating SELECT queries visually

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

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

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';       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_request: String);   var l_c_dbx_transaction: tDbxTransaction;   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(Sender: TObject);   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(Sender: TObject);   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'
  

	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(Sender: TObject);   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:

• 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_request: String);   begin     with Form1 do     begin       Try         Try           SqlConnection1.Open;           SqlConnection1.ExecuteDirect(p_sql_request);         Except           on e: Exception do             display('  *** error '+ e.Message);         end;       finally         SqlConnection1.Close;       end;     end; // with Form1   end; // execute_request procedure TForm1.create_w_sql_connection_Click(Sender: TObject);   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_connection: DbConnection;   var l_c_db_provider_factory: DbProviderFactory;   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_request: String);   var l_c_db_connection: DbConnection;       l_c_db_transaction: DbTransaction;       l_c_db_command: DbCommand;   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(Sender: TObject);   begin     execute_db_request_non_select(k_create_registering);   end; // create_w_dbconnection_Click procedure TForm1.drop_w_db_connection_Click(Sender: TObject);   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: 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

5.1.1 - INSERT INTO with the Active Query Builder

	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

• 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

	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(Sender: TObject);   procedure insert_into_course(p_id: Integer;       p_course_name: String; p_days: Integer; p_price: Double);     var l_request: String;     begin       l_request:= k_insert_into_course         + '('         +          IntToStr(p_id)         +    ', '+ QuotedStr(p_course_name)         +    ', '+ IntToStr(p_days)         +    ', '+ FloatToStr(p_price)         + ')';       execute_request(SqlConnection1, l_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_connection: tSqlConnection;         p_sql_request: String);   implementation     uses DbxCommon        , u_c_display        ;     procedure execute_request(p_c_sql_connection: tSqlConnection;         p_sql_request: String);       var l_c_dbx_transaction: tDbxTransaction;       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

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

6.2 - Display in the Object Inspector

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

	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:

• 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_connection: DbConnection; procedure TForm1.connect_Click(Sender: TObject);   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_filename: String): DbConnection;   implementation     function f_c_db_connection(p_database_filename: String): DbConnection;       var l_c_db_provider_factory: DbProviderFactory;       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(Sender: TObject);   var l_c_db_command: DbCommand;       l_c_db_reader: DbDataReader;   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

6.4 - Parametrized Queries

• 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

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_parameter: tParam; procedure TForm1.prepare_Click(Sender: TObject);   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(Sender: TObject);   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:

 

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_command: DbCommand;     g_c_db_parameter: DbParameter; procedure TForm1.prepare_Click(Sender: TObject);   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(Sender: TObject);   var l_c_db_reader: DbDataReader;   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:

7 - Tracing and Pooling delegate

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

	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:programsusdbblackfishsql_datatraining.jds create=true User_Name=sysdba Password=masterkey BlobSize=-1 TransIsolation=ReadCommited [dbx_training_jds_trace] DriverName=BlackfishSQL HostName=localhost port=2508 Database=C:programsusdbblackfishsql_datatraining.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);