Blackfish SQL Developer's Guide Overview

By: Dee Elling

Abstract: Overview of the Blackfish SQL database and link to the download of the Blackfish SQL Developer's Guide.

    Blackfish™ SQL Developer's Guide Overview

This document provides a brief overview of Blackfish SQL and the Blackfish SQL API for Windows. For updates to this document, see the CodeGear Developer Network Blackfish SQL Developer's Guide Overview.

The Blackfish SQL Developer's Guide describes how to create Blackfish SQL applications. It is available for download at http://cc.codegear.com/Item/24980.

    Contents

    Blackfish SQL

Blackfish SQL is a high-performance, small-footprint, transactional database. Blackfish SQL was originally implemented as an all-Java database called JDataStore, which is now called Blackfish SQL for Java. Blackfish SQL was ported from Java to C#. The C# implementation is called Blackfish SQL for Windows and is supported in RAD Studio 2007.

Blackfish SQL emphasizes database performance, scalability, ease of use, and a strong adherence to industry standards. Blackfish SQL capabilities include:

  • Industry standards compliance
    • Entry level SQL-92
    • Unicode storage of character data
    • Unicode-based collation key support for sorting and indexing
    • dbExpress 4 drivers for Win32 Delphi and C++
    • ADO.NET 2.0 providers for .NET
    • JDBC for Java
    • JavaBean data access components for Java
    • XA/JTA Distributed transactions for Java
  • High performance and scalability for demanding online transaction processing (OLTP) and decision support system (DSS) applications
  • Delphi, C#, and VB.NET stored procedures and triggers for Windows
  • Java-stored procedures and triggers
  • Zero-administration, single assembly or single-jar deployment
  • Database incremental backup and failover

    Blackfish SQL DataStore

Blackfish SQL is the name of the product, its tools, and of the file format. Within RAD STudio 2007, there are assemblies that includes a DataStore class.

    Compatibility Between Windows and Java

Blackfish SQL for Windows and Blackfish SQL for Java are compatible, though some restrictions apply. The database file format is binary-compatible between the two. The database clients and servers are interchangeable. Windows clients can connect to Java servers and Java clients can connect to Windows servers. However, because the Blackfish SQL for Windows implementation is more recent, some Blackfish SQL for Java features are not yet supported for the Windows version.

The Blackfish SQL Developer's Guide describes how to create Blackfish SQL applications. It is available for download at http://cc.codegear.com/Item/24980.

    Blackfish SQL API for Windows

The Blackfish SQL API may be used in Delphi and C++ programs with the DBXClient DBX4 driver. .NET applications can use the API with the ADO.NET Provider. In RAD Studio the API is in the Borland.Data.DataStore and Borland.Data.MetaData namespaces.

The administrative capabilities listed below are not yet supported in DataExplorer for Blackfish SQL for Windows. Use SQL commands or Blackfish SQL built-in DB_ADMIN stored procedures to complete these tasks. For details see the Blackfish SQL Developer's Guide at http://cc.codegear.com/Item/24980.

  • Create/alter autoincrement and max inline properties for columns
  • Create secondary indexes
  • Create, alter, drop users and roles
  • Create, alter, drop database mirrors
  • Database encryption
  • Database backup

    Administration and Utility Functions Available From SQL

Two classes are available, DB_ADMIN and DB_UTIL. These methods can be called from SQL using the CALL statement. They can be called without creating a METHOD alias, because the Blackfish SQL dialect recognizes methods in DB_ADMIN as built-in methods.

    DB_ADMIN class

DB_ADMIN is a group of stored procedures for performing a variety of database administration tasks. Some of the capabilities include:

  • Configuring automatic failover and incremental backup
  • Viewing and altering database configurations
  • Backing up databases (explicit)
  • Encrypting databases
  • Mirror management

    DB_UTIL class

DB_UTIL is a set of SQL stored procedures for performing numeric, string and date/time operations on data stored in database tables. These procedures include such functions as:

  • Mathematical functions, such as trigonometric, arithmetic, and random
  • String manipulation functions
  • Date and time functions

    ADO.NET

