Changes From Version 1.x

The following items have been changed in Version 2.0 from Version 1.x:

•   Version 2.0 includes a physical table format change and any tables that are in pre-2.0 format must be upgraded to the new table format. Please see the UpgradeTable method enhancement below for more information. The DBSYS utility also includes an Upgrade Table menu option that can be used to interactively upgrade tables to the new 2.0 format.

•   The combined run-time and design-time packages have been separated into separate run-time and design-time packages. The naming conventions have also changed - in past versions the packages were named DBISAMDB.DPL/BPL and in this release the package names are now named like this:
 
Constant	'DB'

Version Number	200 for version 2.00, etc.

Product Type	D for Delphi or C for C++Builder

Version Support	Major version number such as 5 for Delphi 5 or C++Builder 5, etc.

Package Type	R for run-time or D for design-time

For example the package name for the run-time package for version 2.00 of DBISAM for Delphi 5 is:

DB200D5R.BPL

•   DBISAM now uses a shared file handle and buffering architecture that allows the engine to re-use cache buffers and file handles for the same physical table among multiple table cursors on a session basis. For example, if you have two TDBISAMTable components that point to the same physical table on disk and both are using the same TDBISAMSession component, then DBISAM will only open the physical files associated with the table once and will share any cache buffers for the table between both table cursors.

•   Transactions may now be used across table opens and closes as well as with INSERT, UPDATE, and DELETE queries. It is recommended, however, that you always use explicit TDBISAMDatabase components to ensure that a temporary database component does not get automatically destroyed after all tables connected to it are closed and it is no longer needed. This behavior will cause an automatic rollback and will result in all changes being lost. You may also now access the same physical table from multiple TDBISAMTable or TDBISAMQuery components during a transaction without any locking conflicts as long as the TDBISAMTable and TDBISAMQuery components all point to the same TDBISAMSession component. Finally, you can update many more records now in a single transaction without experiencing any major slowdowns. This is due to the change in the way that the cache buffering works in version 2.0 noted above.

•   The LockRetryCount, LockWaitTime, ForceBufferFlush, and StrictChangeDetection properties have been moved from the TDBISAMTable and TDBISAMQuery components to the TDBISAMSession component. This is due to the architecture changes in version 2.0 related to file handle sharing and cache buffering noted above. PLEASE BE SURE TO OPEN EVERY FORM IN YOUR PROJECT THAT CONTAINS A TDBISAMTABLE OR TDBISAMQUERY COMPONENT AND RE-SAVE THE FORM. IF YOU DO NOT DO THIS YOU WILL GET RUNTIME ERRORS COMPLAINING ABOUT THE ABSENCE OF THESE PROPERTIES IN THE FORM.

•   The UserMajorVersion and UserMinorVersion properties of the TDBISAMTable component have been changed from string properties to Word properties. This is to make it easier when referencing these properties since they are actually Word values anyways.

•   The unit names have changed and some units have been added in version 2.0:

DBISAM.DCU is now DBISAMEN.DCU
DBINDX.DCU is gone and is included in DBISAMEN.DCU
DBBLOB.DCU is gone and is included in DBISAMEN.DCU
DBBASE.DCU is now DBISAMLB.DCU
DBQURY.DCU is now DBISAMSQ.DCU
DBISAMUT.DCU has been added (utility procedures library)
DBISAMCT.DCU has been added  (utility controls)
DBISAMCP.INC has been added (compiler options include file)
DBISAMVR.INC has been added (version compiler options include file)
DBISAMER.RES has been added (error messages resource file)
DBISAMER.RC has been added (error messages resource script)
DBISAMER.INC has been added (include file for errror messages resource script)

•   The utilities have been completely rewritten in Delphi 5 (they were previously written using Delphi 2.01). We have added numerous new features to the DBSYS (Database System) utility that reflect the new feature set in version 2.0 so the best way to explore these new features is to run DBSYS and try them out. The BDETRAN (BDE Database Transfer) utility now includes alias support and can accept data sources other than Paradox, dBase, or Foxpro.

