Connections can be local, remote, or a combination of both:

• Local connections Local connections access the Blackfish SQL database engine in-process. This provides improved performance over a remote driver, but requires that the Blackfish SQL engine be present and running in the same process as the application. Several simultaneous local connections created in the same process can connect to a database. However, only one process can have a database file open at a time. Consequently, while a process has a database file open, it becomes the only process that can connect to that database over a local connection.
• Remote connections To use a remote connection, first launch the Blackfish SQL server. (For instructions, see Administering Blackfish SQL.) The remote driver communicates with the server over TCP/IP. Remote connections can be slower for database interactions that involve multiple round trips between the client and the database for small packets of data. Remote connections allow more than one process running on one or more computers to access the same database. If several processes require simultaneous access, it is best to use remote connections.
• Combination of local and remote connections A third option is to use a combination of the local and remote connections. If there is one process that performs the majority of database interactions, this process can launch the Blackfish SQL Server inside its own process. In this way, the demanding database process can use the local driver while still allowing other processes to access the same database via the remote driver.

Native applications can use dbExpress to establish remote connections with a Blackfish SQL server. Local connections are currently not supported for dbExpress. You must start the Blackfish SQL server before you can use the remote dbExpress driver to connect. (For instructions, see Administering Blackfish SQL.)  

Example 

This example shows how to acquire a remote dbExpress connection:

[BlackfishSQL] 

      uses DBXCommon; 

      uses DBXClient; 

      var Connection: TDBXConnection; 

      Connection :=
TDBXConnectionFactory.GetConnectionFactory.GetConnection('BLACKFISHSQLCONNECTION');

The dbExpress dbxdriver.ini file contains default driver properties appropriate for most applications. The dbExpress dbxconnections.ini file has a BLACKFISHSQLCONNECTION section that contains the default connection settings. New connections can copy most of these properties. This list of properties are typically customized for new connections:

• [BLACKFISHCUSTOMCONNECTION]
• HostName=localhost
• port=2508
• Database=c:/tmp/test

The Blackfish SQL assembly Borland.Data.BlackfishSQL.LocalClient.dll contains an ADO.NET 2.0 driver. You can build an application without directly referencing this assembly by using the DbProviderFactory class. For this approach to work, the file machine.config must contain references to the Blackfish SQL assemblies in the DbProviderFactory section, and the Blackfish SQL assemblies must be installed in the Global Assembly Cache (GAC). For easier deployment, use a direct reference to the Blackfish SQL assembly. 

You can use a local ADO connection, a remote ADO connection, or a combination of both to connect with the Blackfish SQL server.

You can establish a local ADO connection in either of the following ways:

• Local ADO.NET Connection Using DbProviderFactory
• Local ADO.NET Connection Using a Direct Class Reference

Example 

This example illustrates how to acquire a local ADO connection using DbProviderFactory:

[References: System.Data.dll] 
uses System.Data.Common;
var Factory: DbProviderFactory; 
var Connection: DbConnection; 
Factory := DbProviderFactories.GetFactory('Borland.Data.BlackfishSQL.LocalClient'); 

Connection := Factory.CreateConnection(); 
Connection.ConnectionString := 'database=<filename>;user=<username>;password=<password>';

Connection.Open; 
 

Example 

This example illustrates how to acquire a local ADO connection by using a direct class reference:

[References: System.Data.dll]
[References: Borland.Data.BlackfishSQL.LocalClient.dll]

uses System.Data.Common;
uses Borland.Data.DataStore;
var Connection: DbConnection;
Connection := DataStoreConnection.Create;
Connection.ConnectionString := 'database=<filename>;user=<username>;password=<password>';

Connection.Open;

Managed applications can use ADO.NET to establish remote connections with the Blackfish SQL server. You must start the server before you can use the remote ADO driver to connect. (For Instructions, see Administering Blackfish SQL.) Once the server is running, you can acquire a remote ADO connection in either of the following ways:

• Remote ADO.NET Connection Using DbProviderFactory
• Remote ADO.NET Connection Using a Direct Class Reference

Example This example shows how to acquire a remote ADO connection using DbProviderFactory:

[References: System.Data.dll] 
uses System.Data.Common; 
var Factory: DbProviderFactory; 
var Connection: DbConnection; 
Factory := DbProviderFactories.GetFactory('Borland.Data.BlackfishSQL.RemoteClient'); 

Connection := Factory.CreateConnection(); 
Connection.ConnectionString :=
'database=<filename>;user=<username>;password=<password>;host=<servername>;protocol=TCP'; 

Connection.Open;

Example This example shows how to acquire a remote ADO connection using a direct class reference:

[References: System.Data.dll]
[References: Borland.Data.BlackfishSQL.RemoteClient.dll]

uses System.Data.Common;
uses Borland.Data.DataStore;
var Connection: DbConnection;
Connection := DataStoreConnection.Create;
Connection.ConnectionString :=
'database=<filename>;user=<username>;password=<password>;host=<servername>;protocol=TCP'; 

Connection.Open;

You can use a local JDBC connection, a remote JDBC connection, or a combination of both to connect with the Blackfish SQL server. The following sections provide instructions for each of these procedures.

A Blackfish SQL local JDBC connection allows an application to run in the same process as the Blackfish SQL engine. Applications that make large numbers of method calls into the JDBC API will see a significant performance advantage using the local Blackfish SQL driver.  

You can establish a local JDBC connection in either of the following ways:

