Go Up to Developing Database Applications
RAD Studio provides full support for InterBase 2017 databases. InterBase 2017 is installed with RAD Studio in the following directory:
C:\Program Files (x86)\Embarcadero\Studio\20.0\InterBase2017.
- 1 Requirements
- 2 RAD Studio Support for InterBase
- 3 What's new in InterBase 2017?
- 3.1 New ODS version
- 3.2 isql
- 3.3 Monitor all online databases
- 3.4 Subscriptions reflect ALTER TABLE changes
- 3.5 Exclusive Isolation Level
- 3.6 SQL Derived Table Support
- 3.7 Truncate Table
- 3.8 Transaction Wait Time
- 3.9 Single Line Comment
- 4 Features from previous releases
- 4.1 Change Views™
- 4.2 Linux 32-bit and 64-bit
- 4.3 Performance Enhancements
- 4.4 Performance Monitoring Counters
- 4.5 Partial Segment Selectivity
- 4.6 64-bit Transaction IDs
- 4.7 ODS Changes
- 4.8 OpenSSL Update
- 4.9 Services API Support for Online Dump and Journal Archive Operations
- 4.10 ODBC Driver Improvements
- 4.11 Concurent Index Creation
- 4.12 NO RESERVE SPACE for Database and User Tables
- 4.13 Physical Database Portability between Windows, MacOSX, Linux, iOS and Android devices
- 4.14 Stronger password protection
- 4.15 Larger database cache settings for 64-bit InterBase
- 4.16 EXECUTE STATEMENT for Stored Procedures
- 4.17 Larger index key segment size
- 4.18 ibconfig parameter: THREAD_STACK_SIZE_MB
- 4.19 Table-specific blocking factor
- 4.20 Database fast sweep
- 5 See Also
If you want to use InterBase 2017 databases, you need to have:
- The latest client version of InterBase (When you install RAD Studio, the correct client version of InterBase 2017 is automatically installed, unless you specifically deselect InterBase 2017.)
- An x64 machine for the new platform support (if you want to use 64-bit).
The 64-bit edition can be installed only on the following operating systems:
- Microsoft Windows 10
- Microsoft Windows 8, 8.1
- Microsoft Windows 7
- Windows Vista
- Windows Server 2016
- Windows Server 2012, 2012 R2
- Windows Server 2008, 2008 R2
- macOS El Capitan 10.11
- macOS Sierra 10.12
- macOS High Sierra 10.13
Starting the InterBase Server
- Choose Start | Programs | Embarcadero InterBase 2017 | 64-bit/32-bit instance = gds_db | InterBase Server Manager [instance = gds_db]
- On the InterBase 2017 64 Server Manager, start the InterBase server if it is not running.
RAD Studio Support for InterBase
Integrated licensing for InterBase 2017
InterBase 2017 Developer Edition is installed with RAD Studio (does not apply to the Starter edition) and included in RAD Studio integrated license. InterBase 2017 ToGo does not have to be installed on any server or end-user workstation and it has two licensing options for deployment: ToGo and IBLite. This feature makes it easier for developers to create C++Builder and Delphi InterBase applications using RAD Studio.
What's new in InterBase 2017?
For details, InterBase 2017 Readme is available here
New ODS version
Databases created with InterBase 2017 use ODS 17.
isql supports a new command,
RECONNECT. You can use
isql and SQL scripts, to reconnect to the latest successfully connected database. See RECONNECT for more information.
-names Command-line Option
isql supports a new command line option,
-names <character set name>. You can use this option to specify the character set to use for current database connection. See Command-line Options for more information.
Monitor all online databases
InterBase 2017 provides monitoring of all the online databases in a server with a single connection. Administrators can use the server level statistics to better monitor an instance usage.
To use this feature:
- Connect to the admin database on the system
- Run performance monitoring queries on any TMP$ tables as you would in a single database. You will see cumulative data for all databases that are currently online.
- Run UPDATE operations on TMP$DATABASE for SWEEP, FLUSH and RECLAIM for database other than admin
- Run UPDATE operations on TMP$TRANSACTIONS for COMMIT and ROLLBACK for database other than admin
Subscriptions reflect ALTER TABLE changes
Subscriptions now reflect changes when a column is added with ALTER TABLE. For example, using the following code FOO_SUBS shows the newly added column BID.
CREATE TABLE FOO (AID INT); CREATE SUBSCRIPTION FOO_SUBS ON FOO; INSERT INTO FOO VALUES (1); ALTER TABLE FOO ADD BID INT; UPDATE FOO SET BID=2; COMMIT;
Exclusive Isolation Level
Exclusive Isolation allows transactions to acquire exclusive lock on a target table, and be the only ones able to execute
DELETE on a table. Refer to Exclusive Isolation Level for more information.
SQL Derived Table Support
InterBase now supports derived tables, a highly used SQL development feature. The derived table syntax enhances the abilities of existing applications to use InterBase as a back end RDMBS. For more information refer to derived tables documentation.
The Truncate Table command allows users and applications to empty the contents of a database table. This feature is useful for tables where rows require frequent deletion. The Truncate Table command performs faster, requires less I/O, and journals and archives much less information than an equivalent
DELETE FROM table command. For more information refer to Truncate Table.
Transaction Wait Time
Specify a period of time transactions wait for acquiring lockable resources. Now is possible to specify a time period that transactions wait for a lockable resource to be acquired. For more information refer to Wait time.
Single Line Comment
You can add a single line comment to a SQL statement using two dashes, for more information and examples refer to Comment for more information.
--This is a comment line.
Features from previous releases
The following items represent a list of features from previous releases.
The Change Views™ feature uses InterBase's multigenerational architecture to capture changes in data. This feature allows you to quickly answer the question, "What data has changed since I last viewed it?".
Previously it involved triggers, logging, and/or transaction write-ahead log scraping. This was time-consuming for the developer and affected the database performance for a certain transaction load or change volume. Now with Change Views, there is no performance overhead on existing transactions because it maintains a consistent view of changed data observable by other transactions.
Linux 32-bit and 64-bit
The Linux kits, 32-bit and 64-bit, are available in the InterBase XE7 release. New Linux builds (18.104.22.168) are available for InterBase XE7.
Many performance enhancements have been implemented for this release.
- Improved SMP Performance. In InterBase XE7 the performance of multiple reads and writes has been improved in this version.
- Distinguished Dump. "Incremental Dump" in InterBase XE3 required the database server to read all pages from the database file, but only write the pages that had been modified to the target database dump file. With the implementation of a tracking system in XE7, only those pages that need updating since the last dump would be fetched. This provides instantaneous updates to the target. There can only be one "Distinguished Dump" per source database.
- Improved Read Commmitted (RC) Transaction Processing for large (OIT - OAT) transaction gap.
- Quicker transaction creation.
Performance Monitoring Counters
The performance monitoring counters have been scaled up from 32-bit to 64-bit values. It includes all data stored on tables within the database. IBConsole has been updated for this version.
There are some ODS-related differences to be aware of:
- ODS <= 15 will continue to have 32-bit INTEGER counters as before (for both dialect 1 and dialect 3 databases)
- ODS >= 16 will have the counters defined as "double precision" data type for dialect 1 databases
- ODS >= 16 will have the counters defined as "NUMERIC(18,0)" data type for dialect 3 databases. As you know, by default, any new database is created as ODS 16, dialect 3.
Partial Segment Selectivity
Previous versions of InterBase maintain a single selectivity value per index (whether single or composite key). The values are stored in descending order and track information on how many duplicates exist in the whole index – but only first level. However, there are times when it would be helpful to have information on lower levels. InterBase XE7 can now track this.
64-bit Transaction IDs
InterBase XE7 scales transaction IDs to 64-bit (actual 48-bit) so databases now can accommodate more transactions without the old limitations. It limits itself to 48-bit, as to be aware of future transaction ID growth.
InterBase XE7 allows a database backup to be restored to a particular older ODS version. Although a database restore operation automatically creates a database with the latest ODS version, the developer may need to restore an older version. The reasons are:
- To enable that the same database file is deployed to other compatible database engine versions.
- To provide a workaround for ODS bugs that are hard to resolve.
- To test performance-sensitive operations between the current ODS version and older ODS versions by using a single server/engine to restore multiple copies of the same database (one per ODS) and then running the queries against each one of those database files.
OpenSSL libraries included in InterBase XE7 are upgraded from version 1.0.0d to version 1.0.1i with Security Vulnerability fixes. OpenSSL is used for Encryption and OTW/SSL features in InterBase. For more information on using OpenSSL in InterBase XE7, please reference Network Configuration in the Operations Guide, as well as Encrypting your Data in the Data Definition.
Services API Support for Online Dump and Journal Archive Operations
- Online Dump and Incremental Dump. Services API now supports online dumps and incremental dumps.
- Journal Archive Management Actions. You can now request that the Services Manager perform various operations on InterBase Journal Archives.
ODBC Driver Improvements
The old DataDirect ODBC drivers have been replaced with a new offering of InterBase ODBC driver. However, the new InterBase ODBC driver might not be installed with InterBase XE3. The new ODBC driver is available for registered users to download at http://cc.embarcadero.com/item/28975
Concurent Index Creation
With InterBase XE3, when restoring a database, the indexes for a table can be created all at the same time, after the data of the table is restored. Also, re-computing index SELECTIVITY with SET STATISTICS can be concurrent. InterBase XE3 enables one assistant thread for such operations by default. You can adjust the number of concurrent threads available for such operations by modifying the configuration parameter MAX_ASSISTANTS in ibconfig file.
NO RESERVE SPACE for Database and User Tables
In InterBase XE3, a CREATE/ALTER TABLE clause is introduced to prevent space reservation and to maximize row packing. This clause is useful for tables whose rows are infrequently or never UPDATED or DELETED; whose queries are complex queries, such as aggregates and analytics that process a high percentage of rows; and where indexes are rebuilt and the database is frequently backed up and/or restored. Using this clause can lead to a 20% savings in storage space.
Physical Database Portability between Windows, MacOSX, Linux, iOS and Android devices
Starting from InterBase XE3, you can copy database files between Windows (32-bit and 64-bit), OS X, Linux, and iOS and Android devices. InterBase databases created on the development platform can be easily deployed to other supported platforms. You can create InterBase applications and InterBase databases on your development machine, and then easily deploy the applications and databases to other supported platforms, with some restrictions.
For more information on this feature, see the InterBase XE3 Update 4 Readme file, which is installed with RAD Studio, typically at
C:\Program Files (x86)\Embarcadero\Studio\20.0\InterBaseXE3\Doc, and is also available online at http://docs.embarcadero.com/products/interbase/IBXE3Update4/Readme.html
Stronger password protection
Stronger password protection is implemented in the new InterBase XE3 databases to comply with password requirements from the Payment Card Industry - Data Security Standard (PCI-DSS). This additional functionality supports a longer effective password length resulting in stronger protection.
The password can contain from 8 characters up to 32 characters.
Larger database cache settings for 64-bit InterBase
64-bit InterBase databases have now a larger cache. The limit for the 64-bit engine is 75 million pages, as compared to 750 thousand pages for 32-bit engines.
EXECUTE STATEMENT for Stored Procedures
Stored Procedure developers can embed three variations of EXECUTE STATEMENT within their Stored Procedures. The variations depend on the number of rows returned from the EXECUTE STATEMENT command. The three cases are:
- No rows of data returned.
- One row of data returned.
- Variable number of rows returned.
Larger index key segment size
- An index key can now be up to 4 bytes less than a quarter of the page size.
- By default, InterBase databases are created with a 4Kb page size. This can be overridden up to a 16Kb page size by the database developer.
- The 4Kb page size database would allow indexes that can accommodate 1020 bytes per key.
- A 16Kb page size can accommodate 4092 bytes per key and so on.
ibconfig parameter: THREAD_STACK_SIZE_MB
The THREAD_STACK_SIZE_MB ibconfig parameter controls the stack size of various threads in InterBase. The value is expressed as a multiple of megabytes per thread. The valid range is from 2MB through 32MB. If it is set beyond the range, the value defaults to 2MB.
Table-specific blocking factor
The term blocking factor is used to denote the number of records stored in a block. InterBase employs a single database-wide blocking factor that maximizes the number of rows that can be stored on a data page.
Database fast sweep
Sweeping a database is a systematic way of removing outdated records. Periodic sweeping prevents a database from growing too large. In the past, sweeping slowed down system performance, and users disabled the automatic database sweep function because of the impact on product operations.
- Developing Database Applications Index
- InterBase Videos
- Tutorial: Using an InterBase Database in a Delphi or C++ Application
- Tutorial: Using InterBase Express to Access a Database
- InterBase Database Encryption
- IBLite and IBToGo Deployment Licensing
- InterBase XE7 Readme, available in the product installation directory, inside the folder \InterbaseXE7\Doc.
- FireDAC InterBase sample