•   You can no longer use "True" or "False" to represent Boolean constants - you must use True and False respectively (these constants are case-insensitive).

•   The Date, Time, and Now constants available in SQL have been replaced with CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP functions. This change has been made to make the syntax more SQL-92 compliant. These changes also now apply to the filters.

•   These two methods of the TRestructureFieldDefs object have changed:

procedure TRestructureFieldDefs.Add(const Name: string;
DataType: TFieldType; Size: Word; Required: Boolean; DefaultValue: string;
MinValue: string; MaxValue: string; Description: string;
CharCase: TFieldCharCase; FieldNo: Integer);

procedure TRestructureFieldDefs.Insert(Index: Integer; const Name: string;
DataType: TFieldType; Size: Word; Required: Boolean; DefaultValue: string;
MinValue: string; MaxValue: string; Description: string;
CharCase: TFieldCharCase; FieldNo: Integer);

and the TRestructureFieldDef object now has the following property:

property CharCase: TFieldCharCase read FCharCase write FCharCase;

•   In-memory tables do not use directory or path specifications anymore - the DatabaseName for any in-memory table will automatically default to 'Memory' and cannot be changed. If you use directory or path specifications in queries for in-memory tables (such as with INTO MEMORY), they will be ignored in this version.

•   The constants for DBISAM error messages and language/sort IDs have been moved to the DBISAMEN.DCU  unit. Previously they were located in the DBBASE.DCU unit.

•   The DBISAM error messages are now present in a file called DBISAMER.RES and included with the distributions so that you can modify or translate the DBISAM error messages, if needed. We've also included the raw DBISAMER.RC and DBISAMER.INC files if you want to edit the resources as text and re-compile them.

•   A primary index is no longer required for every physical table. You may now create tables that only contain a natural (input) order and, possibly, secondary indexes.

•   You may now open up a table even if the .IDX file is missing. Please note, however, that this will cause all indexes to be removed from the table and the table will only exist in natural order.

•   The RestructureTable method has been changed and a new parameter has been added for specifying the last auto-increment value present in the physical table. The new RestructureTable method looks like this:

procedure RestructureTable(NewLanguageID: Word; NewSortID: Word;
NewUserMajorVersion: Word; NewUserMinorVersion: Word;
NewEncrypted: Boolean; NewPassword: string; NewDescription: string;
NewBlobBlockSize: Word; NewLastAutoIncValue: Integer;
SuppressBackups: Boolean);

The NewLastAutoIncValue parameter can be passed as -1 to indicate that there should be no changes to the last auto-increment value for the table.

•   Previously only the VersionNum, UserMajorVersion, and UserMinorVersion properties could be referenced when a table was not open (but the TableName property had been set). Now you may reference the following properties when a table is not open:

VersionNum
UserMajorVersion
UserMinorVersion
Encrypted
Password
Description
LanguageID
SortID
BlobBlockSize
LastAutoIncValue

This allows for you to pass these values directly to a RestructureTable method call without having to first open the table, save the values to temporary variables, and then pass them to the RestructureTable method call.

•   You may now save live query result sets as a permanent table.

•   The system-defined Row___No field that was present in query result sets is no longer present. Query result sets exist with no indexes in natural row order.

New Features in Version 2.0

The following items are new features in version 2.0:

•   You may now optionally purchase the source code to the DBISAM engine itself. Please see the ordering page on our web site for pricing information.

•   Complete source code to all utilities is now provided free of charge in the \<INSTALLDIR>\SAMPLES directory created during installation. You will see an additional palette page called DBISAM Utilities when you install the DBISAM packages in the C++Builder or Delphi IDEs, and the components on this palette page are for use with the source code to the DBSYS, BDETRAN, DBCUST, and DBTRIAL utilities provided in this directory.

•   There is now an UpgradeTable method in the TDBISAMTable component that allows you to easily upgrade any table in a format prior to version 2.0 to the 2.0 format. The UpgradeTable method looks like this:

procedure UpgradeTable;

