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:\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_value: integer): Integer; static;         end; // c_compute   implementation       class function c_compute.f_square(p_value: integer): Integer;       begin         result:= p_value* p_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_euro: Double): 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_convert= Class                       public                         class function f_euro_to_dollar(p_euro: Double): Double; static;                     end; // c_convert   implementation     class function c_convert.f_euro_to_dollar(p_euro: Double): 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:    restart the BlackfishSql service (open the service dialog, select BlackfishSql and click the little black forward triangle at the top) Now integrate this new routine to the engine    open the Active Query Builder, (select the Data Explorer, then "dbExpress | BlackfishSql | dbx_training_jds | Sql Window"    make sure the connection is open (by expanding for instance on the Table node)    type the CREATE METHOD for our new function and click the "Execute Sql" button at the top    a "0 rows affected" dialog box is displayed Please note those steps must be followed with some care. You will more often than not hit errors like "server explicitely refuses the connection", or, when the orthograph of the AS is wrong, "the function could not be found on the server" the Package was written using the .Net personality, but we are not forced to use an Asp.Net connection. In our case, we used the dbExpress connection the creation of the method could have been performed by code, but we still must take care of closing and restarting the server Now we can use this f_euro_to_dollar UDF:    open the Visual Query Builder, and write an Sql statement using this UDF (you can paste our routine, or "visually build" the request by clicking COURSE, then the columns, as explained before:   SELECT c_course_name, f_euro_to_dollar(c_price)    FROM course    click the "Execute Sql" button at the top    here is the result Naturally we can use this function now in any Sql request executed from .Net or Win32 code. 8.2.1 - An UDF with an OUT parameter Here is an example of a PROCEDURE with an OUT parameter:    stop the BlackfishSql service    in the previous U_C_BFS_UDF, add a PROCEDURE with this OUT parameter. The c_convert CLASS definition now becomes: type c_convert=          Class            public              class function f_euro_to_dollar(p_euro: Double): Double; static;              class procedure convert_euro_to_dollar(p_euro: Double;                  Out pv_dollar: Double); 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: 8.3 - Stored Procedures Stored procedures can be written using exactly the same steps. First, we will use a SP which does some internal requests, with some input and output parameters. To execute Sql requests, the stored procedure must have a connection. This is done by adding a DbConnection parameter to the request. Our example will simply return the list of training names. Here are the steps:    close the BlackfishSql service    create a new .Net Package, rename it PK_BFS_STORED_PROC    since we will use a DbConnection, we must include a reference to the SYSTEM.DATA assembly. To do so, in the Data Explorer, select "pk_bfs_store_proc.dll | Add Reference"    the .Net Add Reference is displayed, and the available assemblies are gradually added    after a couple of seconds, select in the top listbox SYSTEM.ASSEMBLY, and click "Add Reference"    this assembly is added to the bottom "New references" listbox    click "Ok"    this assembly is added to the "Requires" clause of the Package    add a UNIT to this package ("File | New etc), rename it U_BFS_STORED_PROC    add the code which computes the list of training names: unit u_bfs_stored_proc;   interface     uses system.Data         , system.Data.Common         ;     type u_c_course_sp=              Class                public                  class function f_training_names(                      p_c_connection: DbConnection): String; static;                end; // u_c_course_sp   implementation     // -- u_c_course_sp     class function u_c_course_sp.f_training_names(p_c_connection: DbConnection): String;       var l_c_db_command: DbCommand;           l_c_db_reader: DbDataReader;       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:= Result+ l_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 8.4 - Triggers Triggers can be handled in a way similar to stored procedures. 8.5 - BlackfishSql Live templates We have presented how to write your UDFs and Stored Procedures. In each case, you have to write the CLASS for each method write the declaration of the routine implement this routine write the Sql request to add the routine to the Database To automate this process, Nick HODGES has written an placed in Code Central a complete set of Blacckfishlive templates In order to add this template suite to RAD Studio 2007    download the .ZIP from code central    copy the .XML live templates files to     C:\Documents and Settings\My Documents\RAD Studio\code_templates To use those templates    open the .PAS with our stored procedure    place the caret in the CLASS in order to add a new method    type Ctrl J (or use the contextual menu)    the live templates starting with bf are displayed:    select "bfmethod", for instance and double click it    the template is inserted into the text (Ed : partial The template added the declaration the Sql creation request    as for other templates, fill in slots whith your identifiers The README which comes whith the .ZIP also contains detailed steps to install the additional .BPLs

9 - Download the Sources

• bfs_01_create_database_net.zip which creates a new database (10 K)
  
• bfs_01_create_database_win32.zip which creates a new database (10 K)

• bfs_02_create_table_net.zip: creating tables (12 K)
  
• bfs_02_create_table_win32.zip: creating tables(12 K)

• bfs_03_insert_into_win32.zip: inserting data in a Table (7 K)

• bfs_04_select_net.zip: SELECT and parametrized querries (18 K)
  
• bfs_04_select_win32.zip: SELECT and parametrized querries (17 K)

• bfs_05_tracing_delegate_win32.zip: using a tracing delegate (12 K)

• pk_bfs_udf_net.zip: the UDF package(4 K)
  
• pk_bfs_stored_proc.zip: the stored procedure package (3 K)
  

• 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
  

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

• create or select any folder of your choice
  
• unzip the downloaded file
  
• using Delphi, compile and execute
  

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

• the manual
  
  • BlackfishSql Developper's guide which is 200 pages + manual
    
• articles
  
  • Developing and Deploying with Blackfish SQL and Delphi
    
• add ons
  
  • Nick HODGES has written a set of Blacckfishlive templates that you can download from 24990 Nick's Blackfish SQL Development Pack
    
  • John MOSHAKIS has placed MSBuild task example to deploy Blackfish SQL stored procedures
    
• videos (SWF files)
  
  • BlackFish by Jens Ole LAURIDSEN (from CodeGear)
    
  • dbExpress 4 development by Steve SHAUGHNESSY who heads the database development at CodeGear
    
  • Steve SHAUGNESSY also has a blog with several Dbx4 entries. Here is a link to Steve's blog
    
  • Nick HODGES, who is the Delphi Product Manager at CodeGear has a full RAD Studio presentation and demonstration replay: RAD Studio 2007 announcement dated September 5, 2007. He has also a "Delphi Hour" presentation on the same topic
    

• Delphi Dbx Express 4 Programming which presents the new dbExpress driver layer, and many examples about using it
  

11 - The author