Blackfish SQL includes an ADO.NET implementation. This is similar to the AdoDbx Client, which is also an ADO.NET implementation.

  • DataStoreCommand: Provides execution of SQL statements and execution of stored procedures.
  • DataStoreCommandBuilder: Generates single-table commands to reconcile changes made to a DataSet with its underlying database.
  • DataStoreConnection: Provides a connection to a database.
  • DataStoreConnectionPool: Provides access to a connection pool.
  • DataStoreDataAdapter: Fills a DataSet and updates a database.
  • DataStoreDataReader: Gives access to a result table from a database server.
  • DataStoreDataSourceEnumerator: Provides enumerator for finding all data sources on local network.
  • DataStoreParameter: Specifies parameter for DataStoreCommand.
  • DataStoreParameterCollection: Collection of parameters for DataStoreCommand.
  • DataStoreProviderFactory: Base class for a provider's implementation of data source classes.
  • DataStoreRowUpdatedEventArgs: Holds data for RowUpdated event of DataStoreDataAdapter.
  • DataStoreRowUpdatingEventArgs: Holds data for RowUpdating event of DataStoreDataAdapter.
  • DataStoreTransaction: Provides a transaction.

    DB_ADMIN Table Information Classes

This group contains classes that provide column name information for result tables from various DB_ADMIN functions.

  • ColumnColumns: Column names for result table from built-in stored procedure DB_ADMIN.GET_COLUMNS.
  • DatabaseColumns: Column names for result table returned from DB_ADMIN.GET_DATABASE_PROPS.
  • DataTypeColumns: Column names for result table from the built-in stored procedure DB_ADMIN.GET_DATATYPES.
  • IndexColumns: Provides column names for result table from built-in stored procedure DB_ADMIN.GET_INDEX_COLUMNS.
  • IndexesColumns: Provides the column names for the result table from the built-in stored procedure DB_ADMIN.GET_INDEXES.
  • LockColumns: Provides column names for result table from built-in stored procedure DB_ADMIN.GET_LOCKS.
  • MirrorStatusColumns: Provides columns of result table from built-in stored procedure DB_ADMIN.GET_MIRRORS
  • ProcedureColumnColumns: Provides column names for result table from built-in stored procedure DB_ADMIN.GET_PROCEDURE_COLUMNS.
  • ProcedureColumns: Provides the column names for the result table from the built-in stored procedure DB_ADMIN.GET_PROCEDURES.
  • StatusLogColumns: Provides column names for result table from built-in stored procedure DB_ADMIN.GET_STATUS_LOGS.
  • TableColumns: Provides column names for result table from built-in stored procedure DB_ADMIN.GET_TABLES.

    Additional Classes

These classes provide information on connections, logging, system properties and data access. It also provides classes to work with mirrors and scheduling functions.

  • ConnectionProperties: Names of connection properties supported by Blackfish SQL driver.
  • DataStoreErrorCode: Symbolic error codes for database.
  • DataStoreServer: Controls starting and stopping a server process.
  • LicenseColumns: Licensing related property names.
  • LogFilter: Provides names of events to be logged.
  • MirrorTypes: Values for the SysMirrors.TYPE column.
  • ScheduleColumns: Extends SysMirrors with additional status information.
  • SchedulePeriod: Possible values for SysSchedule.PERIOD column.
  • SysConnections: Table and column names for system /SYS/CONNECTIONS table.
  • SysMirrors: Provides the column names of the system table SYS.MIRRORS.
  • SystemProperties: Specifies names of system properties that should be applied to all databases open in a process.
  • SysQueries: Table and column names for system /SYS/QUERIES table.
  • SysSchedule: Provides column names from system table SYS.MIRROR_SCHEDULE.
  • TriggerContext: Access to connection instance, and access to new and old rows.
  • TriggerReadRow: Provides read access to a row of data.
  • TriggerReadWriteRow: Provides write access to a row of data.

Server Response from: ETNASC03