In addition, two events are provided that can be used to track the upgrade process and log messages regarding the upgrade process:

property OnUpgradeProgress: TUpgradeProgressEvent
         read FOnUpgradeProgress write FOnUpgradeProgress;

TUpgradeProgressEvent = procedure(Sender: TObject; const UpgradeStep: string;
                                  PercentDone: Word) of object;

property OnUpgradeLog: TLogEvent read FOnUpgradeLog write FOnUpgradeLog;

TLogEvent = procedure(Sender: TObject;
                      const LogMessage: string) of object;

•   The speed of the SQL, and read/write operations in general, have been greatly improved.

•   Memo fields can now be used in filters or in the WHERE clause of SQL statements. Memo fields are type-compatible with strings and string fields. Memo fields can also be used as query parameters.

•   Filters now accept the same functions and extended binary operators present in SQL such as LIKE, BETWEEN, IN, CAST, SUBSTRING, POS, etc.

•   You may now use character constants in SQL and filters such as #32, #0, etc. Just prefix the ASCII value of the character with the pound sign (#).

•   There are 7 new functions available for both SQL and filters. They are CAST, POS, OCCURS, REPLACE, ABS, ROUND, and IF, and the following is the syntax for these functions:

CAST(column_reference or constant AS data type)

The CAST function converts a column reference or constant to the specified data type.

Valid data types include:

SMALLINT
INTEGER
INT
DECIMAL
DECIMAL(7) (any scale is valid)
DECIMAL(7,2) (any scale or a precision up to 4 is valid)
NUMERIC
NUMERIC(7) (any scale is valid)
NUMERIC(7,2) (any scale or precision is valid)
FLOAT
FLOAT(7) (any scale is valid)
FLOAT(7,2) (any scale or precision is valid)
CHAR(10) (any length up to 250 characters is valid)
CHARACTER(10) (any length up to 250 characters is valid)
VARCHAR(10) (any length up to 250 characters is valid)
DATE
BOOLEAN
BLOB(0,1) (memo)
BLOB(0,2) (binary)
BLOB(0,3) (formatted memo - legacy BDE compatibility)
BLOB(0,5) (graphic)
BLOB(0,7) (typed binary - legacy BDE compatibility)
TIME
TIMESTAMP
MONEY
AUTOINC (compatibility, handled as an INTEGER type)
BYTES (length of 1)
BYTES(10) (any length up to 250 characters is valid)

POS(search string constant IN column_reference or string constant)

The POS function returns the position of the search string specified in the column reference or string constant.

OCCURS(search string constant IN column_reference or string constant)

The OCCURS function returns the number of times that the search string specified occurs in the column reference or string constant.

REPLACE(search string constant WITH replace string constant
IN column_reference or string constant)

The REPLACE function replaces all occurrences of the search string specified with the replacement string specified in the column reference or string constant.

ABS(column reference or floating point/integer constant)

The ABS function returns the absolute (non-negative) value of any floating-point or integer value.

ROUND(column reference or floating-point constant
[ TO decimal places])

If the Decimal Places are not specified, then the Floating point expression will be rounded to 0 decimal places.

IF(boolean expression THEN expression 1 ELSE expression 2)

Expression 1 and expression 2 must both be of the same type. Use the CAST function to convert any expression to a compatible type if the two expressions are of different types.

•   There is now an OptimizeTable method in the TDBISAMTable component that allows you to optimize a given table by removing deleted records and physically ordering the table according to a specified index. The OptimizeTable method looks like this:

procedure OptimizeTable(const OptimizeIndexName: string;
SuppressBackups: Boolean);

The OptimizeIndexName parameter is used to specify which index should be used to physically align the records on disk. This is called "clustering", and is useful for improving the read-ahead buffering of records in DBISAM.

In addition to this, an OnOptimizeProgress event has been added that can be used to report the progress of the optimization process:

property OnOptimizeProgress: TProgressEvent
         read FOnOptimizeProgress write FOnOptimizeProgress;

TProgressEvent = procedure(Sender: TObject; CurrentRecord: Integer;
                           PercentDone: Word) of object;