• Local JDBC Connection Using the DriverManager
• Local JDBC Connection Using a JDBC DataSource

Example This example shows how to acquire a local JDBC connection using the DriverManager:

[jdsserver.jar must be in classpath]
java.sql.DriverManager.registerDriver(new com.borland.datastore.jdbc.DataStoreDriver());  
connection = java.sql.DriverManager.getConnection("jdbc:borland:dslocal:<filename>", "<username>", "<password>"); 

Example This example shows how to acquire a local JDBC connection using a JDBC DataSource:

[jdsserver.jar must be in classpath] 
com.borland.javax.sql.JdbcDataSource dataSource = new com.borland.javax.sql.JdbcDataSource(); 
dataSource.setDatabaseName("<filename>"); 
connection = dataSource.getConnection("<username>", "<password>");

Managed applications can use JDBC to establish remote connections with the Blackfish SQL server. You must start the server before you can use the remote ADO driver to connect. (For instructions, see Administering Blackfish SQL.)  

You can establish a remote JDBC connection in either of the following ways:

• Remote JDBC Connection Using the DriverManager
• Remote JDBC Connection Using a JDBC DataSource

Example 

This example shows how to acquire a remote JDBC connection using the DriverManager:

[jdsremote.jar must be in classpath] 
java.sql.DriverManager.registerDriver(new com.borland.datastore.jdbc.DataStoreDriver()); 
connection = java.sql.DriverManager.getConnection("jdbc:borland:dsremote://<servername>/<filename>", "<username>", "<password>");

Example This example shows how to acquire a remote JDBC connection using a JDBC DataSource:

[jdsremote.jar must be in classpath] 
com.borland.javax.sql.JdbcDataSource dataSource = new com.borland.javax.sql.JdbcDataSource(); 
dataSource.setDatabaseName("<filename>"); 

dataSource.setNetworkProtocol("tcp");
datasource.setServerName("<servername>");

connection = dataSource.getConnection("<username>", "<password>");

You can specify connection properties for:

• dbExpress
• ADO
• JDBC

For more information, see the RAD Studio help for Borland.Data.DataStore.ConnectionProperties.

dbExpress connection properties are documented in the DbxCommon.pas unit and in the Blackfish SQL ConnectionProperties class. dbExpress connection properties are stored in the dbxconnections.init file.  

Example 

This example shows a sample Blackfish SQL connection properties section in the dbxconnections.ini file:

[BLACKFISHSQLCONNECTION]  
DriverName=BlackfishSQL  
HostName=localhost 
port=2508
Database=/tmp/test
create=true 
User_Name=sysdba
Password=masterkey 
BlobSize=-1 
TransIsolation=ReadCommitted 

The HostName, port, and create properties are documented in ConnectionProperties. The DriverName, User_Name, BlobSize, and TransIsolation properties are documented in TDBXPropertyNames of the DBXCommon unit.

The ConnectionString property in DbConnection or DataStoreConnection can contain settings from ConnectionProperties. 

You can use DataExplorer to set and modify values for ConnectionProperties. For instructions, see the RAD Studio help for the DataExplorer Connection dialog box.

You can specify JDBC connection properties using either:

• a JDBC URL
• java.util.Properties

You can specify connection properties in a JDBC URL, using semicolons to separate the properties:

jdbc:borland:dslocal:c:/mydb.jds;create=true 

Example 

This example shows how to specify JDBC connection properties using a java.util.Properties object:

java.util.Properties props = new java.util.Properties();  
props.setProperty("create","true");  
props.setProperty("user","SYSDBA");  
props.setProperty("password","masterkey");  
connection = DriverManager.getConnection("jdbc:borland:dslocal:c:/mydb.jds", props);

To make the latest version of Blackfish SQL available to JBuilder and the Borland Enterprise Server (BES), copy these files from the Blackfish SQL lib directory to the lib directory of the target product:

• beandt.jar
• dbtools.jar
• dx.jar
• jds.jar
• jdsremote.jar
• jdsserver.jar
  1. For JBuilder or BES, find the listed files in the lib directory of the install tree and copy them to a backup directory.

2. Find the files in the lib directory of the Blackfish SQL installation and copy them to the lib directory of JBuilder or BES.

You can use the DataDirectory macro to specify relative path names for database files. The DataDirectory macro is supported for both the Blackfish SQL ADO.NET and DBXClient drivers. If a database file name is prepended with the following string:

|DataDirectory|

for example:

|DataDirectory|employee.jds

the string |DataDirectory| will be replaced with the appropriate string, as follows:  

Blackfish SQL for Windows:  

• For ASP.NET web based applications, this will be the App_Data folder name.
• For non-web applications, this defaults to the directory of the application executable. You can override the default by setting the DataDirectory property for AppDomain:

AppDomain.CurrentDomain.SetData("DataDirectory", "CustomAppPath") 
          

Blackfish SQL for Java:  

If the System property blackfishsql.datadirectory is set, the setting for this property will be used as the replacement string. Otherwise the setting for the user.home property will be used.

Preface 

Overview 

System Architecture 

Administering Blackfish SQL 

Using Blackfish SQL Security 

Using Stored Procedures and User Defined Functions 

Using Triggers in Blackfish SQL Tables 

Stored Procedures Reference 

SQL Reference 

Optimizing Blackfish SQL Applications 

Deploying Blackfish SQL Database Applications 

Troubleshooting