For forums, blogs and more please visit our
Developer Tools Community.
By: Tim DelChiaro
Abstract: Get the latest update for InterBase XE3 ToGo, Server, Desktop, Trial, and Developer Edition
Update 2 for InterBase XE3 is now available.
Download the InterBase XE3 Update 2 here:
The new version is also included in the bundled versions in RAD Studio XE4, Delphi XE4 and C++Builder XE4.
See the readme below for additional information on this update.
Updated: April, 2013
This file contains important information that may not appear in the online Help. Please read this file in its entirety.
Note: For the latest version of this document, please visit https://docs.embarcadero.com/products/interbase/
The Readme covers the following sections:
For system requirements to install and run InterBase XE3, please refer to the Installation, Registration, and Licensing Information document.
The document set, in PDF format, requires about 13MB of space on your disk. The documents install by default when Client and Server or Client is chosen during the InterBase install process. However, in a custom install, it is possible to choose an install that does not include the document set. If you want to install documents at a later time, run the InterBase install, choose Custom, and select the documentation.
You can also copy them from the Documentation directory of the InterBase CD-ROM or download file. Detailed command-line and step-by-step instructions are provided for most topics. Where applicable, instructions are also provided on how to perform a task or use a feature using IBConsole, the InterBase user interface. The following table displays each user guide and a description of the topics covered.
InterBase XE3 Update 2 contains the following new feature and a significant change to an existing feature. Click here for a list of resolved defects.
InterBase XE3 Update 1 contains the following new feature. Click here for a list of resolved defects.
Listed below are the new features from previous releases:
InterBase XE3 Update 2 introduces InterBase with a new mobile platform (iOS).Both ToGo and IBLite are available for the iOS platform via RAD Studio XE4. The databases deployed on the mobile device can either have centralized user authentication via admin.ib, or use EUA to control user access within the database. VARs and OEMs can now use InterBase on mobile devices by linking their applications with InterBase ToGo.
Applications built for iOS can embed InterBase ToGo with Lite licensing. IBLite is a "free" deployment. This application has limited capabilities when compared to the InterBase ToGo Edition on the same platforms. However, for a fee, you can upgrade IBLite to the InterBase ToGo edition that has additional features.
For more information please refer to the ToGoQuickStart.pdf.
There is a change in behavior in the GLOBAL TEMPORARY TABLE Support with the InterBase XE3 Update 2 release. When an SQL script is executed ISQL reported a "deadlock" if EXIT is called without COMMIT/ROLLBACK on a global temporary table. To resolve this issue, the GLOBAL TEMPORARY TABLES function has been redesigned which changes the behavior and corrects the deadlock error.
It is no longer possible for transactions emanating from the same connection to see each other's rows in a transaction-specific (ON COMMIT DELETE) temporary table. To do that, you must use a session-specific (ON COMMIT PRESERVE) temporary table that makes all rows visible to transactions starting in the same session. This is still not the same in that the rows will persist until the connection is finished.
If you have obtained InterBase from a third party as part of the third party application, it may contain a "call home" feature.This feature makes an automated one-time connection to the Embarcadero Product Registration System to record the following information:
The DataDirect ODBC drivers are no longer shipped with InterBase XE3. InterBase XE3 now includes a new offering of InterBase ODBC driver.
When restoring a database, once a table's data is restored, the indexes for the table can be created all at the same time. This helps the restore response time where there are idle threads/cores that facilitate the engine doing the job that much quicker by benefiting from sharing the cache for table data. 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.
This feature is useful if you have very, large databases (VLDB) with tables that are archival in nature. An archival table means that a table's rows are infrequently or never UPDATED or DELETED; have complex queries, such as aggregates and analytics that process a high percentage of rows; and where indexes are rebuilt and the database is backed and/or restored frequently. These database operations could see a performance improve of 20% or more with a savings in storage space.
By default, InterBase reserves a small amount of space in each data page of a table to optimize UPDATE and DELETE operations on resident rows. This reserve space can amount to 20% or more of the total space occupied by all of the table's rows. Some tables archive historical data or data that are UPDATED infrequently or not at all and their rows may never be deleted. Database operations that process most or all of the rows, such as backup, restore, index creation, aggregate computation have always suffered performance penalties proportional to this reservation overhead.
For this reason, a CREATE/ALTER TABLE clause is introduced that prevents space reservation and maximizes row packing for the most efficient fill ratio. At the database level, it has been possible to restore a database with the -USE_ALL_SPACE switch so that no space is reserved for any table. To change the storage behavior in a like manner for new or existing databases, the same clause is introduced for CREATE/ALTER DATABASE.
To effect the new storage behavior, a non-standard SQL clause is added:
InterBase XE3 Update 2 makes deployment on Windows, Mac OSX, Linux and iOS even easier. It allows you to copy database files between Windows 32-bit and 64-bit, MacOSX, Linux, and iOS. You can develop on one platform, create your database on your development platform, and then easily deploy your database to other supported platforms. For example, you can develop with RAD Studio on Windows 32-bit, create your databases here, and then just include these databases as is as part of your deployment for Windows 64-bit, Mac OSX, and iOS.
There are some restrictions and you may not use certain features. If you want to take advantage of these features, they are listed below. This copying feature is certified for a single-file database that does not have the following; UDF or Filters, External Tables, Journaling, Online, and Incremental dumps, and Shadowing.
As in the previous versions of InterBase you can use the GBAK switch -transportable as a "safe" transportable mechanism. This switch permits the database to be restored to another platform supported by InterBase. It is cross-endian compatible, so using gbak -t allows you to move databases between Windows, MacOSX, Linux, and iOS to Solaris. For more information please refer to the Operations Guide.pdf.General Guidelines for Using GBAK (page 8-4).
Note: It is important to note that database files cannot be directly moved from Little Endian to Big Endian platforms and vice-versa.
The features listed below may require additional attention This usually involves a platforms' peculiar pathname conventions, for example. pathname separator, pathname sensitivity, etc.
It is not possible to certify all the different ways of copying an InterBase multifile DB configuration. So you have to be aware of issues for your particular environment. An option is to simplify to a single DB file and then configure for multifile operation at deployment time. For more information please refer to the Operations Guide.pdf., Initiating Multi- and Single-file Backups (page 8-5ff).
The InterBase XE Update 4 patch installer provides an ibconfig.orig file. This file includes the new IBCONFIG parameter of "MAX_DB_VIRMEM_USE" This parameter controls the limit of Virtual Memory use, after which database cache allocations and expansions are disallowed. The default is 90 (however, if database cache allocation requests come in before we use 90% of Virtual Memory for the process. it is allowed).
If you have not made any changes to your default "ibconfig" from a previous installation, you can copy ibconfig.orig over ibconfig and restart the server. For further details, see the parameter definition and comments in "ibconfig" file.
RAD Studio now has 64-bit drivers for dbExpress (with RAD Studio XE2) which means you can use the 64-bit Ado.NET driver. The existing 32-bit installer of Ado.NET driver has been modified and incorporates the 64-bit assemblies into the same installer. This installer is intelligent enough to install the required assemblies on the target OS platform (32-bit assemblies for 32-bit OS, 32 and 64 assemblies on 64-bit OS).
For more information, please refer to ADO.NET Provider for InterBase (32-bit and 64-bit) in the Developer's Guide.pdf.
If you are using OTW SSL TCP/IP connection:
Starting with InterBase XE Update 3 release you no longer need to specify your server hostname by the IBSSL_SERVER_HOST_NAME parameter in /secure/server/ibss_config file. The new ibss_config default file included with this release has this parameter commented out, so this can be used in most cases. When the IBSSL_SERVER_HOST_NAME parameter is not set, the InterBase server will use the same hostname as used by the non-SSL TCP/IP connection.
If you do specify the hostname in the ibss_config file, you must ensure that the hostname resolves to IPv4 TCP/IP address and not a IPv6 address. One way to force a IPv4 resolution is to include your hostname in the hosts file with a valid IPv4 address. On Windows your hostname should be in the <Windows system file>\drivers\etc\hosts file.
Backup/Restore operations can now be restricted on any encrypted database. For more information, please refer to Encrypting Your Data, page 13-20 in the Data Definition Guide.pdf.
InterBase XE Update 2 has upgraded from 1.0.0a to OpenSSL 1.0.0d. For more information on using OpenSSL in InterBase XE, please reference Network Configuration in the Operations Guide.pdf, as well as Encrypting your Data in the Data Definition.pdf. For additional information on OpenSSL please refer to: http://openssl.org/
InterBase XE Update 2 enables the "direct I/O" functionality on Windows OS. This was implemented to circumvent the issue observed by many on Windows 2008 R2 and Windows 7 64-bit OS editions where System File Cache uses up too much physical memory leading to sluggish system performance. For more information, please refer to InterBase Direct I/O for DataBase Files on page 12-15 in the Operations Guide.pdf.
For both the 32-bit and 64-bit editions, if you are still using dialect 1, you must migrate to dialect 3. Performance monitor tables will not work in the counters and will generate errors. For more information please reference Understanding SQL dialects in the Operations Guide.pdf..
The ib_install.exe is delivered with the 32-bit Edition issue. You will need to run this installer if you want to to use the 32-bit Edition on Windows.
The topics below cover the critical information you need to implement the 64-bit application.
The following table displays the new client library names for the 64-bit DLL's.
New interfaces have been implemented for JDBC. For more information on Blob, Clob, and other related API Interfaces, please refer to Programming with JDBC, page 4-41 in the Developer's Guide.pdf
Stronger password protection on InterBase databases can be implemented with InterBase XE. This additional functionality supports a longer effective password length, resulting in stronger password protection. For more information, please refer to Implementing Stronger Password Protection on page 6-4 in the Operations Guide.pdf
There is now a larger database cache setting for 64-bit InterBase. The limit for the 64-bit engine is 75 million pages, as compared to 750K pages for 32-bit engines. For more information, please refer to Various InterBase Limits on page B-2 in the Operation's Guide.pdf
InterBase XE now provides support for the EXECUTE STATEMENT functionality. This feature enhances the InterBase Stored Procedure language. Once this is implemented, 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 or data returned; One row of data returned; and Variable number of rows returned. For more information, please refer to EXECUTE STATEMENT on page 3-8 in the Language Reference Guide.pdf.
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 system performance and users disabled the automatic database sweep function because of the impact on product operations. With the implementation of the fast sweep optimization in InterBase XE, the memory allocation issue has been mitigated. The user has the option to configure their databases for automatic sweep. For more information, please refer to Sweep Interval and Automated Housekeeping on page 7-22 in the Operation Guide.pdf.
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. The blocking factor values for individual tables can be observed in the system columns:
For more information, please refer to Table 6.26 - RDB$RELATIONS on page 6-30 in the Language Reference Guide.pdf.
With the ODS 15 databases the maximum index key size limit is increased. Now larger column data can use this for both single-byte character sets and multi-byte (such as UTF8) columns. For more information, please refer to Various InterBase Limits, Table B.1 on page B-4 in the Operation Guide.pdf.
InterBase XE3 introduces changes for better Windows compatibility. InterBase XE3 allows default installation of the product under the "Program Files" location and no longer uses the "Borland" subkey in the Windows Registry hierarchy. It now uses the "Embarcadero" subkey to track instance specific information.
We strongly advise and encourage you to migrate your applications to the new environment as soon as possible.
Program Files Install Location
InterBase versions since 2009 installed the product in a different location other than the default Windows location of "Program Files". This was because of Windows UAC guidelines that put restrictions around applications without administrative privilege from writing under the "Program Files" file system folder. InterBase XE3, for server-based editions, now conforms to the Windows Application compatibility guidelines. The default install location has changed from C:\Embarcadero\InterBase to C:\Program Files\Embarcadero\InterBase.
Program Data Location
InterBase XE3 now delivers the program data files under %ALLUSERSPROFILE%\Embarcadero\InterBase directory. For each individual instance of an install, a folder is created under this and all files requiring write access are delivered here. For example if you install InterBase XE3 with the defaults, and get 'gds_db' instance, your InterBase write location and files include the following.
New IBCONFIG Parameter APPDATA_DIRECTORY
To indicate the above "Program Data" location value to all InterBase applications (and database engine), a new IBCONFIG parameter value for APPDATA_DIRECTORY (new parameter in InterBase XE3) is now added automatically at the time of install. This value provides the absolute path of the above "Program Data" location. See the file <interbase>/ibconfig file for further details.
Program Files Location
The "Program Files" location will be used primarily for files which are required read only.
Windows Registry Changes from Borland to Embarcadero
InterBase uses the Windows Registry to store information for a particular installation of InterBase server-based products. Due to its Borland legacy, InterBase versions XE and before had stored this information under the Windows Registry key "HKEY_LOCAL_MACHINE\Software\Borland\InterBase\Servers\".
InterBase XE3 now uses the Windows Registry hierarchy under "HKEY_LOCAL_MACHINE\Software\Embarcadero\InterBase\Servers\". The InterBase server, guardian, client library, IBMgr, and IBConsole programs have been updated to support this change.
If you have applications using older InterBase version client libraries on the same machine and must connect locally to the new server, update your client library to the latest version.
If you cannot do this and still need the old client libraries to access the "Borland" hierarchy registry settings, use the InterBase tool "instreg.exe". instreg now provides a "legacy" option to create the "Borland" hierarchy entries for your new instance so your application continues to work with the new InterBase server.
instreg has the following usage:
instreg install "C:\Program Files\Embarcadero\InterBase" instance gds_db legacy
instreg display instance gds_db
You can also use the Windows command "reg" to query for specific Windows registry key values, and thus verify this change.
REG QUERY HKLM\Software\Borland\Interbase\Servers /s
When restoring a database with InterBase XE, get the error unassigned code. With databases with a long lineage or databases backed up with InterBase 2009 and restored with InterBase XE, each case has different metadata security settings. So when selecting a system table (for example: RDB$RELATIONS) you get the error message: no permission for read/select access to table RDB$RELATIONS by user SYSDBA.
This error occurs with databases which have these criteria:
This behavior is exhibited due to stricter enforcement of meta data rights in InterBase XE during the restore of a database.
To resolve this problem execute readmeta.sql against your database before you back it up. readmeta.sql may be found in \examples\security. You can execute readmeta.sql against your database using isql or IBConsole.
The problem occurs with databases that have a long lineage. The two cases are (1) a database has a restore history of IB6->IB7->IB2007->IB2009->IBXE; and (2) a database backed up and restored as IB2009->IBXE. Each case has different metadata security settings. The first instance never had metadata security because it originated from IB6. However with the second instance, it was created (not restored) by IB2009 with a full complement of security privileges for all system tables.
With the first case, the database was backed up and restored and with each succeeding release, the new release would install privileges for the new system tables in that release (think RDB$USERS, RDB$ENCRYPTIONS, RDB$ROLES, etc.). But it couldn't alter the original system tables because it had no way of knowing if the database owner had already altered their security privileges. For example, a user might have revoked all privileges to RDB$TRIGGERS and RDB$PROCEDURES to conceal their trigger and stored procedure code.
Also, in the first case, a SYSDBA may have run readmeta.sql years ago and refined the metadata from that baseline to a custom security profile. InterBase cannot override that customization by automatically resetting it after the XE restore. InterBase XE can't assume that every database it restores should unconditionally install the default metadata privileges because it doesn't know the history of individual databases.
So it is recommended to run readmeta.sql, which sets the default or starting point for configuring it the way you want it. This advice is independent of whether you are migrating to XE.
Example using isql:
isql "path to database"
-user sysdba -password masterkey -i readmeta.sql
Executing readmeta.sql with IBConsole
Databases originally created before InterBase 6.5 may have the error: no permission for read/select access to table RDB$XXXX by user SYSDBA with InterBase XE.
InterBase XE enforces tighter meta data security and this error may result from doing meta data operations on databases originally created with versions of InterBase prior to version 6.5. Meta data operations involve requesting information about system objects such as listing system objects or updating them and using the Performance Monitor.
To resolve this error you need to perform two similar operations. The first operation grants rights to system tables. To do so, execute readmeta.sql from the examples\security folder in your InterBase install directory.You can execute readmeta.sql against your database using isql or IBConsole.
Second, you need to grant rights to system temorary tables if you are going to do performance monitoring. Due to potential security concerns, most installations will want to grant rights for system temporary tables only to sysdba and the database owner, which is what is presented below. If you wish for all users to be able to view system temporary tables, modify this example to GRANT TO PUBLIC. Some installations will want only specific users to have rights, in which case a more customized script may be needed.
To grant rights for system temporary tables, save the following as a text file, then execute it the same as readmeta.sql above.
create procedure granttmp as
declare variable stmt varchar(1024);
declare variable ownername varchar(66);
declare variable tablename varchar(66);
select rdb$owner_name from rdb$relations where rdb$relation_name = 'RDB$RELATIONS'
for select rdb$relation_name from rdb$relations where rdb$system_flag>0 and rdb$relation_name starts with 'TMP$' into :tablename do
stmt = 'grant all on ' || tablename || ' to sysdba';
execute statement stmt;
stmt = 'grant all on ' || tablename || ' to ' || ownername;
execute statement stmt;
execute procedure granttmp;
drop procedure granttemp;
The 16-bit UNICODE character sets UNICODE_LE and UNICODE_BE only work for Server character sets. These character sets cannot be used as a client character set. If your client needs full UNICODE character support, please use UTF8 instead of UNICODE_LE and UNICODE_BE for the client character set (aka LC_CSET). A client can use the UTF8 (or other native) client character set to connect with a UNICODE database.
InterBase XE3 supports no defined UNICODE collations in this release. The default collation is binary sort order for UNICODE.
Windows Error Reporting (WER) dialog pops up intermittently if and when an InterBase server crashes.
Resolution: We are working on fixing any crashes that we are aware of. In the meantime you can disable the Windows Error Reporting dialog from popping up by modifying the Windows Registry thus. Set the registry attribute HKEY_CURRENT_USERS\Software\Microsoft\Windows\Windows Error Reporting\DontShowUI value to 1 to disable. This is as per recommendation of MSDN article http://msdn.microsoft.com/en-us/library/bb513638(VS.85).aspx. We may address this configurable option from within ibserver.exe in future builds by informing WER to disable this only for InterBase server binary.
The following table lists all resolved defects for InterBase.
This issue lists all IBConsole-related fixes in this update.
QC: 97534 When providing the password for a column encryption in Interactive SQL, an Access violation error message shows up.
QC: 97480 When altering a column to decrypt it, an error message shows up, but the column is decrypted.
QC: 96412 When starting the server from IBConsole, you receive an error message saying that the server can't start, even though the server is actually running; when connecting to a database, you receive an AV error message.
QC: 89388 When using the "New Connection" option from the Tools menu in the IBConsole, an AV error message shows up.
QC: 67161 UTF8 is missing in combo box for default character set.
QC: 97683 The memo dialog box that opens when you see the value for the RDB$DESCRIPTION field should not be read-only.
GBAK fails if database file path contains spaces AND using InterBase Services.
If you are providing file path names with embedded spaces in them and using the InterBase service manager (-service switch in GBAK), you will need to multi-quote the file names:
The above is required because the command shell strips away the external double_quotes and only leaves the internal single_quotes for InterBase to know that it is a single string value.
# gbak service service_mgr r "'/path/with space/foo.ibk'" "'/path/with space/foo.ib'" user sysdba password masterkey
# gbak service service_mgr r "'/path/with space/foo.ibk'" "'/path/with space/foo.ib'" user sysdba password masterkey
If you have a valid maintenance contract with Embarcadero Technologies, the Embarcadero Technical Support team is available to assist you with any problems you have with our applications. Our maintenance contract also entitles registered users of Embarcadero Technologies products to download free software upgrades during the active contract period. Evaluators receive free technical support for the term of their evaluation. We encourage you to visit the Support section of our Web site.
Evaluators receive free technical support for the term of their evaluation. To download evaluations of Embarcadero Technologies products or to learn more about our company and our products visit us at http://www.embarcadero.com/.
Copyright 2013 Embarcadero Technologies Inc. All CodeGear brand and product names are trademarks or registered trademarks of Embarcadero Technologies in the United States and other countries. All other marks are the property of their respective owners.
Could not retrieve comments. Please try again later.
Download Delphi 10 now!
Webinars on demand!
More social media choices:
Delphi on Google+
@RADTools on Twitter
Server Response from: ETNASC01