•   There is now a Boolean property in both the TDBISAMTable and TDBISAMQuery components called AutoDisplayLabels that allows you to specify whether the field descriptions in a given table are automatically populated into the DisplayLabel property of the TField components for the data set.

•   There is now the ability to specify the character case of a given field at the physical table level as Upper-Case, Lower-Case, or No Change. This addition changes the following items:

This is the type definition for the TFieldCharCase enumeration:

TFieldCharCase = (fcNoChange,fcLowerCase,fcUpperCase);

•   DBISAM now performs join re-ordering on INNER JOINs in SQL statements in order to make sure that the driver table in the query is the smallest table.

•   There is now the ability to specify the language ID and sort ID used in an SQL statement and a NOJOINOPTIMIZE flag that turns off join re-ordering:

SELECT [DISTINCT] * | column [AS correlation_name | correlation_name], [column...]
[INTO [MEMORY] destination_table]
FROM [MEMORY] table_reference [AS correlation_name | correlation_name]
[[[[INNER | [LEFT | RIGHT] OUTER JOIN] [MEMORY]
table_reference [AS correlation_name | correlation_name] ON join_condition]
[WHERE predicates]
[GROUP BY group_list]
[HAVING predicates]
[ORDER BY order_list [NOCASE]]
[LANGUAGE language_string SORT sort_string]
[WITH LOCKS]
[ENCRYPTED WITH password]
[NOJOINOPTIMIZE]

•   Previously in the 1.x versions, DBISAM only used the user-selected Windows language driver for SQL statements. Valid language and sort strings are as follows:
 
'ANSI Standard'	'Default Order'
'Afrikaans'	'Default Order'
'Albanian'	'Default Order'
'Arabic (Saudi Arabia)'	'Default Order'
'Arabic (Iraq)'	'Default Order'
'Arabic (Egypt)'	'Default Order'
'Arabic (Libya)'	'Default Order'
'Arabic (Algeria)'	'Default Order'
'Arabic (Morocco)'	'Default Order'
'Arabic (Tunisia)'	'Default Order'
'Arabic (Oman)'	'Default Order'
'Arabic (Yemen)'	'Default Order'
'Arabic (Syria)'	'Default Order'
'Arabic (Jordan)'	'Default Order'

'Arabic (Lebanon)'	'Default Order'
'Arabic (Kuwait)'	'Default Order'
'Arabic (U.A.E)'	'Default Order'
'Arabic (Bahrain)'	'Default Order'
'Arabic (Qatar)'	'Default Order'
'Basque'	'Default Order'
'Belarusian'	'Default Order'
'Bulgarian'	'Default Order'
'Catalan'	'Default Order'
'Croatian'	'Default Order'
'Czech'	'Default Order'
'Danish'	'Default Order'
'Dutch (Standard)'	'Default Order'
'Dutch (Belgian)'	'Default Order'
'English (USA)'	'Default Order'

'English (UK)'	'Default Order'
'English (Australian)'	'Default Order'
'English (Canadian)'	'Default Order'
'English (New Zealand)'	'Default Order'
'English (Irish)'	'Default Order'
'English (South Africa)'	'Default Order'
'English (Jamaica)'	'Default Order'
'English (Caribbean)'	'Default Order'
'English (Belize)'	'Default Order'
'English (Trinidad)'	'Default Order'
'Estonian'	'Default Order'
'Faeroese'	'Default Order'
'Farsi'	'Default Order'
'Finnish'	'Default Order'

'French (Standard)'	'Default Order'
'French (Belgian)'	'Default Order'
'French (Canadian)'	'Default Order'
'French (Swiss)'	'Default Order'
'French (Luxembourg)'	'Default Order'
'German (Standard)'	'Default Order' or 'German Phone Book Order'
'German (Swiss)'	'Default Order' or 'German Phone Book Order'
'German (Austrian)'	'Default Order' or 'German Phone Book Order'
'German (Luxembourg)'	'Default Order' or 'German Phone Book Order'
'German (Liechtenstein)'	'Default Order' or 'German Phone Book Order'

'Greek'	'Default Order'
'Hebrew'	'Default Order'
'Hungarian'	'Default Order'
'Icelandic'	'Default Order'
'Indonesian'	'Default Order'
'Italian (Standard)'	'Default Order'
'Italian (Swiss)'	'Default Order'
'Latvian'	'Default Order'
'Lithuanian'	'Default Order'
'Norwegian (Bokmal)'	'Default Order'
'Norwegian (Nynorsk)'	'Default Order'
'Polish'	'Default Order'
'Portuguese (Standard)'	'Default Order'
'Portuguese (Brazilian)'	'Default Order'
'Romanian'	'Default Order'

'Russian'	'Default Order'
'Serbian (Latin)'	'Default Order'
'Serbian (Cyrillic)'	'Default Order'
'Slovak'	'Default Order'
'Slovenian'	'Default Order'
'Spanish (Castilian)'	'Default Order'
'Spanish (Mexican)'	'Default Order'
'Spanish (Modern)'	'Default Order'
'Spanish (Guatemala)'	'Default Order'
'Spanish (Costa Rica)'	'Default Order'
'Spanish (Panama)'	'Default Order'
'Spanish (Dominican Republic)'	'Default Order'
'Spanish (Venezuela)'	'Default Order'

'Spanish (Colombia)'	'Default Order'
'Spanish (Peru)'	'Default Order'
'Spanish (Argentina)'	'Default Order'
'Spanish (Ecuador)'	'Default Order'
'Spanish (Chile)'	'Default Order'
'Spanish (Uruguay)'	'Default Order'
'Spanish (Paraguay)'	'Default Order'
'Spanish (Bolivia)'	'Default Order'
'Spanish (El Salvador)'	'Default Order'
'Spanish (Honduras)'	'Default Order'
'Spanish (Nicaragua)'	'Default Order'
'Spanish (Puerto Rico)'	'Default Order'
'Swedish (Standard)'	'Default Order'

'Swedish (Finland)'	'Default Order'
'Thai'	'Default Order'
'Turkish'	'Default Order'
'Ukranian'	'Default Order'
'Vietnamese'	'Default Order'

•   You can now open up an in-memory table in the C++Builder or Delphi IDE provided that you are using Delphi 4 or 5 and have specified at least one TFieldDef via the property editor for the TFieldDefs property.

•   There is now an Integer property called MaxRowCount for the TDBISAMQuery component that allows you to specify a maximum number of rows that will be read in a query before the query engine will stop. The value can be -1 (no limit) or a positive integer value. Please note that this row count is not sensitive to any ORDER BY or GROUP BY statements and will not necessarily perform a TOP n type of functionality.

•   There is now a Double property called ExecutionTime for the TDBISAMQuery component that specifies the amount of time consumed by the last query executed. This figure does not include prepare time, only execution time.

•   There is now a Boolean property (public, not published) called SuppressAutoIncValues for the TDBISAMTable and TDBISAMQuery components that allows you to specify that auto-increment values may be over-written with user-supplied or application-supplied values.

•   There is now a Boolean property called CopyOnAppend for the TDBISAMTable and TDBISAMQuery components that allows you to specify that any new records created with the Insert or Append methods will automatically be populated with the contents of the current record.

•   There is now an OnCopyProgress event in the TDBISAMTable component for use with the CopyTable method which looks like this:

property OnCopyProgress: TProgressEvent
         read FOnCopyProgress write FOnCopyProgress;

TProgressEvent = procedure(Sender: TObject; CurrentRecord: Integer;
                           PercentDone: Word) of object;

•   There is now support for the HAVING clause in the SELECT SQL statement. This is an example of using the HAVING clause:

SELECT  e.LastName, 
  EXTRACT(YEAR FROM o.SaleDate) AS Yr, 
  SUM(i.qty * p.listprice * (1-i.Discount/100)) AS SalesTotal
FROM  employee e, orders o, items i, parts p
WHERE  e.empno=o.empno AND o.orderno=i.orderno AND i.partno=p.partno
GROUP BY  e.lastname, Yr
HAVING SUM(i.qty * p.listprice * (1-i.Discount/100)) > 30000
ORDER BY  e.lastname, Yr

The above query could also be coded like this:

SELECT  e.LastName, 
  EXTRACT(YEAR FROM o.SaleDate) AS Yr, 
  SUM(i.qty * p.listprice * (1-i.Discount/100)) AS SalesTotal
FROM  employee e, orders o, items i, parts p
WHERE  e.empno=o.empno AND o.orderno=i.orderno AND i.partno=p.partno
GROUP BY  e.lastname, Yr
HAVING SalesTotal > 30000
ORDER BY  e.lastname, Yr

•   There is now support for the IN and NOT IN operators with sub-queries, or sub-selects. Sub-select support does not exist for the EXISTS,  ANY, SOME, or ALL operators, however. This is an example of a sub-select using the IN operator:

SELECT C.Company, C.State
FROM Customer C
WHERE (C.State IN
  (SELECT R.State
  FROM Regions R
  WHERE (R.Region = "Pacific")))

You may nest sub-select queries to any number of levels.

•   There is now support for using a sub-query in an  INSERT statement in order to insert the records from one SELECT query into another table. This is an example using the biolife table that ships with Delphi and C++Builder as demo data:

INSERT INTO biolife
SELECT * FROM oldbiolife
WHERE Upper(Category)="WRASSE"

•   There is now support for the CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX SQL DDL statements. The following is an example of each statement:

CREATE TABLE biolife
(
   "Species No" FLOAT,
   Category CHARACTER(15),
   Common_Name CHARACTER(30),
   "Species Name" CHARACTER(40),
   "Length (cm)" FLOAT,
   Length_In FLOAT,
   Notes BLOB(0,1),
   Graphic BLOB(0,5),
PRIMARY KEY ("Species No") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
)

ALTER TABLE biolife
ADD COLUMN test char(10),
ADD "test 2" char(20),
ADD COLUMN "test 3" char(30) AT 2,
DROP COLUMN Notes,
REDEFINE Length_In "Length In" DESCRIPTION "Length in Inches",
DROP CONSTRAINT x PRIMARY KEY

DROP TABLE biolife

CREATE INDEX NAME ON animals (NAME) COMPRESS FULL

DROP INDEX animals.NAME

•   You may now execute multi-statement SQL scripts using the ExecSQLScript method of the TDBISAMQuery component. Scripts can contain multiple DDL or non-SELECT DML SQL statements as long as the statements are separated by a semicolon. The method looks like this:

procedure ExecSQLScript;

To track the progress of the script use the OnQueryProgress event. To trap any execution errors and optionally terminate the execution of the script, use the OnScriptError event. The event looks like this:

property OnScriptError: TQueryScriptErrorEvent
         read FOnScriptError write FOnScriptError;

TQueryScriptErrorEvent = procedure(Sender: TObject; E: Exception;
                         var AbortScript: Boolean) of object;

During the execution of the script you may use the ScriptStatementType property (public) to find out what type of SQL statement is currently executing and after the script is complete you may use the ScriptRowsAffected and ScriptExecutionTime properties (both public) to find out how many rows were affected for the entire script and the total execution time for the script. To find out if a script is currently running in the TDBISAMQuery component just use the ScriptIsRunning property (public):

property ScriptStatementType: TSQLStatementType read FScriptStatementType;
property ScriptRowsAffected: Integer read FScriptRowsAffected;
property ScriptExecutionTime: double read FScriptExecutionTime;
property ScriptIsRunning: Boolean read FScriptIsRunning;

•   You can now reverse-engineer tables into full SQL-92 DDL scripts with DBISAM DDL SQL extensions. These scripts can also be executed directly in the DBSYS utility selecting the New SQL Query  option and then entering or loading the script into the SQL Script tab in the query window.

•   BCB5 support is now present in this release.

Copyright © 2001 Elevate Software