Developing and Deploying with Blackfish SQL and Delphi

By: Nick Hodges

Abstract: This article summarizes what you need to know about developing and deploying Blackfish SQL that is part of RAD Studio 2007

CodeGear RAD Studio 2007 includes in the box a deployable version of Blackfish™ SQL. Blackfish is a managed SQL database. “Managed” means that Blackfish runs in the two most popular managed environments, .NET and Java. The version in CodeGear RAD Studio 2007 is the .NET version. This also means Blackfish requires the .NET 2.0 Framework in order to run.

One often thinks of a Relational Database Management System (RDBMS) as a large, monolithic application running on a backroom server and requiring lots of care from a DB Administrator. The perception is that to get that kind of power, you need that kind of application.

Well, Blackfish SQL provides all that kind of power, but in a lean, flexible form that allows a developer to use Blackfish in any number of ways. By leveraging the .NET Framework, Blackfish provides all the capabilities of a full-fledged RDBMS, yet offers the flexibility of scaling from an embedded system all the way up to an enterprise system.

Blackfish can actually be run three different ways:

  1. As a Windows Service
  2. As an standalone executable
  3. As an in-process assembly

An application can connect to Blackfish either remotely or locally. When connecting remotely, the connecting application will use the TCP/IP stack to pass information. When connecting locally, the connecting application will simply bind to the Blackfish assembly like any other.

Upon the installation of CodeGear RAD Studio 2007, Blackfish will be installed as a Windows Service, set to automatically run on startup. This will make Blackfish universally available on a developer’s machine. Blackfish uses port 2508 by default.

    Developing with Blackfish

The local, in-process version of Blackfish allows a single process to access the database. This works well when deploying your application but can cause a conflict during development when both the IDE and your running application may need to connect to the database.

For that reason, CodeGear recommends that developers use a remote server (either the Windows Service or by running “bsqlserver.exe” at the command line) for all development. That way, there will never be contention between multiple processes for access to the database. Once the application is ready to deploy, the application can be easily switched to use the local, in-process server if desired.

Because all of the code for Blackfish is contained in a single assembly, and that assembly is used both as the in-process binary and for the remote server, an application will run exactly the same whether run locally or remotely.

    The License File

No matter how an application connects, the Blackfish assembly will require a license file to run. In addition to the obvious function of ensuring that the developer has a legal copy of Blackfish, the license file will also determine the capabilities and limitations for Blackfish. The license file needs to be found in a location that the Blackfish binaries know about. The most common place to put the file is in the same directory that the binary is running from. In addition, Blackfish will search for the license in the directory of the binary that loaded the Blackfish assembly, as well as the \license directory within the $(BDSCOMMONDIR) path defined in the environment variables.

As for the license file itself, in a default installation, it can be found here:

C:\Program Files\CodeGear\RAD Studio\5.0\bin

The license file is named: BlackfishSQL.slip

The license can be deployed as required with Blackfish-based applications. However, the license cannot be transferred to other users or to other applications not built with RAD Studio. See the End-User License Agreement for more details.

    Deploying as a Windows Service

CodeGear RAD Studio’s installer will install Blackfish as a Windows Service and set it to start automatically. But what if you want to set it up as a service on another machine? This is pretty straightforward. First, you need to deploy the following four (one is optional) files:

File

Description

bsqlserver.exe

The executable file that runs Blackfish as a server. This is basically a host for the Local Client Assembly.

Borland.Data.Blackfish.LocalClient.dll

The assembly that holds the main functionality of Blackfish. You can place this file in the machines GAC, or in the same directory as bsqlserver.exe

BlackfishSQL.slip

The license file for Blackfish. Without this file, Blackfish will run, but won’t accept any connections

bsqlserver.exe.config

This is optional. Deploy only if you have settings in it that you want for your Blackfish deployment.

Once those files are deployed, all you need to do is call the bsqlserver.exe executable from the command-line, passing the –install switch. If you want to give the Service a name, you can pass an additional parameter of a service name.

For example:

C:\> bsqlserver.exe –install MyBlackfish

will install Blackfish as a service with the name “MyBlackfish”.

    Deploying as a Stand-alone Executable

You can also deploy Blackfish as a regular executable on another machine. The process is the same as deploying as a Windows Service, except you run the server without the -install switch. The server will run in a console window. You can shut the server down anytime by merely closing the console window or typing CTRL-C into the console window.

    Deploying an In-process Application

Blackfish can also deploy as an in-process database. As noted above, the recommended manner of developing a Blackfish application is to use the remote connection. However, if an application is to be deployed as an in-process project, the developer will need to convert the application from being a remote application during development to being an in-process one. Making this change is a simple two-step process:

  1. Alter the connection string to point to the local assembly instead of the remote assembly
  2. Ensure that the local assembly is available to the application.

The connection string merely needs to be altered to refer to

providerName="Borland.Data.BlackfishSQL.LocalClient"

instead of

providerName="Borland.Data.BlackfishSQL.RemoteClient"

If the application is an ASP.NET application, there is a “commented-out” connection string in the default web.config file that can be used for deploying local client Blackfish applications.

Once that change is made, then the Borland.Data.BlackfishSQL.LocalClient.dll assembly simply needs to be placed where the application itself can find it. This will most likely be in the same directory as the main application binary or in the GAC of the machine to which the application is deployed..

For ASP.NET 2.0 applications, the assembly can be placed in the applications \bin directory with the other binaries for an application.

    ASP.NET and Connection Strings

Blackfish is a perfect solution for ASP.NET applications. Because it can be so easily used as an in-process database, Blackfish can provide fast, easy access to data for an ASP.NET application.

ASP.NET applications usually access data from a sub-directory called \app_data off of the main virtual directory. To make this easy, Blackfish includes the alias |DataDirectory| for easily referencing this directory. Thus, a connection string for use with an ASP.NET application can contain a reference such as this:

|DataDirectory|AppDataFiles.jds

to point to the proper sub-directory that contains data for an ASP.NET application. This setting is configurable in Blackfish’s configuration file.

    Blackfish’s Configuration File

Blackfish includes a configuration file named bsqlserver.exe.config that contains configuration information that can be used to adjust the settings for Blackfish. bvsqlserver.exe.config is an xml file that contains entries defining configuration items for Blackfish. The entries are done in the following format:

<add key="blackfishsql.<keyname>" value="<setting value>" />

The following options can be set:

Configuration Setting

Key Name

Description

Minimum starting cache size in blocks

minCacheSize

Default block size for databases is 4096 bytes. Sets the minimum number of cache blocks in the cache. Note that all

databases opened in the same process share the same cache

Maximum sort buffer size in bytes

maxSortBuffer

When sorting operations are performed for operations like index building, this is the maximum memory in bytes that will be used for a sort buffer. Increasing this number may speed up very large sorts. Cannot be set to less than 32K (32*1024). Default is currently 12M (12*1024*1024).

Specify directory for temp files.

tempDir

Used for operations like large sorts. If this is not set, the directory of the database will be used for temporary files.

Directory for locating the Blackfish license

licenseDirectory

Full path for the BlackfishSQL.slip file (i.e. the license file). If this entry is not specified, the binary will look for the license in the execution directory, then the directory of the binary that loaded the Blackfish assembly, and finally in the \CodeGear directory within the $(BDSCOMMONDIR) path defined in the environment variables.

Specify the port to communicate with a remote server.

port

TCP/IP port that the server will listen on. Default is 2508.

Defines directory for the |DataDirectory| macro

dataDirectory

If this directory location is specified, this will be used to replace the macro |DataDirectory| at the beginning of a database file name specification.

Log file used for all open databases to log status, errors, and execution.

logFile

If this property is set to "con", the log output will go to the standard output.

Enable logging for the specified events.

logFilters

This property can be set to one of two types of values:

  1. List of comma separated LogFilter String values.
  2. The string representation of the one or more LogFilterCodes that have been or'd together before converted to a String value.

LogFilterCodes class documentation for a filter codes that can be or'd together that can be applied. LogFilter class documentation for a list of filters that can be applied.

This is only used by Blackfish SQL on the .NET platform to specify additional locations for the assemblies that contain stored procedures and triggers.

assemblyPath

If the assembly containing stored procedures and triggers cannot be loaded from the GAC or the application directory, the following search will be performed.

  1. If assemblyPath is set to "none", no other directories will be searched.
  2. If the assemblyPath is set to one or more semicolon separated paths, these paths will all be searched for the assembly.
  3. If the assemblyPath is not set to "none" and the assembly was not found in step number 2, the path specified by the $BDSCOMMONDIR)environment variable will be searched.

    Conclusion

Blackfish SQL is a flexible, powerful, fully database that conforms with the SQL-92 standard. It has the power to run as an enterprise platform, and the flexibility to deploy as an embedded ISV solution. It’s very easy to deploy, administer, and manage. This document showed you the proper way to develop and deploy a Blackfish-based solution.

Server Response from: ETNASC03